SQL Fundamentals.

SQL, short for Structured Query Language, is the standard language for defining, querying and changing data in a relational database. It is declarative, meaning you describe the result and the database decides how to get it. MySQL, PostgreSQL and SQL Server are engines that implement SQL, each with its own extensions.

Its statements fall into four groups:

• Data Definition Language (DDL) defines structure with CREATE, ALTER and DROP. TRUNCATE counts as DDL too.
• Data Manipulation Language (DML) reads data with SELECT and changes it with INSERT, UPDATE and DELETE.
• Data Control Language (DCL) controls access permissions with GRANT and REVOKE.
• Transaction Control Language (TCL) manages transactions with COMMIT, ROLLBACK and SAVEPOINT.

DELETE removes chosen rows, TRUNCATE removes every row but keeps the table, and DROP removes the table itself along with its data.

• DELETE removes the rows its WHERE clause matches, one row at a time. Delete triggers fire, the change can be rolled back, and it is slow on large tables.
• TRUNCATE empties the whole table without scanning it, so it is much faster than DELETE. It does not fire delete triggers, and MySQL and SQL Server also reset the auto-increment counter.
• DROP deletes the table definition together with its data, indexes and constraints.

Rollback depends on the engine. MySQL commits TRUNCATE and DROP immediately, so neither can be rolled back there. PostgreSQL and SQL Server can roll back a TRUNCATE inside a transaction.

DELETE FROM employees WHERE age > 65;  -- Removes specific rows
TRUNCATE TABLE employees;              -- Removes all rows, keeps table
DROP TABLE employees;                  -- Removes entire table

A primary key is the column, or set of columns, that uniquely identifies each row in a table. The database enforces two rules on it: every value must be unique, and no value may be NULL.

A table can have only one primary key, and the database creates an index on it automatically, so lookups by key are fast. Key values should also stay stable once assigned, because foreign keys point at them, but that is a design rule the database does not enforce.

A UNIQUE constraint also blocks duplicates, and a table can have several of them. Unlike a primary key, it accepts NULL. PostgreSQL and MySQL allow many NULL values in a unique column, while SQL Server allows only one.

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

WHERE filters individual rows before they are grouped, and HAVING filters the groups after GROUP BY has formed them.

WHERE runs first, so it cannot use aggregate functions such as COUNT or AVG, because the groups do not exist yet. HAVING runs after grouping, so it can test those results, such as keeping only departments with more than five employees.

A condition that needs no aggregate belongs in WHERE. Filtering early means fewer rows reach the grouping step, which makes the query cheaper.

-- WHERE filters before grouping
SELECT department, COUNT(*) 
FROM employees 
WHERE salary > 50000 
GROUP BY department;
-- HAVING filters after grouping
SELECT department, COUNT(*) 
FROM employees 
GROUP BY department 
HAVING COUNT(*) > 5;

UNION combines the rows of two queries and removes duplicate rows, while UNION ALL combines them and keeps every row, duplicates included.

Removing duplicates costs work, because the database has to sort or hash the combined rows to find repeats. So UNION ALL is usually faster. UNION also removes repeats that sit inside one input, not only the ones shared by both.

Both need the same number of columns in each query, in the same order, with compatible data types. The result takes its column names from the first query.

Use UNION ALL when the rows are already distinct, or when duplicates carry meaning, such as when counting events. Use UNION when each distinct row should appear once.

SELECT name FROM employees_2023
UNION                    -- Removes duplicates
SELECT name FROM employees_2024;
SELECT name FROM employees_2023
UNION ALL               -- Keeps all rows
SELECT name FROM employees_2024;

Constraints are rules the database enforces on column values, so bad data is rejected when it is written. The main types are:

• NOT NULL stops a column from holding NULL.
• UNIQUE stops two rows from sharing a value.
• PRIMARY KEY combines NOT NULL and UNIQUE to identify each row.
• FOREIGN KEY keeps referential integrity by requiring each value to match a row in another table.
• CHECK rejects values that fail a condition, such as a total of zero or less.
• DEFAULT supplies a value when an insert gives none.

A CHECK also passes when its condition is NULL, so pair it with NOT NULL when the column must have a value.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE DEFAULT (CURRENT_DATE),
    total_amount DECIMAL(10,2) CHECK (total_amount > 0),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);