SQL Commands Tutorial – List of SQL Commands with Example

360digital - sql commands
  • SQL Commands are instructions.
  • It is used to communicate with the database. It is also used to perform specific tasks, functions, and queries of data.
  • SQL can perform various tasks like create a table, add data to tables, drop the table, modify the table, and set permission for users.

Types of SQL Command:

This image has an empty alt attribute; its file name is image.png
SQL Commands



1. Data definition language (DDL)

  • DDL changes the structure of the table like
  • creating a table, deleting a table, altering a table, etc.
  • All the command of DDL are auto-committed that means it permanently save all the changes in the database.

Here are some commands that come under DDL:

  • CREATE
  • ALTER
  • DROP
  • TRUNCATE

a. CREATE It is used to create a new table in the database.

Syntax:

CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,….]);  

Example:

SQL> CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE);  

b. DROP: It is used to delete both the structure and record stored in the table.

Syntax

DROP TABLE ;  

Example

SQL> DROP TABLE EMPLOYEE;  

c. ALTER: It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute.

Syntax:

To add a new column in the table

ALTER TABLE table_name ADD column_name COLUMN-definition;    

To modify existing column in the table:

ALTER TABLE MODIFY(COLUMN DEFINITION….);  

EXAMPLE

SQL> ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));  

SQL> ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));  

d. TRUNCATE: It is used to delete all the rows from the table and free the space containing the table.

Syntax:

TRUNCATE TABLE table_name;  

Example:

SQL> TRUNCATE TABLE EMPLOYEE;  

2. Data Manipulation Language

  • DML commands are used to modify the database. It is responsible for all form of changes in the database.
  • The command of DML is not auto-committed that means it can’t permanently save all the changes in the database. They can be rollback.

Here are some commands that come under DML:

  • INSERT
  • UPDATE
  • DELETE

a. INSERT: The INSERT statement is a SQL query. It is used to insert data into the row of a table.

Syntax:

INSERT INTO TABLE_NAME    (col1, col2, col3,…. col N)  VALUES (value1, value2, value3, …. valueN;  

Or

INSERT INTO TABLE_NAME    VALUES (value1, value2, value3, …. valueN);    

For example:

SQL> INSERT INTO Intellect (Student, App Support) VALUES (“Mandar”, “Intellect”);  

b. UPDATE: This command is used to update or modify the value of a column in the table.

Syntax:

UPDATE table_name SET [column_name1= value1,…column_nameN = valueN] [WHERE CONDITION]   

For example:

SQL> UPDATE students SET User_Name = ‘Intellect’    WHERE Student_Id = ‘3’;

c. DELETE: It is used to remove one or more row from a table.

Syntax:

DELETE FROM table_name [WHERE condition];  

For example:

SQL> DELETE FROM intellect  WHERE Student =”Mandar”;  

3. Data Control Language

DCL commands are used to grant and take back authority from any database user.

Here are some commands that come under DCL:

  • Grant
  • Revoke

a. Grant: It is used to give user access privileges to a database.

Example

SQL> GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;  

b. Revoke: It is used to take back permissions from the user.

Example

SQL> REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;  

4. Transaction Control Language

TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.

These operations are automatically committed in the database that’s why they cannot be used while creating tables or dropping them.

Here are some commands that come under TCL:

  • COMMIT
  • ROLLBACK
  • SAVEPOINT

a. Commit: Commit command is used to save all the transactions to the database.

Syntax:

COMMIT;  

Example:

SQL> DELETE FROM CUSTOMERS  WHERE AGE = 25;  

SQL> COMMIT;  

b. Rollback: Rollback command is used to undo transactions that have not already been saved to the database.

Syntax:

ROLLBACK;  

Example:

SQL> DELETE FROM CUSTOMERS  WHERE AGE = 25;  

SQL> ROLLBACK;  

c. SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire transaction.

Syntax:

SAVEPOINT SAVEPOINT_NAME;

5. Data Query Language

DQL is used to fetch the data from the database.

It uses only one command:

  • SELECT

a. SELECT: This is the same as the projection operation of relational algebra. It is used to select the attribute based on the condition described by WHERE clause.

Syntax:

SELECT expressions    FROM TABLES    WHERE conditions;  

For example:

SQL> SELECT emp_name  FROM employee  WHERE age > 20;  

SQL Clauses

sql clauses
SQL Clauses

1. GROUP BY

  • SQL GROUP BY statement is used to arrange identical data into groups. The GROUP BY statement is used with the SQL SELECT statement.
  • The GROUP BY statement follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
  • The GROUP BY statement is used with aggregation function.

Syntax

SELECT column  FROM table_name  WHERE conditions   GROUP BY column  ORDER BY column  

Sample table:

PRODUCT_MAST

PRODUCT COMPANY QTY RATE COST
Item1 Com1 2 10 20
Item2 Com2 3 25 75
Item3 Com1 2 30 60
Item4 Com3 5 10 50
Item5 Com2 2 20 40
Item6 Cpm1 3 25 75
Item7 Com1 5 30 150
Item8 Com1 3 10 30
Item9 Com2 2 25 50
Item10 Com3 4 30 120

Example:

SQL> SELECT COMPANY, COUNT(*)  FROM PRODUCT_MAST   GROUP BY COMPANY;  

Output:

Com1   5
Com2   3
Com3   2

2. HAVING

  • HAVING clause is used to specify a search condition for a group or an aggregate.
  • Having is used in a GROUP BY clause. If you are not using GROUP BY clause then you can use HAVING function like a WHERE clause.

Syntax:

SELECT column1, column2   FROM table_name  WHERE conditions   GROUP BY column1, column2  HAVING  conditions  ORDER BY column1, column2;  

Example:

SQL> SELECT COMPANY, COUNT(*)  FROM PRODUCT_MAST   GROUP BY COMPANY  HAVING COUNT(*)>2;  

Output:

Com1   5
Com2   3

3. ORDER BY

  • The ORDER BY clause sorts the result-set in ascending or descending order.
  • It sorts the records in ascending order by default. DESC keyword is used to sort the records in descending order.

Syntax:

SELECT column1, column2  FROM table_name  WHERE condition  ORDER BY column1, column2… ASC|DESC;  

Where

ASC: It is used to sort the result set in ascending order by expression.

DESC: It sorts the result set in descending order by expression.

Example: Sorting Results in Ascending Order

Table:

CUSTOMER

CUSTOMER_ID NAME ADDRESS
12 Kathrin US
23 David Bangkok
34 Alina Dubai
45 John UK
56 Harry US

Enter the following SQL statement:

SQL> SELECT * FROM CUSTOMER ORDER BY NAME;  

Output:

CUSTOMER_ID NAME ADDRESS
34 Alina Dubai
23 David Bangkok
56 Harry US
45 John UK
12 Kathrin US

Example: Sorting Results in Descending Order

Using the above CUSTOMER table

SQL> SELECT * FROM CUSTOMER ORDER BY NAME DESC;  

Output:

CUSTOMER_ID NAME ADDRESS
12 Kathrin US
45 John UK
56 Harry US
23 David Bangkok
34 Alina Dubai

SQL JOIN

As the name shows, JOIN means to combine something. In case of SQL, JOIN means “to combine two or more tables“. In SQL, JOIN clause is used to combine the records from two or more tables in a database.

Types of SQL JOIN

  1. INNER JOIN
  2. LEFT JOIN
  3. RIGHT JOIN
  4. FULL JOIN

Sample Table

EMPLOYEE

EMP_ID EMP_NAME CITY SALARY AGE
1 Angelina Chicago 200000 30
2 Robert Austin 300000 26
3 Christian Denver 100000 42
4 Kristen Washington 500000 29
5 Russell Los angels 200000 36
6 Marry Canada 600000 48

PROJECT

PROJECT_NO EMP_ID DEPARTMENT
101 1 Testing
102 2 Development
103 3 Designing
104 4 Development

1. INNER JOIN

In SQL, INNER JOIN selects records that have matching values in both tables as long as the condition is satisfied. It returns the combination of all rows from both the tables where the condition satisfies.

Syntax

SELECT table1.column1, table1.column2, table2.column1,….  FROM table1   INNER JOIN table2 ON table1.matching_column = table2.matching_column;  

Query

SQL> SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT   FROM EMPLOYEE  INNER JOIN PROJECT  

ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;  

Output

EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development

2. LEFT JOIN

The SQL left join returns all the values from left table and the matching values from the right table. If there is no matching join value, it will return NULL.

Syntax

SELECT table1.column1, table1.column2, table2.column1,….  FROM table1   LEFT JOIN table2  

ON table1.matching_column = table2.matching_column;  

Query

SQL> SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT   FROM EMPLOYEE  LEFT JOIN PROJECT  

ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;  

Output

EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development
Russell NULL
Marry NULL

3. RIGHT JOIN

In SQL, RIGHT JOIN returns all the values from the values from the rows of right table and the matched values from the left table. If there is no matching in both tables, it will return NULL.

Syntax

SELECT table1.column1, table1.column2, table2.column1,….  FROM table1   RIGHT JOIN table2  

ON table1.matching_column = table2.matching_column;  

Query

SQL> SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT   FROM EMPLOYEE  RIGHT JOIN PROJECT  

ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;  

Output

EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development

4. FULL JOIN

In SQL, FULL JOIN is the result of a combination of both left and right outer join. Join tables have all the records from both tables. It puts NULL on the place of matches not found.

Syntax

SELECT table1.column1, table1.column2, table2.column1,….  FROM table1   FULL JOIN table2  

ON table1.matching_column = table2.matching_column;  

Query

SQL> SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT   FROM EMPLOYEE  FULL JOIN PROJECT   

ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;  

Output

EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development
Russell NULL
Marry NULL

1. GROUP BY

  • SQL GROUP BY statement is used to arrange identical data into groups. The GROUP BY statement is used with the SQL SELECT statement.
  • The GROUP BY statement follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
  • The GROUP BY statement is used with aggregation function.

Syntax

SELECT column  FROM table_name  WHERE conditions   GROUP BY column  ORDER BY column  

Sample table:

PRODUCT_MAST

PRODUCT COMPANY QTY RATE COST
Item1 Com1 2 10 20
Item2 Com2 3 25 75
Item3 Com1 2 30 60
Item4 Com3 5 10 50
Item5 Com2 2 20 40
Item6 Cpm1 3 25 75
Item7 Com1 5 30 150
Item8 Com1 3 10 30
Item9 Com2 2 25 50
Item10 Com3 4 30 120

Example:

SQL> SELECT COMPANY, COUNT(*)  FROM PRODUCT_MAST   GROUP BY COMPANY;  

Output:

Com1   5
Com2   3
Com3   2

2. HAVING

  • HAVING clause is used to specify a search condition for a group or an aggregate.
  • Having is used in a GROUP BY clause. If you are not using GROUP BY clause then you can use HAVING function like a WHERE clause.

Syntax:

SELECT column1, column2   FROM table_name  WHERE conditions   GROUP BY column1, column2  HAVING  conditions  ORDER BY column1, column2;  

Example:

SQL> SELECT COMPANY, COUNT(*)  FROM PRODUCT_MAST   GROUP BY COMPANY  HAVING COUNT(*)>2;  

Output:

Com1   5
Com2   3

3. ORDER BY

  • The ORDER BY clause sorts the result-set in ascending or descending order.
  • It sorts the records in ascending order by default. DESC keyword is used to sort the records in descending order.

Syntax:

SELECT column1, column2  FROM table_name  WHERE condition  ORDER BY column1, column2… ASC|DESC;  

Where

ASC: It is used to sort the result set in ascending order by expression.

DESC: It sorts the result set in descending order by expression.

Example: Sorting Results in Ascending Order

Table:

CUSTOMER

CUSTOMER_ID NAME ADDRESS
12 Kathrin US
23 David Bangkok
34 Alina Dubai
45 John UK
56 Harry US

Enter the following SQL statement:

SQL> SELECT * FROM CUSTOMER ORDER BY NAME;  

Output:

CUSTOMER_ID NAME ADDRESS
34 Alina Dubai
23 David Bangkok
56 Harry US
45 John UK
12 Kathrin US

Example: Sorting Results in Descending Order

Using the above CUSTOMER table

SQL> SELECT * FROM CUSTOMER ORDER BY NAME DESC;  

Output:

CUSTOMER_ID NAME ADDRESS
12 Kathrin US
45 John UK
56 Harry US
23 David Bangkok
34 Alina Dubai

SQL JOIN

As the name shows, JOIN means to combine something. In case of SQL, JOIN means “to combine two or more tables“. In SQL, JOIN clause is used to combine the records from two or more tables in a database.

Types of SQL JOIN

  1. INNER JOIN
  2. LEFT JOIN
  3. RIGHT JOIN
  4. FULL JOIN

Sample Table

EMPLOYEE

EMP_ID EMP_NAME CITY SALARY AGE
1 Angelina Chicago 200000 30
2 Robert Austin 300000 26
3 Christian Denver 100000 42
4 Kristen Washington 500000 29
5 Russell Los angels 200000 36
6 Marry Canada 600000 48

PROJECT

PROJECT_NO EMP_ID DEPARTMENT
101 1 Testing
102 2 Development
103 3 Designing
104 4 Development

1. INNER JOIN

In SQL, INNER JOIN selects records that have matching values in both tables as long as the condition is satisfied. It returns the combination of all rows from both the tables where the condition satisfies.

Syntax

SELECT table1.column1, table1.column2, table2.column1,….  FROM table1   INNER JOIN table2 ON table1.matching_column = table2.matching_column;  

Query

SQL> SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT   FROM EMPLOYEE  INNER JOIN PROJECT  

ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;  

Output

EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development

2. LEFT JOIN

The SQL left join returns all the values from left table and the matching values from the right table. If there is no matching join value, it will return NULL.

Syntax

SELECT table1.column1, table1.column2, table2.column1,….  FROM table1   LEFT JOIN table2  

ON table1.matching_column = table2.matching_column;  

Query

SQL> SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT   FROM EMPLOYEE  LEFT JOIN PROJECT  

ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;  

Output

EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development
Russell NULL
Marry NULL

3. RIGHT JOIN

In SQL, RIGHT JOIN returns all the values from the values from the rows of right table and the matched values from the left table. If there is no matching in both tables, it will return NULL.

Syntax

SELECT table1.column1, table1.column2, table2.column1,….  FROM table1   RIGHT JOIN table2  

ON table1.matching_column = table2.matching_column;  

Query

SQL> SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT   FROM EMPLOYEE  RIGHT JOIN PROJECT  

ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;  

Output

EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development

4. FULL JOIN

In SQL, FULL JOIN is the result of a combination of both left and right outer join. Join tables have all the records from both tables. It puts NULL on the place of matches not found.

Syntax

SELECT table1.column1, table1.column2, table2.column1,….  FROM table1   FULL JOIN table2  

ON table1.matching_column = table2.matching_column;  

Query

SQL> SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT   FROM EMPLOYEE  FULL JOIN PROJECT   

ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;  

Output

EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development
Russell NULL
Marry NULL





1

Article Tags:
Article Categories:
Technology
Likes:
1

Leave a Comment

Your email address will not be published. Required fields are marked *