Ultimate Guide to SQL: Master DDL, DML, DQL, DCL, TCL Commands

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. It allows you to define database structures, manipulate data, query data, control access, and manage transactions effectively.

This article explains the core components of SQL, including detailed examples for each SQL command, helping you understand how to work with databases using this powerful language.

What is SQL?

SQL is a language used to communicate with relational databases. It enables you to perform operations such as creating, altering, and querying database objects (tables, views, indexes), as well as manipulating data and controlling access. SQL commands are divided into five major categories:

1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)
3. Data Query Language (DQL)
4. Data Control Language (DCL)
5. Transactional Control Language (TCL)

Data Definition Language (DDL)

DDL commands are used to define and manage database structures, such as creating, altering, or deleting databases and tables. These commands form the foundation of a database system and are essential for setting up its structure.

Key DDL Commands:

1. CREATE: Used to create databases, tables, and other database objects.
2. ALTER: Modifies an existing database object (like a table).
3. DROP: Deletes an existing database object.
4. TRUNCATE: Removes all rows from a table but does not delete the table structure.

Creating a Database

The CREATE DATABASE command is used to create a new database.

Syntax:

CREATE DATABASE database_name;

Example: To create a database called “company”:

CREATE DATABASE company;

To verify the creation of the database, use:

SHOW DATABASES;

Dropping a Database

The DROP DATABASE command is used to delete an existing database.

Syntax:

DROP DATABASE database_name;

Example: To drop the “company” database:

DROP DATABASE company;

Verify the database has been deleted with:

SHOW DATABASES;

Creating Tables

Before creating tables, you must select a database to work with using the USE command.

Syntax to create a table:

CREATE TABLE table_name (

    column1 datatype,

    column2 datatype,

    ...

);

Example: To create a “customers” table with columns for customer details:

CREATE TABLE customers (

    CustomerId INT,

    first_name VARCHAR(20),

    last_name VARCHAR(20),

    country VARCHAR(20)

);

Altering Tables

The ALTER command is used to modify the structure of an existing table, such as adding, removing, or changing columns.

Changing a Column

Syntax:

ALTER TABLE table_name CHANGE old_column_name new_column_name datatype;

Example: To rename the “Second_name” column to “last_name” in the “customers” table:

ALTER TABLE customers CHANGE Second_name last_name VARCHAR(20);

Modifying a Column

Syntax:

ALTER TABLE table_name MODIFY column_name datatype constraint;

Example: To change the data type of the “first_name” column to VARCHAR(25) and make it NOT NULL:

ALTER TABLE customers MODIFY first_name VARCHAR(25) NOT NULL;

Adding a Column

Syntax:

ALTER TABLE table_name ADD COLUMN column_name datatype;

Example: To add a “Salary” column to the “customers” table:

ALTER TABLE customers ADD COLUMN Salary INT;

Dropping a Column

Syntax:

ALTER TABLE table_name DROP COLUMN column_name;

Example: To drop the “Salary” column from the “customers” table:

ALTER TABLE customers DROP COLUMN Salary;

Dropping Tables

The DROP TABLE command is used to delete an existing table from the database.

Syntax:

DROP TABLE table_name;

Example: To delete the “customers” table:

DROP TABLE customers;

Renaming Tables

The RENAME TABLE command allows you to change the name of an existing table.

Syntax:

RENAME TABLE old_table_name TO new_table_name;

Example: To rename the “customers” table to “customer_info”:

RENAME TABLE customers TO customer_info;

Data Manipulation Language (DML)

DML commands are used to manipulate data stored in the database. These commands allow you to insert, update, delete, or select data in the database.

Key DML Commands:

1. INSERT: Adds new records to a table.
2. UPDATE: Modifies existing records in a table.
3. DELETE: Removes records from a table.
4. SELECT: Retrieves data from one or more tables.

Inserting Data into a Table

The INSERT INTO command is used to add new records into a table.

Syntax:

INSERT INTO table_name (column1, column2, column3)

VALUES (value1, value2, value3);

Example: To add a new customer to the “customers” table:

INSERT INTO customers (CustomerId, first_name, last_name, country)

VALUES (1, 'John', 'Doe', 'USA');

Updating Data in a Table

The UPDATE command is used to modify existing records in a table.

Syntax:

UPDATE table_name

SET column1 = value1, column2 = value2

WHERE condition;

Example: To update the last name of the customer with CustomerId = 1:

UPDATE customers

SET last_name = 'Smith'

WHERE CustomerId = 1;

Deleting Data from a Table

The DELETE command is used to remove records from a table.

Syntax:

DELETE FROM table_name

WHERE condition;

Example: To delete a customer with CustomerId = 1:

DELETE FROM customers

WHERE CustomerId = 1;

Warning: If you omit the WHERE clause, all records in the table will be deleted.

Selecting Data from a Table

The SELECT command is used to retrieve data from one or more tables. It is one of the most commonly used SQL commands.

Syntax:

SELECT column1, column2, ...

FROM table_name

WHERE condition;

Example: To retrieve all customer names from the “customers” table:

SELECT first_name, last_name

FROM customers;

To retrieve customer details for customers from the USA:

SELECT * FROM customers

WHERE country = 'USA';

You can also sort the results using ORDER BY:

SELECT * FROM customers

ORDER BY last_name ASC;

Data Query Language (DQL)

DQL is primarily used for querying and retrieving data from the database. The main DQL command is SELECT, which allows you to extract data based on specific criteria.

As discussed earlier, the SELECT command allows you to retrieve records from one or more tables and filter them using various conditions. You can also perform joins, group data, and apply functions like COUNT, AVG, MAX, etc., to summarize the data.

Data Control Language (DCL)

DCL commands are used to control access to data in the database. They help manage permissions and roles for users.

Key DCL Commands:

1. GRANT: Provides privileges to users.
2. REVOKE: Removes previously granted privileges.

Granting Permissions

The GRANT command is used to provide specific privileges to a user or role on a database object.

Syntax:

GRANT privilege ON object TO user;

Example: To grant a user the ability to select data from the “customers” table:

GRANT SELECT ON customers TO user_name;

Revoking Permissions

The REVOKE command is used to remove previously granted privileges from a user.

Syntax:

REVOKE privilege ON object FROM user;

Example: To revoke the select privilege on the “customers” table from the user:

REVOKE SELECT ON customers FROM user_name;

Transaction Control Language (TCL)

TCL commands are used to manage transactions, ensuring that operations are completed successfully or undone in case of errors.

Key TCL Commands:

1. COMMIT: Saves all changes made during the current transaction.
2. ROLLBACK: Undoes changes made during the current transaction.
3. SAVEPOINT: Sets a point within a transaction that can be rolled back to.
4. SET TRANSACTION: Defines the characteristics of a transaction.

Committing Changes

The COMMIT command saves all changes made during the current transaction.

Syntax:

COMMIT;

Example:

COMMIT;

Rolling Back Changes

The ROLLBACK command is used to undo the changes made during the current transaction.

Syntax:

ROLLBACK;

Example:

ROLLBACK;

Starting a Transaction:

START TRANSACTION;

Or:

BEGIN;

Creating a Savepoint:

SAVEPOINT savepoint_name;

Example:

SAVEPOINT Save1;

Rolling Back to a Savepoint:

ROLLBACK TO savepoint_name;

Example:

ROLLBACK TO Save1;

Releasing a Savepoint:

Once a savepoint is released, it can no longer be rolled back.

RELEASE SAVEPOINT savepoint_name;

Example:

RELEASE SAVEPOINT Save1;

For more clarification and explanation, you can go to this website.

SQL is a powerful language for managing and manipulating data in relational databases. This guide covers the key SQL commands and provides examples of their usage. Understanding these SQL commands—DDL, DML, DQL, DCL, and TCL—is essential for any database management or application development task. Master these commands to handle data efficiently, query information, and manage transactions securely.

If you’re looking to expand your programming skills beyond SQL, check out our comprehensive guide on Python Basics for Beginners. It covers everything you need to start coding in Python.