SQL Basics Cheat Sheet

Basics

SELECT Statement

SELECT column1, column2, ...
FROM table_name;

Selects data from specified columns in a table.

Example:

SELECT first_name, last_name
FROM employees;

SELECT DISTINCT

SELECT DISTINCT column1, column2, ...
FROM table_name;

Selects unique values from specified columns in a table.

Example:

SELECT DISTINCT department
FROM employees;

WHERE Clause

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Filters records based on specified conditions.

Example:

SELECT first_name, last_name
FROM employees
WHERE department = 'Sales';

AND, OR, NOT Operators

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2;

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2;

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Combines multiple conditions in a WHERE clause to refine the selection criteria.

Example:

SELECT first_name, last_name
FROM employees
WHERE department = 'Sales' AND salary > 50000;

SELECT first_name, last_name
FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

SELECT first_name, last_name
FROM employees
WHERE NOT department = 'Sales';

ORDER BY Clause

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC];

Sorts the result set in ascending or descending order.

Example:

SELECT first_name, last_name
FROM employees
ORDER BY last_name ASC;

LIMIT Clause

SELECT column1, column2, ...
FROM table_name
LIMIT number;

Limits the number of rows returned in the result set.

Example:

SELECT first_name, last_name
FROM employees
LIMIT 10;

IN Operator

SELECT column1, column2, ...
FROM table_name
WHERE column IN (value1, value2, ...);

Filters records where the column value matches any value in a list.

Example:

SELECT first_name, last_name
FROM employees
WHERE department IN ('Sales', 'Marketing');

BETWEEN Operator

SELECT column1, column2, ...
FROM table_name
WHERE column BETWEEN value1 AND value2;

Filters records where the column value is within a specified range.

Example:

SELECT first_name, last_name
FROM employees
WHERE salary BETWEEN 40000 AND 60000;

LIKE Operator

SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;

Filters records based on a pattern match using wildcards.

Example:

SELECT first_name, last_name
FROM employees
WHERE first_name LIKE 'A%';

Subqueries

Single Row Subquery

SELECT column1
FROM table_name
WHERE column2 = (SELECT column2 FROM table_name2 WHERE condition);

Returns a single value from the subquery to use in the main query.

Example:

SELECT first_name, last_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');

Multiple Row Subquery

SELECT column1
FROM table_name
WHERE column2 IN (SELECT column2 FROM table_name2 WHERE condition);

Returns multiple values from the subquery to use in the main query.

Example:

SELECT first_name, last_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

Set Operations

UNION

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

Combines the result set of two queries, removing duplicates.

Example:

SELECT first_name FROM employees
UNION
SELECT first_name FROM managers;

UNION ALL

SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

Combines the result set of two queries, including duplicates.

Example:

SELECT first_name FROM employees
UNION ALL
SELECT first_name FROM managers;

INTERSECT

SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2;

Returns only the records that are common to both queries.

Example:

SELECT first_name FROM employees
INTERSECT
SELECT first_name FROM managers;

EXCEPT (or MINUS in some databases)

SELECT column1, column2 FROM table1
EXCEPT
SELECT column1, column2 FROM table2;

Returns records from the first query that are not in the second query.

Example:

SELECT first_name FROM employees
EXCEPT
SELECT first_name FROM managers;

Data Modification

INSERT INTO

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Inserts new rows into a table.

Example:

INSERT INTO employees (first_name, last_name, department_id)
VALUES ('John', 'Doe', 1);

UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Updates existing rows in a table based on a condition.

Example:

UPDATE employees
SET salary = 60000
WHERE department_id = 1;

DELETE

DELETE FROM table_name
WHERE condition;

Deletes rows from a table based on a condition.

Example:

DELETE FROM employees
WHERE department_id = 1;

Views

CREATE VIEW

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Creates a virtual table based on the result set of a query.

Example:

CREATE VIEW sales_employees AS
SELECT first_name, last_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');

DROP VIEW

DROP VIEW view_name;

Deletes an existing view.

Example:

DROP VIEW sales_employees;

Advanced SQL Functions

CASE Statement

SELECT column1,
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE resultN
END
FROM table_name;

Creates conditional logic within a SQL statement.

Example:

SELECT first_name, last_name,
CASE
    WHEN salary > 50000 THEN 'High'
    WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium'
    ELSE 'Low'
END AS salary_range
FROM employees;

COALESCE Function

SELECT COALESCE(column1, column2, ...) AS result
FROM table_name;

Returns the first non-null value in the list.

Example:

SELECT first_name, last_name, COALESCE(email, 'No Email') AS contact_email
FROM employees;

NULLIF Function

SELECT NULLIF(column1, column2) AS result
FROM table_name;

Returns NULL if the two specified expressions are equal, otherwise returns the first expression.

Example:

SELECT first_name, last_name, NULLIF(salary, 0) AS salary
FROM employees;

STRING Functions

SELECT column1, LENGTH(column1), SUBSTRING(column1, start, length), REPLACE(column1, 'search', 'replace')
FROM table_name;

Performs operations on string data.

Example:

SELECT first_name, LENGTH(first_name) AS name_length, SUBSTRING(first_name, 1, 3) AS short_name, REPLACE(first_name, 'a', 'A') AS modified_name
FROM employees;

DATE Functions:

SELECT column1, CURRENT_DATE, DATE_ADD(column1, INTERVAL value unit), DATE_SUB(column1, INTERVAL value unit)
FROM table_name;

Performs operations on date data.

Example:

SELECT first_name, hire_date, CURRENT_DATE AS today, DATE_ADD(hire_date, INTERVAL 1 YEAR) AS next_year_anniversary
FROM employees;

CONCAT Function

SELECT CONCAT(column1, ' ', column2) AS full_column
FROM table_name;

Concatenates two or more strings into one.

Example:

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

Aggregate Functions

COUNT()

SELECT COUNT(column)
FROM table_name;

Returns the number of rows that match the specified criteria.

Example:

SELECT COUNT(*)
FROM employees
WHERE department = 'Sales';

SUM()

SELECT SUM(column)
FROM table_name;

Returns the total sum of a numeric column.

Example:

SELECT SUM(salary)
FROM employees;

AVG()

SELECT AVG(column)
FROM table_name;

Returns the average value of a numeric column.

Example:

SELECT AVG(salary)
FROM employees;

MIN()

SELECT MIN(column)
FROM table_name;

Returns the smallest value of the selected column.

Example:

SELECT MIN(salary)
FROM employees;

MAX()

SELECT MAX(column)
FROM table_name;

Returns the largest value of the selected column.

Example:

SELECT MAX(salary)
FROM employees;

Grouping Data

GROUP BY Clause

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;

Groups rows that have the same values into summary rows.

Example:

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

HAVING Clause

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING COUNT(*) > value;

Filters groups based on aggregate conditions.

Example:

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

Joins

INNER JOIN

SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b ON a.common_column = b.common_column;

Selects records that have matching values in both tables.

Example:

SELECT employees.first_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

LEFT JOIN

SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b ON a.common_column = b.common_column;

Selects all records from the left table and matched records from the right table.

Example:

SELECT employees.first_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

RIGHT JOIN

SELECT a.column1, b.column2
FROM table1 a
RIGHT JOIN table2 b ON a.common_column = b.common_column;

Selects all records from the right table and matched records from the left table.

Example:

SELECT employees.first_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

FULL OUTER JOIN

SELECT a.column1, b.column2
FROM table1 a
FULL OUTER JOIN table2 b ON a.common_column = b.common_column;

Selects all records when there is a match in either left or right table.

Example:

SELECT employees.first_name, departments.department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.department_id;

Creating and Dropping Tables

CREATE TABLE

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

Creates a new table with specified columns and data types.

Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2)
);

DROP TABLE

DROP TABLE table_name;

Deletes a table and its data.

Example:

DROP TABLE employees;

ALTER TABLE (Add Column):

ALTER TABLE table_name
ADD column_name datatype;

Adds a new column to an existing table.

Example:

ALTER TABLE employees
ADD email VARCHAR(100);

ALTER TABLE (Drop Column):

ALTER TABLE table_name
DROP COLUMN column_name;

Removes a column from an existing table.

Example:

ALTER TABLE employees
DROP COLUMN email;

ALTER TABLE (Modify Column):

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Changes the data type of an existing column.

Example:

ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(12, 2);

Constraints

PRIMARY KEY

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    ...
);

Sets a column as the primary key, ensuring unique values and not null.

Example:

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

FOREIGN KEY

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    FOREIGN KEY (column_name) REFERENCES other_table (column_name)
);

Sets a column as a foreign key, creating a relationship with another table.

Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

UNIQUE

CREATE TABLE table_name (
    column1 datatype UNIQUE,
    column2 datatype,
    ...
);

Ensures all values in a column are unique.

Example:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    password VARCHAR(50)
);

CHECK

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    CHECK (condition)
);

Ensures values in a column meet a specific condition.

Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2),
    CHECK (salary > 0)
);

DEFAULT

CREATE TABLE table_name (
    column1 datatype DEFAULT default_value,
    column2 datatype,
    ...
);

Sets a default value for a column if no value is specified.

Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE DEFAULT CURRENT_DATE
);

Indexes

CREATE INDEX

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Creates an index on specified columns to speed up queries.

Example:

CREATE INDEX idx_last_name
ON employees (last_name);

DROP INDEX

DROP INDEX index_name;

Deletes an existing index.

Example:

DROP INDEX idx_last_name;

Transactions

BEGIN TRANSACTION

BEGIN TRANSACTION;

Starts a new transaction.

COMMIT

COMMIT;

Commits the current transaction.

ROLLBACK

ROLLBACK;

Rolls back the current transaction.

ROLLBACK

ROLLBACK;

Rolls back the current transaction.

SAVEPOINT

SAVEPOINT savepoint_name;

Creates a savepoint within a transaction.

ROLLBACK TO SAVEPOINT

ROLLBACK TO SAVEPOINT savepoint_name;

Rolls back to a specific savepoint.

Previous Lesson

Back to Tutorial

Cookie	Duration	Description
cookielawinfo-checkbox-advertisement	1 year	Set by the GDPR Cookie Consent plugin, this cookie is used to record the user consent for the cookies in the "Advertisement" category .
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
CookieLawInfoConsent	1 year	Records the default button state of the corresponding category & the status of CCPA. It works only in coordination with the primary cookie.
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

Cookie	Duration	Description
_ga	2 years	The _ga cookie, installed by Google Analytics, calculates visitor, session and campaign data and also keeps track of site usage for the site's analytics report. The cookie stores information anonymously and assigns a randomly generated number to recognize unique visitors.
_gid	1 day	Installed by Google Analytics, _gid cookie stores information on how visitors use a website, while also creating an analytics report of the website's performance. Some of the data that are collected include the number of visitors, their source, and the pages they visit anonymously.
tk_lr	1 year	The tk_lr is a referral cookie set by the JetPack plugin on sites using WooCommerce, which analyzes referrer behaviour for Jetpack.
tk_or	5 years	The tk_or is a referral cookie set by the JetPack plugin on sites using WooCommerce, which analyzes referrer behaviour for Jetpack.
tk_r3d	3 days	JetPack installs this cookie to collect internal metrics for user activity and in turn improve user experience.
tk_tc	session	JetPack sets this cookie to record details on how user's use the website.