SQL In One Go - Hands on Guide

This is a practical, one sitting tour of SQL using a simple store: users, products, and orders. You will read, join, group, filter, and update data with queries you can run right now.

Use the online compiler to practice queries side by side:
onecompiler

---

What is a database

A database is an organized way to store information so you can ask questions and get reliable answers.
In our store:

1. users: people who place orders
2. products: things we sell
3. orders: which user bought which product, and whether it was paid

---

Database design in plain words

1. What data are we storing
   users, products, orders

2. What properties do they have
   users: first name, last name
   products: name, department, price, weight
   orders: user id, product id, paid flag

3. What types are those properties
   text: names and departments
   number: price and weight
   boolean: paid

---

Create the tables

We will use three tables and keep the design simple.

Why this design

• Each table has a primary key called id so every row is unique.
• orders.user_id points to users.id.
• orders.product_id points to products.id.
• paid says whether the order is completed.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR,
  last_name  VARCHAR
);

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name       VARCHAR,
  department VARCHAR,
  price      INTEGER,
  weight     INTEGER
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id    INTEGER REFERENCES users(id),
  product_id INTEGER REFERENCES products(id),
  paid       BOOLEAN
);

Practice all queries side by side

1. Open One Compiler or any other sql compiler of your own choice.

2. Create the three tables

3. Go to this link and copy the dummy data for tables we are going to use in our journey
   Dummy Data Docs

4. Paste and run the queries as you learn.

---

Relationships you should know

1. one to many
   One user can have many orders. users.id → many orders.user_id.

2. many to one
   Many orders belong to one product. orders.product_id → products.id.

3. one to one
   Not in this design, but an example would be users and a user_profiles table with the same id.

4. primary keys
   id in each table. They uniquely identify rows.

5. foreign keys
   orders.user_id references users.id.
   orders.product_id references products.id.

6. foreign key delete rules
   Default is restrict (Postgres uses NO ACTION), which prevents deleting a user if there are orders pointing to them.
   If you want to delete a user and also delete their orders:
   

-- Option A: define with cascade from the start
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id    INTEGER REFERENCES users(id) ON DELETE CASCADE,
  product_id INTEGER REFERENCES products(id),
  paid       BOOLEAN
);

-- Option B: add a named FK later with cascade
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE;

If you want to keep orders but clear the link when a user is deleted:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user_setnull
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE SET NULL;

---

Insert data

You can paste bulk sample data to save time.

Go to this link and copy the dummy data for tables we are going to use in our journey
https://docs.google.com/document/d/1mA7tXgljt5bVGMMg2fCybyeon3PF3UJzPFz8iXfPzLw/edit?usp=sharing

After inserting, a quick sanity check:

Why: confirm rows exist.

SELECT COUNT(*) AS users, 
       (SELECT COUNT(*) FROM products) AS products, 
       (SELECT COUNT(*) FROM orders)   AS orders;

Example result

users	products	orders
50	100	300

---

Update rows

Why: fix a typo or move a product to a new department.

UPDATE products
SET department = 'Home'
WHERE id = 11;  -- Fantastic Metal Chair

Result idea: 1 row updated.

---

Delete rows

Why: remove a test user that has no orders.

DELETE FROM users
WHERE id = 51;  -- only if it exists and has no related orders

If a user has orders and you did not set ON DELETE CASCADE, the delete will fail to protect data integrity.

---

Retrieve data with SELECT

1) Simple SELECT

SELECT id, first_name, last_name
FROM users
LIMIT 3;

Example result

id	first_name	last_name
1	Iva	Lindgren
2	Ignatius	Johns
3	Jannie	Boehm

2) Calculations in SELECT

Why: show price with a simple sales tax preview.

SELECT id, name, price,
       ROUND(price * 1.15) AS price_with_tax
FROM products
ORDER BY id
LIMIT 3;

Example result

id	name	price	price_with_tax
1	Practical Fresh Shirt	876	1007
2	Gorgeous Steel Towels	412	474
3	Rustic Plastic Bacon	10	12

3) String operations

Why: display a friendly full name.

SELECT id,
       first_name || ' ' || last_name AS full_name,
       UPPER(last_name)               AS last_upper
FROM users
LIMIT 3;

Example result

id	full_name	last_upper
1	Iva Lindgren	LINDGREN
2	Ignatius Johns	JOHNS
3	Jannie Boehm	BOEHM

4) WHERE filter

SELECT id, name, department, price
FROM products
WHERE department = 'Toys' AND price > 800
ORDER BY price DESC;

Example result

id	name	department	price
33	Handcrafted Concrete Bike	Toys	748
31	Sleek Granite Towels	Toys	797
9	Generic Fresh Computer	Toys	926

5) Compound WHERE with OR

SELECT id, name, department, price
FROM products
WHERE department = 'Sports' OR department = 'Baby'
ORDER BY department, price DESC
LIMIT 5;

6) WHERE with lists

SELECT id, name, department
FROM products
WHERE department IN ('Home','Garden','Electronics')
ORDER BY department, id
LIMIT 5;

---

Joins

Why joins exist: data lives in separate tables. Joins bring them together.

1) INNER JOIN

Question: show paid orders with customer and product name.

SELECT o.id AS order_id,
       u.first_name || ' ' || u.last_name AS customer,
       p.name AS product,
       p.price,
       o.paid
FROM orders o
JOIN users u    ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
WHERE o.paid = TRUE
ORDER BY o.id
LIMIT 5;

Example result

order_id	customer	product	price	paid
1	Arely McGlynn	Practical Steel Shoes	947	true
2	Ignatius Johns	Generic Fresh Computer	926	true
3	Jannie Boehm	Licensed Steel Towels	939	true
4	Iva Lindgren	Gorgeous Concrete Towels	328	true
5	Kailee Jacobson	Fantastic Soft Fish	10	true

(Your rows will differ. This is a shape preview.)

2) LEFT JOIN

Question: list users and whether they have any orders.

SELECT u.id,
       u.first_name || ' ' || u.last_name AS customer,
       COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, customer
ORDER BY order_count DESC, u.id
LIMIT 5;

3) JOIN with extra WHERE

Question: paid orders for the Sports department.

SELECT u.first_name || ' ' || u.last_name AS customer,
       p.name AS product,
       p.department,
       p.price
FROM orders o
JOIN users u    ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
WHERE o.paid = TRUE
  AND p.department = 'Sports'
ORDER BY p.price DESC;

---

Aggregations

1) GROUP BY

Question: revenue by department for paid orders.

SELECT p.department,
       SUM(p.price) AS revenue,
       COUNT(*)     AS orders_count
FROM orders o
JOIN products p ON p.id = o.product_id
WHERE o.paid = TRUE
GROUP BY p.department
ORDER BY revenue DESC
LIMIT 5;

Example result

department	revenue	orders_count
Home	4300	6
Sports	2850	4
Toys	2600	3
Beauty	1800	3
Baby	1700	3

(Numbers are illustrative. Run it to see your data.)

2) Aggregation functions

COUNT, SUM, AVG, MIN, MAX

SELECT
  COUNT(*)              AS total_products,
  AVG(price)            AS avg_price,
  MIN(price)            AS min_price,
  MAX(price)            AS max_price
FROM products;

3) The COUNT with null gotcha

COUNT(column) ignores nulls. COUNT(*) counts rows.

SELECT
  COUNT(*)      AS rows_considered,
  COUNT(paid)   AS paid_not_null
FROM orders;

If any paid is null, paid_not_null will be smaller. With our seed, paid is set to true or false, so both counts often match.

4) Filter groups with HAVING

Question: show only departments with at least 5 paid orders.

SELECT p.department,
       COUNT(*) AS paid_orders
FROM orders o
JOIN products p ON p.id = o.product_id
WHERE o.paid = TRUE
GROUP BY p.department
HAVING COUNT(*) >= 5
ORDER BY paid_orders DESC;

---

Group by with joins: useful patterns

Top 5 customers by total spend on paid orders

SELECT u.id,
       u.first_name || ' ' || u.last_name AS customer,
       SUM(p.price) AS total_spent,
       COUNT(*)     AS orders_count
FROM orders o
JOIN users u    ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
WHERE o.paid = TRUE
GROUP BY u.id, customer
ORDER BY total_spent DESC
LIMIT 5;

Average price per department for paid orders

SELECT p.department,
       AVG(p.price) AS avg_paid_price
FROM orders o
JOIN products p ON p.id = o.product_id
WHERE o.paid = TRUE
GROUP BY p.department
ORDER BY avg_paid_price DESC
LIMIT 5;

---

ORDER BY, LIMIT, OFFSET

Question: show the 5 most expensive products, then the next 5.

-- top 5
SELECT id, name, department, price
FROM products
ORDER BY price DESC
LIMIT 5;

-- next 5
SELECT id, name, department, price
FROM products
ORDER BY price DESC
LIMIT 5 OFFSET 5;

---

UNION, INTERSECT, EXCEPT

Think in sets of user ids.

1) Users who ordered from Sports or Baby

SELECT DISTINCT o.user_id
FROM orders o JOIN products p ON p.id = o.product_id
WHERE p.department = 'Sports'
UNION
SELECT DISTINCT o.user_id
FROM orders o JOIN products p ON p.id = o.product_id
WHERE p.department = 'Baby';

2) Users who ordered from Sports and also from Baby

SELECT DISTINCT o.user_id
FROM orders o JOIN products p ON p.id = o.product_id
WHERE p.department = 'Sports'
INTERSECT
SELECT DISTINCT o.user_id
FROM orders o JOIN products p ON p.id = o.product_id
WHERE p.department = 'Baby';

3) Users who have orders but no paid orders

SELECT DISTINCT user_id
FROM orders
EXCEPT
SELECT DISTINCT user_id
FROM orders
WHERE paid = TRUE;

---

Subqueries

What is a subquery

A query inside another query. You can use its result as a value, a list, or a table.

Three shapes:

1. single value: one row one column
2. list: many rows one column
3. table: many rows many columns

Tip: always give subqueries an alias when used as a table.

1) Subquery in SELECT (single value)

Question: show price and how it compares to the average price.

SELECT id, name, price,
       (SELECT ROUND(AVG(price)) FROM products) AS avg_price_all
FROM products
ORDER BY id
LIMIT 3;

2) Subquery in WHERE (list)

Question: users who bought Toys.

SELECT DISTINCT u.id, u.first_name || ' ' || u.last_name AS customer
FROM users u
WHERE u.id IN (
  SELECT o.user_id
  FROM orders o
  JOIN products p ON p.id = o.product_id
  WHERE p.department = 'Toys'
)
ORDER BY u.id
LIMIT 5;

3) Subquery as a table in FROM or JOIN

Question: top 3 departments by paid revenue, then list their orders.

WITH top_depts AS (
  SELECT p.department, SUM(p.price) AS revenue
  FROM orders o
  JOIN products p ON p.id = o.product_id
  WHERE o.paid = TRUE
  GROUP BY p.department
  ORDER BY revenue DESC
  LIMIT 3
)
SELECT td.department,
       u.first_name || ' ' || u.last_name AS customer,
       p.name AS product,
       p.price
FROM top_depts td
JOIN products p ON p.department = td.department
JOIN orders   o ON o.product_id = p.id AND o.paid = TRUE
JOIN users    u ON u.id = o.user_id
ORDER BY td.department, p.price DESC
LIMIT 10;

4) Correlated subquery

Question: for each user, count their paid orders without a GROUP BY on users.

SELECT u.id,
       u.first_name || ' ' || u.last_name AS customer,
       (
         SELECT COUNT(*)
         FROM orders o
         WHERE o.user_id = u.id AND o.paid = TRUE
       ) AS paid_orders
FROM users u
ORDER BY paid_orders DESC, u.id
LIMIT 5;

---

DISTINCT

Question: list all unique product departments.

SELECT DISTINCT department
FROM products
ORDER BY department;

---

Utility operators: GREATEST and LEAST

Question: simple shipping rule

• base fee is 100
• weight fee is weight × 10
• charge the greater of the two

SELECT id, name, weight,
       GREATEST(100, weight * 10) AS shipping_fee
FROM products
ORDER BY id
LIMIT 5;

Question: cap a discount at 20

SELECT id, name, price,
       LEAST(price * 0.10, 20) AS discount_preview
FROM products
ORDER BY id
LIMIT 5;

---

CASE

Question: bucket products by price.

SELECT id, name, price,
       CASE
         WHEN price >= 800 THEN 'Premium'
         WHEN price >= 300 THEN 'Standard'
         ELSE 'Budget'
       END AS price_band
FROM products
ORDER BY price DESC
LIMIT 10;

---

How queries are executed

Logical order in most SQL engines:

• Always remember source - From where the data is coming (table/tables) - is always made first

1. FROM
2. JOIN
3. WHERE
4. GROUP BY
5. HAVING
6. SELECT
7. DISTINCT
8. ORDER BY
9. LIMIT and OFFSET

This order explains many surprises. For example, WHERE runs before grouping, while HAVING filters after grouping.

If you have any questions, comment down.

Happy Learning.