Downloaded 1,259 times
DML, DDL, DCL ,DRL/DQL and TCL Statements in SQL with Examples
SQL language includes four primary statement types: DML, DDL, DCL, and TCL. DML statements manipulate data within tables using operations like SELECT, INSERT, UPDATE, and DELETE. DDL statements define and modify database schema using commands like CREATE, ALTER, and DROP. DCL statements control user access privileges with GRANT and REVOKE. TCL statements manage transactions with COMMIT, ROLLBACK, and SAVEPOINT to maintain data integrity.
DML, DDL, DCL ,DRL/DQL and TCL Statements in SQL with Examples
- 4. SQL languageis divided into four types of primary language statements: DML, DDL, DCL andTCL. Using these statements, we can define the structure of a database by creating and altering database objects, and we can manipulate data in a table through updates or deletions.We also can control which user can read/write data or manage transactions to create a single unit of work.
- 5. 1. DML(Data Manipulation Language) 2. DDL (Data Definition Language) 3. DCL (Data Control Language) 4. TCL (Transaction Control Language) 5. DRL
- 6. DML statementsaffect records in a table.These are basic operations we perform on data such as selecting a few records from a table, inserting new records, deleting unnecessary records, and updating/modifying existing records. DML statements include the following: SELECT – select records from a table INSERT – insert new records UPDATE – update/Modify existing records DELETE – delete existing records
- 7. INSERT -insert data into a table UPDATE - updates existing data within a table DELETE - deletes all records from a table, the space for the records remain MERGE - UPSERT operation (insert or update) CALL - call a PL/SQL or Java subprogram EXPLAIN PLAN - explain access path to data LOCKTABLE - control concurrency
- 8. DDL statementsare used to alter/modify a database or table structure and schema. These statements handle the design and storage of database objects. CREATE – create a newTable, database, schema ALTER – alter existing table, column description DROP – delete existing objects from database
- 9. CREATE -to create objects in the database ALTER - alters the structure of the database DROP - delete objects from the database TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed COMMENT - add comments to the data dictionary RENAME - rename an object
- 10. DCL statementscontrol the level of access that users have on database objects. GRANT - gives user's access privileges to database GRANT – allows users to read/write on certain database objects REVOKE - withdraw access privileges given with the GRANT command REVOKE – keeps users from read/write permission on database objects
- 11. TCL statementsallow you to control and manage transactions to maintain the integrity of data within SQL statements. BEGINTransaction – opens a transaction COMMITTransaction – commits a transaction ROLLBACKTransaction – ROLLBACK a transaction in case of any error
- 12. COMMIT -save work done SAVEPOINT - identify a point in a transaction to which you can later roll back ROLLBACK - restore database to original since the last COMMIT SETTRANSACTION - Change transaction options like isolation level and what rollback segment to use
- 13. Transaction ControlLanguage(TCL) commands are used to manage transactions in database.These are used to manage the changes made by DML statements. It also allows statements to be grouped together into logical transactions.
- 14. Commit command Commit command is used to permanently save any transaaction into database. Following is Commit command's syntax, commit;
- 15. Rollback command This command restores the database to last commited state. It is also use with savepoint command to jump to a savepoint in a transaction. Following is Rollback command's syntax, rollback to savepoint-name;
- 16. Savepoint command savepoint command is used to temporarily save a transaction so that you can rollback to that point whenever necessary. Following is savepoint command's syntax, savepoint savepoint-name;
- 17. ID NAME 1 dar 2wahab 4 sadiq
- 18. INSERT intoclass values(5,'Rahul'); commit; UPDATE class set name='abhijit' where id='5'; savepoint A; INSERT into class values(6,'Chris'); savepoint B; INSERT into class values(7,'Bravo'); savepoint C; SELECT * from class;
- 19. ID NAME 1 dar 2wahab 4 sadiq 5 hmftj 6 maha 7 anza
- 20. rollback toB; SELECT * from class;
- 21. ID NAME 1 dar 2wahab 4 sadiq 5 maha 6 anza
- 22. rollback toA; SELECT * from class;
- 23. ID NAME 1 dar 2wahab 4 sadiq 5 sarim
- 24. DQL: DataQuery Language OR DRL: Data Retrieval Language DRL means Data Retrieval Language.This will be used for the retrieval of the data from the database. In order to see the data present in the database, we will use DRL statement.We have only one DRL statement. SELECT is the only DRL statement in SQL Select is DRL/DQL i.e. data retrieval Language
- 25. DML (DataManipulation Language). These SQL statements are used to retrieve and manipulate data. This category encompasses the most fundamental commands including DELETE, INSERT, SELECT, and UPDATE. DML SQL statements have only minor differences between SQL variations. DML SQL commands include the following: DELETE to remove rows. INSERT to add a row. SELECT to retrieve row. UPDATE to change data in specified columns. DDL (Data Definition Language).These SQL statements define the structure of a database, including rows, columns, tables, indexes, and database specifics such as file locations. DDL SQL statements are more part of the DBMS and have large differences between the SQL variations. DML SQL commands include the following: CREATE to make a new database, table, index, or stored query. DROP to destroy an existing database, table, index, or view. DBCC (Database Console Commands) statements check the physical and logical consistency of a database. DCL (Data Control Language). These SQL statements control the security and permissions of the objects or parts of the database(s). DCL SQL statements are also more part of the DBMS and have large differences between the SQL variations. DML SQL commands include the following: GRANT to allow specified users to perform specified tasks. DENY to disallow specified users from performing specified tasks. REVOKE to cancel previously granted or denied permissions. »
- 26. During theexecution of DDL command. DDL command would not copy the actual content to rollback table space, hence it is fast compared to DML command.