BASIC SQL
QUERY REFERENCE
Every essential query type — DDL · DML · DQL · DCL · TCL · Advanced
§1 CREATE / ALTER / DROP §2 INSERT / UPDATE / DELETE §3 SELECT & Filtering
§4 JOINs §5 Subqueries §6 Aggregates & GROUP BY
§7 Views §8 Transactions §9 Permissions
§10 CTEs & Window Functions §11 Stored Procedures §12 Triggers
-- COMPREHENSIVE BASIC SQL QUERY REFERENCE
-- Covers: DDL, DML, DQL, DCL, TCL, and common query patterns
-- Compatible with: MySQL / PostgreSQL / SQLite (noted where different)
Basic SQL Query Reference Page 1
§1 — DDL — Data Definition Language
1.1 CREATE DATABASE
CREATE DATABASE company_db;
1.2 USE DATABASE (MySQL) / Connect (PostgreSQL uses \c company_db)
USE company_db;
1.3 CREATE TABLE
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100) NOT NULL,
location VARCHAR(100)
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
hire_date DATE,
salary DECIMAL(10,2),
department_id INT,
manager_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id),
FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);
CREATE TABLE projects (
project_id INT PRIMARY KEY,
project_name VARCHAR(100) NOT NULL,
start_date DATE,
end_date DATE,
budget DECIMAL(12,2)
);
CREATE TABLE employee_projects (
employee_id INT,
project_id INT,
role VARCHAR(50),
PRIMARY KEY (employee_id, project_id),
FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
FOREIGN KEY (project_id) REFERENCES projects(project_id)
);
1.4 CREATE TABLE WITH CHECK CONSTRAINT
Basic SQL Query Reference Page 2
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) CHECK (price > 0),
stock INT DEFAULT 0 CHECK (stock >= 0),
category VARCHAR(50)
);
1.5 CREATE TABLE AS SELECT (copy structure + data)
CREATE TABLE employees_backup AS
SELECT * FROM employees;
1.6 ALTER TABLE — Add Column
ALTER TABLE employees
ADD COLUMN phone VARCHAR(20);
1.7 ALTER TABLE — Modify Column
ALTER TABLE employees
MODIFY COLUMN phone VARCHAR(30); -- MySQL
-- ALTER TABLE employees ALTER COLUMN phone TYPE VARCHAR(30); -- PostgreSQL
1.8 ALTER TABLE — Rename Column
ALTER TABLE employees
RENAME COLUMN phone TO phone_number; -- MySQL 8+ / PostgreSQL
1.9 ALTER TABLE — Add Constraint
ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary >= 0);
1.10 ALTER TABLE — Drop Column
ALTER TABLE employees
DROP COLUMN phone_number;
1.11 CREATE INDEX
CREATE INDEX idx_employees_last_name
ON employees (last_name);
1.12 CREATE UNIQUE INDEX
CREATE UNIQUE INDEX idx_employees_email
ON employees (email);
1.13 DROP INDEX
DROP INDEX idx_employees_last_name ON employees; -- MySQL
-- DROP INDEX idx_employees_last_name; -- PostgreSQL
1.14 TRUNCATE TABLE (removes all rows, keeps structure)
TRUNCATE TABLE employees_backup;
1.15 DROP TABLE
DROP TABLE IF EXISTS employees_backup;
1.16 DROP DATABASE
-- DROP DATABASE IF EXISTS company_db; (commented out to preserve demo DB)
Basic SQL Query Reference Page 3
§2 — DML — Data Manipulation Language
2.1 INSERT — single row
INSERT INTO departments (department_id, department_name, location)
VALUES (1, 'Engineering', 'New York');
2.2 INSERT — multiple rows
INSERT INTO departments (department_id, department_name, location)
VALUES
(2, 'Marketing', 'Chicago'),
(3, 'HR', 'Austin'),
(4, 'Finance', 'New York'),
(5, 'Operations', 'Seattle');
2.3 INSERT into employees
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department_id,
manager_id)
VALUES
(1, 'Alice', 'Smith', 'alice@[Link]', '2018-03-15', 95000.00, 1, NULL),
(2, 'Bob', 'Johnson', 'bob@[Link]', '2019-07-22', 82000.00, 1, 1),
(3, 'Carol', 'Williams', 'carol@[Link]', '2020-01-10', 78000.00, 2, NULL),
(4, 'David', 'Brown', 'david@[Link]', '2017-11-05', 110000.00,1, 1),
(5, 'Eve', 'Davis', 'eve@[Link]', '2021-06-30', 67000.00, 3, NULL),
(6, 'Frank', 'Miller', 'frank@[Link]', '2022-02-14', 72000.00, 4, NULL),
(7, 'Grace', 'Wilson', 'grace@[Link]', '2016-08-19', 125000.00,1, 1),
(8, 'Hank', 'Moore', 'hank@[Link]', '2023-03-01', 60000.00, 2, 3),
(9, 'Iris', 'Taylor', 'iris@[Link]', '2015-05-12', 98000.00, 5, NULL),
(10, 'Jack', 'Anderson', 'jack@[Link]', '2024-01-08', 55000.00, 3, 5);
2.4 INSERT — from SELECT
INSERT INTO employees_backup
SELECT * FROM employees WHERE department_id = 1;
2.5 UPDATE — single column
UPDATE employees
SET salary = 100000.00
WHERE employee_id = 1;
2.6 UPDATE — multiple columns
UPDATE employees
SET salary = 85000.00,
hire_date = '2019-01-01'
WHERE employee_id = 2;
2.7 UPDATE — with subquery
Basic SQL Query Reference Page 4
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = (
SELECT department_id FROM departments WHERE department_name = 'Engineering'
);
2.8 UPDATE — with CASE expression
UPDATE employees
SET salary = CASE
WHEN salary < 70000 THEN salary * 1.15
WHEN salary < 90000 THEN salary * 1.10
ELSE salary * 1.05
END;
2.9 DELETE — specific rows
DELETE FROM employees
WHERE employee_id = 10;
2.10 DELETE — with subquery
DELETE FROM employees
WHERE department_id IN (
SELECT department_id FROM departments WHERE location = 'Austin'
);
2.11 DELETE — all rows (keep structure)
DELETE FROM employee_projects;
Basic SQL Query Reference Page 5
§3 — DQL — Data Query Language (SELECT)
-- Select all columns
SELECT * FROM employees;
-- Select specific columns
SELECT first_name, last_name, salary FROM employees;
-- Select with column alias
SELECT
first_name AS "First Name",
last_name AS "Last Name",
salary AS "Annual Salary"
FROM employees;
-- Select with expression
SELECT
first_name,
last_name,
salary / 12 AS monthly_salary
FROM employees;
-- Select distinct values
SELECT DISTINCT department_id FROM employees;
Basic SQL Query Reference Page 6
-- Equality
SELECT * FROM employees WHERE department_id = 1;
-- Comparison operators
SELECT * FROM employees WHERE salary > 80000;
SELECT * FROM employees WHERE salary >= 80000;
SELECT * FROM employees WHERE salary < 80000;
SELECT * FROM employees WHERE salary <= 80000;
SELECT * FROM employees WHERE salary <> 80000; -- not equal
-- AND / OR / NOT
SELECT * FROM employees
WHERE department_id = 1 AND salary > 90000;
SELECT * FROM employees
WHERE department_id = 1 OR department_id = 2;
SELECT * FROM employees
WHERE NOT department_id = 3;
-- BETWEEN
SELECT * FROM employees
WHERE salary BETWEEN 70000 AND 100000;
-- IN
SELECT * FROM employees
WHERE department_id IN (1, 2, 4);
-- NOT IN
SELECT * FROM employees
WHERE department_id NOT IN (3, 5);
-- LIKE — pattern matching
SELECT * FROM employees WHERE last_name LIKE 'S%'; -- starts with S
SELECT * FROM employees WHERE first_name LIKE '%e'; -- ends with e
SELECT * FROM employees WHERE email LIKE '%@company%'; -- contains
-- IS NULL / IS NOT NULL
SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM employees WHERE manager_id IS NOT NULL;
-- Ascending (default)
SELECT * FROM employees ORDER BY last_name ASC;
-- Descending
SELECT * FROM employees ORDER BY salary DESC;
-- Multiple columns
SELECT * FROM employees
ORDER BY department_id ASC, salary DESC;
-- Limit rows returned
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;
-- Pagination (skip first 5, return next 5)
SELECT * FROM employees ORDER BY employee_id LIMIT 5 OFFSET 5;
Basic SQL Query Reference Page 7
SELECT COUNT(*) AS total_employees FROM employees;
SELECT COUNT(manager_id) AS employees_with_mgr FROM employees;
SELECT SUM(salary) AS total_payroll FROM employees;
SELECT AVG(salary) AS avg_salary FROM employees;
SELECT MIN(salary) AS lowest_salary FROM employees;
SELECT MAX(salary) AS highest_salary FROM employees;
SELECT ROUND(AVG(salary), 2) AS avg_salary_rounded FROM employees;
-- Count employees per department
SELECT
department_id,
COUNT(*) AS employee_count,
AVG(salary) AS avg_salary,
SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;
-- Departments with average salary above 80,000
SELECT
department_id,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 80000;
Basic SQL Query Reference Page 8
-- INNER JOIN
SELECT
e.first_name,
e.last_name,
d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
-- LEFT JOIN (all employees, even without a department)
SELECT
e.first_name,
e.last_name,
d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
-- RIGHT JOIN (all departments, even with no employees)
SELECT
d.department_name,
e.first_name,
e.last_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
-- FULL OUTER JOIN (MySQL uses UNION workaround)
SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
-- SELF JOIN (employees and their managers)
SELECT
e.first_name AS employee,
m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
-- CROSS JOIN (cartesian product)
SELECT
e.first_name,
d.department_name
FROM employees e
CROSS JOIN departments d
LIMIT 20;
Basic SQL Query Reference Page 9
-- Subquery in WHERE
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Subquery in FROM (derived table)
SELECT dept_stats.department_id, dept_stats.avg_sal
FROM (
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
) AS dept_stats
WHERE dept_stats.avg_sal > 80000;
-- Subquery with IN
SELECT first_name, last_name
FROM employees
WHERE department_id IN (
SELECT department_id FROM departments WHERE location = 'New York'
);
-- Correlated subquery
SELECT e.first_name, e.last_name, [Link]
FROM employees e
WHERE [Link] > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);
-- EXISTS
SELECT d.department_name
FROM departments d
WHERE EXISTS (
SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);
-- NOT EXISTS
SELECT d.department_name
FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);
Basic SQL Query Reference Page 10
-- UNION (removes duplicates)
SELECT first_name FROM employees WHERE department_id = 1
UNION
SELECT first_name FROM employees WHERE salary > 90000;
-- UNION ALL (keeps duplicates)
SELECT first_name FROM employees WHERE department_id = 1
UNION ALL
SELECT first_name FROM employees WHERE salary > 90000;
-- INTERSECT (employees in dept 1 AND salary > 90k) — PostgreSQL/SQLite
-- SELECT first_name FROM employees WHERE department_id = 1
-- INTERSECT
-- SELECT first_name FROM employees WHERE salary > 90000;
-- EXCEPT / MINUS — PostgreSQL / Oracle
-- SELECT first_name FROM employees WHERE department_id = 1
-- EXCEPT
-- SELECT first_name FROM employees WHERE salary > 90000;
SELECT
first_name,
salary,
CASE
WHEN salary >= 100000 THEN 'Senior'
WHEN salary >= 80000 THEN 'Mid-Level'
WHEN salary >= 60000 THEN 'Junior'
ELSE 'Entry-Level'
END AS salary_band
FROM employees;
-- Simple CASE
SELECT
first_name,
department_id,
CASE department_id
WHEN 1 THEN 'Engineering'
WHEN 2 THEN 'Marketing'
WHEN 3 THEN 'HR'
ELSE 'Other'
END AS dept_label
FROM employees;
SELECT
UPPER(first_name) AS upper_name,
LOWER(last_name) AS lower_name,
CONCAT(first_name, ' ', last_name) AS full_name,
LENGTH(last_name) AS name_length,
SUBSTRING(email, 1, 5) AS email_prefix,
TRIM(' Alice ') AS trimmed,
REPLACE(email, '@[Link]', '') AS username,
LEFT(first_name, 3) AS name_short
FROM employees;
Basic SQL Query Reference Page 11
SELECT
salary,
ROUND(salary, -3) AS rounded_to_thousands,
FLOOR(salary / 1000) AS salary_k_floor,
CEIL(salary / 1000) AS salary_k_ceil,
ABS(salary - 80000) AS deviation_from_80k,
MOD(employee_id, 2) AS even_odd
FROM employees;
SELECT
hire_date,
YEAR(hire_date) AS hire_year, -- MySQL
MONTH(hire_date) AS hire_month,
DAY(hire_date) AS hire_day,
DATEDIFF(CURDATE(), hire_date) AS days_employed, -- MySQL
DATE_ADD(hire_date, INTERVAL 1 YEAR) AS one_year_later,
DATE_FORMAT(hire_date, '%M %d, %Y') AS formatted_date -- MySQL
FROM employees;
SELECT
first_name,
manager_id,
IFNULL(manager_id, 0) AS mgr_or_zero, -- MySQL
COALESCE(manager_id, employee_id, 0) AS first_non_null,
NULLIF(department_id, 1) AS null_if_eng -- returns NULL when dept=1
FROM employees;
Basic SQL Query Reference Page 12
§4 — VIEWS
4.1 Create a view
CREATE VIEW vw_employee_details AS
SELECT
e.employee_id,
CONCAT(e.first_name, ' ', e.last_name) AS full_name,
[Link],
[Link],
d.department_name,
[Link]
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
4.2 Query a view (same as querying a table)
SELECT * FROM vw_employee_details WHERE salary > 80000;
4.3 Drop a view
DROP VIEW IF EXISTS vw_employee_details;
Basic SQL Query Reference Page 13
§5 — TCL — Transaction Control Language
5.1 START TRANSACTION
START TRANSACTION;
5.2 Perform DML within transaction
UPDATE employees SET salary = salary + 5000 WHERE department_id = 1;
INSERT INTO departments (department_id, department_name, location)
VALUES (6, 'Legal', 'Boston');
5.3 COMMIT — save changes permanently
COMMIT;
5.4 ROLLBACK — undo all changes since last commit
START TRANSACTION;
DELETE FROM employees WHERE department_id = 6;
ROLLBACK; -- employees NOT deleted
5.5 SAVEPOINT — partial rollback point
START TRANSACTION;
UPDATE employees SET salary = 99999 WHERE employee_id = 1;
SAVEPOINT before_dept_change;
UPDATE employees SET department_id = 5 WHERE employee_id = 1;
ROLLBACK TO SAVEPOINT before_dept_change; -- only dept change undone
COMMIT;
5.6 RELEASE SAVEPOINT
-- RELEASE SAVEPOINT before_dept_change;
Basic SQL Query Reference Page 14
§6 — DCL — Data Control Language
6.1 CREATE USER
CREATE USER 'hr_user'@'localhost' IDENTIFIED BY 'StrongPass123!';
6.2 GRANT privileges
GRANT SELECT, INSERT, UPDATE ON company_db.employees TO 'hr_user'@'localhost';
6.3 GRANT all on a specific table
GRANT ALL PRIVILEGES ON company_db.departments TO 'hr_user'@'localhost';
6.4 REVOKE privileges
REVOKE INSERT ON company_db.employees FROM 'hr_user'@'localhost';
6.5 Show grants
SHOW GRANTS FOR 'hr_user'@'localhost';
6.6 DROP USER
DROP USER IF EXISTS 'hr_user'@'localhost';
Basic SQL Query Reference Page 15
§7 — ADVANCED SELECT PATTERNS
7.1 CTE — Common Table Expression (WITH clause)
WITH high_earners AS (
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 90000
)
SELECT h.first_name, h.last_name, [Link], d.department_name
FROM high_earners h
JOIN employees e ON h.employee_id = e.employee_id
JOIN departments d ON e.department_id = d.department_id;
7.2 Recursive CTE — org chart hierarchy
WITH RECURSIVE org_chart AS (
-- Anchor: top-level employees (no manager)
SELECT employee_id, first_name, last_name, manager_id, 0 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: employees reporting to someone
SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, [Link] + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart ORDER BY level, last_name;
7.3 Window Functions
SELECT
first_name,
last_name,
department_id,
salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_with_gaps,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank,
SUM(salary) OVER (PARTITION BY department_id) AS dept_total_salary,
AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary,
salary - AVG(salary) OVER (PARTITION BY department_id) AS diff_from_dept_avg,
LAG(salary) OVER (ORDER BY hire_date) AS prev_hire_salary,
LEAD(salary) OVER (ORDER BY hire_date) AS next_hire_salary,
NTILE(4) OVER (ORDER BY salary) AS salary_quartile
FROM employees;
7.4 PIVOT simulation (MySQL — no native PIVOT)
Basic SQL Query Reference Page 16
SELECT
department_id,
SUM(CASE WHEN YEAR(hire_date) = 2019 THEN 1 ELSE 0 END) AS hired_2019,
SUM(CASE WHEN YEAR(hire_date) = 2020 THEN 1 ELSE 0 END) AS hired_2020,
SUM(CASE WHEN YEAR(hire_date) = 2021 THEN 1 ELSE 0 END) AS hired_2021,
SUM(CASE WHEN YEAR(hire_date) = 2022 THEN 1 ELSE 0 END) AS hired_2022
FROM employees
GROUP BY department_id;
7.5 ROLLUP — subtotals and grand total
SELECT
department_id,
COUNT(*) AS employee_count,
SUM(salary) AS total_salary
FROM employees
GROUP BY department_id WITH ROLLUP;
Basic SQL Query Reference Page 17
§8 — STORED PROCEDURE & FUNCTION (MySQL)
8.1 Stored Procedure — no parameters
DELIMITER $$
CREATE PROCEDURE sp_all_employees()
BEGIN
SELECT * FROM employees ORDER BY last_name;
END$$
DELIMITER ;
CALL sp_all_employees();
8.2 Stored Procedure — with IN parameter
DELIMITER $$
CREATE PROCEDURE sp_employees_by_dept(IN dept_id INT)
BEGIN
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = dept_id
ORDER BY salary DESC;
END$$
DELIMITER ;
CALL sp_employees_by_dept(1);
8.3 Stored Procedure — with OUT parameter
DELIMITER $$
CREATE PROCEDURE sp_dept_avg_salary(IN dept_id INT, OUT avg_sal DECIMAL(10,2))
BEGIN
SELECT AVG(salary) INTO avg_sal
FROM employees
WHERE department_id = dept_id;
END$$
DELIMITER ;
CALL sp_dept_avg_salary(1, @avg);
SELECT @avg AS engineering_avg_salary;
8.4 User-Defined Function
DELIMITER $$
CREATE FUNCTION fn_annual_to_monthly(annual_salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN ROUND(annual_salary / 12, 2);
END$$
DELIMITER ;
SELECT first_name, salary, fn_annual_to_monthly(salary) AS monthly_salary
FROM employees;
8.5 Drop stored procedure / function
Basic SQL Query Reference Page 18
DROP PROCEDURE IF EXISTS sp_all_employees;
DROP FUNCTION IF EXISTS fn_annual_to_monthly;
Basic SQL Query Reference Page 19
§9 — TRIGGERS
9.1 BEFORE INSERT trigger — auto-format email to lowercase
DELIMITER $$
CREATE TRIGGER trg_before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
SET [Link] = LOWER([Link]);
END$$
DELIMITER ;
9.2 AFTER UPDATE trigger — audit log (requires audit table)
CREATE TABLE salary_audit (
audit_id INT AUTO_INCREMENT PRIMARY KEY,
employee_id INT,
old_salary DECIMAL(10,2),
new_salary DECIMAL(10,2),
changed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
DELIMITER $$
CREATE TRIGGER trg_after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF [Link] <> [Link] THEN
INSERT INTO salary_audit (employee_id, old_salary, new_salary)
VALUES (OLD.employee_id, [Link], [Link]);
END IF;
END$$
DELIMITER ;
9.3 Drop trigger
DROP TRIGGER IF EXISTS trg_before_insert_employee;
DROP TRIGGER IF EXISTS trg_after_salary_update;
Basic SQL Query Reference Page 20
§10 — INDEXES IN DEPTH
-- Single-column index
CREATE INDEX idx_emp_dept ON employees (department_id);
-- Composite index
CREATE INDEX idx_emp_dept_salary ON employees (department_id, salary);
-- Covering index (includes extra columns to avoid table lookups)
CREATE INDEX idx_emp_cover ON employees (department_id, salary, first_name, last_name);
-- SHOW indexes on a table (MySQL)
SHOW INDEX FROM employees;
-- Drop indexes
DROP INDEX idx_emp_dept ON employees;
DROP INDEX idx_emp_dept_salary ON employees;
DROP INDEX idx_emp_cover ON employees;
-- END OF REFERENCE FILE
Basic SQL Query Reference Page 21