SQL Guide

Overview

This guide is intended to be a general [[SQL]] reference for data engineers. It is not specific to any particular [[SQL#SQL Variants|variant of SQL]]. It also does not cover every concept or feature of SQL - only the most important or commonly used ones in data engineering.

[!info]- ### SQL Learning Resources ![[Learning Resources#SQL Learning Resources]]

1. Beginner SQL

Order of Operations

SQL executes each clause in a query in a defined order.

FROM, including JOINs
WHERE
GROUP BY
HAVING
WINDOW functions
SELECT
DISTINCT
UNION
ORDER BY
LIMIT and OFFSET

Basic Commands

SELECT

Used to select data from a database
The data returned is stored in a result table, called the result-set.

FROM

Used to specify which table to select or delete data from.

WHERE

Used to filter records.
It is used to extract only those records that fulfill a specified condition.

ORDER BY

Used to sort the result set in ascending or descending order.
Sorts the result set in ascending order by default. To sort the records in descending order, use the DESC keyword.

Joins

INNER JOIN

Returns only those records or rows that have matching values and is used to retrieve data that appears in both tables.

LEFT JOIN

Gives the output of the matching rows between both tables. In case, no records match from the left table, it shows those records with null values.

RIGHT JOIN

Gives the output of the matching rows between both tables. In case, no records match from the right table, it shows those records with null values.

FULL (OUTER) JOIN

Will retrieve not only the matching rows but also the unmatched rows as well.

CROSS (CARTESIAN) JOIN

Joins every row from the first table with every row from the second table and its result comprises all combinations of records in two tables.

SELF JOIN

Joins a table to itself.

UNION vs UNION ALL

UNION and UNION ALL are both used to retrieve records from multiple tables. Both UNION and UNION ALL are known as set operators. In SQL, set operators combine the results of two or more queries into a single result.

There is one major difference:

UNION only returns unique
UNION ALL returns all records, including duplicates.

Example: The columns in both SELECT statements are of the same or matching data types.

SELECT column_1, column_2
FROM table_1
[WHERE condition]
 
UNION [ALL]
 
SELECT column_1, column_2
FROM table_2
[WHERE condition]

Filtering Data

Filtering data with SQL is useful for returning desired reults from a dataset or table. Filtering can be accomplsihed with the WHERE clause.

Examples:

Filter with logical operators

AND operator

# this will return rows where the City column has a value of "London" and the Country column has a value of "UK"

SELECT *
FROM Customers
WHERE City = "London" AND Country = "UK"

OR operator

# this will return rows where the City column has a value of either "London" or "Paris"

SELECT *
FROM Customers
WHERE City = "London" OR City = "Paris"

BETWEEN operator

# this will return rows where the Price column has values that are between 50 and 60

SELECT *
FROM Products
WHERE Price BETWEEN 50 AND 60

LIKE operator

# this will return rows where the City column has values that start with 'S' with no character limit

SELECT *
FROM Customers
WHERE City LIKE 'S%'

>> Santiago, Sydney, San Antonio

# the '%' wildcard can placed any where in a string to try and find a match

SELECT *
FROM Customers
WHERE City LIKE '%ar%'

>> Paris, Barcelona, Jakarta

# the '_' wildcard can also be used in conjuction with '%' to find a single value
# this will return rows where 'a' has to be the second character and the rest of the characters can be anything

SELECT *
FROM Customers
WHERE City LIKE '_a%'

>> Lagos, Manila, Cairo

Limit

Used to specify the number of records to return. Different database systems use their own syntax:

SQL Server = SELECT TOP 3 * FROM Customers;
MySQL = SELECT * FROM Customers LIMIT 3;
Oracle = SELECT * FROM Customers WHERE ROWNUM <= 3

Where vs Having

WHERE
- Introduces a condition on individual rows.
- Use before GROUP BY clause.
HAVING
- Introduces a condition on aggregations, i.e. results of selection where a single result, such as COUNT(), SUM(), MAX(), MIN() has been produced from multiple rows.
- Use after GROUP BY clause.

2. Intermediate SQL

CTE vs Subquery

CTE

Can be used multiple times in the body of a query.
Allows for recursive queries.
Generally more readable.

Subquery

Can only be used once in a query.
Can be used to filter results in the WHERE clause.
Can be used as a column in your query.

Table vs View vs Materialized View

Table	View	Materialized View
A table contains records (rows) with data values for specified columns (fields) and is stored physically in a database. Tables can be joined together to create reports, manipulated through SQL queries, and changed directly by updating or deleting individual rows or columns.	A view is a virtual table based on the results of a SQL statement. It does not physically exist in the database and the fields in the view are fields from one or more real tables in the database.	A materialized view is a snapshot of a query saved as a physical object within the database. Materialized views can be used instead of tables and support all operations available to real tables with some drawbacks such as maintenance costs.
Data physically stored in database	Data not physically stored	Data physically stored in database
Faster to query	Slower to query	Faster to query
	If underlying table is dropped then view will no longer work	Materialized view will continue to work if underlying table is dropped
No additional maintenance required	No additional maintenance required	Needs to be updated as new/updated data arrive in underlying table

Case Statements

Case statements are SQL’s version of an if-else logic. The CASE expression goes through conditions and returns a value when the first condition is met. Once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

Example:

--Sytanx
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

--Example
SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
    WHEN Quantity = 30 THEN 'The quantity is 30'
    ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

DML vs DDL

DML

DML is short name of Data Manipulation Language which deals with data manipulation, and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE etc, and it is used to store, modify, retrieve, delete and update data in database.

SELECT – retrieve data from one or more tables.
INSERT – insert data into a table.
UPDATE – updates existing data within a table.
DELETE – delete all records from a table.
MERGE – UPSERT operation (insert or update)
CALL – call a PL/SQL or Java subprogram.
EXPLAIN PLAN – interpretation of the data access path.
LOCK TABLE – concurrency control.

DDL

DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.

CREATE – to create database and its objects like (table, index, views, store procedure, function and triggers).
ALTER – alters the structure of the existing database.
DROP – delete objects from the database.
TRUNCATE – remove all records from a table; also, all spaces allocated for the records are removed.
COMMENT – add comments to the data dictionary.
RENAME – rename an object.

(Source: StackOverflow)

Aggregate Functions

An SQL aggregate function calculates on a set of values and returns a single value. For example, the average function (AVG) takes a list of values and returns the average.

Other basic aggregate functions include:

COUNT() – returns the number of items in a set.
MAX() – returns the maximum value in a set.
MIN() – returns the minimum value in a set
SUM() – returns the sum of all or distinct values in a set

Note that aggregate functions do not work within in a WHERE clause due to the order of evaluation of clauses. Instead, GROUP BY and HAVING clases are used in place of a WHERE clause.

3. Advanced SQL

Window Functions

Online Reference

Window functions perform calculations on a set of rows that are related together, but, unlike aggregate functions, windowing functions do not collapse the result of the rows into a single value. Instead, all the rows maintain their original identity and the calculated result is returned for every row.

![[Assets/window-vs-aggregate-function.png]]

Correlated Subqueries

![[Correlated Subquery]]