Introduction

SQL, or Structured Query Language, is the industry-standard language used for managing and manipulating data in relational databases. It's the go-to tool for data analysts, business intelligence professionals, and developers alike. With SQL, you can easily retrieve, modify, and analyze data stored in a database, making it an essential skill for anyone working with data.

It is worthy of note, whether you're looking to extract insights from a large dataset, or simply update some records in a database, SQL has got you covered. So, if you're ready to dive into the world of data and take your skills to the next level, buckle up and get ready to master the art of SQL!

Article Objectives

Oftentimes, many dabble into learning programming languages by randomly picking up syntax and applying them without understanding the backdrop of the usage of those codes or syntax as the case may be. Hence, I will walk you through the different sub-languages in SQL to enhance better your understanding of the language and improve your learning approach.

Central to this article, I will be walking you through the different categories/types of statements in SQL and code implementation for subset statement under each categories. By the end of this articles, you will have an insight into the following:

• The different categories/ types of statement in SQL with code implementations
• An insight into how best to approach learning SQL based on the statements categories.
• Get your hands dirty with some basic SQL programs central to each statement categories.

For more background overview into understanding relational database management system, you can check out this Link

Categories/Types of SQL Statement

Are you ready to elevate your database game and take control of your data like never before? In this article, we're going to explore the different categories/types of SQL statements OR subset languages that makes up SQL that every data professional should know:

1. Data Definition Language (DDL)

This is used to define or modify the objects of the database. Simply put, it consist of statements that are used to define the structure of a database, such as creating, modifying, and deleting tables and other database objects.

Examples of some common DDL statements and SQL code implementation for each:

• CREATE

The CREATE statement in SQL is used to create a new database object, such as a table, view, index, or procedure. The syntax for creating a new table is as follows:

CREATE TABLE table_name (
    column_1 datatype constraints,
    column_2 datatype constraints,
    ...
    column_n datatype constraints
);

For example, to create a table named "employees_data" with columns for "employee_id", "name", "age", and "salary", the SQL statement would be:

CREATE TABLE employees_data (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    salary DECIMAL(10, 2)
);

• ALTER

The ALTER statement in SQL is used to modify the structure of an existing database object, such as a table, view, index, or procedure. The basic syntax for altering a table is as follows:

ALTER TABLE table_name action;

For example, to add a new column named "email" to the "employees_data" table, the SQL statement would be:

ALTER TABLE employees_data ADD email VARCHAR(255);

This will add a new column named "email" of type VARCHAR with a maximum length of 255 to the "employees_data" table.

There are several actions that can be performed with the ALTER statement such as:

1. ADD: used to add a new column to a table (as used in the above code sample above)
2. DROP: used to delete an existing column from a table.
3. MODIFY: used to change the data type or constraints of an existing column.
4. RENAME: used to change the name of an existing column or table.

For example:

ALTER TABLE employees_data DROP COLUMN salary;

ALTER TABLE employees_data MODIFY age INT NOT NULL;

ALTER TABLE employees_data RENAME TO emp;

• DROP

The DROP statement in SQL is used to remove an existing database, table, index, or other objects in the database.

Here is an example of using the DROP statement to remove a table called "employee data":

DROP TABLE employee_data;

Note: Be careful when using the DROP statement, as it permanently deletes the object and the data stored in it, and it cannot be recovered.

• TRUNCATE

The TRUNCATE statement in SQL is used to remove all data from a table, but unlike the DROP statement, it does not remove the table structure.

It is worthy of note that the TRUNCATE statement is faster than the DELETE statement because it does not generate any undo logs, and it resets the identity seed for any auto-incrementing columns in the table.

Here is an example of using the TRUNCATE statement to remove all data from a table called "employee_data":

TRUNCATE Table employee_data;

Note: Just like the DROP statement, be careful when using the TRUNCATE statement as it permanently deletes all data from the table, and the data cannot be recovered.

2. Data Query Language (DQL)

This refers to a set of SQL statement used to retrieve/query database. It is popularly argued that it also manipulates data. There are merits to this argument, not least that there is but a single member in this category: the SELECT statement.

• SELECT - used to retrieve data from one or more tables in a database.

Here are some examples of using the SELECT statement with the "employee" table:

• a. Select all columns and rows from the "employee_table" table:

SELECT * FROM employee_data;

/* Comment : This select all columns of the table */

• b. Select specific columns from the "employee_data" table:

SELECT first_name, last_name, salary FROM employee_data;

/* Comment: This select only the first_name, last_name and salary
columns from the employee_data table */

• c. Select based on certain conditions

SELECT * FROM employee_data WHERE salary > 50000;

/* Comment: This select all columns of the table where
salary greater than 50000 */

• d. Select and order the selection of the result

SELECT * FROM employee_data ORDER BY salary DESC;

/* Comment: This select all the columns in the table and gives an expanded ordering of the salary column in descending order */

SELECT * FROM employee_data ORDER BY salary ASC;

/* Comment: This select all the columns in the table and gives an expanded ordering of the salary column in asscending order */

• e. Select with some aggregate function and group result based on a specific column

SELECT department, COUNT(salary) 
FROM employee_data 
GROUP BY department;

SELECT department, AVG(salary) 
FROM employee_data 
GROUP BY department;

SELECT department, SUM(salary) 
FROM employee_data 
GROUP BY department;

SELECT department, MIN(salary) 
FROM employee_data 
GROUP BY department;

SELECT department, MAX(salary) 
FROM employee_data 
GROUP BY department;

• f. Select and join with another table

SELECT e.first_name, e.last_name, d.department_name
FROM employee_data e
JOIN department_data d 
ON e.department_id = d.department_id;

/* Comment: This select the employee_data table and joins it with
department_data table by joining both tables on department_id column */

Note: The SELECT statement can be used in combination with other statements in SQL especially in manipulation of tables, views , index, or procedure in a database.

3. Data Manipulation Language (DML)

This is a sublanguage in SQL. It refers to a set of SQL statements that allow you to manipulate the data stored in a database.

Examples of some common DML statements and SQL code implementation for each:

• INSERT - used to insert new data into a table.

Here are some examples of using the INSERT statement with the "employee" table:

• a. Insert data into the “employee_data” table

INSERT INTO employee_data 
(first_name, last_name, salary, department_id)
VALUES
  ('Jane', 'Doe', 60000, 2),
  ('Jim', 'Smith', 65000, 3),
  ('Sarah', 'Johnson', 70000, 4);

• b. Inserting data from another table

Note: The column names and data types must match between the source and target tables. The number of columns in the target table must also match the number of values being inserted.

For instance, let’s say we have a “temp_employee” table with temporary employee data with similar columns and data types to that of the “employee_data” table

INSERT INTO employee_data (first_name, last_name, salary, department_id)
SELECT first_name, last_name, salary, department_id
FROM temp_employee;

• UPDATE - used to modify existing data in a table.

Here are some examples of using the UPDATE statement with the "employee" table:

• a. Update data from a single row in the “employee_data” table

UPDATE employee_data
SET salary = 60000
WHERE first_name = 'John' AND last_name = 'Doe';

• b. Update data in multiple rows in the “employee_data” table

UPDATE employee_data
SET salary = salary * 1.1
WHERE department_id = 1;

• c. Update based on another table

UPDATE employee_data e
SET salary = t.new_salary
FROM temp_employee t
WHERE e.employee_id = t.employee_id;

Note: The UPDATE statement should be used with caution, as it can modify multiple rows at once, potentially affecting the data in unintended ways. It is always a good idea to back up the data before making any updates.

• DELETE - used to delete data from a table.

Here is an example of using the DELETE statement with the "employee_data" table:

• a. Delete a single row from the “employee_data” table

DELETE FROM employee_data
WHERE first_name = 'John' AND last_name = 'Doe';

• b. Delete multiple rows from the “employee_data” table

DELETE FROM employee_data
WHERE salary < 50000;

• c. Delete all rows in the “employee_data” table

DELETE FROM employee_data;

Note: The DELETE statement should be used with caution, as it can permanently remove data from the database. It is always a good idea to back up the data before making any deletions.

Quick Tip: If you only want to mark data as "deleted" but keep it in the database, you can add a "deleted" column to the table and set its value to "true" instead of actually deleting the rows.

• MERGE - used to update or insert data in a table based on the results of a SELECT statement.

Here is an example of using the MERGE statement with the "employee_data" table:

In this example, the MERGE statement joins the "employee_data" table with the "temp_employee" table using the "employee_id" column. If a matching row is found in the "employee_data" table, the row is updated with the values from the "temp_employee" table. If no matching row is found, a new row is inserted into the "employee_data" table with the values from the "temp_employee" table. Let’s take a look at the code:

MERGE INTO employee_data e
USING temp_employee t
ON (e.employee_id = t.employee_id)
WHEN MATCHED THEN
  UPDATE SET e.salary = t.salary,
             e.department_id = t.department_id
WHEN NOT MATCHED THEN
  INSERT (employee_id, first_name, last_name, salary, department_id)
  VALUES (t.employee_id, t.first_name, t.last_name, t.salary, t.department_id);

Note: The MERGE statement is useful for maintaining data consistency between two tables and can be a faster alternative to separate INSERT and UPDATE statements, especially for large datasets. However, it can also be more complex to understand and use than separate statements, so it should be used with caution.

4. Data Control Language (DCL)

This is a set of SQL commands used to control access to data in a database. The two main DCL commands are GRANT and REVOKE. This is mostly used by database managers to give access or restrict access on certain actions respectively, such as - SELECT, INSERT, UPDATE, DELETE and so on

Here is an example on implementing both commands:

• GRANT

GRANT SELECT ON employee_data TO user1;

/* Comment: This grants the user "user1" the permission to SELECT data 
from the "employee_data" table.*/

• REVOKE

REVOKE SELECT ON employee_data FROM user1;

/* Comment: This revokes the SELECT permission on the "employee_data" table 
from the user "user1".*/

It is important to note that, DCL commands are important for controlling access to data in a database and ensuring data security and privacy. By granting and revoking permissions, database administrators can control who has access to sensitive data and what actions they can perform on the data.

5. Data Transaction Language (DTL)

Data Transaction Language (DTL) refers to the set of SQL commands used to manage transactions in a database management system.

The main DTL commands are COMMIT, ROLLBACK, and SAVEPOINT.

• COMMIT

The COMMIT command is used to make permanent changes to the data in a database. When a transaction is committed, all changes made to the data during the transaction are saved and cannot be undone.

For example:

BEGIN TRANSACTION;
UPDATE employee_data
SET salary = salary + 1000
WHERE department_id = 10;
COMMIT;

In this example, the BEGIN TRANSACTION statement starts a transaction, the UPDATE statement updates the salary of employees in the "employee_data" table with a department_id of 10, and the COMMIT statement makes the changes permanent.

• ROLLBACK

The ROLLBACK command is used to undo all changes made during a transaction. When a transaction is rolled back, all changes made to the data during the transaction are discarded and the database is returned to its state before the transaction began.

For example:

BEGIN TRANSACTION;
UPDATE employee_data
SET salary = salary + 1000
WHERE department_id = 10;
ROLLBACK;

In this example, the BEGIN TRANSACTION statement starts a transaction, the UPDATE statement updates the salary of employees in the "employee" table with a department_id of 10, and the ROLLBACK statement undoes the changes.

• SAVEPOINT

The SAVEPOINT command is used to create a point within a transaction at which changes can be rolled back without undoing the entire transaction.

For example:

BEGIN TRANSACTION;
SAVEPOINT savepoint1;
UPDATE employee_data
SET salary = salary + 1000
WHERE department_id = 10;
ROLLBACK TO savepoint1;
COMMIT;

In this example, the BEGIN TRANSACTION statement starts a transaction, the SAVEPOINT statement creates a savepoint named "savepoint1", the UPDATE statement updates the salary of employees in the "employee_data" table with a department_id of 10, the ROLLBACK TO statement rolls back the transaction to the savepoint "savepoint1", and the COMMIT statement makes the changes permanent.

In conclusion, I hope this article expose you to a structured way of understanding the diverse statement types in SQL and best way to approach learning them in a bid to improve your SQL skills. However, if you have not started learning SQL or looking to improving your SQL skills you can check out our SQL Analyst track