DDL, DML, DQL, DCL, and TCL – SitePoint

SQL (Structured Query Language) is a programming language that allows data management and manipulation within relational database management systems. These days, all small and large enterprises are relying on SQL for their data storage and transformation. In most scenarios, learning only basic commands enables us to manage our databases effectively.

Key Takeaways

• SQL commands are categorized into five categories such as DDL, DML, DCL, DQL, and TCL, each serving specific database needs.
• SQL commands range from allowing basic queries such as CREATE and UPDATE to incorporating complex functions such as aggregate functions and joining tables to create complex queries.
• Choosing the right SQL dialect depends on the application requirements, budget, and integration capabilities.
• Developers can build data-driven applications by integrating SQL with programming languages and business intelligence (BI) tools to manage data and extract meaningful insights from it.
• Secure authentication methods, access control, and encryption protect the database from unauthorized access.

What Are SQL Commands?

SQL provides a comprehensive sql commands list to communicate with databases. We can think of SQL as an instruction set passed to the database. These instructions, known as SQL language commands, enable us to perform a wide range of actions. For instance, we can use SQL commands to create a database structure, create a table or temporary table, populate the database, retrieve specific information, modify data, and control access and security.

Basic SQL Commands

Here’s a quick overview of the basic SQL commands explained below in the article. Beginners can learn these commands to understand the SQL basics.

1. SELECT
2. INSERT
3. UPDATE
4. DELETE

SQL Statement

It is a structured query used to communicate with the database. It follows a specific syntax that includes clauses, keywords, and conditions to write a query. The users can customize the SQL statements based on their specific database needs and the operation they are performing.

Basic SQL Statement Structure

SELECT column_name;
FROM table_name WHERE condition;

Types of SQL Commands

Below are the different types of SQL Commands:

DDL (Data Definition Language) Commands

DDL consists of database-level commands to alter database structures. These DDL commands define, modify, and delete database tables, views, indexes, and database schemas. Moreover, the DDL commands are auto-committed, which ensures that changes are permanently saved in the database and cannot rolled back to the previous change.

CREATE

This command creates new database objects. An object can be a Table or a Database as below.

CREATE DATABASE database_db;

 This SQL statement creates a new database database_db.

CREATE TABLE PERSONS (id INT, name VARCHAR(255));

This SQL statement creates a new table PERSONS, with columns id and name.

ALTER

This command modifies the structure of an existing object by adding, altering, or deleting table columns, changing data types, or renaming objects.

ALTER TABLE PERSONS ADD COLUMN address VARCHAR(255);

This SQL command adds a new column ADDRESS to the PERSONS table.

DROP

The DROP command deletes existing database objects.

DROP DATABASE database_db;

This SQL statement deletes the entire database database_db.

This delete statement deletes the existing table PERSONS from the database.

TRUNCATE

This deletes all the existing data from a table while keeping the original table structure. TRUNCATE is typically faster than DELETE as it doesn’t log individual row deletions.

The above SQL statement removes all records/rows from the PERSONS table.

Note: The CASCADE keyword is required if we are truncating the table containing the primary keys that are used in other tables as foreign keys. It will truncate all the dependent tables.

This SQL statement adds a comment to the definition of a specific database object, which is essential for documentation purposes.

COMMENT ON TABLE PERSONS IS 'Table contains persons information';

DML (Data Manipulation Language) Commands

DML consists of SQL main commands to manipulate the data present in the database. For instance, these SQL command lists include commands to insert, modify, and delete data. The DML commands are not auto-committed, ensuring that changes are not permanently saved in the database and we can roll back to the previous state. For instance, we can retrieve back the deleted row by using the ROLLBACK statement.

INSERT

This command adds new data to a table. The below command adds a new row to the PERSONS table.

INSERT INTO PERSONS (id, name) VALUES (10, 'Alice');

UPDATE

This updates existing data in a table. As depicted below, the UPDATE command updates PERSONS name with ID 10.

UPDATE PERSONS SET name = 'Alice' WHERE id = 10;

DELETE

This deletes existing data based on some condition.

DELETE FROM PERSONS WHERE id = 5;

The delete statement removes the person with ID 5 from the PERSONS table.

DQL (Data Query Language) commands

DQL command is a subset of SQL commands specifically designed for querying and retrieving data from the database. DQL command (SELECT) performs specific tasks on the data within schema objects and extracts schema relations based on the query passed to it. It utilizes various clauses, functions, and keywords to filter and manipulate the data, thus enhancing its functionality. 

SELECT (Retrieve Data)

This command retrieves the specified column (name) from the table:

SELECT name FROM PERSONS;

To retrieve data from all columns, you can use SELECT * (asterisk):

However, using * is usually not recommended as it increases the amount of data transferred by including all columns, even those that aren’t required. This can impact query performance. Instead, it’s better to explicitly list the columns you need:

SELECT id, name, email FROM PERSONS;

The SELECT statement is usually used with other clauses and functions like DISTINCT, AVG(), WHERE, ORDER BY, GROUP BY, and HAVING to extract data and aggregate, filter, sort, or group it to return one or more columns.

DISTINCT

SELECT DISTINCT name FROM PERSONS;

This command ignores the duplicate rows or multiple values and returns only the unique values from the specified column, such as the name column from the table PERSONS.

WHERE

SELECT column_name(s) FROM table_name WHERE column_name operator value;

The WHERE clause filters the data based on a specified condition, such as WHERE name = ‘Alice’.

AND/OR

SELECT column_name(s) FROM table_name WHERE column_1 = value_1 AND column_2 = value_2;

This allows us to combine multiple conditions using logical operators.

LIKE

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;

We can use wildcard characters (% for any string, _ for a single character) to perform a pattern search with the LIKE operator.

LIMIT

SELECT column_name(s) FROM table_name LIMIT number;

This clause limits the number of returned rows.

ORDER BY

SELECT column_name FROM table_name ORDER BY column_name ASC | DESC;

This clause sorts the results based on the specified table column in ascending (ASC) or descending (DESC) order.

GROUP BY

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

This clause is often used with aggregate functions such as COUNT() to groups rows based on the values in the specified column..

HAVING

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > value;

This clause is used with GROUP BY to filter the grouped results.

INNER JOIN

SELECT column_name(s) FROM table_1 JOIN table_2 ON table_1.column_name = table_2.column_name;

This clause combines rows from multiple tables where the join condition is true.

OUTER JOIN

SELECT column_name(s) FROM table_1 LEFT JOIN table_2 ON table_1.column_name = table_2.column_name;

This clause retrieves data from two or more tables. Here, it combines all rows from the table_1 and matching rows from the table_2. If there’s no match in the table_2, it uses NULL values.

AS

SELECT column_name AS 'Alias' FROM table_name;

This keyword displays the results with a temporary column name.

WITH

WITH temporary_name AS (SELECT  FROM table_name) SELECT  FROM temporary_name WHERE column_name operator value;

This clause defines a temporary result set that can be referenced within the query.

Aggregate Functions

We can also use SELECT statements to extract and aggregate data from a database by using in-built functions such as AVG(), SUM(), COUNT(), etc.

AVG()

This function retrieves the average number from the selected column within the SQL statement. Here, the AVG() calculates the average value of the marks column from the student table.

SELECT AVG(MARKS) as AVERAGE_SCORE from STUDENT;

SUM()

This function retrieves the sum of numbers from the selected column within the SQL statement. Here, the SUM() calculates the average value of the marks column from the student table.

SELECT SUM(MARKS) as TOTAL_MARKS from STUDENT;

SQL Query Logical Ordering

Based on the above SQL commands, there is a logical order that is followed whenever a table or set of tables is retrieved. The below image shows how 2 tables are used to retrieve the relational data based on several SQL commands.

DCL (Data Control Language) commands

The commands that manage the rights and permissions of users in a database belong to DCL. The DCL commands control access to the database objects by granting or revoking privileges to users and also control the level of access that users have to different parts of the database.

GRANT

 This command is used to grant users specific privileges to database objects.

GRANT SELECT, INSERT ON PERSONS TO admin; 

This allows the admin to select and insert data in the PERSONS table. 

REVOKE

This command is used to revoke assigned privileges from users.

REVOKE INSERT ON PERSONS FROM admin; 

This revokes the insert permission on the PERSONS table from admin.

TCL (Transaction Control Language) commands

TCL maintains data consistency by ensuring that either all statements within a transaction are successfully committed or none of them are applied. We use the TCL commands such as “COMMIT” and “ROLLBACK” in combination with the DML commands(data manipulation language).

COMMIT

This command permanently saves all changes made within a transaction.

BEGIN TRANSACTION; 
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 456; 
COMMIT;  

The commit statement updates both accounts together, ensuring that the data is consistent. This ensures transactional data is pushed without any discrepancy.

ROLLBACK

 This command rolls back all changes made within a transaction since the last COMMIT or ROLLBACK.

BEGIN TRANSACTION; 
DELETE FROM accounts WHERE account_id = 555;
ROLLBACK;

The above command rolls back the deletion, restoring the accounts.

SAVEPOINT

This command defines a transaction point to which the table state can rolled back at any given time.

BEGIN TRANSACTION; 
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
SAVEPOINT after_insert;
UPDATE accounts SET balance = balance + 50 WHERE account_id = 123;
ROLLBACK TO after_insert; 

This rolls back the balance addition update in the accounts after the SAVEPOINT is used.

Conditional Expressions

These expressions add logic to the queries. IF, CASE, and COALESCE statements can be used to write conditional expressions.

IF

This command is not an SQL command but can be used in certain SQL dialects such as MySQL, PostgreSQL, etc. It executes SQL statements based on a given condition:

IF (Score > 50) THEN
    SELECT 'Pass' AS ExamStatus;
ELSE
    SELECT 'Fail' AS ExamStatus;
END IF;

IF Score > 50 THEN
    result := 'Pass';
ELSE
    result := 'Fail';
END IF;

Important Note: IF statements cannot be used in regular SQL queries. For conditional logic in standard SQL queries, use the CASE expression instead. CASE is supported by all SQL databases and is considered the standard way to handle conditional query logic.

CASE

This command works like an if-else statement in the programming languages:

SELECT StudentID,
 CASE

           WHEN Score > 50 THEN 'Pass'
           ELSE 'Fail'
       END AS ExamStatus

FROM STUDENTS;

COALESCE

This SQL function manages the NULL values and returns the first non-NULL value. For instance, if I give it a list of expressions with only one non-NULL value, it will return only that value. In the below code, if the Score column has a NULL value, this function replaces it with zero.

SELECT StudentID, 
       COALESCE(Score1, 0) AS FinalScore
FROM STUDENTS;

Security Best Practices

SQL Dialects

Following are the primary SQL dialects and their uses on which they are compared and are used for the development purposes of various database systems.

SQL Integration

SQL plays a crucial role in interacting with relational databases, and its integration with programming languages such as Python and JAVA and business intelligence (BI) tools enhances the ability to build powerful, data-driven applications. 

Suppose there is a customer table in the database and a Python application is being developed that retrieves customer data and uses it to generate customer insights. Here is how SQL queries are integrated into Python using SQLite3 or SQLalchemy libraries.

import sqlite3


conn = sqlite3.connect('database.db')
cursor = conn.cursor()

cursor.execute("SELECT * FROM CUSTOMERS")



rows = cursor.fetchall()

Conclusion

All in all, SQL commands help us in effective database management. The users can choose from its categories and multiple dialects to connect to the database, perform operations, and ensure their data integrity and security. 

FAQs on SQL Commands

Why should we use SQL commands?

SQL commands are used to communicate the relational databases to store, retrieve, and manipulate data.

Can I use SQL commands in my applications?

We can integrate SQL using various in-built libraries such as SQLalchemy.

How is the “DELETE” command different from the “TRUNCATE” in SQL?

The truncate command deletes all the table rows while keeping the table structure intact. However, the delete command removes data from a table based on some user’s condition or logic provided in the query. Moreover, deleted (DML command) objects can be rolled back while truncated (DDL command) rows are deleted permanently.