SQL Commands and Query Execution Order

By /

SQL Syntax

SQL syntax refers to the set of rules that dictate how SQL statements are constructed. This language is primarily used to interact with relational databases, allowing users to perform operations such as creating, reading, updating, and deleting data within a database.

The fundamental structure of a SQL statement typically follows this format:

Keyword [parameter_1, parameter_2, ...]

For instance, consider the following example of a SELECT statement, which retrieves the Name and Department ID columns from the Students table:

SELECT Name, DepartmentID
FROM Students;

In this statement:

  • The keyword is SELECT, indicating that we want to retrieve specific data from the database.
  • The parameters are Name and DepartmentID, representing the columns we wish to retrieve data from.
  • FROM Students specifies the table from which we want to retrieve the data.

Understanding SQL syntax is crucial for writing correct and efficient SQL statements to interact with databases effectively.

SQL Commands

  • SQL syntax is the set of guidelines governing the writing of SQL statements.
  • The SQL language uses commands like CREATE, DROP, INSERT, and others to perform necessary activities.
  • SQL statements provide instructions to a table.
  • SQL is used to perform various tasks on a database.
  • It is utilized for carrying out specific duties, functions, and data inquiries.
  • SQL capabilities include:
    • Table creation
    • Data addition
    • Table deletion
    • Table modification
    • User permission settings

The five main categories into which SQL commands fall are:

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

1. Data Definition Language (DDL)

DDL commands are used to define and modify the structure of database objects such as tables, indexes, and views. They include commands like CREATE, ALTER, DROP, and TRUNCATE.

  • CREATE: Used to create new database objects like tables.
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    DepartmentID INT,
    Salary DECIMAL(10, 2)
);
  • ALTER: Used to modify an existing database object.
ALTER TABLE Employees ADD COLUMN BirthDate DATE;
  • DROP: Used to delete an existing database object.
DROP TABLE Employees;
  • TRUNCATE: Used to remove all records from a table but retain the structure for future use.
TRUNCATE TABLE Employees;
  • RENAME: Used to change the name of an existing database object such as a table or a column.
RENAME TABLE old_table_name TO new_table_name;

2. Data Query Language (DQL)

DQL commands are used to query and retrieve data from the database. The primary DQL command is SELECT.

  • SELECT: Used to retrieve data from one or more tables.
SELECT Name, DepartmentID FROM Employees;

3. Data Manipulation Language (DML)

DML commands are used to manipulate the data stored in database tables. They include commands like INSERT, UPDATE, DELETE.

  • INSERT: Used to add new records to a table.
INSERT INTO Employees (EmployeeID, Name, DepartmentID, Salary) VALUES (1, 'John Doe', 101, 60000.00);
  • UPDATE: Used to modify existing records in a table.
UPDATE Employees SET Salary = 65000.00 WHERE EmployeeID = 1;
  • DELETE: Used to remove records from a table.
DELETE FROM Employees WHERE EmployeeID = 1;
  • LOCK: The LOCK command is used to control concurrency by preventing other transactions from accessing a table or tables in a database while one transaction is ongoing. This helps ensure data consistency and integrity.
LOCK TABLE Employees IN EXCLUSIVE MODE;
  • CALL: The CALL command is used to execute a stored procedure in the database. Stored procedures are precompiled collections of SQL statements that perform a specific task.
CALL UpdateEmployeeSalary(1, 75000.00);
  • EXPLAIN PLAN: The EXPLAIN PLAN command is used to display the execution plan of a SQL statement. This helps in understanding how the database engine will execute the query, which is useful for optimizing query performance.
-- Generating an execution plan for a SELECT statement 
EXPLAIN PLAN FOR 
SELECT Name, DepartmentID FROM Employees WHERE Salary > 50000.00; 
-- Retrieving the generated execution plan 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

4. Data Control Language (DCL)

DCL commands are used to control access to data within the database, managing permissions and roles. They include commands like GRANT and REVOKE.

  • GRANT: Used to give users access privileges to the database.
GRANT SELECT, INSERT ON Employees TO 'user';
  • REVOKE: Used to remove access privileges from users.
REVOKE INSERT ON Employees FROM 'user';

5. Transaction Control Language (TCL)

TCL commands are used to manage transactions within the database, ensuring data integrity and consistency. They include commands like COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION.

  • COMMIT: Used to save all changes made during the current transaction.
COMMIT;
  • ROLLBACK: Used to undo changes made during the current transaction.
ROLLBACK;
  • SAVEPOINT: Used to set a savepoint within a transaction, allowing partial rollback.
SAVEPOINT Savepoint1;
  • SET TRANSACTION: Used to specify characteristics for the current transaction.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

These categories and commands provide a comprehensive toolkit for managing databases, allowing for the creation, manipulation, and protection of data, as well as ensuring the consistency and reliability of database operations.

SQL Query Execution Order

Understanding the order in which SQL queries are executed is crucial for writing efficient and correct SQL statements. The execution order of a SQL query follows a specific sequence of operations, which can be summarized as follows:

  1. FROM: Determine the source tables.
  2. JOIN: Perform join operations to combine data from multiple tables.
  3. WHERE: Filter rows based on specified conditions.
  4. GROUP BY: Group rows that have the same values in specified columns into summary rows.
  5. HAVING: Filter groups based on specified conditions.
  6. SELECT: Select the required columns.
  7. DISTINCT: Remove duplicate rows from the results.
  8. ORDER BY: Sort the results.
  9. LIMIT/OFFSET: Limit the number of rows returned (specific to some SQL dialects).

Detailed Explanation with Example

Consider the following query:

SELECT DISTINCT DepartmentID, COUNT(EmployeeID) AS EmployeeCount
FROM Employees
WHERE Salary > 50000
GROUP BY DepartmentID
HAVING COUNT(EmployeeID) > 5
ORDER BY EmployeeCount DESC
LIMIT 10;

1. FROM

First, the FROM clause identifies the source table(s) for the query. In this example:

FROM Employees

2. JOIN

Next, if there are any join operations, they are performed to combine data from multiple tables. This query does not have a join operation, but an example would be:

FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID

3. WHERE

The WHERE clause filters rows based on the specified conditions. Only rows where the Salary is greater than 50000 are selected:

WHERE Salary > 50000

4. GROUP BY

The GROUP BY clause groups the filtered rows based on the DepartmentID:

GROUP BY DepartmentID

5. HAVING

The HAVING clause filters groups created by the GROUP BY clause. Only groups with more than 5 employees are included:

HAVING COUNT(EmployeeID) > 5

6. SELECT

The SELECT clause specifies which columns to include in the final result set. Here, we select the DepartmentID and a count of EmployeeID:

SELECT DepartmentID, COUNT(EmployeeID) AS EmployeeCount

7. DISTINCT

The DISTINCT keyword ensures that duplicate rows are removed. Although in this case, the grouping and aggregation would naturally result in distinct rows:

SELECT DISTINCT DepartmentID, COUNT(EmployeeID) AS EmployeeCount

8. ORDER BY

The ORDER BY clause sorts the result set. In this case, it sorts by EmployeeCount in descending order:

ORDER BY EmployeeCount DESC

9. LIMIT/OFFSET

Finally, the LIMIT clause restricts the number of rows returned to 10. OFFSET can be used to skip a specific number of rows:

LIMIT 10

Summary of Execution Order

Here is the same query, annotated with the steps in the order of execution:

-- Step 1: FROM
FROM Employees

-- Step 2: (JOIN - if applicable)
-- JOIN Departments d ON e.DepartmentID = d.DepartmentID

-- Step 3: WHERE
WHERE Salary > 50000

-- Step 4: GROUP BY
GROUP BY DepartmentID

-- Step 5: HAVING
HAVING COUNT(EmployeeID) > 5

-- Step 6: SELECT
SELECT DISTINCT DepartmentID, COUNT(EmployeeID) AS EmployeeCount

-- Step 7: (DISTINCT - if applicable)
-- DISTINCT

-- Step 8: ORDER BY
ORDER BY EmployeeCount DESC

-- Step 9: LIMIT/OFFSET
LIMIT 10;

Understanding this order helps in optimizing queries and ensures that the SQL statements are constructed correctly to get the desired results.

Related Posts

Scroll to Top