What are SQL commands

There are five types of SQL commands:

1.DDL(Data definition language)

2.DML(Data Manipulation Language)

3.DCL(Data Control Language)

4.TCL(Transaction Control Language)

5.DQL(Data Query Language)

1.DDL(Data definition language)-

DDL use for creating a table, deleting a table, altering a table from SQL database.

DDL commands:

(i)Create

(ii)Alter

(iii)Drop

(iv)Truncate

(i) CREATE-This command is used to create a new table in the database.

Syntax:

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

Example:

CREATE TABLE Employee_master(Name VARCHAR2(10), Email VARCHAR2(70), DOB DATE);

(ii) DROP: This command is used to delete structure and record stored in the table.

Syntax

• DROP TABLE table_name;  

Example

• DROP TABLE SCOOL;  

(iii) ALTER:This command is used to alter the structure of the database.

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 table_name MODIFY(column_definitions..);  

EXAMPLE

1. ALTER TABLE SCHOOL ADD(ADDRESS VARCHAR2(10));  
2. ALTER TABLE SCHOOL MODIFY (NAME VARCHAR2(10));  

(iv) TRUNCATE: This command is used for delete all the rows from the table.

Syntax:

• TRUNCATE TABLE table_name;  

Example:

• TRUNCATE TABLE EMPLOYEE;  

2.DCL(Data Control Language)

DCL includes commands like GRANT and REVOKE, which are useful to give rights & permissions to database.

Examples of DCL commands:

Commands 2 types under DCL:

• Grant
• Revoke

Grant:

This command is use to give user access privileges to a database.

Syntax:

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;  

For example:

GRANT SELECT ON Users TO'Man'@'localhost;

Revoke:

It is useful to back permissions from the user.

Syntax:

REVOKE privilege_nameON object_nameFROM {user_name |PUBLIC |role_name}

For example:

REVOKE SELECT, UPDATE ON student FROM BCA, MCA;  

3.DML(Data Manipulation Language)

Below important DML commands in SQL:

• INSERT
• UPDATE
• DELETE

INSERT:

This is a statement is a SQL query. This command 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:

INSERT INTO students (RollNo, FIrstName, LastName) VALUES ('60', 'Tom', Erichsen');

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:

UPDATE students    
SET FirstName = 'Jhon', LastName= 'Wick' 
WHERE StudID = 3;

DELETE:

This command is used to remove one or more rows from a table.

4.TCL(Transaction Control Language)

Commit

This command is used to save all the transactions to the database.

Syntax:

Commit;

For example:

DELETE FROM Students  
WHERE RollNo =25;  
COMMIT;  

Rollback

Rollback command allows you to undo transactions that have not already been saved to the database.

Syntax:

ROLLBACK;  

Example:

DELETE FROM Students  
WHERE RollNo =25;  

SAVEPOINT

This command helps you to sets a savepoint within a transaction.

Syntax:

SAVEPOINT SAVEPOINT_NAME;

Example:

SAVEPOINT RollNo;

5.DQL(Data Query Language)

Data Query Language (DQL) is used to fetch the data from the database. It uses only one command which is select *:

SELECT:

This command helps you to select the attribute based on the condition described by the WHERE clause.

Syntax:

SELECT expressions    
FROM TABLES    
WHERE conditions;  

For example:

SELECT FirstName  
FROM Employee_master  
WHERE IDNo > 20;