What is SQL?

SQL stands for Structured Query Language. It's the standard language used to communicate with databases. Think of a database as a massive, organized collection of digital file cabinets 🗄️. SQL is the language you use to ask that file cabinet to find, add, change, or remove files.

We'll be focusing on Relational Databases, which store data in tables. A table is just like a spreadsheet: it has rows and columns.

Database Structure

Database (e.g., 'CompanyDB')

↓

Table (e.g., 'Users')

Table (e.g., 'Products')

↓

'Users' Table Example:

Col: UserID Col: Name Col: Email

Row 1: 1 John Doe john@e.com

Row 2: 2 Jane Smith jane@e.com

In this guide, we'll cover the main commands you need to know. We'll use example tables like `Products` and `Users` to make things clear. Click on any topic in the navigation to get started! 👉

Basic Queries: `SELECT`

The `SELECT` command is your most-used tool. It's used to "select" or retrieve data from a database. You tell it *what* columns you want and *from* what table.

Syntax

SELECT column1, column2, ...
FROM table_name;

To select *all* columns from a table, you can use the asterisk `*` (a wildcard):

SELECT * FROM table_name;

Interactive Example

Let's use this sample `Products` table. Click the buttons to see different `SELECT` queries in action.

Products Table

ProductID	Name	Category	Price	InStock
1	Laptop	Electronics	1200.00	true
2	Coffee Maker	Appliances	80.00	true
3	Desk Chair	Furniture	150.00	false
4	Headphones	Electronics	250.00	true

Practice Terminal 🧠

Practice Question:

> Terminal ready...

Filtering: `WHERE`

The `WHERE` clause is used to filter your results and pull only the rows that meet specific conditions. It always comes after the `FROM` clause.

Syntax

SELECT column1, column2
FROM table_name
WHERE condition;

Common Operators

Operator	Description	Example
=	Equal to	Price = 80.00
>	Greater than	Price > 100
<=	Less than or equal to	Price <= 150
!=	Not equal to	Category != 'Furniture'
LIKE	Search for a pattern (% wildcard)	Name LIKE 'L%'
IN	Matches any value in a list	Category IN ('Electronics', 'Furniture')
AND / OR	Combine multiple conditions	Price > 100 AND InStock = true

Interactive Example

Let's filter the `Products` table. Filtered rows stay bold, others become faded.

Products Table

ProductID	Name	Category	Price	InStock
1	Laptop	Electronics	1200.00	true
2	Coffee Maker	Appliances	80.00	true
3	Desk Chair	Furniture	150.00	false
4	Headphones	Electronics	250.00	true

Practice Terminal 🧠

Practice Question:

> Terminal ready...

Sorting: `ORDER BY`

The `ORDER BY` clause is used to sort your results. You can sort by one or more columns, in ascending (`ASC`) or descending (`DESC`) order. The default is `ASC`.

Syntax

SELECT * FROM table_name
ORDER BY column1 ASC, column2 DESC;

Interactive Example

Let's sort the `Products` table.

Products Table

ProductID	Name	Category	Price	InStock
1	Laptop	Electronics	1200.00	true
2	Coffee Maker	Appliances	80.00	true
3	Desk Chair	Furniture	150.00	false
4	Headphones	Electronics	250.00	true

Practice Terminal 🧠

Practice Question:

> Terminal ready...

Modifying Data (CRUD)

CRUD stands for Create, Read, Update, Delete. We've covered "Read" (`SELECT`). Now let's modify data! ✏️

Create: `INSERT`

Adds a new row to a table.

INSERT INTO table (col1, col2)
VALUES (val1, val2);

Update: `UPDATE`

Modifies existing rows. Always use `WHERE`!

UPDATE table
SET col1 = new_val
WHERE condition;

Delete: `DELETE`

Removes rows. ALWAYS use `WHERE`!

DELETE FROM table
WHERE condition;

Interactive Example

Let's modify this sample `Users` table. Changes will be highlighted.

Users Table

UserID	Name	Email
1	Alice	alice@email.com
2	Bob	bob@email.com
3	Jane	jane@email.com

Practice Terminal 🧠

Practice Question:

> Terminal ready...

Visualized: `JOIN`s

`JOIN`s combine rows from two or more tables based on a related column (a "key"). This is where SQL gets really powerful! 🔗

We'll use a `Customers` table and an `Orders` table, linked by `CustomerID`.

Customers (Table 1)

CustomerID	Name
1	Alice
2	Bob
3	Charlie

Orders (Table 2)

Important: No matching customer 99

OrderID	Item	CustomerID
101	Book	2
102	Pen	2
103	Laptop	1
104	Mug	99

Try Different `JOIN`s

Click the buttons! Matches are highlighted blue, unmatched rows included by OUTER joins are red.

JOIN Result

CustomerID	Name	OrderID	Item
Click a JOIN button to see the result.

Practice Terminal 🧠

Practice Question:

> Terminal ready...

Grouping: `GROUP BY`

The `GROUP BY` clause groups rows with the same values into summary rows, often used with Aggregate Functions (like `COUNT`, `SUM`, `AVG`).

Think "per" category, "per" region, etc. `GROUP BY` does the "per" part, summarizing data instead of showing every row. 📊

Syntax

SELECT category, AVG(price)
FROM products
GROUP BY category;

Use `HAVING` to filter *after* grouping (like `WHERE`, but for groups).

SELECT category, COUNT(*)
FROM products
GROUP BY category
HAVING COUNT(*) > 1; {/* Only show categories with more than 1 product */}

Interactive Example

Using the `Products` table with a few extra items:

Products Table

{/* Added max-height */}

Name	Category	Price
Laptop	Electronics	1200.00
Coffee Maker	Appliances	80.00
Desk Chair	Furniture	150.00
Headphones	Electronics	250.00
Toaster	Appliances	45.00
Mouse	Electronics	60.00

Group By Results

Practice Terminal 🧠

Practice Question:

> Terminal ready...

Aggregate Functions

Aggregate functions perform calculations on a set of rows and return a single summary value (e.g., total count, average price).

Function	Description
COUNT()	Counts rows. `COUNT(*)` counts all.
SUM()	Adds up values in a numeric column.
AVG()	Calculates the average of numeric values.
MIN()	Finds the minimum value in a column.
MAX()	Finds the maximum value in a column.

Example

Using the `Products` table from the `GROUP BY` section:

SELECT
  COUNT(*) AS TotalProducts,
  SUM(Price) AS TotalValue,
  AVG(Price) AS AveragePrice,
  MIN(Price) AS CheapestItem,
  MAX(Price) AS MostExpensiveItem
FROM Products;

Result Breakdown:

TotalProducts: 6
TotalValue: 1785.00
AveragePrice: 297.50
CheapestItem: 45.00
MostExpensiveItem: 1200.00

The `AS` keyword creates an Alias, renaming the calculated column for clarity.

Practice Terminal 🧠

Practice Question:

> Terminal ready...

Schema & DDL

DDL (Data Definition Language) commands define or modify the database *structure* (like tables and columns), not the data itself. 🏛️

`CREATE TABLE`

Creates a new table, defining columns and data types.

CREATE TABLE Users (
  UserID INT PRIMARY KEY,
  Name VARCHAR(100),
  Email VARCHAR(100) UNIQUE,
  Age INT
);

`ALTER TABLE`

Modifies an existing table (add, drop, modify columns).

ALTER TABLE Users
ADD COLUMN JoinDate DATE;

ALTER TABLE Users
DROP COLUMN Age;

`DROP TABLE`

Deletes an entire table. Irreversible! ⚠️

DROP TABLE Users;

Common Data Types

Data Type	Description
INT	Integer (whole number)
VARCHAR(n)	Variable-length string (text) up to n chars
DECIMAL(p,s)	Fixed-point number (money); p=total digits, s=decimals
BOOLEAN	True or False
DATE	Date (YYYY-MM-DD)
TIMESTAMP	Date and Time

Constraints (Rules)

PRIMARY KEY: Uniquely identifies each row (no NULLs allowed).

FOREIGN KEY: Links to a PRIMARY KEY in another table (enforces relationships).

UNIQUE: Ensures all values in a column are different (allows one NULL).

NOT NULL: Ensures a column cannot have an empty (NULL) value.

Practice Terminal 🧠

Practice Question:

> Terminal ready...

Tricky Stuff & Pitfalls 🤔

Level up your SQL! This section covers concepts that often trip beginners up, plus some more advanced (but useful!) techniques.

Advanced Techniques

1. Window Functions (`OVER`, `PARTITION BY`)

Aggregates (`COUNT`, `SUM`) squash rows. Window functions perform calculations across related rows *without* squashing them. Great for ranking, running totals, etc.

Example: Rank rappers by streams *within* their genre, keeping all rows.

`Rappers` Table:

Artist	Genre	Streams
Kendrick	West Coast	50B
Drake	Toronto	80B
Snoop	West Coast	30B
J. Cole	East Coast	45B
Weeknd	Toronto	75B

Result with Rank:

Artist	Genre	Streams	Rank
J. Cole	East	45B	1
Drake	Toronto	80B	1
Weeknd	Toronto	75B	2
Kendrick	West	50B	1
Snoop	West	30B	2

Query Snippet:

SELECT Artist, Genre, Streams,
  RANK() OVER (
    PARTITION BY Genre   -- Group calculation by genre
    ORDER BY Streams DESC  -- Rank within genre
  ) AS GenreRank
FROM Rappers;

2. Common Table Expressions (CTEs) (`WITH`)

CTEs create temporary, named result sets you can reference within a single query. They break down complex logic, making queries *much* easier to read and debug. 🤓

Example: Find the average price of just the 'Chocolate' candies.

`Candy` Table:

Name	Type	Price
Snickers	Chocolate	1.50
Skittles	Fruity	1.25
Hershey's	Chocolate	1.00
Sour Patch	Fruity	1.75

Query using CTE:

WITH ChocolateCandies AS (  -- Define the CTE
  SELECT Price FROM Candy
  WHERE Type = 'Chocolate'
)
SELECT AVG(Price) AS AvgChocolatePrice
FROM ChocolateCandies;  -- Use the CTE like a table

Result: 1.25

Common Pitfalls & Mistakes

1. `DELETE`/`UPDATE` without `WHERE` 😱

Most dangerous mistake! Forgetting `WHERE` applies the change to EVERY row. Double-check before running `DELETE` or `UPDATE`.

BAD: DELETE FROM ImportantData; -- DELETES EVERYTHING!

GOOD: DELETE FROM ImportantData WHERE RecordID = 123; -- Safe.

2. Mixing Aggregates and Non-Aggregates

If you `SELECT` an aggregate (`COUNT`, `AVG`, etc.) alongside a regular column, that regular column *must* be in the `GROUP BY` clause. SQL needs to know how to group the rows before aggregating.

BAD: SELECT Brand, Name, AVG(Price) FROM Candy GROUP BY Brand; -- Error! Which 'Name' for the Brand?

GOOD: SELECT Brand, AVG(Price) FROM Candy GROUP BY Brand; -- Works.

3. `INNER JOIN` vs. `LEFT JOIN` Confusion

`INNER JOIN` = Only rows with matches in *both* tables.
`LEFT JOIN` = All rows from the *left* table, plus matching rows from the right (or NULLs if no match).

Think carefully: Do you need *only* matching records, or *all* records from one side, regardless of matches?

`Artists` (Left)

ID	Name
1	Kendrick
2	Drake
3	J. Cole

`Grammys` (Right)

{/* No matching artist */}

ArtistID	Award
1	Best Album
99	Other

INNER JOIN Result

{/* Drake, J. Cole, Other Award excluded */}

Name	Award
Kendrick	Best Album

LEFT JOIN Result

{/* Other Award excluded */}

Name	Award
Kendrick	Best Album
Drake	NULL
J. Cole	NULL

SQL Learning Lab

What is SQL?

Database Structure

Basic Queries: `SELECT`

Syntax

Interactive Example

Products Table

Practice Terminal 🧠

Practice Question:

Filtering: `WHERE`

Syntax

Common Operators

Interactive Example

Products Table

Practice Terminal 🧠

Practice Question:

Sorting: `ORDER BY`

Syntax

Interactive Example

Products Table

Practice Terminal 🧠

Practice Question:

Modifying Data (CRUD)

Create: `INSERT`

Update: `UPDATE`

Delete: `DELETE`

Interactive Example

Users Table

Practice Terminal 🧠

Practice Question:

Visualized: `JOIN`s

Customers (Table 1)

Orders (Table 2)

Try Different `JOIN`s

JOIN Result

Practice Terminal 🧠

Practice Question:

Grouping: `GROUP BY`

Syntax

Interactive Example

Products Table

Group By Results

Practice Terminal 🧠

Practice Question:

Aggregate Functions

Example

Practice Terminal 🧠

Practice Question:

Schema & DDL

`CREATE TABLE`

`ALTER TABLE`

`DROP TABLE`

Common Data Types

Constraints (Rules)

Practice Terminal 🧠

Practice Question:

Tricky Stuff & Pitfalls 🤔

Advanced Techniques

1. Window Functions (`OVER`, `PARTITION BY`)

2. Common Table Expressions (CTEs) (`WITH`)

Common Pitfalls & Mistakes

1. `DELETE`/`UPDATE` without `WHERE` 😱

2. Mixing Aggregates and Non-Aggregates

3. `INNER JOIN` vs. `LEFT JOIN` Confusion

Perspective Reports

Locations Served

Contact