SQL Lesson Guide — Structured Lessons

Lesson 1 / 4

🗄️ Intro to SQL and Databases

What SQL is, why it matters, what a database looks like, and the difference between SQL and Excel.

SQL Database Primary Key Foreign Key Northwind

🔍 What is SQL?

Definition

SQL = Structured Query Language — a structured query language.

Unlike languages such as Python or Java, which are "general-purpose languages," SQL is a domain-specific language created exclusively for working with data.

SQL's syntax resembles plain English — commands like SELECT, FROM, WHERE make the code readable and clear.

📜
History: SQL was developed in the 1970s at IBM's labs and was originally called SEQUEL. It has remained the leading language in the world of data ever since — over 50 years!

❓ Why SQL at all, and not Excel?

3 main reasons

1. Big Data — huge volumes of information
When Excel starts to "stutter" (around a million rows) — SQL is only just warming up. SQL processes billions of rows in mere seconds.

2. The "single source of truth" — a central database
Unlike files that float around in emails and keep changing, SQL maintains one central database, prevents errors, and keeps logical relationships intact.

3. Surgical retrieval
Excel loads the entire file into memory. SQL retrieves only the specific data you need — saving memory and shortening run times.

Criterion	Excel	SQL
Row count	up to ~1M rows	billions of rows
Speed	slow with large files	very fast
Data sharing	files passed around	one central database
Security	anyone can edit	permissions and control
Performing calculations	convenient for the average user	powerful and precise

🗃️ What does a database look like?

Database structure

A database is made up of tables. Each table contains columns and rows.

Picture several Excel sheets linked to one another — that's what a database looks like.

Database — the overall database (e.g., Northwind)

Tables — tables within the database (e.g., customers, orders, products)

Columns — columns in a table (e.g., id, first_name, city)

Rows — rows of data (e.g., the details of a specific customer)

🔑 Keys and Relationships

Primary Key

A column that uniquely identifies each row. No two values can be identical. It cannot be NULL.
Example: the id column in a customers table — every customer has their own unique number.

Foreign Key

A column in one table that points to a Primary Key in another table. It creates the relationship between the tables.
Example: the customer_id column in the orders table points to id in the customers table.

💡
Tip: the relationship between a primary key and a foreign key is the basis for the JOIN command — that's how you combine information from different tables.

🏪 The Course Database — Northwind

What is Northwind?

A classic database that simulates a trading company. It includes customers, orders, products, suppliers, and employees. Widely used to teach SQL for decades.

The Main Tables

Lesson 2 / 4

📝 String Functions

Cleaning, extracting, formatting, and standardizing text data. 4 main categories of operations.

UPPER / LOWER LEFT / RIGHT SUBSTRING TRIM / REPLACE LENGTH CONCAT

🧹 Category 1: Cleaning Data

TRIM / LTRIM / RTRIM

Removes extra spaces from the string.
TRIM — both sides | LTRIM — left only | RTRIM — right only

SQL

SELECT TRIM('   hello world   ') AS clean_text;
-- result: 'hello world'

SELECT company, TRIM(company) AS clean_company
FROM customers;

REPLACE

Replaces one substring with another.
REPLACE(column, 'what to replace', 'with what')

SQL

-- remove dashes from phone numbers
SELECT REPLACE(business_phone, '-', '') AS clean_phone
FROM customers;

-- replace UK with United Kingdom
SELECT REPLACE(country_region, 'UK', 'United Kingdom')
FROM customers;

✂️ Category 2: Extracting Data

LEFT / RIGHT

LEFT(column, N) — N characters from the start
RIGHT(column, N) — N characters from the end

SQL

-- first 3 letters of the company name
SELECT company, LEFT(company, 3) AS short_code
FROM customers;

-- last 4 digits of the phone
SELECT RIGHT(business_phone, 4) AS phone_end
FROM customers;

SUBSTRING

SUBSTRING(column, start, length) — extraction from a specific position.
Important: the position starts at 1 (not 0!).

SQL

-- 5 characters starting from the 2nd
SELECT SUBSTRING(product_name, 2, 5) AS middle_text
FROM products;

LENGTH and INSTR

LENGTH(column) — returns the length of the string in characters
INSTR(column, 'char') — returns the position of a substring

SQL

SELECT email_address,
       LENGTH(email_address) AS email_length,
       INSTR(email_address, '@') AS at_position
FROM employees;

🎨 Category 3: Reformatting

CONCAT and CONCAT_WS

CONCAT(a, b, c) — concatenates strings. If any value is NULL — the result is NULL.
CONCAT_WS(separator, a, b, c) — concatenates with a fixed separator and automatically skips NULL.

SQL

-- full address with commas (skips NULL)
SELECT CONCAT_WS(', ', city, state_province, zip_postal_code) AS full_address
FROM customers;

-- price with a $ sign
SELECT CONCAT('$', ROUND(list_price, 2)) AS formatted_price
FROM products;

✅ Category 4: Standardization & Consistency

UPPER / LOWER

UPPER(column) — converts all text to uppercase
LOWER(column) — converts all text to lowercase

Useful for normalizing data before comparison.

SQL

SELECT UPPER(company) AS company_caps
FROM suppliers;

SELECT LOWER(email_address) AS standard_email
FROM employees;

⚠️
Note: UPPER/LOWER change the display only — they don't save the change to the table. For a permanent change you need UPDATE.

Lesson 3 / 4

🔎 WHERE — Filtering Data

Filtering rows by conditions. The most essential tool for real queries — including AND/OR, IN, BETWEEN, LIKE, and IS NULL.

WHERE AND / OR IN BETWEEN LIKE IS NULL

📍 Basic WHERE

Syntax

WHERE comes after FROM and filters rows by a condition.
Comparison operators: = != < > <= >=

SQL

-- products priced over 50
SELECT product_name, list_price
FROM products
WHERE list_price > 50;

-- employees who are not Sales Representatives
SELECT first_name, job_title
FROM employees
WHERE job_title != 'Sales Representative';

🔗 AND / OR — Multiple Conditions

The Difference

AND — both conditions must hold
OR — at least one of the conditions must hold

Parentheses set precedence: WHERE (a OR b) AND c

SQL

-- AND: customers from the USA and New York
SELECT * FROM customers
WHERE country_region = 'USA'
  AND city = 'New York';

-- OR: products from Beverages or Condiments
SELECT product_name FROM products
WHERE category = 'Beverages'
   OR category = 'Condiments';

📋 IN — A List of Values

What is IN?

IN is a convenient shorthand for multiple ORs. More readable and shorter.
WHERE city IN ('Tel Aviv', 'Jerusalem', 'Haifa')

SQL

-- suppliers from 3 cities (shorthand for OR)
SELECT company, city
FROM suppliers
WHERE city IN ('Tokyo', 'London', 'New York');

-- NOT IN: not from these cities
SELECT * FROM customers
WHERE country_region NOT IN ('USA', 'UK');

📏 BETWEEN — A Range of Values

Good to Know

BETWEEN x AND y — includes both endpoints (x and y themselves).
Equivalent to: WHERE col >= x AND col <= y

SQL

-- orders with a shipping fee between 20 and 50
SELECT id, shipping_fee
FROM orders
WHERE shipping_fee BETWEEN 20 AND 50;

🔍 LIKE — Pattern Search

Wildcard Characters

% — any number of characters (including zero)
_ — exactly one character

'%gmail%' — contains gmail | 'j%' — starts with j | '%@gmail.com' — ends with @gmail.com

SQL

-- emails ending in northwindtraders.com
SELECT * FROM employees
WHERE email_address LIKE '%northwindtraders.com';

-- names starting with A
SELECT first_name FROM employees
WHERE first_name LIKE 'A%';

🚫 IS NULL / IS NOT NULL

NULL — A Missing Value

NULL is a missing / unknown value — not zero, not empty!
You can't use = NULL — you must use IS NULL.

SQL

-- orders not yet shipped
SELECT id, order_date
FROM orders
WHERE shipped_date IS NULL;

-- customers with a website
SELECT company, web_page
FROM customers
WHERE web_page IS NOT NULL;

❌
Common mistake! WHERE shipped_date = NULL — this doesn't work in SQL! Always use IS NULL.

Lesson 4 / 4

📦 GROUP BY and Aggregation

Grouping rows and computing statistics — COUNT, SUM, AVG, MIN, MAX. Includes HAVING, ROLLUP, and UNION.

GROUP BY COUNT SUM / AVG MIN / MAX HAVING ROLLUP UNION

📊 Aggregate Functions

The Main Functions

COUNT(*) — counts rows | COUNT(column) — counts non-NULL values
SUM(column) — computes the sum
AVG(column) — computes the average
MIN(column) — the lowest value
MAX(column) — the highest value

SQL

-- statistics on the products table
SELECT
  COUNT(*) AS total_products,
  AVG(list_price) AS avg_price,
  MIN(list_price) AS min_price,
  MAX(list_price) AS max_price
FROM products;

🗂️ GROUP BY — Grouping into Groups

The Important Rule

Every column in SELECT that isn't an aggregate function must appear in GROUP BY.
GROUP BY splits the table into groups and computes an aggregate for each group.

SQL

-- how many products are in each category?
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;

-- average price per category
SELECT category, AVG(list_price) AS avg_price
FROM products
GROUP BY category
ORDER BY avg_price DESC;

🔍 HAVING — Filtering After Grouping

WHERE vs HAVING

WHERE — filters rows before grouping
HAVING — filters groups after grouping

Rule: you can't use aggregate functions (COUNT, SUM…) in WHERE — that's why HAVING is needed.

SQL

-- categories with more than 5 products
SELECT category, COUNT(*) AS cnt
FROM products
GROUP BY category
HAVING COUNT(*) > 5;

-- WHERE (before) + HAVING (after)
SELECT category, AVG(list_price) AS avg
FROM products
WHERE list_price > 10        -- filter before grouping
GROUP BY category
HAVING AVG(list_price) > 30; -- filter after grouping

💡
A trick to remember: WHERE → before GROUP BY (on rows). HAVING → after GROUP BY (on groups).

📈 ROLLUP — Grand Total

What is ROLLUP?

GROUP BY column WITH ROLLUP — adds a grand-total summary row at the end of the results.
The last row contains NULL in the grouping column and an overall total.

SQL

-- count by category + TOTAL
SELECT
  COALESCE(category, '--- TOTAL ---') AS category,
  COUNT(*) AS total
FROM products
GROUP BY category WITH ROLLUP;

🔀 UNION — Combining Results

UNION vs UNION ALL

UNION — combines the results of two queries and removes duplicates
UNION ALL — combines and keeps duplicates (faster)

Requirement: both queries must return the same number of columns with compatible data types.

SQL

-- a combined list of cities from customers and suppliers
SELECT city, 'customer' AS type FROM customers
UNION
SELECT city, 'supplier' AS type FROM suppliers
ORDER BY city;

📋 SQL Order of Execution — Important to Know!

FROM — which table to read from

WHERE — filtering rows

GROUP BY — grouping into groups

HAVING — filtering groups

SELECT — choosing columns to display

ORDER BY — sorting the results

LIMIT — limiting the number of rows

💡
The order in which we write SQL differs from the order the DB executes it. That's why you can't use a SELECT alias inside WHERE.