What SQL commands do you know?

The main SQL commands used for working with databases are divided into several categories:

DDL (Data Definition Language) - for defining and modifying the structure of a database:

• CREATE DATABASE: Creating a new database.
• CREATE TABLE: Creating a new table.
• ALTER TABLE: Modifying the structure of an existing table (adding, removing, changing columns).
• DROP DATABASE: Deleting a database.
• DROP TABLE: Deleting a table.
• TRUNCATE TABLE: Removing all rows from a table while preserving its structure.

DML (Data Manipulation Language) - for managing data within tables:

• SELECT: Selecting data from one or more tables.
• INSERT: Inserting new rows into a table.
• UPDATE: Modifying existing rows in a table.
• DELETE: Deleting rows from a table.

DCL (Data Control Language) - for managing access rights to data:

• GRANT: Granting permissions to users.
• REVOKE: Revoking permissions from users.

TCL (Transaction Control Language) - for managing transactions:

• COMMIT: Saving changes made in transactions.
• ROLLBACK: Canceling changes made in transactions.
• SAVEPOINT: Setting a savepoint within a transaction.

Some common constructs and operators used with commands, especially with SELECT:

• WHERE: Filtering rows based on conditions.
• GROUP BY: Grouping rows by one or more column values.
• HAVING: Filtering groups created with GROUP BY.
• ORDER BY: Sorting the resulting data set.
• JOIN: Combining data from multiple tables (e.g., INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN).
• UNION: Combining results of multiple SELECT queries.
• Aggregate functions (COUNT, SUM, AVG, MIN, MAX).
• Scalar functions (e.g., LOWER, UPPER, SUBSTRING).
• Comparison operators (=, !=, <, >, <=, >=, IN, BETWEEN, LIKE).
• Logical operators (AND, OR, NOT).

Example of using SELECT with some constructs:

-- Select employee names and salaries from the 'IT' department,
-- where salary is greater than 50000,
-- group by department and calculate the average salary,
-- sort by average salary in descending order.
SELECT
    department,
    AVG(salary) AS average_salary
FROM
    employees
WHERE
    department = 'IT' AND salary > 50000
GROUP BY
    department
HAVING
    AVG(salary) > 60000
ORDER BY
    average_salary DESC;