DML, DCL and TCL commands in SQL database.pptx

These slides contain the Data Manipulation Language (DML) commands, Data Control Language(DCL) Commands and Transaction Control Language (TCL)commands used in SQL. Using DML commands, the user can insert, delete, or update the data stored in a database. DCL commands are used to create users in the database. TCL commands are used to control the transactions in the database.

1. DQL commands It consists of only 1 command i.e. SELECT command. SELECT it is used to retrieve all the records or particular record from table. Syntax : SELECT * from < table_name >; This will retrieve all the records from table. ii. SELECT * from < table_name > WHERE condition; This will retrieve particular records from table, according to specified condition. Mrs. Pallavi Patil

2. DML commands 1. Insert command Used to insert values into created table. Syntax 1: for inserting single row Insert into < table_name > values(value1, value2,…..,value n); Ex. Insert into student values(111,’aaa’,86); Syntax 2 : by using attribute list Insert into < table_name > (attribute1, attribute2,…….,attribute n) values (value1, value2,……., value n); Mrs. Pallavi Patil

3. Syntax 3: for inserting values for particular column Insert into < table_name >(column1, column2)values(value1, value2); 2. Update command It is used to change data values in the table. We can update one or more values using update command. Syntax: UPDATE < table_name > SET column_name =expression WHERE condition; Ex. UPDATE student SET marks= marks+3 where class =‘TY’; Mrs. Pallavi Patil

4. DELETE This command is used to delete particular rows or records from the database table. DELETE command without WHERE clause will delete all the records from table. syntax 1: DELETE from < table_name > WHERE condition; This syntax is used to delete particular record. Syntax 2: DELETE from < Table_name >; This syntax will delete all the records from table. Mrs. Pallavi Patil

5. DCL commands Used to control various user actions like insert, update, delete or viewing data. DCL commands also perform task of assigning privileges. So user can access certain objects in database. Mrs. Pallavi Patil

6. Create user The database administrator can create users of database by using this command. Syntax: Create user <username> identified by <password>; Ex. Create user admin1 identified by ad123; GRANT Grant command allows user to do certain operations on other user’s table. The different types of previleges can be granted to users such as alter, insert, delete, update, select, etc. Mrs. Pallavi Patil

7. Syntax: Grant <privilege list> on < TableName > to <username>; Ex. Grant insert , update on student_info to admin1; REVOKE It is used to cancel or to take back the granted privileges from the specific user. Syntax Revoke <privileges list> on table_name from < user_name >; Ex. Revoke insert on student_info from admin1; Mrs. Pallavi Patil

8. TCL Commands COMMIT command It is used to make changes permanent in the database. This command is used to end the transaction. Syntax: COMMIT; Mrs. Pallavi Patil

9. Savepoint Savepoints are treated as marker to divide very lengthy transactions into smaller one. We can create marker within current transaction using savepoint statement. All savepoints get erased when commit command is used. You can rollback the transactions after certain savepoint without rolling back entire transaction. Syntax: Savepoint savepoint_name ; Mrs. Pallavi Patil

10. Savepoint sp1; DELETE from student where rollno =3; Savepoint sp2; Update student set student_name =‘ABC’ where rollno =1; Delete from student where rollno =5; Savepoint sp3; Delete from student where rollno =8; Mrs. Pallavi Patil

11. 3. Rollback Rollback is used to undo or cancel the changes done in current transactions. We rollback entire transactions using rollback command or if savepoint is given, rollback can be done till specific savepoint . Syntax: Rollback; Or Rollback to savepoint_name ; Mrs. Pallavi Patil