About the author

SQL stands for Structured Query language , also pronounced as ‘Sequel’. Itis the language for communication between user and database. The various SQL databases are Oracle, MS SQL server, MySQL etc.

Sublanguages in SQL  :

• Data Definition Language (DDL)
• Data Manipulation language (DML)
• Data Query Language (DQL)
• Data Control Language (DCL)
• Transaction Control language (TCL)

Importing Data from File:

Syntax :

COPY {table_name} (col1, col2, col3 , ……colN)

FROM “ path / location of file “ DELIMITER ‘ , ‘ CSV HEADER ;

OR

BULK INSERT Orders

FROM  ‘ C:\Users\aparna\Downloads\orders.csv ‘   —dummy file path / location

WITH

(

    FIRSTROW = 2,                         —  as the 1st row is header

    FIELDTERMINATOR = ‘ , ‘ ,         — delimiter

    ROWTERMINATOR = ‘ \n ‘ ,            –Used to shift the control to next row

    TABLOCK

)

OR

COPY {table_name} from “ location of file / path “ delimiter ‘ , ‘ ;

DDL Commands and Uses :

• CREATE : It is used to create a table and define the columns and their data types.

CREATE TABLE { TABLE NAME }

( column1 data type,

column2 data type ,

column3 data type ,

…… columnN data type) ;

Example :

•   ALTER : It is used to modify the database table.

• DROP : Drops the table permanently from the database.

DROP TABLE {table_name};

• TRUNCATE : Removes the data and not the structure of the table.

TRUNCATE TABLE {table_name} ;

DML Commands and Uses :

• INSERT : INSERT INTO statement is often used to add new records into the existing table.

INSERT INTO { TABLE NAME }

( column1 , column2, ……) VALUES ( value1, value2,……) ;

Example :

INSERT INTO student ( column1, column2 ) VALUES ( value1, value2 );

• UPDATE :  Used to modify the existing records in a table.

UPDATE { TABLE NAME }

SET column1 = ‘value1’,

column2 =  ‘value2’,

……..

WHERE { condition } ;

Example :

• DELETE :  Used to delete the existing records from a table.

DELETE FROM { TABLE NAME } WHERE { condition } ;

    DELETE FROM student WHERE student_id = 6;

DQL Commands and Uses :

• SELECT : Used to fetch data from a database table.

    SELECT * from { TABLE NAME } WHERE { condition };

1. SELECT student_id ,first_name, std from student ;

2. SELECT student_id from student WHERE Class = ‘VII’ ;

3. SELECT cust_id, name , address from customer where age > 40 ;

• SELECT DISTINCT : The DISTINCT keyword is used with SELECT to eliminate all the duplicate records and fetch only the unique ones.

     SELECT DISTINCT { column name } FROM {TABLE NAME };

1. SELECT DISTINCT address  FROM customer ;

2. SELECT DISTINCT first_name FROM student ;

DCL Commands and Uses :

• GRANT : Used to provide any user access or privileges for the database.

GRANT CREATE ANY TABLE TO Username ;

GRANT DROP ANY TABLE TO Username ;

• REVOKE :  Used to take back privileges / access for the database.

REVOKE CREATE ANY TABLE FROM Username ;

REVOKE DROP ANY TABLE FROM Username;

TCL Commands and Uses :

• COMMIT – It is used to make the transaction permanent.

UPDATE { TABLE NAME } SET column1 = ‘value1’ WHERE { condition };

COMMIT ;

1. UPDATE student SET Name = ‘XYZ’ WHERE Name = ‘ABC’ ;

COMMIT;

2. UPDATE orders SET order_date  = ‘2020-09-18’’

WHERE order_id = 15 ;

COMMIT;

• ROLLBACK – It is used so that the database can be restored to the state when it was last committed or to the state when last changes were made to the database.

ROLLBACK TO savept_name;

• SAVEPOINT – This command is used to save a transaction temporarily so that we can rollback to that point whenever we need to.

     SAVEPOINT savepoint_name ;

HOW TO USE COMMIT,  ROLLBACK AND SAVEPOINT ?

1. Create a table and insert records into it.

2. To use the TCL commands in SQL, we need to first initiate a transaction by using the BEGIN / START TRANSACTION command.

BEGIN TRANSACTION ;

3. Updating the table and using COMMIT –

UPDATE student SET age = 14 WHERE student_id = 4 ;

  COMMIT;

Using COMMIT makes sure that the command will be saved successfully.

The output after COMMIT will give us a table with saved changes.

4. Using Rollback – ROLLBACK;

DELETE FROM ORDER WHERE order_id = 17;

ROLLBACK;

The above command ensures that the changes made by DELETE command are reverted back successfully.

5. Using SAVEPOINT – We can create as many SAVEPOINTs as we want after doing some changes to the table.

    SAVEPOINT {savepoint_name};

UPDATE orders SET amount = 12900 WHERE order_id = 15;

SAVEPOINT upd;

DELETE FROM orders WHERE order_id = 12;

SAVEPOINT del;

6. Now if we want to go back / revert back to the table before DELETE OR UPDATE COMMAND we can ROLLBACK TO upd / del, and if we want to roll back to the table when records were just inserted then we can ROLLBACK TO A.

ROLLBACK TO upd;

ROLLBACK TO save_upd;

select * from orders;

When we run these commands we get the records before any of the changes were caused.

And these commands will give back the table as they were before any changes

Aggregation functions in SQL –

Aggregate functions are called only in the SELECT / HAVING clause.

• AVG( ) – Returns floating point value.

SELECT AVG (column_name) FROM TABLE;

• COUNT( ) – Returns the number of rows which is an integer value.

1. SELECT COUNT(*) FROM TABLE;

This query counts and returns the number of records in the table

2. SELECT COUNT( * ) FROM TABLE WHERE { condition };

• MAX( ) – Returns the maximum value.

SELECT MAX (column_name) FROM TABLE;

• MIN( ) – Returns the minimum value.

     SELECT MIN (column_name) FROM TABLE;

• SUM( ) – Returns the total sum.

SELECT SUM (column_name) FROM TABLE;

• ROUND( ) – It can be used to specify precision after decimal.

SELECT ROUND(AVG ( column_name ),3 ) FROM TABLE;

Use of ORDER BY :

It is used to sort rows based on a specific column either in ascending / descending order.

Syntax :

1. SELECT Column1, Column2 FROM TABLE ORDER BY column1 ASC / DESC ;

2. SELECT column1 , column2 WHERE { condition } ORDER BY;

Use of LIMIT :

It allows us to limit the number of rows returned for a query.

Syntax :

SELECT * FROM TABLE

ORDER BY column_name DESC/ ASC

LIMIT 10 ;

Use of BETWEEN  Operator:

It can be used to get a range of values.

Syntax :

1. Value BETWEEN LOW and  HIGH
2. Value NOT BETWEEN LOW and HIGH.
3. Value BETWEEN ‘YYYY-MM-DD’ AND ‘YYYY-MM-DD’

SELECT COUNT(*) FROM orders WHERE amount BETWEEN 800 AND 2000;

This statement will return the number of orders between 800 and 2000.

So we need to provide a range for BETWEEN clauses.

Use of IN Operator :

It checks if the value is included in the list of options provided.

Syntax :

1. SELECT * FROM TABLE WHERE column_name IN (option1, option2, option3,…..) ;

2. SELECT * FROM orders WHERE amount IN (200, 800, 9000) ;

3. SELECT * FROM orders WHERE order_id NOT IN (12,13,14) ;

This way the IN / NOT IN Operator can be used to fetch the required records from the table, it acts as a filter to get only those records which you entered into the list of options.

Use of LIKE Operator :

It is helpful in pattern matching against string data with the use of wildcard characters.

LIKE is case – sensitive.

1. I want to get those data from the table where all names begin with ‘D’.

SELECT * FROM student WHERE first_name LIKE ‘D%’  ;

2. All names that end with ‘a’.

SELECT * FROM orders WHERE first_name LIKE “%a” ;

3. All names with ‘on’  in it.

SELECT * FROM student WHERE first_name LIKE “_on%” ;

4. All email ids with ”ar” in the middle.

SELECT * FROM Customer WHERE first_name LIKE ‘%ar%’ ;

This way we can use LIKE to find / match patterns.

Use of Group By Clause:

Group By allows us to aggregate the columns according to some category. The GROUP BY

Clause must appear right after a FROM or WHERE.

Syntax :

SELECT column1, column2, SUM( column3)

FROM column4

WHERE column2 IN (option1 , option2)

GROUP BY column1, column2 ;

SELECT COUNT(cust_id) , address

FROM customer

GROUP BY address;

Use of HAVING clause :

It allows us to use the aggregate result as a filter along the GROUP BY.

Syntax :

SELECT COUNT(cust_id), address

FROM customer

GROUP BY address

HAVING COUNT(cust_id) > 4;

Use of AS :

‘AS’ keyword is used to assign alias names to a column or a table.

Syntax :

SELECT { column_name } AS { column _alias } FROM { table_name };

USE OF VIEW :

A view is a virtual table , it is not a physical table. It is created to see a set of results , because it provides an overview of the result-set.

Syntax :

CREATE { OR  REPLACE } VIEW { view_name } AS

SELECT { column1 , column2 , column3 ,….. columnN }

FROM { table_name }

WHERE { conditions } ;

Joins :

Types of Joins :

• Inner Join
• Left Join
• Right Join
• Full Join

INNER JOIN : It compares each row of table ” A “ with each row of table “ B “ to find all of the rows which satisfy the join predicate and returns back the result – record.

Syntax:

SELECT columns

FROM tableA

INNER JOIN tableB

ON tableA.required_column = tableBrequired_.column ;

LEFT JOIN : Left join returns all the records from the left table , even if there are no matched in the right table , it shows null where the records are not matched

Syntax :

SELECT tableA.column1 , tableB.column2

FROM tableA

LEFT JOIN tableB ON tableA.common_field = tableB.common_field

ORDER BY tableA.required_column , tableA.required_column  ;

RIGHT JOIN :  Returns all the records from the right table , even if there are no matches in the left table , it shows null where the records are not matched.

Syntax:

SELECT tableA.col1 , tableB.col2

FROM tableA

RIGHT JOIN tableB

ON tableA.required_column = tableB.required_column ;

FULL JOIN : Full Join combines the results of both left and right tables and returns the records.

Syntax :

SELECT tableA.col1 , tableB.col2

FROM tableA

FULL JOIN tableB

ON tableA.required_column = tableB.required_column ;