SQL Basics Cheat Sheet & Quick Reference

Create Database:

Creates a new database.

CREATE DATABASE my_database;

Use:

Selects a database to use.

USE my_database;

Alter Database:

Modifies an existing database.

ALTER DATABASE my_database
MODIFY NAME = new_database_name;

Drop Database:

Deletes an existing database.

DROP DATABASE my_database;

Create Table:

Creates a new table.

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    age INT
);

Create Index:

Creates an index for faster query performance.

CREATE INDEX idx_employee_name
ON employees (name);

Insert into:

Inserts new rows into a table.

INSERT INTO employees (id, name, age)
VALUES (1, 'Alice', 30);

Alter Table:

Modifies an existing table.

ALTER TABLE employees
ADD COLUMN department VARCHAR(50);

Drop Table:

Deletes an entire table.

DROP TABLE employees;

Select:

Retrieves data from a table.

SELECT * FROM employees;

Distinct:

Retrieves unique values from a column.

SELECT DISTINCT department FROM employees;

Limit:

Limits the number of rows returned by a query.

SELECT * FROM employees
LIMIT 5;

Offset:

Specifies an offset for the rows returned by a query.

SELECT * FROM employees
LIMIT 5 OFFSET 10;

Fetch:

Retrieves a specific number of rows.

SELECT * FROM employees
FETCH FIRST 5 ROWS ONLY;

Case:

Provides conditional logic in a query.

SELECT name,
  CASE
    WHEN age < 30 THEN 'Young'
    ELSE 'Experienced'
  END as experience
FROM employees;

Update:

Modifies existing rows in a table.

UPDATE employees
SET age = 31
WHERE id = 1;

Column constraints:

Sets rules for column values.

ALTER TABLE employees
ADD CONSTRAINT unique_name UNIQUE (name);

Primary key:

Uniquely identifies each row.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

Unique:

Ensures all values in a column are unique.

ALTER TABLE employees
ADD CONSTRAINT unique_name UNIQUE (name);

Not null:

Ensures a column cannot have NULL values.

ALTER TABLE employees
MODIFY COLUMN name VARCHAR(50) NOT NULL;

Default:

Sets a default value for a column.

ALTER TABLE employees
ADD COLUMN hire_date DATE DEFAULT CURRENT_DATE;

Where:

Filters records based on a condition.

SELECT * FROM employees
WHERE age > 30;

Like:

Filters records using pattern matching.

SELECT * FROM employees
WHERE name LIKE 'A%';

In:

Filters records that match a list of values.

SELECT * FROM employees
WHERE department IN ('HR', 'IT');

Between:

Filters records within a range.

SELECT * FROM employees
WHERE age BETWEEN 25 AND 35;

Is Null:

Filters records with NULL values.

SELECT * FROM employees
WHERE department IS NULL;

Order by:

Sorts records in ascending or descending order.

SELECT * FROM employees
ORDER BY name ASC;

AND:

Combines multiple conditions.

SELECT * FROM employees
WHERE age > 30 AND department = 'IT';

OR:

At least one of the conditions must be true.

SELECT * FROM employees
WHERE age > 30 OR department = 'HR';

NOT:

Excludes specified condition.

SELECT * FROM employees
WHERE NOT department = 'HR';

LIKE:

Searches for a specified pattern.

SELECT * FROM employees
WHERE name LIKE 'A%';

IN:

Matches any value in a list.

SELECT * FROM employees
WHERE department IN ('HR', 'Finance');

Between:

Matches values within a range.

SELECT * FROM employees
WHERE age BETWEEN 25 AND 35;

IS NULL:

Matches NULL values.

SELECT * FROM employees
WHERE department IS NULL;

ORDER BY:

Sorts the result set.

SELECT * FROM employees
ORDER BY age DESC;

GROUP BY:

Groups rows sharing a property.

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

COUNT:

Counts the number of rows.

SELECT COUNT(*) FROM employees;

SUM:

Calculates the sum of a column.

SELECT SUM(salary) FROM employees;

AVG:

Calculates the average value.

SELECT AVG(age) FROM employees;

MIN:

Finds the minimum value.

SELECT MIN(age) FROM employees;

MAX:

Finds the maximum value.

SELECT MAX(age) FROM employees;

GROUP BY:

Groups rows that have the same values.

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

HAVING:

Filters groups based on a condition.

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

PRIMARY KEY:

Uniquely identifies each row in a table.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

FOREIGN KEY:

Uniquely identifies a row in another table.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    employee_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

UNIQUE:

Ensures all values in a column are unique.

ALTER TABLE employees
ADD CONSTRAINT unique_name UNIQUE (name);

NOT NULL:

Ensures a column cannot have NULL values.

ALTER TABLE employees
MODIFY COLUMN name VARCHAR(50) NOT NULL;

CHECK:

Ensures a column meets a condition.

ALTER TABLE employees
ADD CONSTRAINT check_age CHECK (age >= 18);

OUTER JOIN:

Returns rows when there is a match in one of the tables.

SELECT employees.name, orders.order_id
FROM employees
LEFT JOIN orders ON employees.id = orders.employee_id;

WITH:

Creates a named temporary result set.

WITH department_count AS (
    SELECT department, COUNT(*) AS num
    FROM employees
    GROUP BY department
)
SELECT * FROM department_count;

UNION:

Combines the result sets of two queries.

SELECT name FROM employees
UNION
SELECT name FROM managers;

CROSS JOIN:

Returns the Cartesian product of both tables.

SELECT employees.name, departments.name
FROM employees
CROSS JOIN departments;

INNER JOIN:

Returns rows with a match in both tables.

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

Aggregate Functions:

Calculates average value.

SELECT AVG(salary) FROM employees;

String Functions:

Concatenates two or more strings.

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

Extracts a substring from a string:

SELECT SUBSTR(name, 1, 3) AS short_name FROM employees;

Inserts a substring into a string:

SELECT INSERT(name, 1, 0, 'Dr. ') AS titled_name FROM employees;

Retrieves the current date:

SELECT CURRENT_DATE;

SQRT():

Calculates the square root of a number.

SELECT SQRT(salary) FROM employees;