SQL Quick Reference
Databases · Tables · Queries · Joins · Subqueries · Views
Database Commands
Use these commands to create, select, and remove databases. Always run USE before working on any tables.
CREATE DATABASE temp; -- create a blank database CREATE DATABASE IF NOT EXISTS student; -- warns if already exists DROP DATABASE temp; -- delete permanently DROP DATABASE IF EXISTS mumbai; -- warns if doesn't exist SHOW DATABASES; -- list all databases SHOW TABLES; -- list tables in active database USE college; -- select a database to work in
Always run USE database_name; before creating or querying tables — otherwise SQL won't know where to look.
Creating & Dropping Tables
Define a table's structure with column names and data types. You can set a Primary Key inline or at the end of the definition.
CREATE TABLE student ( id INT PRIMARY KEY, name VARCHAR(50), age INT NOT NULL ); DROP TABLE IF EXISTS student; -- warns if table doesn't exist
For a composite primary key (two columns together = unique identifier):
CREATE TABLE student ( id INT, name VARCHAR(50), age INT, PRIMARY KEY (id, name) -- combination must be unique );
For a foreign key (links to another table's primary key):
CREATE TABLE student ( student_id INT PRIMARY KEY, name VARCHAR(50), subject_id INT, FOREIGN KEY (subject_id) REFERENCES subject(id) );
Must be unique and NOT NULL. One per table.
References a primary key in another table. Allows NULL & duplicates.
Two or more columns that together form a unique row identifier.
Constraints
Constraints enforce rules on column data when rows are inserted or updated.
| Constraint | What it does |
|---|---|
| NOT NULL | Column cannot store a NULL value — a value must always be provided. |
| UNIQUE | All values in the column must be different. |
| PRIMARY KEY | Combines UNIQUE + NOT NULL. Identifies each row. |
| FOREIGN KEY | Links to a primary key in another table. |
| DEFAULT | Assigns a fallback value when no value is provided on insert. |
| CHECK | Validates column data against a custom condition before inserting. |
-- DEFAULT: use when no salary is provided CREATE TABLE salary ( id INT, salary INT DEFAULT 25000 ); INSERT INTO salary(id) VALUES(2); -- salary automatically becomes 25000 -- CHECK: enforce valid values CREATE TABLE city ( id INT PRIMARY KEY, city VARCHAR(50), age INT, CONSTRAINT age_check CHECK (age > 18), CONSTRAINT city_check CHECK (city = 'Delhi') );
Inserting Data
Add rows to a table with INSERT INTO. You can insert all columns at once, or specify which columns to fill.
-- Insert a single row (all columns, order matters) INSERT INTO student VALUES (1, 'Abhay', 32); -- Insert specific columns + multiple rows at once INSERT INTO student (id, name, age) VALUES (3, 'Aman', 22), (4, 'Anup', 33);
SELECT Queries
Retrieve data from a table. Use WHERE to filter rows, ORDER BY to sort, and LIMIT to cap results.
SELECT * FROM student; -- all rows and columns SELECT name, marks FROM student; -- specific columns only SELECT DISTINCT city FROM student; -- remove duplicate values
SELECT * FROM student WHERE city = 'Pune' OR city = 'Delhi'; SELECT * FROM student WHERE marks >= 82 AND city = 'Delhi'; SELECT * FROM student WHERE marks BETWEEN 82 AND 93; SELECT * FROM student ORDER BY city ASC, marks ASC; SELECT * FROM student ORDER BY marks DESC LIMIT 3; -- top 3
Operators in WHERE
Aggregate Functions
Aggregate functions compute a single result from multiple rows. Most useful combined with GROUP BY.
| Function | Returns | Example |
|---|---|---|
| COUNT(col) | Number of non-null rows | COUNT(city) |
| MAX(col) | Highest value in column | MAX(marks) |
| MIN(col) | Lowest value in column | MIN(marks) |
| SUM(col) | Total of all values | SUM(marks) |
| AVG(col) | Average of all values | AVG(marks) |
SELECT COUNT(city) FROM student; SELECT MAX(marks) FROM student; SELECT MIN(marks) FROM student; SELECT SUM(marks) FROM student; SELECT AVG(marks) FROM student;
GROUP BY & HAVING
GROUP BY collapses rows sharing the same column value so you can run aggregates per group. HAVING filters those groups after grouping.
Key difference: WHERE filters rows before grouping. HAVING filters grouped results after aggregation.
SELECT city, COUNT(city) FROM student GROUP BY city; SELECT city, AVG(marks) FROM student GROUP BY city; -- HAVING: only cities where average marks < 80 SELECT city, AVG(marks) FROM student GROUP BY city HAVING AVG(marks) < 80 ORDER BY city DESC;
Clause Execution Order
SQL clauses must be written — and are evaluated — in this exact order:
SELECT column(s) FROM table_name WHERE condition GROUP BY column(s) HAVING condition ORDER BY column(s) ASC;
Joins
Joins combine rows from two tables based on a matching condition (usually a shared ID).
| Join Type | Returns |
|---|---|
| INNER JOIN | Only rows that match in both tables. |
| LEFT JOIN | All rows from the left table + matching rows from right. NULL if no match. |
| RIGHT JOIN | All rows from the right table + matching rows from left. NULL if no match. |
| FULL OUTER | All rows from both tables. In MySQL, use LEFT JOIN UNION RIGHT JOIN. |
| SELF JOIN | Joins a table with itself using aliases to compare rows within the same table. |
SELECT * FROM student INNER JOIN course ON student.id = course.id; SELECT * FROM student LEFT JOIN course ON student.id = course.id; SELECT * FROM student RIGHT JOIN course ON student.id = course.id; -- Full Outer Join (MySQL workaround) SELECT * FROM student LEFT JOIN course ON student.id = course.id UNION SELECT * FROM student RIGHT JOIN course ON student.id = course.id; -- Self Join: match employees to their managers SELECT a.name AS 'Manager', b.name AS 'Employee' FROM empmgr AS a JOIN empmgr AS b ON a.id = b.mgrid;
UNION
Combine results of two SELECT queries into one result set. Both queries must return the same number and type of columns.
Merges results and removes duplicate rows automatically.
Merges results and keeps all duplicates. Faster than UNION.
SELECT name FROM empmgr UNION SELECT name FROM empmgr; -- distinct names only SELECT name FROM empmgr UNION ALL SELECT name FROM empmgr; -- all names including duplicates
Subqueries
A subquery is a SELECT statement nested inside another query. It can appear in WHERE (to filter) or FROM (as a temporary table).
When a subquery is in the FROM clause, it must always be given an alias — e.g. AS temp.
-- Subquery in WHERE: students above average marks SELECT * FROM student WHERE marks > (SELECT AVG(marks) FROM student); -- Subquery in FROM: highest mark among Delhi students SELECT MAX(marks) FROM (SELECT * FROM student WHERE city = 'Delhi') AS temp;
SQL Views
A view is a saved SELECT query stored as a virtual table. You can query it just like a real table — without rewriting the query each time.
Views don't store data themselves. They re-run the underlying query each time they are accessed.
-- Create a view CREATE VIEW view1 AS SELECT name, rollno FROM student; -- Query a view just like a table SELECT * FROM view1; -- Remove the view DROP VIEW view1;