Skip to main content

Structured Query Language

SQL is a standard programming language used for managing and manipulating relational databases.

Data Definition Language (DDL)​

DDL commands define and manage the structure of the database and its objects. Common DDL commands include:

  • CREATE: Creates a new table, database, index, view, or other objects.

    CREATE TABLE Employees (
        EmployeeID int,
        LastName varchar(255),
        FirstName varchar(255),
        BirthDate date
    );

  • ALTER: Modifies an existing database object, such as a table or index.

    ALTER TABLE Employees
    ADD Email varchar(255);

  • DROP: Deletes an existing database object.

    DROP TABLE Employees;

Data Manipulation Language (DML)​

DML commands are used for managing data within schema objects. They allow you to query and modify data. Common DML commands include:

  • SELECT: Retrieves data from one or more tables.

    SELECT FirstName, LastName FROM Employees;

  • INSERT: Adds new rows of data to a table.

    INSERT INTO Employees (EmployeeID, LastName, FirstName, BirthDate)
    VALUES (1, 'Doe', 'John', '1980-01-01');

  • UPDATE: Modifies existing data in a table.

    UPDATE Employees
    SET Email = 'john.doe@example.com'
    WHERE EmployeeID = 1;

  • DELETE: Removes existing data from a table.

    DELETE FROM Employees
    WHERE EmployeeID = 1;

Data Control Language (DCL)​

DCL commands control access to data within the database. The main DCL commands are:

  • GRANT: Gives users access privileges to the database.

    GRANT SELECT, INSERT ON Employees TO 'username';

  • REVOKE: Removes access privileges from users.

    REVOKE SELECT, INSERT ON Employees FROM 'username';

Transaction Control Language (TCL)​

TCL commands manage transactions in the database, ensuring data integrity. Common TCL commands include:

  • COMMIT: Saves all transactions to the database.

    COMMIT;

  • ROLLBACK: Undoes transactions that have not been committed.

    ROLLBACK;

  • SAVEPOINT: Sets a savepoint within a transaction.

    SAVEPOINT SavepointName;

  • RELEASE SAVEPOINT: Removes a savepoint.

    RELEASE SAVEPOINT SavepointName;

Additional Elements​

  • Clauses: Clauses modify SQL commands to specify additional parameters, such as WHERE, ORDER BY, GROUP BY, and HAVING.

    SELECT FirstName, LastName
    FROM Employees
    WHERE BirthDate > '1990-01-01'
    ORDER BY LastName ASC;

  • Functions and Expressions: SQL includes built-in functions (e.g., COUNT, SUM, AVG) and allows for expressions to perform calculations or operations on data.

    SELECT COUNT(*) AS NumberOfEmployees
    FROM Employees;

  • Joins: Joins combine rows from two or more tables based on related columns.

    SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
    FROM Employees
    JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

  • Subqueries: Subqueries are nested queries used within another SQL query to provide results for the main query.

    SELECT FirstName, LastName
    FROM Employees
    WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'HR');