SQL Basics

SQL is a domain-specific, declarative language designed for managing data held in a relational database. It adheres to the principles of the relational model, introduced by E.F. Codd in 1970, which organizes data into relations (tables) with attributes (columns) and tuples (rows). SQL has been standardized by ANSI and ISO since 1986, ensuring broad interoperability across database systems like PostgreSQL, MySQL, Oracle, and SQL Server.

In a university database curriculum, SQL bridges the gap between theoretical data organization (relational algebra, ER modeling) and practical data interaction. Its declarative nature means the query optimizer determines the most efficient execution plan — you focus on the logic, not the mechanics. Mastery of SQL is indispensable for data science, software engineering, database administration, and business intelligence.

Essential terms for understanding SQL and relational databases at the university level.

DDL commands define, modify, and drop the structure of database objects. These operations typically involve an implicit COMMIT, meaning changes are permanent and cannot be rolled back.

CREATE TABLE Employees (    employee_id   SERIAL PRIMARY KEY,    first_name    VARCHAR(50) NOT NULL,    last_name     VARCHAR(50) NOT NULL,    email         VARCHAR(100) UNIQUE,    hire_date     DATE DEFAULT CURRENT_DATE,    salary        DECIMAL(10,2) CHECK (salary >= 0),    department_id INT,    FOREIGN KEY (department_id)        REFERENCES Departments(department_id)        ON DELETE SET NULL        ON UPDATE CASCADE);

-- 1. Add a new columnALTER TABLE EmployeesADD COLUMN phone_number VARCHAR(20);-- 2. Add a self-referencing FK columnALTER TABLE EmployeesADD COLUMN manager_id INT    REFERENCES Employees(employee_id);-- 3. Change a column's data typeALTER TABLE EmployeesALTER COLUMN phone_number TYPE VARCHAR(25);-- 4. Drop a columnALTER TABLE EmployeesDROP COLUMN phone_number;-- 5. Add a constraintALTER TABLE EmployeesADD CONSTRAINT chk_salary_min    CHECK (salary >= 15000);

DROP TABLE vs TRUNCATE TABLE

DML commands query and modify data within the database. These operations are transactional and can be rolled back before a COMMIT.

-- 1. Single row insertINSERT INTO Employees (name, salary, dept)VALUES ('Alice', 75000, 'Engineering');-- 2. Multi-row insertINSERT INTO Employees (name, salary, dept) VALUES    ('Bob', 65000, 'Marketing'),    ('Carol', 80000, 'Engineering');-- 3. Insert another rowINSERT INTO Employees (name, salary, dept)VALUES ('Dave', 55000, 'Marketing');-- 4. Update specific rowUPDATE EmployeesSET salary = 72000WHERE name = 'Bob';-- 5. Update with different columnUPDATE EmployeesSET dept = 'Engineering'WHERE name = 'Dave';-- 6. Delete specific rowDELETE FROM EmployeesWHERE name = 'Carol';-- 7. Update multiple rows (10% raise)UPDATE Employees SET salary = salary * 1.10WHERE dept = 'Engineering';

The SELECT statement is the most frequently used SQL command. Understanding both its written syntax and logical execution order is essential.

Written Order vs Logical Execution Order

-- 1. INNER JOIN: only matching rowsSELECT e.Name, d.dept_nameFROM Employees eINNER JOIN Departments d ON e.dept_id = d.dept_id;-- 2. LEFT JOIN: all left rows + matchesSELECT e.Name, d.dept_nameFROM Employees eLEFT JOIN Departments d ON e.dept_id = d.dept_id;-- 3. FULL OUTER JOIN: all rows from bothSELECT e.Name, d.dept_nameFROM Employees eFULL OUTER JOIN Departments d ON e.dept_id = d.dept_id;

SELECT    department,    COUNT(*) AS emp_count,    AVG(salary) AS avg_salary,    MAX(salary) AS max_salaryFROM EmployeesGROUP BY departmentHAVING COUNT(*) >= 2ORDER BY avg_salary DESC;

-- 1. Scalar subquery: above overall averageSELECT name, salaryFROM EmployeesWHERE salary > (SELECT AVG(salary) FROM Employees);-- 2. Correlated subquery: above dept averageSELECT e.name, e.salary, e.deptFROM Employees eWHERE e.salary > (    SELECT AVG(e2.salary) FROM Employees e2    WHERE e2.dept = e.dept);

Constraints are rules enforced on table columns to ensure data integrity and consistency. They prevent invalid data from entering the database.

-- 1. Valid rowINSERT INTO Employees VALUES (1, 'Alice', '[email protected]', 75000, 1);-- 2. Valid rowINSERT INTO Employees VALUES (2, 'Bob', '[email protected]', 65000, 2);-- 3. NOT NULL violationINSERT INTO Employees VALUES (3, NULL, '[email protected]', 80000, 1);-- 4. UNIQUE violationINSERT INTO Employees VALUES (3, 'Carol', '[email protected]', 80000, 1);-- 5. CHECK violationINSERT INTO Employees VALUES (3, 'Carol', '[email protected]', -5000, 1);-- 6. FK violationINSERT INTO Employees VALUES (3, 'Carol', '[email protected]', 80000, 999);-- 7. Valid rowINSERT INTO Employees VALUES (3, 'Carol', '[email protected]', 80000, 1);-- 8. NULL FK (allowed)INSERT INTO Employees VALUES (4, 'Dave', '[email protected]', 60000, NULL);

Foreign Key Actions

FOREIGN KEY (department_id)
    REFERENCES Departments(department_id)
    ON DELETE CASCADE      -- Delete child rows when parent deleted
    ON UPDATE CASCADE;     -- Update child FKs when parent PK changes

-- Other options:
-- ON DELETE SET NULL      -- Set FK to NULL when parent deleted
-- ON DELETE RESTRICT      -- Prevent parent deletion if children exist
-- ON DELETE SET DEFAULT   -- Set FK to default value

What is the difference between DDL and DML in SQL?

DDL (Data Definition Language) defines and modifies database structure — commands like CREATE TABLE, ALTER TABLE, and DROP TABLE. DML (Data Manipulation Language) works with the data itself — SELECT to query, INSERT to add, UPDATE to modify, and DELETE to remove rows. DDL changes are typically auto-committed and cannot be rolled back, while DML operations participate in transactions.

When should I use GROUP BY vs HAVING?

GROUP BY groups rows that share common values so aggregate functions (COUNT, AVG, SUM, etc.) can operate on each group. HAVING filters those groups after aggregation. Use WHERE to filter individual rows before grouping, and HAVING to filter groups after. For example: "departments with more than 5 employees" requires GROUP BY department_id HAVING COUNT(*) > 5.

How does SQL handle NULL values?

NULL represents an unknown or missing value. SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. Any comparison with NULL (even NULL = NULL) returns UNKNOWN. Use IS NULL or IS NOT NULL to test for NULLs. Aggregate functions like COUNT(column) ignore NULLs, while COUNT(*) counts all rows. The COALESCE function returns the first non-NULL value from a list of expressions.

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that have matching values in both tables — unmatched rows are excluded. LEFT JOIN (LEFT OUTER JOIN) returns all rows from the left table and matching rows from the right table. If no match exists on the right, NULL values fill in. Use LEFT JOIN when you need all records from one table regardless of matches (e.g., all customers, even those with no orders).

What is the difference between DELETE, TRUNCATE, and DROP?

DELETE removes specific rows (uses WHERE) and can be rolled back within a transaction. TRUNCATE removes all rows but keeps the table structure — it is faster because it deallocates data pages rather than logging individual row deletions, and it resets auto-increment counters. DROP removes the entire table including its structure, data, indexes, and constraints. TRUNCATE and DROP are DDL commands and typically cannot be rolled back.

What are window functions and when should I use them?

Window functions perform calculations across a set of rows related to the current row without collapsing them into groups (unlike GROUP BY). They use the OVER clause with optional PARTITION BY and ORDER BY. Common uses include ranking (ROW_NUMBER, RANK, DENSE_RANK), running totals (SUM OVER), and moving averages. Use them for "top-N per group" queries, cumulative calculations, or when you need both detail and aggregate data in the same result set.