Datatypes

Data Type	Description
`{sql} INT`	Whole numbers
`{sql} DECIMAL(M, N)`	`M`: total number of digits `N`: number of digits after the decimal point
`{sql} FLOAT`	Floating-point real number
`{sql} CHAR(L)`	Fixed-length string of text with length `L`
`{sql} VARCHAR(L)`	Variable-length string of text with a maximum length of `L`
`{sql} TEXT`	Large text string
`{sql} BLOB`	Binary Large OBject
`{sql} DATE`	`YYYY-MM-DD`
`{sql} TIME`	`HH:MM:SS`
`{sql} DATETIME`	`YYYY-MM-DD HH:MM:SS`
`{sql} BOOLEAN`	True or false values

DDL

Data Definition Language

DATABASE

CREATE DATABASE company;
-- This command creates a new database named "company."

USE company;
-- This command selects the database named "company" for further operations.

ALTER DATABASE database_name
SET CHARACTER SET utf8;
-- This command changes the character set of the specified database to UTF-8.

DROP DATABASE company;
-- This command deletes the database named "company" and all its associated data.

TABLE

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  department VARCHAR(50),
  salary DECIMAL(10, 2)
);
-- This command creates a table named "employees" with columns for employee ID, first name, last name, department, and salary. The employee_id column is set as the primary key.

-- PRIMARY KEY: Uniquely Identifies Each Record in a Table
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);
-- employee_id is designated as the primary key, ensuring that each employee record has a unique identifier.
 
 
-- FOREIGN KEY: Establishes a Relationship Between Two Tables
  -- On DELETE CASCADE: Automatically deletes child records
  -- On DELETE SET NULL: Foreign key values in child records are set to NULL
CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(50)
);
 
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) -- ON DELETE option
);
-- department_id column in the employees table is a foreign key that references the department_id column in the departments table, establishing a relationship between the two tables.
 
 
-- UNIQUE: Ensures That All Values in a Column Are Unique
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE
);
-- email column must contain unique values for each employee.
 
 
-- NOT NULL: Ensures That a Column Does Not Contain NULL Values
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL
);
-- first_name and last_name columns must have values and cannot be NULL.
 
 
-- CHECK: Specifies a Condition That Must Be Met for a Column's Value
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  age INT CHECK (age >= 18)
);
-- age column must have a value of 18 or greater due to the CHECK constraint.

-- Add a New Column
ALTER TABLE employees
ADD COLUMN middle_name VARCHAR(50);
-- This command adds a new column named "middle_name" to the "employees" table.
 
 
-- Rename a Column
ALTER TABLE employees
RENAME COLUMN middle_name TO middle_initial;
-- This command renames the "middle_name" column to "middle_initial".
 
 
-- Modify a Column's Data Type
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(12, 2);
-- This command changes the data type of the "salary" column to DECIMAL with a precision of 12 and scale of 2.
 
 
-- Drop a Column
ALTER TABLE employees
DROP COLUMN middle_initial;
-- This command drops the "middle_initial" column from the "employees" table.
 
 
-- Add a Constraint
ALTER TABLE employees
ADD CONSTRAINT chk_age CHECK (age >= 18);
-- This command adds a CHECK constraint to ensure that the age is at least 18.

DROP TABLE employees;
-- This command deletes the entire "employees" table along with all its data.

VIEW

CREATE VIEW high_paid_employees AS
SELECT *
FROM employees
WHERE salary > 60000;
-- This query creates a views named high_paid_employees that contains all employees with a salary greater than 60000.

DROP VIEW IF EXISTS high_paid_employees;
-- This query drops the high_paid_employees view if it exists.

INDEX

CREATE INDEX idx_department ON employees (department);
-- This query creates an index named idx_department on the department column of the employees table.

DROP INDEX IF EXISTS idx_department;
-- This query drops the idx_department index if it exists.

Link to original

DML

Data Manipulation Language

SELECT

SELECT * FROM employees;
-- This query will retrieve all columns from the employees table.

Querying

-- DISTINCT: Select Unique Values From A Column
SELECT DISTINCT department 
FROM employees;
-- This query will return unique department names from the employees table.
 
 
-- WHERE: Filter Rows Based On Specified Conditions
SELECT * 
FROM employees 
WHERE salary > 55000.00;
-- This query will return employees whose salary is greater than 55000.00.
 
 
-- LIMIT: Limit The Number Of Rows Returned In The Result Set
SELECT * 
FROM employees 
LIMIT 3;
-- This query will limit the result set to the first 3 rows.
 
 
-- OFFSET: Skip A Specified Number Of Rows Before Returning The Result Set 
SELECT * 
FROM employees 
OFFSET 2;
-- This query will skip the first 2 rows and return the rest.
 
 
-- CASE: Perform Conditional Logic In A Query
SELECT 
    first_name,
    last_name,
    CASE 
        WHEN salary > 55000 THEN 'High'
        WHEN salary > 50000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_category
FROM employees;
-- This query will categorize employees based on their salary into 'High', 'Medium', or 'Low'.
 
 
-- IF: Conditional Logic in a Query
SELECT 
    first_name,
    last_name,
    IF(salary > 55000, 'Above Average', 'Below Average') AS salary_status
FROM employees;
-- This query checks each employee's salary and categorizes it as 'Above Average' or 'Below Average'.

Filtering (WHERE)

-- WHERE: Filter Rows Based On Specified Conditions
SELECT * 
FROM employees
WHERE department = 'IT';
-- This query will retrieve all employees who work in the IT department.
 
 
-- LIKE: Match A Pattern In A Column
    -- %: zero or more characters
    -- _: single character
SELECT * 
FROM employees
WHERE first_name LIKE 'J%';
-- This query will retrieve all employees whose first name starts with 'J'.
 
 
-- IN: Match Any Value In A List
SELECT * 
FROM employees
WHERE department IN ('HR', 'Finance');
-- This query will retrieve all employees who work in the HR or Finance departments.
 
 
-- BETWEEN: Match Values Within A Specified Range
SELECT * 
FROM employees
WHERE salary BETWEEN 50000 AND 60000;
-- This query will retrieve all employees whose salary is between 50000 and 60000.
 
 
-- IS NULL: Match NULL Values
SELECT * 
FROM employees
WHERE department IS NULL;
-- This query will retrieve all employees where the department is not assigned (NULL).
 
 
-- AND: Combines Multiple Conditions In A WHERE Clause
SELECT * 
FROM employees
WHERE department = 'IT' AND salary > 60000;
-- This query will retrieve employees who work in the IT department and have a salary greater than 60000.
 
 
-- OR: Specifies Multiple Conditions Where Any One Of Them Should Be True
SELECT * 
FROM employees
WHERE department = 'HR' OR department = 'Finance';
-- This query will retrieve employees who work in either the HR or Finance department.
 
 
-- NOT: Negates A Condition
SELECT * 
FROM employees
WHERE NOT department = 'IT';
-- This query will retrieve employees who do not work in the IT department.

Subqueries

-- Single-row Subquery: Returns One Row of Result
SELECT first_name, last_name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
-- In this example, the subquery (SELECT MAX(salary) FROM employees) returns a single row containing the maximum salary, and it's used to filter employees who have the maximum salary.
 
 
-- Multiple-row Subquery: Returns Multiple Rows of Result
SELECT department_name
FROM departments
WHERE department_id IN (SELECT department_id FROM employees);
-- In this example, the subquery (SELECT department_id FROM employees) returns multiple rows containing department IDs, and it's used to filter department names based on those IDs.
 
 
-- Correlated Subquery: References a Column from the Outer Query
SELECT first_name, last_name
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);
/* It is the same as:
SELECT *
FROM employees e
    JOIN
    (SELECT department, AVG(salary) as avg_salary
     FROM employees
     GROUP BY department
    ) as dep_avg
    ON e.department = dep_avg.department
WHERE salary > avg_salary
*/
-- In this example, the subquery (SELECT AVG(salary) FROM employees WHERE department = e.department) is correlated with the outer query by referencing the department column from the outer query. It calculates the average salary for each department and is used to filter employees whose salary is greater than the average salary of their respective department.
 
 
-- Nested Subquery: A Subquery Inside Another Subquery
SELECT first_name, last_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name = 'IT'
);
-- In this example, the subquery (SELECT department_id FROM departments WHERE department_name = 'IT') is nested within the outer query. It retrieves the department ID for the IT department, which is then used in the outer query to filter employees belonging to the IT department.

Joins

-- INNER JOIN: Retrieves Records That Have Matching Values in Both Tables
SELECT * 
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
/* We can filter the values before applying join by specifying condition on ON claues. 
e.g. 
FROM employees JOIN departments ON 
    employees.department_id = departments.department_id 
    AND employees.salary > 10000
    AND departments.department_name = 'HR'
*/
 
-- NATURAL JOIN: Retrieves Records That Have Matching Values Based on the Common Columns
SELECT * 
FROM employees NATURAL JOIN departments
 
-- These queries will retrieve records from both the employees and departments tables where there is a match on the department_id column.

SELECT e1.first_name, e2.first_name
FROM employees e1, employees e2
WHERE e1.employee_id = e2.manager_id;
 
SELECT e1.first_name, e2.first_name
FROM employees e1
JOIN employees e2 ON e1.employee_id = e2.manager_id;
 
-- In this example, the employees table is joined to itself to find employees and their respective managers based on the manager_id column.

-- LEFT JOIN: Retrieves All Records from the Left Table and the Matched Records from the Right Table
SELECT * 
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;
-- This query will retrieve all records from the employees table and only the matching records from the departments table.
 
 
-- RIGHT JOIN: Retrieves All Records from the Right Table and the Matched Records from the Left Table
SELECT * 
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;
-- This query will retrieve all records from the departments table and only the matching records from the employees table.
 
 
-- FULL OUTER JOIN: Retrieves All Records When There Is a Match in Either the Left or Right Table
SELECT * 
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.department_id;
 
SELECT * 
FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id
UNION
SELECT * 
FROM employees RIGHT JOIN departments ON employees.department_id = departments.department_id;
-- This query will retrieve all records from both the employees and departments tables, including unmatched records.

SELECT * 
FROM employees
CROSS JOIN departments;
 
SELECT * 
FROM employees, departments;
 
-- These queries will retrieve all possible combinations of records from the employees and departments tables.

Operators

-- ORDER BY: Sorts the Result Set in Ascending or Descending Order
SELECT * 
FROM employees
ORDER BY salary DESC;
-- This query will retrieve all employees sorted by salary in descending order.
 
 
-- GROUP BY: Groups Rows that have the Same Values into Summary Rows
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
-- This query will group employees by department and count the number of employees in each department.
 
 
-- HAVING: Filter Groups Based on Specified Conditions
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 55000;
-- This query will calculate the average salary for each department and return only those departments where the average salary is greater than 55000.

Set Operations

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
-- This command combines the results from two SELECT statements and removes duplicate rows.

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
-- This command combines the results from two SELECT statements and includes all duplicates.

SELECT column_name(s) FROM table1
INTERSECT
SELECT column_name(s) FROM table2;
-- This command returns only the records that are common to both SELECT statements.

SELECT column_name(s) FROM table1
EXCEPT
SELECT column_name(s) FROM table2;
-- This command returns only the records from the first SELECT statement that are not in the second.

Common Table Expressions

WITH high_paid_employees AS (
    SELECT * FROM employees WHERE salary > 60000
)
SELECT * FROM high_paid_employees;
-- This query uses a common table expression named high_paid_employees to retrieve all employees with a salary greater than 60000.

Functions

Data Aggregation Functions

Function	Description
`{sql} COUNT([DISTINCT] column)`	Returns the number of rows that match a specified condition.
`{sql} SUM(column)`	Returns the total sum of a numeric column.
`{sql} AVG(column)`	Returns the average value of a numeric column.
`{sql} MIN(column)`	Returns the smallest value in a set.
`{sql} MAX(column)`	Returns the largest value in a set.

String Functions

Function	Description
`{sql} LENGTH(string)`	Returns the length of a string.
`{sql} UPPER(string)`	Converts a string to uppercase.
`{sql} LOWER(string)`	Converts a string to lowercase.
`{sql} TRIM(string)`	Removes leading and trailing spaces from a string.
`{sql} CONCAT(string1, string2)`	Concatenates two or more strings.
`{sql} SUBSTRING(string, start, length)`	Returns a substring from a string.

Date and Time Functions

Function	Description
`{sql} NOW()`	Returns the current date and time.
`{sql} CURDATE()`	Returns the current date.
`{sql} DATE_FORMAT(date, format)`	Formats a date value according to a specified format.
`{sql} DATEDIFF(date1, date2)`	Returns the difference between two dates.
`{sql} DATE_ADD(date, INTERVAL value unit)`	Add the interval to the given date
`{sql} DATE_SUB(date, INTERVAL value unit)`	Subtract the interval to the given date
`{sql} YEAR(date)`	Extracts the year from the given date.

NULL Handling Functions

Function	Description
`{sql} IFNULL(column, alternative)`	Returns the expression if it is not NULL; otherwise, returns the alternate value.
`{sql} COALESCE(value1, value2, ...)`	Returns the first non-NULL value in the list of arguments.

Window Functions

SELECT window_function(arguments) OVER (
  [PARTITION BY columns]
  [ORDER BY columns]
  [ROWS | RANGE] frame_specification frame_boundary)
FROM table_name;

Window Functions

Ranking Functions

Function	Description
`{sql} ROW_NUMBER()`	Assigns a unique sequential integer to each row within the partition.
`{sql} RANK()`	Assigns a rank to each row, with the same rank for ties; gaps in the sequence can appear after ties (e.g., 1, 2, 2, 4).
`{sql} DENSE_RANK()`	Similar to `RANK`, but assigns consecutive ranks without gaps, even with ties (e.g., 1, 2, 2, 3).
`{sql} PERCENT_RANK()`	Calculates the relative rank of a row as a percentage (from 0 to 1).
`{sql} NTILE(n)`	Divides the partition into `n` groups (buckets) as equally as possible and assigns a group number to each row.

Aggregate Functions

Function	Description
`{sql} SUM()`	Calculates the sum of an expression over the window frame.
`{sql} AVG()`	Calculates the average of an expression over the window frame.
`{sql} COUNT()`	Counts the number of rows or non-NULL values in the window frame.
`{sql} MIN()`	Returns the minimum value of an expression in the window frame.
`{sql} MAX()`	Returns the maximum value of an expression in the window frame.

Value Functions

Function	Description
`{sql} LAG(column, offset, default)`	Accesses a value from a previous row within the partition based on the specified `offset`.
`{sql} LEAD(column, offset, default)`	Accesses a value from a following row within the partition.
`{sql} FIRST_VALUE(column)`	Returns the value of the specified column from the first row in the window frame.
`{sql} LAST_VALUE(column)`	Returns the value of the specified column from the last row in the window frame.
`{sql} NTH_VALUE(column, n)`	Returns the value of the specified column from the nth row in the window frame.

Frame Specifications

Keyword	Description
`{sql} ROWS`	Defines the frame based on a specific number of rows preceding or following the current row.
`{sql} RANGE`	Defines the frame based on a range of values in the `ORDER BY` column relative to the current row’s value.

Frame Boundaries

Default: {sql} BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Boundary	Description
`{sql} UNBOUNDED PRECEDING`	The frame starts from the first row of the partition.
`{sql} n PRECEDING`	The frame starts `n` rows before the current row (for `ROWS`) or `n` units before the current row’s value in the `ORDER BY` column (for `RANGE`).
`{sql} CURRENT ROW`	The frame starts or ends at the current row.
`{sql} n FOLLOWING`	The frame ends `n` rows after the current row (for `ROWS`) or `n` units after the current row’s value in the `ORDER BY` column (for `RANGE`).
`{sql} UNBOUNDED FOLLOWING`	The frame ends at the last row of the partition.

Examples

SELECT
    sale_id,
    sales_person,
    sale_date,
    amount,
    SUM(amount) OVER (PARTITION BY sales_person ORDER BY sale_date) AS running_total
FROM
    sales;

SELECT
    sales_person,
    SUM(amount) AS total_sales,
    RANK() OVER (ORDER BY SUM(amount) DESC) AS sales_rank
FROM
    sales
GROUP BY
    sales_person;

SELECT
    sale_id,
    sales_person,
    sale_date,
    amount,
    AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma
FROM sales;

INSERT

INSERT INTO employees (employee_id, first_name, last_name, department, salary)
VALUES
    (1, 'John', 'Doe', 'HR', 50000.00),
    (2, 'Jane', 'Smith', 'IT', 60000.00),
    (3, 'Alice', 'Johnson', 'Finance', 55000.00),
    (4, 'Bob', 'Williams', 'IT', 62000.00),
    (5, 'Emily', 'Brown', 'HR', 48000.00);
-- This command inserts sample data into the "employees" table with values for employee ID, first name, last name, department, and salary.

UPDATE

UPDATE employees
SET salary = 55000.00
WHERE employee_id = 1;
-- This query will update the salary of the employee with employee_id 1 to 55000.00.

DELETE

DELETE FROM employees
WHERE employee_id = 5;
-- This query will delete the record of the employee with employee_id 5 from the employees table.

DELETE p1
FROM employees AS p1
JOIN departments AS p2 ON p1.department_id = p2.department_id
WHERE p2.department_name = 'Sales';
-- This query deletes all employees who belong to the 'Sales' department from the employees table.

Link to original

DCL

Transaction Control Language

BEGIN TRANSACTION;
-- This statement starts a new transaction.

COMMIT;
-- This statement saves all changes made during the current transaction.

ROLLBACK;
-- This statement undoes all changes made during the current transaction.

Link to original

My Knowledge Base

Explorer

SQL Cheat-Sheet

Datatypes

DDL

Data Definition Language

DATABASE

TABLE

VIEW

INDEX

DML

Data Manipulation Language

SELECT

Querying

Filtering (WHERE)

Subqueries

Joins

Operators

Set Operations

Common Table Expressions

Functions

Data Aggregation Functions

String Functions

Date and Time Functions

NULL Handling Functions

Window Functions

Window Functions

Frame Specifications

Examples

INSERT

UPDATE

DELETE

DCL

Transaction Control Language

Graph View

Table of Contents

Backlinks