DEV Community

Cover image for SQL Basic Cheat Sheet
Ochwada Linda
Ochwada Linda

Posted on

11 6

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

Sql 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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

2. FILTERING THE OUTPUT

Comparison

SELECT *
FROM table_name
WHERE col1 BETWEEN range1 AND range2; 

-- aggregate functions
SELECT *
FROM table_name
WHERE col1 > 3;
Enter fullscreen mode Exit fullscreen mode

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'); 
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
    );
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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

👋 Was this post useful to you?

Why haven't you joined DEV yet?

It takes one minute to join DEV and is worth it for your career.

Join now

Top comments (1)

Collapse
 
stubowles profile image
Stu Bowles •

Well done. Thanks!

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay