Practice - Comprehensive Basic Queries

Practice: Comprehensive Basic Queries

In the first 5 lessons, we learned SQL's "listening, speaking, reading, and writing" — creating tables, querying, filtering, sorting, CRUD operations, and data types. Now it's time to combine these skills to solve real business problems. This lesson has no new syntax — only practice.

Project Requirements

You are a data analyst at an e-commerce company, and you need to extract the following information from the company's database:

Employee Management: Query employees in specific departments, sort by salary, paginate results
Product Filtering: Filter products by price range and stock, fuzzy search product names
Order Analysis: Query recent orders, calculate order amounts, update order status
Data Maintenance: Batch update prices, clean up expired data, insert new data

The database contains four tables: employees, departments, orders, products.

Complete Code Implementation

Step 1: Create Database and Tables

SQL

-- Create departments table
CREATE TABLE departments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    location TEXT
);

-- Create employees table
CREATE TABLE employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    department_id INTEGER,
    salary DECIMAL(10,2),
    hire_date DATE,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- Create products table
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    category TEXT,
    price DECIMAL(10,2),
    stock INTEGER DEFAULT 0
);

-- Create orders table
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_name TEXT NOT NULL,
    product_id INTEGER,
    quantity INTEGER,
    order_date DATE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Step 2: Insert Test Data

SQL

-- Insert department data
INSERT INTO departments (name, location) VALUES
    ('Engineering', 'New York'),
    ('Marketing', 'Los Angeles'),
    ('Finance', 'New York'),
    ('HR', 'Chicago');

-- Insert employee data
INSERT INTO employees (name, department_id, salary, hire_date) VALUES
    ('John', 1, 15000.00, '2023-01-15'),
    ('Jane', 1, 18000.00, '2022-06-01'),
    ('Bob', 2, 12000.00, '2023-03-20'),
    ('Alice', 3, 13000.00, '2021-11-10'),
    ('Charlie', 1, 20000.00, '2020-08-05'),
    ('Diana', 2, 11000.00, '2024-01-10'),
    ('Eve', NULL, 9000.00, '2024-05-01'),
    ('Frank', 3, 14000.00, '2022-09-15');

-- Insert product data
INSERT INTO products (name, category, price, stock) VALUES
    ('iPhone 15', 'Phone', 5999.00, 100),
    ('MacBook Pro', 'Computer', 12999.00, 50),
    ('AirPods Pro', 'Accessory', 1899.00, 200),
    ('iPad Air', 'Tablet', 4799.00, 80),
    ('Apple Watch', 'Watch', 2999.00, 150),
    ('Magic Keyboard', 'Accessory', 999.00, 300),
    ('Mac Mini', 'Computer', 4499.00, 0),
    ('iPhone 14', 'Phone', 4999.00, 20);

-- Insert order data
INSERT INTO orders (customer_name, product_id, quantity, order_date) VALUES
    ('Tom', 1, 1, '2024-06-01'),
    ('Lucy', 3, 2, '2024-06-02'),
    ('Mike', 2, 1, '2024-06-03'),
    ('Tom', 5, 1, '2024-06-05'),
    ('Sarah', 4, 3, '2024-06-10'),
    ('Lucy', 6, 5, '2024-06-15'),
    ('David', 1, 2, '2024-06-20'),
    ('Mike', 8, 1, '2024-06-25');

Step 3: Employee Management Queries

SQL

-- Q1: Query all Engineering employees, sorted by salary from high to low
SELECT name, salary, hire_date
FROM employees
WHERE department_id = 1
ORDER BY salary DESC;

Output:

TEXT

name     salary    hire_date
-------  --------  ----------
Charlie  20000.00  2020-08-05
Jane     18000.00  2022-06-01
John     15000.00  2023-01-15

SQL

-- Q2: Query top 3 employees by salary (pagination: page 1, 3 per page)
SELECT name, salary, department_id
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 0;

Output:

TEXT

name     salary    department_id
-------  --------  -------------
Charlie  20000.00  1
Jane     18000.00  1
John     15000.00  1

SQL

-- Q3: Query page 2 (3 records per page)
SELECT name, salary, department_id
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 3;

Output:

TEXT

name   salary    department_id
-----  --------  -------------
Frank  14000.00  3
Alice  13000.00  3
Bob    12000.00  2

SQL

-- Q4: Query employees without a department assigned
SELECT name, salary
FROM employees
WHERE department_id IS NULL;

Output:

TEXT

name  salary
----  --------
Eve   9000.00

Step 4: Product Filtering Queries

SQL

-- Q5: Query products priced between 1000 and 5000, sorted by price ascending
SELECT name, category, price, stock
FROM products
WHERE price BETWEEN 1000 AND 5000
ORDER BY price ASC;

Output:

TEXT

name            category  price    stock
--------------  --------  -------  -----
AirPods Pro     Accessory 1899.00  200
Apple Watch     Watch     2999.00  150
Mac Mini        Computer  4499.00  0
iPad Air        Tablet    4799.00  80

SQL

-- Q6: Fuzzy search for products with "iPhone" in the name
SELECT name, price, stock
FROM products
WHERE name LIKE '%iPhone%';

Output:

TEXT

name        price    stock
----------  -------  -----
iPhone 15   5999.00  100
iPhone 14   4999.00  20

SQL

-- Q7: Query products in stock (stock > 0) priced below 3000
SELECT name, price, stock
FROM products
WHERE stock > 0 AND price < 3000
ORDER BY price DESC;

Output:

TEXT

name            price    stock
--------------  -------  -----
AirPods Pro     1899.00  200
Magic Keyboard  999.00   300

Step 5: Order Analysis Queries

SQL

-- Q8: Query the 5 most recent orders, showing product name and customer name
SELECT o.customer_name, p.name AS product_name,
       o.quantity, o.order_date
FROM orders o
JOIN products p ON o.product_id = p.id
ORDER BY o.order_date DESC
LIMIT 5;

Output:

TEXT

customer_name  product_name   quantity  order_date
-------------  -------------  --------  ----------
Mike           iPhone 14      1         2024-06-25
David          iPhone 15      2         2024-06-20
Lucy           Magic Keyboard 5         2024-06-15
Sarah          iPad Air       3         2024-06-10
Tom            Apple Watch    1         2024-06-05

SQL

-- Q9: Query each order's amount (unit price × quantity), sorted by amount descending
SELECT o.customer_name, p.name AS product_name,
       p.price, o.quantity,
       (p.price * o.quantity) AS total_amount
FROM orders o
JOIN products p ON o.product_id = p.id
ORDER BY total_amount DESC;

Output:

TEXT

customer_name  product_name   price     quantity  total_amount
-------------  -------------  --------  --------  ------------
Sarah          iPad Air       4799.00   3         14397.00
Mike           MacBook Pro    12999.00  1         12999.00
David          iPhone 15      5999.00   2         11998.00
Tom            iPhone 15      5999.00   1         5999.00
Lucy           Magic Keyboard 999.00    5         4995.00
Mike           iPhone 14      4999.00   1         4999.00
Tom            Apple Watch    2999.00   1         2999.00
Lucy           AirPods Pro    1899.00   2         3798.00

Step 6: Data Maintenance Operations

SQL

-- Q10: Increase the price of all "Accessory" category products by 10%
-- First confirm the affected scope
SELECT name, price FROM products WHERE category = 'Accessory';

Before adjustment:

TEXT

name            price
--------------  -------
AirPods Pro     1899.00
Magic Keyboard  999.00

SQL

-- Execute the update
UPDATE products
SET price = ROUND(price * 1.1, 2)
WHERE category = 'Accessory';

-- Verify results
SELECT name, price FROM products WHERE category = 'Accessory';

After adjustment:

TEXT

name            price
--------------  -------
AirPods Pro     2088.90
Magic Keyboard  1098.90

SQL

-- Q11: Mark out-of-stock products (stock = 0) as delisted
-- Here we delete out-of-stock products as an example
-- First confirm
SELECT id, name, stock FROM products WHERE stock = 0;

Confirmation result:

TEXT

id  name      stock
--  --------  -----
7   Mac Mini  0

SQL

-- Delete out-of-stock products
DELETE FROM products WHERE stock = 0;

-- Q12: Insert a new product
INSERT INTO products (name, category, price, stock)
VALUES ('AirTag', 'Accessory', 229.00, 500);

-- Final view of all products
SELECT id, name, category, price, stock
FROM products
ORDER BY id;

Final product list:

TEXT

id  name            category  price     stock
--  --------------  --------  --------  -----
1   iPhone 15       Phone     5999.00   100
2   MacBook Pro     Computer  12999.00  50
3   AirPods Pro     Accessory 2088.90   200
4   iPad Air        Tablet    4799.00   80
5   Apple Watch     Watch     2999.00   150
6   Magic Keyboard  Accessory 1098.90   300
8   iPhone 14       Phone     4999.00   20
9   AirTag          Accessory 229.00    500

Code Walkthrough

Query	Knowledge Points Used	Key Techniques
Q1-Q3	SELECT + WHERE + ORDER BY + LIMIT/OFFSET	Pagination uses `LIMIT n OFFSET (page-1)*n`
Q4	WHERE + IS NULL	Must use IS NULL to check for NULL
Q5	BETWEEN + ORDER BY	BETWEEN includes both endpoint values
Q6	LIKE + wildcard `%`	`%keyword%` matches any position
Q7	Multiple AND conditions + comparison operators	Combining conditions for filtering
Q8	JOIN + ORDER BY + LIMIT	Multi-table related queries
Q9	JOIN + expression calculation + ORDER BY	Calculations in SELECT
Q10	UPDATE + WHERE + ROUND	SELECT to confirm before updating
Q11	DELETE + WHERE	SELECT to confirm before deleting
Q12	INSERT + final verification	Query to verify after insertion

❓ FAQ

Q: With LIMIT OFFSET pagination, if data is added or deleted, will there be duplicates or missing records? A: Yes. If data changes during pagination, OFFSET-based pagination may result in duplicate or missing records. A more stable approach is "cursor pagination": WHERE id > last_id_from_previous_page LIMIT 10, but this requires each record to have a consistent sort key.

Q: LIKE '%keyword%' causes index failure. What about large datasets? A: LIKE starting with a wildcard indeed cannot use a regular index. For large datasets, consider: ① Using full-text search (FTS5, supported by SQLite); ② Using search engines like Elasticsearch at the application layer; ③ If only prefix matching is needed, LIKE 'keyword%' can utilize an index.

Q: What if two tables have the same column name in a JOIN query? A: Use table aliases or column aliases to differentiate. For example: SELECT e.name, d.name AS dept_name FROM employees e JOIN departments d ON e.department_id = d.id;. It's a good habit to alias columns for clearer output.

Q: Can UPDATE and DELETE operations be undone? A: If within a transaction (between BEGIN...COMMIT), you can use ROLLBACK to undo. If already COMMITTED, in SQLite it's basically unrecoverable (unless you have a backup). MySQL can recover through binlog replay. So always back up before production operations.

📖 Summary

Combine SELECT + WHERE + ORDER BY + LIMIT for complex queries and pagination
Use IS NULL for null values, LIKE for fuzzy matching, BETWEEN for range queries
Multi-table queries use JOIN; calculated fields can use expressions in SELECT
SELECT to confirm before UPDATE/DELETE — this is the most important safety habit
Pagination uses LIMIT + OFFSET; for large datasets, consider cursor pagination
Fuzzy search starting with % defeats indexes; for large datasets, consider full-text search

📝 Exercises

Exercise 1 (⭐)

Write queries to complete the following tasks:

Query all employees who joined in 2023, sorted by hire date ascending
Query the highest-priced product in the "Phone" category
Query all orders from customer "Tom," showing product name and quantity

Exercise 2 (⭐⭐)

Write queries to complete the following tasks:

Query the number of employees in each department (hint: requires GROUP BY — you can skip ahead to later lessons and come back to challenge this)
Query the total amount of all orders
Decrease the price of "Computer" category products by 5%, then query to verify

Exercise 3 (⭐⭐⭐)

Simulate a "low stock alert" scenario:

Query all products with stock below 50, sorted by stock ascending
Insert these products into a newly created low_stock_alert table
Increase the stock of each of these products by 100
Verify the update results

Next Lesson

👉 07-join-intro - Introduction to JOIN Queries

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

Practice - Comprehensive Basic Queries

Practice: Comprehensive Basic Queries

Project Requirements

Complete Code Implementation

Step 1: Create Database and Tables

Step 2: Insert Test Data

Step 3: Employee Management Queries

Step 4: Product Filtering Queries

Step 5: Order Analysis Queries

Step 6: Data Maintenance Operations

Code Walkthrough

❓ FAQ

📖 Summary

📝 Exercises

Exercise 1 (⭐)

Exercise 2 (⭐⭐)

Exercise 3 (⭐⭐⭐)

Next Lesson

🙏 帮我们做得更好