SQL Commands | DDL, DQL, DML, DCL and TCL Commands

Mastery of SQL commands is essential for someone who deals with SQL databases. SQL provides an easy system to create, modify, and arrange data. This article uses straightforward language to explain SQL commands—DDL, DQL, DML, DCL, and TCL commands.

SQL serves as one of the fundamental subjects that beginners frequently ask about its nature. SQL stands for Structured Query Language. The programming system is a database communication protocol instead of a complete programming language.

The Structured Query Language is a powerful tool for handling data management, even though it does not qualify as a programming language.

What is SQL?

What Are SQL Commands?

A database connects through SQL commands, which transmit instructions to it. The system enables users to build database tables, input data and changes, and delete existing data. The database administration work of developers depends on the daily execution of SQL commands.

The function of SQL is to manage data within databases, which is its core use. The program manages storing customer names, tracking sales, and other database operations.

A database can be accessed through five primary SQL commands.

DDL (Data Definition Language)
DQL (Data Query Language)
DML (Data Manipulation Language)
DCL (Data Control Language)
TCL (Transaction Control Language)

Also check: SQL Roadmap

DDL Command

(Data Definition Language Commands)

The database structure undergoes definition and change processes through DDL commands. The library of commands in DDL includes CREATE, ALTER, DROP, TRUNCATE, COMMENT, and RENAME.

1. CREATE TABLE Command

The following statement allows users to create new tables using the syntax below:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    enrollment_date DATE
);

2. ALTER TABLE Command

Add a new column to an existing table

ALTER TABLE students
ADD email VARCHAR(100);

This adds a new email column to the students table.

3. DROP TABLE Command

This completely removes the students table and all of its data.

DROP TABLE students;

4. TRUNCATE TABLE Command

This deletes all rows from the students table but keeps the table ready for new data.

TRUNCATE TABLE students;

5. COMMENT Command

This adds a comment to a table for documentation.

COMMENT ON TABLE students IS 'Table to store student information and enrollment details';

6. RENAME TABLE Command

This changes the table name from students to student_records.

RENAME TABLE students TO student_records;

DQL Command

(Data Query Language Commands)

The database provides data through DQL commands for retrieval purposes. The central command is SELECT. The SELECT query allows you to find particular database information.

Here is a simple example for the SELECT command.

SELECT first_name, last_name, hire_date
FROM employees
WHERE department = 'Sales'
ORDER BY hire_date DESC;

Explanation:

This query selects the product_name, price, and stock_quantity columns.
It fetches data from the products table.
It only displays products where the category is 'Electronics'.
It sorts the results in ascending order based on the price (lowest to highest).

Here is another example for the library database:

SELECT title, author, published_year
FROM library_books
WHERE genre = 'Science Fiction'
ORDER BY published_year DESC;

Explanation:

This query selects the title, author, and published_year columns.
It retrieves data from the library_books table.
It only shows books where the genre is 'Science Fiction'.
It lists the books in descending order of published_year (newest first).

DML Commands

(Data Manipulation Language Commands)

DML commands operate on the data elements found within database tables. The commands enable you to enter new data entries, modify, or remove records from the table. The primary DML commands include INSERT, UPDATE, DELETE, and CALL.

1. INSERT Command

Purpose: Adds new data into a table.

INSERT INTO students (student_id, first_name, last_name, grade)
VALUES (101, 'John', 'Doe', 'A');

Explanation:

Inserts a new student record into the students table.
Fills student_id, first_name, last_name, and grade columns.

2. UPDATE Command

Purpose: Changes existing data in a table.

UPDATE students
SET grade = 'A+'
WHERE student_id = 101;

Explanation:

Updates the grade status of the student whose ID is 101.
Changes the grade from ‘A’ to ‘A+’.

3. DELETE Command

Purpose: Removes data from a table.

DELETE FROM students
WHERE student_id = 101;

Explanation:

Deletes the student record with the student_id of 101.
The record will be removed entirely from the table.

4. CALL Command

Purpose: Executes a stored procedure.

CALL UpdateStudentGrade(101, 'B');

Explanation:

Calls a stored procedure named UpdateStudentGrade.
Passes student_id and new grade values as input.
Stored procedures automate tasks like updating records.

DCL Commands

(Data Control Language Commands)

The database uses DCL commands to enable or disable permissions for database elements. The database management system includes two primary DCL commands, GRANT and REVOKE.

1. GRANT Command

Purpose: Gives specific permissions to a user or role on a database object (like a table, view, or procedure).

GRANT SELECT, INSERT ON employees TO user1;

Explanation:

This command allows user1 to view (SELECT) and add (INSERT) data in the employees table.
It controls what actions a user can perform without giving full access to the database.

2. REVOKE Command

Purpose: Removes permissions that were previously granted to a user or role.

REVOKE INSERT ON employees FROM user1;

Explanation:

This command takes back the INSERT permission from user1 on the employees table.
After this, user1 can still view data (because SELECT is still there), but cannot add new data.

TCL Commands

(Transaction Control Language)

The TCL commands allow database systems to control their transaction management functions. Database changes will either succeed ultimately or fail under the management of these commands. The primary TCL database operations include SAVEPOINT, BEGIN TRANSACTION, COMMIT, and ROLLBACK.

Common TCL commands:

COMMIT: to save changes.
ROLLBACK: to undo changes.
SAVEPOINT: to save a specific point.

1. BEGIN TRANSACTION Command

Purpose:
Starts a new transaction. It groups multiple SQL actions into one single unit of work.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

Explanation:

BEGIN TRANSACTION Tells the database that the following actions are part of one group.
If anything goes wrong, you can undo all of them at once.

2. SAVEPOINT Command

Purpose: It creates a point inside a transaction where you can roll back without canceling it.

SAVEPOINT SaveAfterFirstUpdate;

Explanation:

After the first update, you create a SAVEPOINT.
If a later query fails, you can roll back to this point, not the beginning.

3. COMMIT Command

Purpose: Finalizes the transaction. All changes made in the transaction become permanent.

COMMIT;

Explanation:

After checking that everything is correct, you use COMMIT to save the changes to the database.
You cannot undo actions after a commit.

4. ROLLBACK Command

Purpose: Cancels part or all of a transaction. It undoes the changes.

ROLLBACK TO SaveAfterFirstUpdate;

Explanation:

If something goes wrong after the savepoint, you can rollback to the SaveAfterFirstUpdate.
You can also roll back the entire transaction if you don’t use a savepoint.

Simple flow of using these commands together:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
SAVEPOINT SaveAfterFirstUpdate;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- If error occurs
ROLLBACK TO SaveAfterFirstUpdate;
-- Else
COMMIT;

Important SQL Commands

Developers need to learn several SQL commands as essential skills. The database system includes seven core statements: SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, ALTER TABLE, DROP TABLE, and COMMIT.

Here are the most important SQL commands you must know:

CREATE TABLE: to make a new table.
ALTER TABLE: to change a table.
DROP TABLE: to delete a table.
SELECT: to find and show data.
INSERT INTO: to add new data.
UPDATE: to change existing data.
DELETE: to remove data.
GRANT: to give someone permission.
REVOKE: to take back permission.
COMMIT: to save all changes.
ROLLBACK: to cancel changes.

These commands provide daily support for database creation and task control processes. The commands offer developers an easy way to communicate with databases through website administration and application development.

You can learn standard SQL basics during three weeks of active practice, even if you are a beginner curious about SQL. Your skills as a learner will grow with time while you work on individual steps at first.

Why SQL Commands Are Important

Clients who use SQL servers and database systems such as Oracle must understand these commands.

The database query tools provide an effective way to practice your commands.

Your command of SQL commands enables both understanding of Oracle data dictionary structures and the creation of simple SQL reports.

Now you may ask, “How can I learn SQL?” Good news — SQL is easy to learn!

Many beginners ask, “How long to learn SQL?” or “How long does it take to learn SQL?” If you practice daily, you can learn essential SQL in just 2 to 4 weeks!

Learning essential SQL requires daily practice for 2 to 4 weeks until mastery.

SQL Reports and Query Tools

Professional developers use database query tools to execute SQL commands in actual development projects efficiently.

You will use SQL query tools like:

SQL Server Management Studio (SSMS)
Oracle SQL Developer
DBeaver
MySQL Workbench

These tools allow you to create SQL reports and accelerate operational speed when working with databases.

When you work with Oracle databases, you will hear about the Oracle data dictionary.

It is a special place where Oracle stores details about your tables, columns, users, and more.
Whenever you use a SQL command like SELECT or INSERT, Oracle checks its data dictionary to know where and how to find your data.

This is why understanding the Oracle data dictionary is essential.

The database tool is crucial for SQL query writing and report generation activities. The database engine requires directions from this feature about its data location.

Using SQL query tools and the data dictionary enables you to work more efficiently by shortening your work process.

SQL Injection and How to Prevent It

SQL injection stands as a fundamental concept that you need to master. The act of database hacking through unauthorized access causes significant damage to your system. Businesses need to understand how to stop SQL injection attacks. The protection of your system requires both validation and input cleaning procedures.

SQL vs SQL Server

Many users make the common mistake of mixing SQL with Microsoft SQL Server functions. But what is SQL Server?

Microsoft developed this software tool to function as a database management system through SQL command execution.

SQL vs MySQL

SQL
SQL stands for Structured Query Language. It is the standard language used to manage and work with databases.

MySQL
MySQL is a popular open-source database management system. It uses SQL commands to store, organize, and retrieve data.

How to Learn SQL?

If you ask, “How can I learn SQL?” or “How long does it take to learn SQL?“, start with simple online tutorials. Practice simple queries first. Try SELECT, INSERT, and UPDATE commands. Build small projects.

It may take a few weeks to get basic skills, and a few months to become good. But with daily practice, you will become confident quickly.

Conclusion

Understanding SQL commands—DDL, DQL, DML, DCL, and TCL commands and their functions in data management—is complete.

SQL is simple yet powerful.

The vital element for everyone learning about SQL basics, understanding SQL terminology, or exploring SQL fundamentals is practice.

Work with genuine databases using query tools to develop minor projects as you expand your SQL knowledge. SQL will offer you both a pleasant and satisfying learning experience.

What Are SQL Commands?

DDL Command

1. CREATE TABLE Command

2. ALTER TABLE Command

3. DROP TABLE Command

4. TRUNCATE TABLE Command

5. COMMENT Command

6. RENAME TABLE Command

DQL Command

DML Commands

1. INSERT Command

2. UPDATE Command

3. DELETE Command

4. CALL Command

DCL Commands

1. GRANT Command

2. REVOKE Command

TCL Commands

1. BEGIN TRANSACTION Command

2. SAVEPOINT Command

3. COMMIT Command

4. ROLLBACK Command

Important SQL Commands

Why SQL Commands Are Important

SQL Reports and Query Tools

SQL Injection and How to Prevent It

SQL vs SQL Server

SQL vs MySQL

How to Learn SQL?

Conclusion

Read More

Similar Posts

Leave a Reply Cancel reply

Contact us

Lets Get in Touch

Headquarters

Free Source Codes

Company