SQL Crash Course | Ayman Alzaid

1. Schema Reference

All examples below use two related tables. Understanding their structure is essential before writing queries.

Products

PK ProductID INT

Name VARCHAR(100)

Price DECIMAL(10,2)

Category VARCHAR(50)

Stock INT

Orders

PK OrderID INT

FK ProductID INT

CustomerName VARCHAR(100)

Quantity INT

OrderDate DATE

Info

FK (Foreign Key) = Orders.ProductID references Products.ProductID, linking the two tables for JOINs.

2. Introduction to SQL

SQL (Structured Query Language) is the standard language for managing relational databases. It is divided into four categories:

DQL

Data Query Language
SELECT

DML

Data Manipulation
INSERT UPDATE DELETE

DDL

Data Definition
CREATE ALTER DROP

DCL

Data Control
GRANT REVOKE

Info

SQL is case-insensitive for keywords, but it's a strong convention to write keywords in UPPERCASE (SELECT, FROM) and table/column names in their original case.

3. Reading Data: SELECT

SELECT retrieves rows from one or more tables. It is the most commonly used SQL statement.

-- Select all columns and all rows
SELECT *
FROM   Products;

-- Select specific columns only
SELECT Name, Price
FROM   Products;

-- Column aliases (rename in output)
SELECT Name AS ProductName,
       Price * 1.1 AS PriceWithTax
FROM   Products;

-- Remove duplicate values
SELECT DISTINCT Category
FROM   Products;

Performance Tip

Avoid SELECT * in production queries — always name the columns you need to reduce data transfer and improve query speed.

4. Filtering & Sorting

WHERE filters rows before they are returned. ORDER BY sorts results. LIMIT caps the number of rows.

-- Filter: price > 50 AND in 'Electronics'
SELECT Name, Price
FROM   Products
WHERE  Price > 50
  AND  Category = 'Electronics';

-- IN operator (cleaner than multiple OR)
SELECT Name
FROM   Products
WHERE  Category IN ('Electronics', 'Books');

-- BETWEEN (inclusive on both ends)
SELECT Name, Price
FROM   Products
WHERE  Price BETWEEN 20 AND 100;

-- LIKE for pattern matching (% = any chars)
SELECT Name
FROM   Products
WHERE  Name LIKE '%Laptop%';

-- Sort: most expensive first, then by Name A→Z
SELECT Name, Price
FROM   Products
ORDER BY Price DESC, Name ASC
LIMIT  10;

5. Insert / Update / Delete

These DML statements modify the data stored in your tables. Always use a WHERE clause with UPDATE and DELETE.

-- INSERT: add a new row
INSERT INTO Products (Name, Price, Category, Stock)
VALUES ('Laptop Pro', 1299.99, 'Electronics', 50);

-- UPDATE: modify existing rows
UPDATE Products
SET    Price = 1199.99,
       Stock = 45
WHERE  Name = 'Laptop Pro';    -- ← REQUIRED!

-- DELETE: remove rows
DELETE FROM Products
WHERE  ProductID = 101;         -- ← REQUIRED!

⚠ Critical Warning

UPDATE or DELETE without a WHERE clause affects every row in the table. Always double-check your condition before executing.

Best Practice

Before running a DELETE, first run a SELECT with the same WHERE condition to verify which rows will be affected.

6. JOINs

JOIN combines rows from two tables based on a shared column (the foreign key relationship).

-- INNER JOIN: only rows matching in BOTH tables
SELECT   p.Name, o.Quantity, o.OrderDate
FROM     Products p
INNER JOIN Orders o ON p.ProductID = o.ProductID;

-- LEFT JOIN: all Products, matched Orders (NULL if none)
SELECT   p.Name, o.OrderID
FROM     Products p
LEFT JOIN Orders o ON p.ProductID = o.ProductID;

-- Tip: filter after joining with WHERE
SELECT   p.Name, o.Quantity
FROM     Products p
INNER JOIN Orders o ON p.ProductID = o.ProductID
WHERE    o.Quantity > 5
ORDER BY o.Quantity DESC;

Info

INNER JOIN returns only matching rows. LEFT JOIN returns all rows from the left table, filling unmatched right columns with NULL.

7. Aggregates & GROUP BY

Aggregate functions compute summary values over groups of rows. Use GROUP BY to define the groups, and HAVING to filter groups after aggregation.

-- Common aggregate functions
SELECT
    COUNT(ProductID)  AS TotalProducts,
    SUM(Stock)        AS TotalStock,
    AVG(Price)        AS AvgPrice,
    MIN(Price)        AS CheapestPrice,
    MAX(Price)        AS MostExpensive
FROM Products;

-- GROUP BY: one row per category
SELECT   Category,
         COUNT(*) AS NumProducts,
         AVG(Price) AS AvgPrice
FROM     Products
GROUP BY Category;

-- HAVING: filter groups (not individual rows)
SELECT   Category, COUNT(*) AS NumProducts
FROM     Products
GROUP BY Category
HAVING   COUNT(*) >= 5
ORDER BY NumProducts DESC;

WHERE vs HAVING

WHERE filters individual rows before grouping. HAVING filters groups after the GROUP BY has been applied.

Bonus: Subqueries

A subquery is a SELECT nested inside another statement. They can appear in WHERE, FROM, or SELECT clauses.

-- Products with above-average price
SELECT Name, Price
FROM   Products
WHERE  Price > (
    SELECT AVG(Price) FROM Products
);

-- Products that have at least one order
SELECT Name
FROM   Products
WHERE  ProductID IN (
    SELECT DISTINCT ProductID FROM Orders
);

← Back to SQL Learning Paths →