SQL Basics Cheat Sheet
Creating a Database Creating Data
Create Database: Create Table: Alter Table:
- Creates a new database. - Creates a new table. - Modifies an existing table.
Unset Unset Unset
CREATE DATABASE my_database; CREATE TABLE employees ( ALTER TABLE employees
id INT, ADD COLUMN department VARCHAR(50);
name VARCHAR(50),
age INT
Use:
);
Drop Table:
- Selects a database to use.
- Deletes an entire table.
Create Index:
Unset
USE my_database; - Creates an index for faster query performance. Unset
DROP TABLE employees;
Unset
Alter Database:
CREATE INDEX idx_employee_name
ON employees (name);
- Modifies an existing database.
Unset Insert into:
ALTER DATABASE my_database
MODIFY NAME = new_database_name; - Inserts new rows into a table.
Unset
Drop Database:
INSERT INTO employees (id, name, age)
VALUES (1, 'Alice', 30);
- Deletes an existing database.
Unset
DROP DATABASE my_database;
Reading & Querying Data Updating & Manipulating Data
Select: Fetch: Update:
- Retrieves data from a table. - Retrieves a specific number of rows. - Modifies existing rows in a table.
Unset Unset Unset
SELECT * FROM employees; SELECT * FROM employees UPDATE employees
FETCH FIRST 5 ROWS ONLY; SET age = 31
WHERE id = 1;
Distinct:
Case:
- Retrieves unique values from a column. Column constraints:
- Provides conditional logic in a query.
- Sets rules for column values.
Unset
SELECT DISTINCT department FROM employees; Unset
SELECT name, Unset
CASE ALTER TABLE employees
WHEN age < 30 THEN 'Young' ADD CONSTRAINT unique_name UNIQUE (name);
Limit:
ELSE 'Experienced'
END as experience
- Limits the number of rows returned by a query.
FROM employees;
Primary key:
Unset - Uniquely identifies each row.
SELECT * FROM employees
LIMIT 5;
Unset
CREATE TABLE employees (
id INT PRIMARY KEY,
Offset:
name VARCHAR(50)
);
- Specifies an offset for the rows returned by a query.
Unset
SELECT * FROM employees
LIMIT 5 OFFSET 10;
Updating & Manipulating Data (continued) Filtering Data
Unique: Where: Is Null:
- Ensures all values in a column are unique. - Filters records based on a condition. - Filters records with NULL values.
Unset Unset Unset
ALTER TABLE employees SELECT * FROM employees SELECT * FROM employees
ADD CONSTRAINT unique_name UNIQUE (name); WHERE age > 30; WHERE department IS NULL;
Not null: Like: Order by:
- Ensures a column cannot have NULL values. - Filters records using pattern matching. - Sorts records in ascending or descending order.
Unset Unset Unset
ALTER TABLE employees SELECT * FROM employees SELECT * FROM employees
MODIFY COLUMN name VARCHAR(50) NOT NULL; WHERE name LIKE 'A%'; ORDER BY name ASC;
Default: In:
- Sets a default value for a column. - Filters records that match a list of values.
Unset Unset
ALTER TABLE employees SELECT * FROM employees
ADD COLUMN hire_date DATE DEFAULT CURRE WHERE department IN ('HR', 'IT');
Between:
- Filters records within a range.
Unset
SELECT * FROM employees
WHERE age BETWEEN 25 AND 35;
SQL Operators
AND: IN: GROUP BY:
- Combines multiple conditions. - Matches any value in a list. - Groups rows sharing a property.
Unset Unset Unset
SELECT * FROM employees SELECT * FROM employees SELECT department, COUNT(*)
WHERE age > 30 AND department = 'IT'; WHERE department IN ('HR', 'Finance'); FROM employees
GROUP BY department;
OR: Between:
- At least one of the conditions must be true. - Matches values within a range.
Unset Unset
SELECT * FROM employees SELECT * FROM employees
WHERE age > 30 OR department = 'HR'; WHERE age BETWEEN 25 AND 35;
NOT: IS NULL:
- Excludes specified condition. - Matches NULL values.
Unset Unset
SELECT * FROM employees SELECT * FROM employees
WHERE NOT department = 'HR'; WHERE department IS NULL;
LIKE: ORDER BY:
- Searches for a specified pattern. - Sorts the result set.
Unset Unset
SELECT * FROM employees SELECT * FROM employees
WHERE name LIKE 'A%'; ORDER BY age DESC;
Aggregate Data Constraints
COUNT: MAX: PRIMARY KEY:
- Counts the number of rows. - Finds the maximum value. - Uniquely identifies each row in a table.
Unset Unset Unset
SELECT COUNT(*) FROM employees; SELECT MAX(age) FROM employees; CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50)
);
SUM: GROUP BY:
- Calculates the sum of a column. - Groups rows that have the same values.
FOREIGN KEY:
Unset Unset - Uniquely identifies a row in another table.
SELECT SUM(salary) FROM employees; SELECT department, COUNT(*)
FROM employees
GROUP BY department; Unset
CREATE TABLE orders (
AVG:
order_id INT PRIMARY KEY,
employee_id INT,
- Calculates the average value. HAVING:
FOREIGN KEY (employee_id) REFERENCES employees(id)
);
- Filters groups based on a condition.
Unset
SELECT AVG(age) FROM employees;
Unset UNIQUE:
SELECT department, COUNT(*)
FROM employees - Ensures all values in a column are unique.
MIN:
GROUP BY department
HAVING COUNT(*) > 5;
- Finds the minimum value. Unset
ALTER TABLE employees
Unset ADD CONSTRAINT unique_name UNIQUE (name);
SELECT MIN(age) FROM employees;
Multiple Tables SQL Functions
OUTER JOIN: CROSS JOIN: Aggregate Functions:
- Returns rows when there is a match in one of the tables. - Returns the Cartesian product of both tables. - SELECT AVG: Calculates average value.
Unset Unset Unset
SELECT employees.name, orders.order_id SELECT employees.name, departments.name SELECT AVG(salary) FROM employees;
FROM employees FROM employees
LEFT JOIN orders ON employees.id = orders.employee_id; CROSS JOIN departments;
String Functions:
WITH: INNER JOIN: - SELECT CONCAT: Concatenates two or more strings.
Unset
- Creates a named temporary result set. - Returns rows with a match in both tables.
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM
employees;
Unset Unset
WITH department_count AS ( SELECT employees.name, departments.name
SELECT department, COUNT(*) AS num FROM employees
- SELECT SUBSTR: Extracts a substring from a string.
FROM employees INNER JOIN departments ON employees.department
GROUP BY department Unset
) SELECT SUBSTR(name, 1, 3) AS short_name FROM employees;
SELECT * FROM department_count;
- SELECT INSERT: Inserts a substring into a string.
UNION:
Unset
- Combines the result sets of two queries. SELECT INSERT(name, 1, 0, 'Dr. ') AS titled_name FROM
employees;
Unset
SELECT name FROM employees
- SELECT CURRENT_DATE: Retrieves the current date.
UNION
SELECT name FROM managers; Unset
SELECT CURRENT_DATE;
- SQRT(): Calculates the square root of a number.
Unset
SELECT SQRT(salary) FROM employees
