SQL Basic Cheat Sheet

Structured Query Language (SQL), is a programming language that is used to manage relational databases, and perform operations on the data in them.

Sample Data

1. SIMPLE QUERYING

-- Fetch / retrieve all columns from a table.
SELECT *
FROM table_name;

-- Fetch columns from the table and order the results.
SELECT col1, col2
FROM table_name
ORDER BY col1 DESC;

ALIASES

-- COLUMNS
SELECT col1 AS col_11
FROM table_name;

-- TABLES
SELECT col1, col1_1, col1_2
FROM table_name AS t1
JOIN name_table AS t2
ON t1.col1 = t2.col1_1;

2. FILTERING THE OUTPUT

Comparison

SELECT *
FROM table_name
WHERE col1 BETWEEN range1 AND range2; 

-- aggregate functions
SELECT *
FROM table_name
WHERE col1 > 3;

TEXT OPERATORS

-- List with Names starting with Mar
SELECT * 
FROM employees
WHERE first_name LIKE ('Mar%');

-- list with NAMES ENDING WITH 'rk'
SELECT * 
FROM employees 
WHERE first_name LIKE ('%rk');

-- Others
SELECT  * 
FROM employees
WHERE first_name
NOT IN ('John' , 'Mark', 'Jacob'); 

3. QUERYING MULTIPLE TABLES

-- CROSS JOIN 
-- Connecting two tables
SELECT
    dm.*, d.*
FROM
    dept_manager dm
     CROSS JOIN
    departments d
ORDER BY dm.emp_no, d.dept_no;  

-- INNER JOIN 
-- Null values are also not displayed
SELECT m.dept_no, m.emp_no, d.dept_name
FROM dept_manager_dup m
INNER JOIN
departments_dup d on m.dept_no = d.dept_no
GROUP BY m.emp_no
ORDER BY m.dept_no;

-- LEFT JOIN (interchangeable with LEFT OUTER JOIN)
-- All matching values of the two tables + all values from the left table that matches no values
SELECT m.dept_no, m.emp_no, d.dept_name
FROM dept_manager_dup m
    LEFT JOIN departments_dup d ON m.dept_no = d.dept_no
GROUP BY m.emp_no
ORDER BY m.dept_no;


-- RIGHT JOIN
-- Identical to Left JOIN, with the only difference being that the direction of the operation is inverted. 
SELECT m.dept_no, m.emp_no, m.dept_name
FROM dept_manager_dup m,
     departments_dup d
WHERE m.dept_no = d.dept_no
ORDER BY m.dept_no DESC;

-- SQL Self Join
-- Applied when a table must join itself (Combining rows OF A TABLE with other rows of same table)
-- From the emp_manager table, extract the record data only of those employees who are managers as well.
SELECT *
FROM emp_manager em
ORDER BY em.emp_no;
SELECT em1.*
FROM emp_manager em1
    JOIN emp_manager em2 ON em1.emp_no = em2.manager_no;

4. SUBQUERIES

SQL Sub-queries with IN Nested Inside WHERE Queries inside queries
also called inner queries/nested queries/inner select/ outer select.

SELECT * FROM dept_manager;

SELECT e.first_name, e.last_name
FROM employees e
WHERE e.emp_no IN (
        SELECT dmd.emp_no
        FROM dept_manager_dup dmd
    );

5. SET OPERATIONS

Set operations are used to combine the results of two or more queries into a single result. The combined queries must return the same number of columns and compatible data types. The names of the corresponding columns can be different.

-- UNION
 SELECT
    e.emp_no,  e.first_name, e.last_name,
    NULL AS dept_no, NULL AS from_date
FROM employees_dup e
WHERE e.emp_no = 10001
UNION  -- difference
    SELECT 
        NULL AS emp_no, NULL AS first_name,
        NULL AS last_name, dmd.dept_no,
        dmd.from_date
FROM dept_manager_dup dmd;

-- UNION ALL
 SELECT
    e.emp_no,  e.first_name, e.last_name,
    NULL AS dept_no, NULL AS from_date
FROM employees_dup e
WHERE e.emp_no = 10001
UNION ALL -- difference
    SELECT 
        NULL AS emp_no, NULL AS first_name,
        NULL AS last_name, dmd.dept_no,
        dmd.from_date
FROM dept_manager_dup dmd;

NOTE: I highly recommend MySQL for Data Analytics and Business Intelligence tutorial for SQL beginners with focus on Data analysis.

Comments

[Stu Bowles]

Well done. Thanks!