Giao diện
Exercise 1: Basic Queries 🎯
🎯 Objective
Master the fundamentals of SQL: SELECT, WHERE, ORDER BY, and LIMIT clauses.
📋 Problem Statement
You have a products table with the following structure:
sql
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
price DECIMAL(10, 2),
stock_quantity INT,
supplier_id INT
);Sample Data:
sql
INSERT INTO products VALUES
(1, 'Laptop Pro 15', 'Electronics', 1299.99, 25, 101),
(2, 'Wireless Mouse', 'Electronics', 29.99, 150, 102),
(3, 'Office Chair', 'Furniture', 199.99, 40, 103),
(4, 'Standing Desk', 'Furniture', 499.99, 15, 103),
(5, 'USB-C Cable', 'Electronics', 12.99, 200, 102),
(6, 'Monitor 27"', 'Electronics', 349.99, 30, 101),
(7, 'Desk Lamp', 'Furniture', 45.99, 60, 104),
(8, 'Keyboard Mechanical', 'Electronics', 89.99, 75, 102);📥 Tasks
Task 1: Select All Electronics
Write a query to retrieve all products in the 'Electronics' category.
Task 2: Expensive Products
Find all products with price greater than $100, ordered by price descending.
Task 3: Low Stock Alert
Find products with stock_quantity less than 50, showing product_name, stock_quantity, and price.
Task 4: Top 3 Most Expensive
Retrieve the top 3 most expensive products with their names and prices.
Task 5: Price Range
Find products with prices between $20 and $100 (inclusive).
💡 Hints
Hint 1: WHERE Clause
Use `WHERE column_name = 'value'` to filter rows.Hint 2: ORDER BY
`ORDER BY column_name DESC` sorts in descending order.Hint 3: LIMIT
`LIMIT n` restricts the result to n rows.Hint 4: BETWEEN
`WHERE price BETWEEN 20 AND 100` is equivalent to `WHERE price >= 20 AND price <= 100`.✅ Solutions
Click to reveal solutions
Solution 1: Select All Electronics
sql
SELECT *
FROM products
WHERE category = 'Electronics';Result: 5 rows (Laptop, Mouse, Cable, Monitor, Keyboard)
Solution 2: Expensive Products
sql
SELECT product_name, price
FROM products
WHERE price > 100
ORDER BY price DESC;Result:
product_name | price
----------------------|--------
Laptop Pro 15 | 1299.99
Standing Desk | 499.99
Monitor 27" | 349.99
Office Chair | 199.99Solution 3: Low Stock Alert
sql
SELECT product_name, stock_quantity, price
FROM products
WHERE stock_quantity < 50
ORDER BY stock_quantity ASC;Result:
product_name | stock_quantity | price
----------------------|----------------|--------
Standing Desk | 15 | 499.99
Laptop Pro 15 | 25 | 1299.99
Monitor 27" | 30 | 349.99
Office Chair | 40 | 199.99
Desk Lamp | 45 | 45.99Solution 4: Top 3 Most Expensive
sql
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 3;Result:
product_name | price
----------------------|--------
Laptop Pro 15 | 1299.99
Standing Desk | 499.99
Monitor 27" | 349.99Solution 5: Price Range
sql
SELECT product_name, price
FROM products
WHERE price BETWEEN 20 AND 100
ORDER BY price;Alternative (explicit comparison):
sql
SELECT product_name, price
FROM products
WHERE price >= 20 AND price <= 100
ORDER BY price;Result:
product_name | price
----------------------|-------
Wireless Mouse | 29.99
Desk Lamp | 45.99
Keyboard Mechanical | 89.99🚀 Follow-up Challenges
- Pattern Matching: Find all products whose name contains "Desk" (Hint: use
LIKE) - Multiple Conditions: Find Electronics products with price < $50
- NULL Handling: What happens if some products have NULL prices? How would you handle them?
- Case Sensitivity: How does your database handle case-sensitive comparisons?
🔑 Key Takeaways
SELECTspecifies which columns to retrieveWHEREfilters rows based on conditionsORDER BYsorts results (ASC = ascending, DESC = descending)LIMITrestricts the number of rows returnedBETWEENis inclusive on both ends- Always consider NULL values in real-world data