SQL
SQL Cheat Sheet
A one-page reference for everyday SQL, written for SQLite (the engine behind our Try it Yourself editor), with MySQL differences noted. The examples use the sample shop database from the lessons.
SQL basics Lesson
SELECT * FROM Customers; | A statement; end each one with a semicolon |
select / SELECT | Keywords are not case-sensitive; capitals are a convention |
-- comment /* comment */ | One-line and multi-line comments |
'Chennai' | Text values go in single quotes |
"Order Date" | Double quotes are for names with spaces (MySQL: backticks) |
NULL | Means "no value"; it is not 0 or an empty string |
SELECT Lesson
SELECT * FROM Products; | Every column, every row |
SELECT ProductName, Price FROM Products; | Only the columns you name |
SELECT DISTINCT City FROM Customers; | Each value once, no duplicates |
SELECT Price * 2 AS DoublePrice FROM Products; | Calculated column with an alias |
SELECT FirstName || ' ' || LastName FROM Employees; | Join text in SQLite (MySQL: CONCAT()) |
SELECT 2 + 3; | SELECT works without a table too |
WHERE Lesson
WHERE City = 'Chennai' | Rows where the condition is true |
WHERE Price > 100 | Comparison: = <> != < > <= >= |
WHERE Country = 'India' AND State = 'Kerala' | Both conditions must be true |
WHERE Status = 'Pending' OR Status = 'Shipped' | Either condition can be true |
WHERE NOT Country = 'India' | Reverses a condition |
WHERE (A OR B) AND C | Brackets control which test runs first |
WHERE State IS NULL | Find missing values |
WHERE State = NULL | Wrong: never true; use IS NULL |
ORDER BY and LIMIT Lesson
ORDER BY Price | Ascending (smallest first) by default |
ORDER BY Price DESC | Descending (largest first) |
ORDER BY Country, City | Sort by Country, then City within each |
LIMIT 5 | Only the first 5 rows (SQL Server: TOP 5) |
LIMIT 5 OFFSET 10 | Skip 10 rows, then return 5 |
ORDER BY Price DESC LIMIT 3 | The three most expensive items |
Without ORDER BY, the database may return rows in any order.
INSERT, UPDATE, DELETE Lesson
INSERT INTO Categories (CategoryName) VALUES ('Sweets'); | Adds one row; unlisted columns get defaults |
INSERT INTO T (a, b) VALUES (1, 'x'), (2, 'y'); | Adds several rows at once |
UPDATE Products SET Price = 300 WHERE ProductID = 1; | Changes matching rows |
UPDATE Products SET Price = Price * 1.1; | No WHERE: changes every row |
DELETE FROM Orders WHERE Status = 'Cancelled'; | Removes matching rows |
DELETE FROM Orders; | No WHERE: removes every row |
Run the WHERE part as a SELECT first to see which rows will change.
Aggregate functions Lesson
COUNT(*) | Number of rows |
COUNT(State) | Rows where State is not NULL |
COUNT(DISTINCT City) | Number of different values |
SUM(Quantity) | Total |
AVG(Price) | Average (NULLs are ignored) |
MIN(Price) MAX(Price) | Smallest and largest |
ROUND(AVG(Price), 2) | Round to 2 decimal places |
GROUP BY and HAVING Lesson
SELECT City, COUNT(*) FROM Customers GROUP BY City; | One result row per group |
GROUP BY CategoryID | Aggregates are worked out for each group |
HAVING COUNT(*) > 1 | Filters groups, after grouping |
WHERE vs HAVING | WHERE filters rows before grouping; HAVING filters groups |
SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT | The order clauses must be written in |
Every selected column should be either in GROUP BY or inside an aggregate.
JOINs Lesson
FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID | Inner join: only rows that match in both |
LEFT JOIN Orders o ON … | All left rows, NULLs where no match |
o.OrderID, c.CustomerName | Prefix with the table alias to avoid ambiguity |
JOIN … ON … JOIN … ON … | Chain joins to combine three or more tables |
FROM Employees e LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID | Self join: a table joined to itself |
RIGHT JOIN FULL JOIN | SQLite 3.39+ supports both; MySQL has no FULL JOIN |
CROSS JOIN | Every row paired with every row |
LIKE and wildcards Lesson
LIKE 'S%' | Starts with S |
LIKE '%a' | Ends with a |
LIKE '%nn%' | Contains nn anywhere |
LIKE '_a%' | _ is exactly one character; a is second |
NOT LIKE '%Tea%' | Does not contain Tea |
LIKE in SQLite | Not case-sensitive for A–Z (MySQL: depends on collation) |
IN and BETWEEN Lesson
WHERE City IN ('Chennai', 'Madurai') | Matches any value in the list |
WHERE City NOT IN ('Chennai', 'Madurai') | Matches none of them |
WHERE Price BETWEEN 50 AND 100 | Inclusive: 50 and 100 both count |
WHERE OrderDate BETWEEN '2026-01-01' AND '2026-01-31' | Dates stored as YYYY-MM-DD text compare correctly |
WHERE CustomerID IN (SELECT CustomerID FROM Orders) | IN with a subquery |
NOT IN returns no rows if the list contains NULL.
CREATE TABLE Lesson
CREATE TABLE Suppliers (SupplierID INTEGER PRIMARY KEY, Name TEXT); | A new table with its columns |
CREATE TABLE IF NOT EXISTS Suppliers (…); | No error if it already exists |
INTEGER REAL TEXT BLOB NUMERIC | SQLite's storage types |
INT VARCHAR(100) DECIMAL(10,2) DATE | Common MySQL types |
ALTER TABLE Suppliers ADD COLUMN Phone TEXT; | Adds a column |
ALTER TABLE Suppliers RENAME TO Vendors; | Renames a table |
DROP TABLE IF EXISTS Suppliers; | Deletes the table and all its data |
Constraints and keys Lesson
PRIMARY KEY | Unique ID for each row |
INTEGER PRIMARY KEY | SQLite fills it automatically (MySQL: AUTO_INCREMENT) |
NOT NULL | The column must have a value |
UNIQUE | No two rows may share the value |
DEFAULT 'Pending' | Used when INSERT leaves the column out |
CHECK (Price >= 0) | Rejects rows that fail the test |
REFERENCES Customers(CustomerID) | Foreign key: must match a row in that table |
PRAGMA foreign_keys = ON; | SQLite only enforces foreign keys after this |