SQL Basics

Solution:

SELECT name, stock FROM products;

Why This Works:

• SELECT specifies which columns to retrieve
• Column names are separated by commas
• FROM specifies the source table
• This returns all rows but only the name and stock columns

Solution:

SELECT name, (price * 0.8) AS discount_price
FROM products;

Explanation:

• Multiplying by 0.8 gives 80% of original price (20% off)
• Alternative: price - (price * 0.2) also works
• AS keyword creates a readable column name

Sample Result:

+------------------+----------------+
| name             | discount_price |
+------------------+----------------+
| Laptop Pro 15    | 1039.20        |
| Wireless Mouse   | 23.20          |
| Office Chair     | 199.20         |
+------------------+----------------+

Solution:

SELECT 
    UPPER(name) AS name,
    price,
    (price * 1.15) AS price_with_tax,
    (price * 1.15 * stock) AS total_value_with_tax
FROM products;

Breaking It Down:

• UPPER(name) - Converts name to uppercase
• price * 1.15 - Adds 15% tax (multiply by 1.15)
• price * 1.15 * stock - Calculates total value with tax
• Each calculation gets a descriptive alias

Key Concepts:

• You can combine multiple functions and calculations in one SELECT
• Order of operations matters: multiplication before addition
• Use parentheses for complex calculations
• Descriptive aliases make results easier to understand

Solution:

SELECT name, price FROM products
WHERE price >= 300;

Expected Result:

+------------------+-------+
| name             | price |
+------------------+-------+
| Laptop Pro 15    | 1299  |
| Standing Desk    | 599   |
| Monitor 27"      | 349   |
+------------------+-------+

Note:

>= means "greater than or equal to", so $300 would be included if it existed.

Solution:

SELECT name, price FROM products
WHERE name LIKE '%Pro%';

Explanation:

• %Pro% matches "Pro" anywhere in the name
• First % matches any characters before "Pro"
• Second % matches any characters after "Pro"
• Would find: "Laptop Pro 15", "MacBook Pro", "Pro Mouse"

Expected Result:

+------------------+-------+
| name             | price |
+------------------+-------+
| Laptop Pro 15    | 1299  |
+------------------+-------+

Solution:

SELECT name, price, stock, category
FROM products
WHERE category IN ('Tech', 'Office')
  AND price BETWEEN 50 AND 500
  AND stock > 40;

Alternative Approach:

SELECT name, price, stock, category
FROM products
WHERE (category = 'Tech' OR category = 'Office')
  AND (price >= 50 AND price <= 500)
  AND stock > 40;

Key Concepts:

• IN operator is cleaner than multiple ORs
• BETWEEN is inclusive (includes 50 and 500)
• All three conditions connected by AND must be true
• Proper indentation makes complex queries readable

Expected Result:

+------------------+-------+-------+----------+
| name             | price | stock | category |
+------------------+-------+-------+----------+
| Monitor 27"      | 349   | 50    | Tech     |
| Headphones       | 89    | 75    | Tech     |
+------------------+-------+-------+----------+

Solution:

SELECT name, stock FROM products
ORDER BY stock DESC;

Explanation:

• DESC sorts from highest to lowest
• Shows which products have the most inventory
• Useful for inventory management

Expected Result:

+------------------+-------+
| name             | stock |
+------------------+-------+
| Wireless Mouse   | 120   |
| Headphones       | 75    |
| Monitor 27"      | 50    |
| Laptop Pro 15    | 45    |
| Office Chair     | 30    |
| Standing Desk    | 15    |
+------------------+-------+

Solution:

SELECT name, price, stock FROM products
WHERE category = 'Tech'
ORDER BY stock ASC, price DESC;

Explanation:

• First filters to show only Tech products
• Sorts by stock ascending (lowest stock first - these need reordering)
• For items with same stock, shows expensive ones first
• Each column can have its own sort direction

Expected Result:

+------------------+-------+-------+
| name             | price | stock |
+------------------+-------+-------+
| Laptop Pro 15    | 1299  | 45    |
| Monitor 27"      | 349   | 50    |
| Headphones       | 89    | 75    |
| Wireless Mouse   | 29    | 120   |
+------------------+-------+-------+

Solution:

SELECT 
    name, 
    price, 
    stock,
    category,
    (price * 0.4) AS profit_margin
FROM products
WHERE price > 100
ORDER BY category ASC, profit_margin DESC;

Breaking It Down:

• WHERE price > 100 filters to expensive products only
• (price * 0.4) calculates 40% profit margin
• AS profit_margin names the calculated column
• First sorts by category alphabetically
• Within each category, highest profit margin first

Key Concepts:

• Can combine WHERE with ORDER BY
• Can sort by calculated columns
• Multi-column sorts process left to right
• Useful for business analytics and reporting

Solution:

SELECT name, stock FROM products
ORDER BY stock DESC
LIMIT 5;

Explanation:

• ORDER BY stock DESC puts highest stock first
• LIMIT 5 takes only the first 5 results
• Useful for finding best-stocked items

Solution:

SELECT name, price FROM products
ORDER BY name
LIMIT 3 OFFSET 3;

Explanation:

• Page 1 would be: LIMIT 3 OFFSET 0 (items 1-3)
• Page 2 is: LIMIT 3 OFFSET 3 (items 4-6)
• Formula: OFFSET = (page_number - 1) × items_per_page
• So: (2 - 1) × 3 = 3

Expected Result (Page 2):

+------------------+-------+
| name             | price |
+------------------+-------+
| Office Chair     | 249   |
| Standing Desk    | 599   |
| Wireless Mouse   | 29    |
+------------------+-------+

Solution:

SELECT 
    name, 
    price, 
    stock,
    (price * stock) AS total_value
FROM products
WHERE category = 'Tech' AND stock > 40
ORDER BY (price * stock) DESC
LIMIT 3;

Query Flow:

1. WHERE: Filters to Tech products with stock > 40
2. SELECT: Calculates total_value for each row
3. ORDER BY: Sorts by total_value (highest first)
4. LIMIT: Takes only top 3 results

Key Concepts:

• SQL processes: WHERE → SELECT → ORDER BY → LIMIT
• Can use calculated columns in ORDER BY
• Combines filtering, calculation, sorting, and limiting
• This type of query is common in business analytics

Expected Result:

+------------------+-------+-------+-------------+
| name             | price | stock | total_value |
+------------------+-------+-------+-------------+
| Laptop Pro 15    | 1299  | 45    | 58455       |
| Monitor 27"      | 349   | 50    | 17450       |
| Headphones       | 89    | 75    | 6675        |
+------------------+-------+-------+-------------+