A Complete SQL Practice Flow

To finish, connect the course into one path from table creation to reporting. The setting remains an online store. The goal is not a complex application, but seeing CREATE TABLE, INSERT, SELECT, WHERE, JOIN, GROUP BY, UPDATE, and DELETE work together.

01 · DESIGN — design schema

02 · CREATE — create tables

03 · INSERT — add data

04 · SELECT — inspect data

05 · WHERE — filter rows

06 · JOIN — connect data

07 · GROUP — summarize

08 · UPDATE — change safely

09 · DELETE — remove safely

10 · VERIFY — verify results

01Create four tables

First create users, products, orders, and order_items. No business rows exist yet, but primary keys, required values, default status, and foreign-key relationships are ready.

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(200) NOT NULL UNIQUE
);

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  category VARCHAR(100)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT NOT NULL,
  order_date DATE NOT NULL,
  status VARCHAR(50) DEFAULT 'pending',
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

02Insert sample data

Insert referenced users and products first, then orders, and finally order_items that depend on orders and products. This order ensures every foreign key can find its target.

INSERT INTO users (id, name, email) VALUES
  (1, 'Alice', '[email protected]'),
  (2, 'Bob', '[email protected]'),
  (3, 'Chris', '[email protected]');

INSERT INTO products (id, name, price, category) VALUES
  (1, 'Keyboard', 80.00, 'Electronics'),
  (2, 'Mouse', 40.00, 'Electronics'),
  (3, 'Coffee Mug', 15.00, 'Home'),
  (4, 'Backpack', 60.00, 'Fashion');

INSERT INTO orders (id, user_id, order_date, status) VALUES
  (1001, 1, '2026-08-01', 'paid'),
  (1002, 2, '2026-08-02', 'pending');

INSERT INTO order_items (id, order_id, product_id, quantity) VALUES
  (1, 1001, 1, 1),
  (2, 1001, 2, 2),
  (3, 1002, 4, 1);

03Inspect the base data

After loading or initializing data, simple SELECT statements quickly confirm that expected rows exist. This basic step catches problems before more complex queries.

SELECT * FROM users;
SELECT * FROM products;
SELECT * FROM orders;
SELECT * FROM order_items;

04Filter and sort products

Show products priced at least 50, highest first. SELECT chooses columns, WHERE chooses rows, and ORDER BY chooses display order.

SELECT name, price
FROM products
WHERE price >= 50
ORDER BY price DESC;

05Query complete order details

Inspecting order_items alone gives mostly IDs. Follow foreign keys through users, orders, order_items, and products to reveal who bought what and in what quantity.

SELECT o.id AS order_id, u.name AS user_name,
       p.name AS product_name, oi.quantity, p.price
FROM orders AS o
INNER JOIN users AS u ON o.user_id = u.id
INNER JOIN order_items AS oi ON o.id = oi.order_id
INNER JOIN products AS p ON oi.product_id = p.id
ORDER BY o.id;

06Calculate each order total

Each line amount is quantity × price, then SUM is calculated per order. In order 1001, Keyboard is 80 × 1 and Mouse is 40 × 2, totaling 160.

SELECT o.id AS order_id, u.name AS user_name,
       SUM(oi.quantity * p.price) AS total_amount
FROM orders AS o
INNER JOIN users AS u ON o.user_id = u.id
INNER JOIN order_items AS oi ON o.id = oi.order_id
INNER JOIN products AS p ON oi.product_id = p.id
GROUP BY o.id, u.name;

07Count orders per user

LEFT JOIN preserves Chris even without an order. COUNT(o.id) ignores NULL, so Chris shows zero rather than incorrectly counting the preserved row.

SELECT u.name, COUNT(o.id) AS order_count
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY order_count DESC;

08Safely update an order status

When order 1002 is paid, SELECT the target first, UPDATE it, then verify with the same condition. This completes the safe “inspect, change, inspect” loop.

SELECT * FROM orders WHERE id = 1002;

UPDATE orders
SET status = 'paid'
WHERE id = 1002;

SELECT * FROM orders WHERE id = 1002;

09Change a product price in one place

Changing Mouse to 45 modifies one products row. Product information has one source of truth, so future queries read the new price. Confirm id 2 is Mouse before changing and verifying it.

SELECT * FROM products WHERE id = 2;
UPDATE products SET price = 45.00 WHERE id = 2;
SELECT * FROM products WHERE id = 2;

10Carefully remove test data

Removing an unreferenced test product also follows inspect, delete, inspect. If order_items references the product, a foreign key may reject deletion. That is the database protecting relationship integrity.

SELECT * FROM products WHERE id = 3;
DELETE FROM products WHERE id = 3;
SELECT * FROM products WHERE id = 3;

Not every problem needs the entire workflow. The three most expensive products need SELECT, ORDER BY, and LIMIT; orders per user need JOIN, COUNT, and GROUP BY; changing a status needs UPDATE with a precise WHERE. Choose only the parts that answer the problem.

You now have a SQL problem-solving path

The important skill is not memorizing every command. It is deciding where the data lives, which columns matter, whether rows need filtering, whether tables must be joined, and whether the task reads or changes data. That reasoning will support later work with subqueries, CTEs, window functions, indexes, and transactions.