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.

My Knowledge Base

Explorer

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

Graph View

Table of Contents

Backlinks