SQL Cheat Sheet

Operation Syntax Example
CREATE TABLE CREATE TABLE table_name (column_name data_type, ...); CREATE TABLE employees (id INT, name VARCHAR(100));
CREATE TABLE with PRIMARY KEY CONSTRAINT CREATE TABLE table_name (column_name data_type PRIMARY KEY, ...); CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(100));
DELETE PRIMARY KEY ALTER TABLE table_name DROP CONSTRAINT constraint_name; ALTER TABLE employees DROP CONSTRAINT pk_employee_id;
CREATE TABLE with COMPOSITE KEY CREATE TABLE table_name (col1 data_type, col2 data_type, PRIMARY KEY (col1, col2)); CREATE TABLE orders (order_id INT, product_id INT, PRIMARY KEY (order_id, product_id));
CREATE TABLE with Foreign Key CREATE TABLE table_name (col1 data_type, col2 data_type, FOREIGN KEY (col2) REFERENCES other_table(column)); CREATE TABLE orders (id INT, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(id));
CREATE TEMPORARY TABLE CREATE TEMPORARY TABLE table_name (column_name data_type, ...); CREATE TEMPORARY TABLE temp_employees (id INT, name VARCHAR(100));
CREATE TABLE with NOT NULL CONSTRAINT CREATE TABLE table_name (column_name data_type NOT NULL, ...); CREATE TABLE employees (id INT NOT NULL, name VARCHAR(100));
CREATE TABLE with CHECK CONSTRAINT CREATE TABLE table_name (column_name data_type, CONSTRAINT constraint_name CHECK (condition)); CREATE TABLE employees (id INT, salary INT, CONSTRAINT chk_salary CHECK (salary > 0));
CREATE TABLE with DEFAULT CONSTRAINT CREATE TABLE table_name (column_name data_type DEFAULT default_value, ...); CREATE TABLE employees (id INT, status VARCHAR(20) DEFAULT 'Active');
CREATE TABLE with UNIQUE CONSTRAINT CREATE TABLE table_name (column_name data_type UNIQUE, ...); CREATE TABLE employees (email VARCHAR(100) UNIQUE, name VARCHAR(100));
DESCRIBE [table_name] DESCRIBE table_name; DESCRIBE employees;
DROP TABLE DROP TABLE table_name; DROP TABLE employees;
TRUNCATE TABLE TRUNCATE TABLE table_name; TRUNCATE TABLE employees;
INSERT INTO INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); INSERT INTO employees (id, name) VALUES (1, 'John');
INSERT with AUTO_INCREMENT INSERT INTO table_name (column2, ...) VALUES (value2, ...); INSERT INTO employees (name) VALUES ('John');
SELECT SELECT column1, column2 FROM table_name [WHERE condition]; SELECT id, name FROM employees WHERE id = 1;
LIKE SELECT column1 FROM table_name WHERE column2 LIKE 'pattern'; SELECT name FROM employees WHERE name LIKE 'J%';
LIMIT SELECT column1 FROM table_name [WHERE condition] LIMIT number; SELECT name FROM employees LIMIT 5;
DISTINCT SELECT DISTINCT column_name FROM table_name; SELECT DISTINCT department_id FROM employees;
AS SELECT column_name AS alias_name FROM table_name; SELECT name AS employee_name FROM employees;
Operators Examples include: =, !=, <, >, <=, >=, AND, OR, BETWEEN, IN, NOT, IS NULL, LIKE. SELECT name FROM employees WHERE salary > 5000 AND department_id = 10;
ORDER BY Clause SELECT * FROM table_name ORDER BY column_name [ASC|DESC]; SELECT * FROM student ORDER BY fname;
SELECT * FROM student ORDER BY fname DESC;
SELECT * FROM student ORDER BY fname DESC, lname ASC;
WHERE Clause SELECT * FROM table_name WHERE condition; SELECT * FROM student WHERE city="Helsinki";
SELECT * FROM student WHERE city="Helsinki" AND gender='M';
SELECT * FROM student WHERE city IN ('Helsinki','Kerava','Espoo');
BETWEEN Clause SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2; SELECT * FROM teachers WHERE salary BETWEEN 3000 AND 4000;
SELECT * FROM teachers WHERE salary BETWEEN 3000 AND 4000 ORDER BY salary;
LIKE Clause SELECT * FROM table_name WHERE column_name LIKE pattern; SELECT * FROM student WHERE fname LIKE 'L%';
SELECT * FROM student WHERE lname LIKE '%ka';
SELECT * FROM student WHERE lname LIKE 'l_i%';
LIMIT Clause SELECT * FROM table_name ORDER BY column_name LIMIT offset, count; SELECT * FROM student ORDER BY lname LIMIT 3;
SELECT * FROM student ORDER BY lname LIMIT 2, 3;
DISTINCT SELECT DISTINCT column_name FROM table_name; SELECT DISTINCT city FROM student;
SELECT COUNT(DISTINCT city) FROM student;
Operators Arithmetic: SELECT 5+5;
Comparison: SELECT * FROM grades WHERE grade > 3;		 Logical: SELECT * FROM student WHERE city="Helsinki" AND gender='M';
INSERT INTO INSERT INTO table_name (columns) VALUES (values); INSERT INTO grades (name, course_name, grade) VALUES ('John', 'Math', 5);
INSERT with AUTO_INCREMENT INSERT INTO table_name (columns) VALUES (values); INSERT INTO students (name) VALUES ('John');
Temporary Tables CREATE TEMPORARY TABLE table_name AS SELECT * FROM existing_table WHERE condition; CREATE TEMPORARY TABLE pekka_grades SELECT * FROM grades WHERE name='Pekka';
Aggregations Examples: SELECT name, AVG(grade) AS 'Average Grade' FROM grades GROUP BY name; SELECT course_name, AVG(grade) FROM grades GROUP BY course_name;
Find Failures or High Scores Examples: SELECT * FROM grades WHERE grade=0; SELECT * FROM grades WHERE grade>3;
Check for NULL Values SELECT * FROM table_name WHERE column_name IS NULL; SELECT * FROM course WHERE teacherId IS NULL;
ALTER TABLE ALTER TABLE table_name action; Examples:
- Rename table: ALTER TABLE mytable RENAME TO table1;
- Change column type: ALTER TABLE table1 MODIFY id TINYINT NOT NULL;
- Rename column: ALTER TABLE table1 CHANGE name fullname VARCHAR(25);
- Add column: ALTER TABLE table1 ADD createdon TIMESTAMP;
- Add multiple columns after a specific column: ALTER TABLE table1 ADD emailid VARCHAR(50) AFTER fullname, ADD city VARCHAR(50) AFTER emailid;
- Drop column: ALTER TABLE table1 DROP COLUMN createdon;
- Add primary key: ALTER TABLE table1 ADD PRIMARY KEY (id);
- Drop primary key: ALTER TABLE table1 DROP PRIMARY KEY;
UPDATE UPDATE table_name SET column_name=value [WHERE condition]; Examples:
- Update all rows: UPDATE author SET country='Finland';
- Update with condition: UPDATE author SET country='Finland' WHERE aut_id='AUT002';
- Update with LIMIT: UPDATE author SET country='Finland' WHERE country='UK' LIMIT 4;
- Increase price by 10%: UPDATE products SET buyPrice = buyPrice + (0.1*buyPrice) WHERE productLine='Motorcycles';
- Add a new column and update:
ALTER TABLE products ADD COLUMN oldbuyPrice DECIMAL(10,2) AFTER buyPrice;
UPDATE products SET oldbuyPrice = buyPrice - (0.2*buyPrice) WHERE productLine='Motorcycles';
DELETE DELETE FROM table_name WHERE condition; Examples:
- Delete specific row: DELETE FROM table1 WHERE id=1;
- Delete rows matching condition: DELETE FROM employees WHERE department_id=5;
INDEX CREATE INDEX index_name ON table_name (column_name);
CREATE UNIQUE INDEX index_name ON table_name (column_name);		 Examples:
- Create a simple index: CREATE INDEX ind_fn ON table3 (fullname);
- Create a composite index: CREATE INDEX in1 ON table3 (fullname, address);
- Show indexes: SHOW INDEXES FROM table3;
- Drop index: DROP INDEX ind_fn ON table3;
CREATE VIEW CREATE VIEW view_name AS SELECT col1, col2, ... FROM table_name WHERE condition; Examples:
- Create a view: CREATE VIEW view1 AS SELECT studentId, fname, lname, city FROM student WHERE city='Helsinki';
- Retrieve records from a view: SELECT * FROM view1;
- Update a view: CREATE OR REPLACE VIEW view1 AS SELECT studentId, fname, lname, city, birthDate, gender FROM student WHERE city='Helsinki';
- Drop a view: DROP VIEW view1;
Inner Join SELECT column_list FROM table_1 INNER JOIN table_2 ON join_condition; Example:
SELECT m.member_id, m.name AS members, c.committee_id, c.name AS committee_name FROM members m INNER JOIN committees c ON c.name = m.name;
Left Join SELECT column_list FROM table_1 LEFT JOIN table_2 ON join_condition; Examples:
- Join members and committees: SELECT m.member_id, m.name AS members, c.committee_id, c.name AS committee FROM members m LEFT JOIN committees c USING(name);
- Find members not in committees: SELECT m.member_id, m.name AS members, c.committee_id, c.name AS committee FROM members m LEFT JOIN committees c USING(name) WHERE c.committee_id IS NULL;
Right Join SELECT column_list FROM table_1 RIGHT JOIN table_2 ON join_condition; Example:
SELECT m.member_id, m.name AS members, c.committee_id, c.name AS committee FROM members m RIGHT JOIN committees c ON c.name = m.name;
Cross Join SELECT select_list FROM table_1 CROSS JOIN table_2; Example:
SELECT m.member_id, m.name AS members, c.committee_id, c.name AS committee FROM members m CROSS JOIN committees c;
Comparison Functions Various comparisons such as BETWEEN, NOT BETWEEN, LIKE, NOT LIKE. - SELECT * FROM employees WHERE salary BETWEEN 3000 AND 4000;
- SELECT * FROM employees WHERE salary NOT BETWEEN 3000 AND 4000;
- SELECT * FROM employees WHERE name LIKE 'J%';
Control Flow Functions CASE, IF(), IFNULL(), NULLIF() - SELECT CASE WHEN salary > 5000 THEN 'High' ELSE 'Low' END AS salary_status FROM employees;
- SELECT IF(salary > 5000, 'High', 'Low') AS salary_status FROM employees;
- SELECT IFNULL(bonus, 0) FROM employees;
String Functions ASCII(), BIN(), LOWER() - SELECT ASCII('A');
- SELECT BIN(12);
- SELECT LOWER('HELLO');
Mathematical Functions DIV, MOD - SELECT 12 DIV 5;
- SELECT 12 MOD 5;
Date and Time Functions Various date manipulation functions - Add 10 days: SELECT ADDDATE('2019-11-25', INTERVAL 10 DAY) AS required_date;
- Add 2 years: SELECT ADDDATE('2019-11-15', INTERVAL 2 YEAR) AS required_date;
- Current date and time: SELECT CURRENT_TIMESTAMP();
- Current date: SELECT CURDATE();
Aggregate Functions COUNT(), MAX(), MIN(), AVG() - Count rows: SELECT COUNT(customerNumber) FROM customers;
- Max salary: SELECT MAX(SALARY) FROM employees;
- Min salary: SELECT MIN(SALARY) FROM employees;
- Average salary: SELECT AVG(SALARY) FROM employees;
Length and Case Functions LENGTH(), LOWER() - Length of string: SELECT LENGTH('hello world');
- Convert to lowercase: SELECT LOWER('HELLO WORLD');
Encryption Functions SHA1(), SHA2() - SELECT SHA1('abc');
- SELECT SHA2('ABC', 224);