All Types of SQL Commands with Syntax & Example | DDL, DQL, DML, DCL, TCL | Learn SQL Commands Types

Summary

This video provides a comprehensive overview of SQL commands, focusing on their types and usage. It introduces five main categories of SQL commands: DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), TCL (Transaction Control Language), and DQL (Data Query Language). Each type is explained with essential commands such as CREATE, INSERT, UPDATE, DELETE, GRANT, COMMIT, and SELECT, along with their syntax and examples. This video serves as an invaluable resource for anyone looking to learn or reinforce their understanding of SQL and how to work with databases effectively.

Takeaways

• 😀 SQL stands for Structured Query Language and is used to communicate with and manage databases.
• 😀 SQL commands allow users to perform various operations like creating, modifying, and deleting tables, as well as querying data.
• 😀 There are five main types of SQL commands: DDL, DML, DCL, TCL, and DQL.
• 😀 Data Definition Language (DDL) commands are used to define and modify database structure, and changes made by DDL commands are permanent.
• 😀 Common DDL commands include CREATE, DROP, ALTER, and TRUNCATE.
• 😀 Data Manipulation Language (DML) commands modify the data in the database, and changes can be rolled back as they are not auto-committed.
• 😀 Key DML commands include INSERT (adding data), UPDATE (modifying data), and DELETE (removing data).
• 😀 Data Control Language (DCL) commands manage user access to the database, including GRANT (providing access) and REVOKE (removing access).
• 😀 Transaction Control Language (TCL) commands manage changes to data and transactions, with commands like COMMIT (saving changes), ROLLBACK (undoing changes), and SAVEPOINT (setting a rollback point).
• 😀 Data Query Language (DQL) is used for querying data from a database, and the primary command in this category is SELECT.

Q & A

What is SQL and what is its primary purpose?

SQL stands for Structured Query Language, and its primary purpose is to communicate with databases to manage and manipulate data efficiently. It allows users to perform tasks such as querying, inserting, updating, and deleting data in the database.

What are the five main types of SQL commands?

The five main types of SQL commands are: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Transaction Control Language (TCL), and Data Query Language (DQL).

What is the purpose of Data Definition Language (DDL) in SQL?

DDL commands are used to define and modify the structure of a database, such as creating, altering, or deleting tables. These changes are auto-committed, meaning they are permanently saved in the database.

Can you give an example of a DDL command?

An example of a DDL command is the 'CREATE' statement, which is used to create a new table. For instance: `CREATE TABLE EMPLOYEE (Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE);`

What is the difference between DML and DDL commands?

DML (Data Manipulation Language) commands are used to modify the data within tables (such as INSERT, UPDATE, and DELETE). Unlike DDL commands, DML changes are not auto-committed and can be rolled back. DDL commands, on the other hand, define or change the database structure and are auto-committed.

What does the 'INSERT' DML command do?

The 'INSERT' command is used to add new rows of data into a table. For example: `INSERT INTO javatpoint (Author, Subject) VALUES ('Sonoo', 'DBMS');`

What is the role of Data Control Language (DCL) commands in SQL?

DCL commands are used to manage permissions and access control in a database. They allow administrators to grant or revoke user privileges. Common DCL commands include 'GRANT' and 'REVOKE'.

What is the 'COMMIT' command in Transaction Control Language (TCL)?

'COMMIT' is used to permanently save all changes made during a transaction in the database. For example, after a DELETE statement, you would use `COMMIT;` to ensure the changes are saved.

How does the 'ROLLBACK' command differ from 'COMMIT'?

'ROLLBACK' is used to undo changes made during a transaction that have not yet been committed, effectively reverting the database to its previous state. In contrast, 'COMMIT' saves all changes permanently.

What is the function of the 'SELECT' command in Data Query Language (DQL)?

The 'SELECT' command is used to query and retrieve data from a database based on specified conditions. For example: `SELECT emp_name FROM employee WHERE age > 20;`