SQL Basic Cheat Sheet
Tags: sql, programming, beginners, tutorial
Published: 2022-04-30 20:06:24

**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 
![Sql Sample data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cgstx09ahgttzqhqdls6.png)

### 1. SIMPLE QUERYING 

```SQL
-- 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 
```SQL
-- 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
```SQL
SELECT *
FROM table_name
WHERE col1 BETWEEN range1 AND range2; 

-- aggregate functions
SELECT *
FROM table_name
WHERE col1 > 3;
```
#### TEXT OPERATORS
```SQL
-- 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

```SQL
-- 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.
```SQL
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.
```SQL
-- 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](https://www.udemy.com/course/sql-mysql-for-data-analytics-and-business-intelligence/learn/lecture/8692218#notes) tutorial for SQL beginners with focus on Data analysis.