Posted on Aug 26

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:

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

Database design in plain words

What data are we storing
users, products, orders
What properties do they have
users: first name, last name
products: name, department, price, weight
orders: user id, product id, paid flag
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

Open One Compiler or any other sql compiler of your own choice.
Create the three tables
Go to this link and copy the dummy data for tables we are going to use in our journey
Dummy Data Docs
Paste and run the queries as you learn.

Relationships you should know

one to many
One user can have many orders. users.id → many orders.user_id.
many to one
Many orders belong to one product. orders.product_id → products.id.
one to one
Not in this design, but an example would be users and a user_profiles table with the same id.
primary keys
id in each table. They uniquely identify rows.
foreign keys
orders.user_id references users.id.
orders.product_id references products.id.
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:

single value: one row one column
list: many rows one column
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;