SELECT with WHERE, ORDER BY, GROUP BY in DBMS Theory - Mini Project: Build & Apply

Filtering and Sorting Sales Data with SELECT, WHERE, ORDER BY, and GROUP BY

📖 Scenario: You work as a data analyst for a retail company. You have a sales database table that records each sale with the product name, category, quantity sold, and sale date. Your manager wants you to find specific sales data by filtering, sorting, and grouping the sales records.

🎯 Goal: Build SQL queries step-by-step to filter sales by category, sort them by quantity, and group sales by product to find total quantities sold.

📋 What You'll Learn

Create a table called sales with columns product (text), category (text), quantity (integer), and sale_date (date).

Write a SELECT query to get all sales where the category is exactly 'Electronics'.

Add an ORDER BY clause to sort the filtered sales by quantity in descending order.

Write a GROUP BY query to find total quantity sold per product.

💡 Why This Matters

🌍 Real World

Filtering, sorting, and grouping data are common tasks in analyzing sales, inventory, or customer data in businesses.

💼 Career

Database querying skills with SELECT, WHERE, ORDER BY, and GROUP BY are essential for data analysts, database administrators, and backend developers.

1

Create the sales table and insert data

Write SQL statements to create a table called sales with columns product (text), category (text), quantity (integer), and sale_date (date). Then insert these exact rows: ('Laptop', 'Electronics', 5, '2024-04-01'), ('Chair', 'Furniture', 10, '2024-04-02'), ('Smartphone', 'Electronics', 8, '2024-04-03'), ('Desk', 'Furniture', 3, '2024-04-04'), ('Headphones', 'Electronics', 7, '2024-04-05').

DBMS Theory

-- Create the sales table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows with the exact values.

2

Filter sales by category 'Electronics'

Write a SELECT query to get all columns from the sales table where the category is exactly 'Electronics'. Use WHERE category = 'Electronics'.

DBMS Theory

CREATE TABLE sales (
    product TEXT,
    category TEXT,
    quantity INTEGER,
    sale_date DATE
);

INSERT INTO sales (product, category, quantity, sale_date) VALUES
('Laptop', 'Electronics', 5, '2024-04-01'),
('Chair', 'Furniture', 10, '2024-04-02'),
('Smartphone', 'Electronics', 8, '2024-04-03'),
('Desk', 'Furniture', 3, '2024-04-04'),
('Headphones', 'Electronics', 7, '2024-04-05');

-- Select all columns where category is 'Electronics'
-- Your code here

Hint

Use SELECT * FROM sales WHERE category = 'Electronics' to get only electronics sales.

3

Sort filtered sales by quantity descending

Extend the previous SELECT query to add an ORDER BY quantity DESC clause to sort the electronics sales by quantity sold from highest to lowest.

DBMS Theory

CREATE TABLE sales (
    product TEXT,
    category TEXT,
    quantity INTEGER,
    sale_date DATE
);

INSERT INTO sales (product, category, quantity, sale_date) VALUES
('Laptop', 'Electronics', 5, '2024-04-01'),
('Chair', 'Furniture', 10, '2024-04-02'),
('Smartphone', 'Electronics', 8, '2024-04-03'),
('Desk', 'Furniture', 3, '2024-04-04'),
('Headphones', 'Electronics', 7, '2024-04-05');

-- Select all electronics sales and order by quantity descending
-- Your code here

Hint

Add ORDER BY quantity DESC after the WHERE clause to sort results by quantity from highest to lowest.

4

Group sales by product and sum quantities

Write a SELECT query to get each product and the total quantity sold for that product. Use GROUP BY product and SUM(quantity) AS total_quantity.

DBMS Theory

CREATE TABLE sales (
    product TEXT,
    category TEXT,
    quantity INTEGER,
    sale_date DATE
);

INSERT INTO sales (product, category, quantity, sale_date) VALUES
('Laptop', 'Electronics', 5, '2024-04-01'),
('Chair', 'Furniture', 10, '2024-04-02'),
('Smartphone', 'Electronics', 8, '2024-04-03'),
('Desk', 'Furniture', 3, '2024-04-04'),
('Headphones', 'Electronics', 7, '2024-04-05');

-- Select product and total quantity sold grouped by product
-- Your code here

Hint

Use SELECT product, SUM(quantity) AS total_quantity FROM sales GROUP BY product to get total quantities per product.

Practice

1. What does the WHERE clause do in a SQL SELECT statement?

easy

A. Filters rows based on a condition

B. Groups rows with the same values

C. Sorts the result set

D. Joins two tables together

Solution

1. Step 1: Understand the role of WHERE clause

   The WHERE clause filters rows that meet a specific condition before any grouping or sorting.

2. Step 2: Differentiate from GROUP BY and ORDER BY

   GROUP BY groups rows, ORDER BY sorts rows, but WHERE filters rows.

3. Final Answer:

   Filters rows based on a condition -> Option A

4. Quick Check:

   WHERE = Filters rows [OK]

Hint: WHERE filters rows before grouping or sorting [OK]

Common Mistakes:

• Confusing WHERE with GROUP BY
• Thinking WHERE sorts data
• Assuming WHERE joins tables

2. Which of the following is the correct syntax to select all columns from a table named Employees where the Department is 'Sales'?

easy

A. SELECT * FROM Employees WHERE Department = 'Sales';

B. SELECT * Employees WHERE Department == 'Sales';

C. SELECT * FROM Employees WHERE Department := 'Sales';

D. SELECT * FROM Employees WHERE Department = Sales;

Solution

1. Step 1: Check correct SELECT syntax

   The correct syntax uses SELECT * FROM table WHERE condition with single equals and quotes around string values.

2. Step 2: Identify syntax errors in other options

   SELECT * Employees WHERE Department == 'Sales'; uses '==' which is invalid in SQL; SELECT * FROM Employees WHERE Department := 'Sales'; uses ':=' which is not standard; SELECT * FROM Employees WHERE Department = Sales; misses quotes around 'Sales'.

3. Final Answer:

   SELECT * FROM Employees WHERE Department = 'Sales'; -> Option A

4. Quick Check:

   Correct WHERE syntax uses = and quotes for strings [OK]

Hint: Use single = and quotes for string in WHERE [OK]

Common Mistakes:

• Using == instead of =
• Missing quotes around string values
• Wrong assignment operator :=

3. Given the table Sales with columns Region and Amount, what will this query return?

SELECT Region, SUM(Amount) FROM Sales WHERE Amount > 100 GROUP BY Region ORDER BY SUM(Amount) DESC;

medium

A. Sum of all sales amounts regardless of region

B. All sales records with amount over 100, sorted by region

C. Total sales amount per region for sales over 100, sorted highest to lowest

D. Syntax error due to ORDER BY on aggregate

Solution

1. Step 1: Analyze WHERE and GROUP BY

   WHERE filters rows with Amount > 100, GROUP BY groups rows by Region, SUM(Amount) calculates total per group.

2. Step 2: Understand ORDER BY with aggregate

   ORDER BY SUM(Amount) DESC sorts groups by total sales descending.

3. Final Answer:

   Total sales amount per region for sales over 100, sorted highest to lowest -> Option C

4. Quick Check:

   WHERE filters, GROUP BY groups, ORDER BY sorts aggregates [OK]

Hint: ORDER BY can sort aggregates after GROUP BY [OK]

Common Mistakes:

• Thinking ORDER BY on aggregate causes error
• Ignoring WHERE filter before grouping
• Confusing sorting by region instead of sum

4. Identify the error in this SQL query:

SELECT Department, COUNT(*) FROM Employees ORDER BY Department WHERE COUNT(*) > 5 GROUP BY Department;

medium

A. Missing semicolon at the end

B. ORDER BY appears before WHERE and GROUP BY, causing syntax error

C. WHERE cannot filter on aggregate functions like COUNT(*)

D. COUNT(*) cannot be used with GROUP BY

Solution

1. Step 1: Check SQL clause order

   The correct order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. ORDER BY before WHERE is invalid.

2. Step 2: Confirm other clauses

   COUNT(*) with GROUP BY is valid; filtering on aggregates requires HAVING, not WHERE; but main syntax error is clause order.

3. Final Answer:

   ORDER BY appears before WHERE and GROUP BY, causing syntax error -> Option B

4. Quick Check:

   Clause order matters: WHERE before GROUP BY, ORDER BY last [OK]

Hint: Remember SQL clause order: WHERE, GROUP BY, HAVING, ORDER BY [OK]

Common Mistakes:

• Placing ORDER BY before WHERE
• Using WHERE to filter aggregates instead of HAVING
• Ignoring clause order rules

5. You have a table Orders with columns CustomerID, OrderDate, and Total. Write a query to find customers who placed more than 3 orders in 2023, showing their CustomerID and total number of orders, sorted by number of orders descending.

hard

A. SELECT CustomerID, COUNT(*) FROM Orders WHERE YEAR(OrderDate) == 2023 GROUP BY CustomerID HAVING COUNT(*) > 3 ORDER BY COUNT(*) DESC;

B. SELECT CustomerID, COUNT(*) FROM Orders GROUP BY CustomerID WHERE YEAR(OrderDate) = 2023 HAVING COUNT(*) > 3 ORDER BY COUNT(*) DESC;

C. SELECT CustomerID, COUNT(*) FROM Orders WHERE OrderDate >= '2023-01-01' AND OrderDate <= '2023-12-31' GROUP BY CustomerID HAVING COUNT(*) >= 3 ORDER BY COUNT(*) DESC;

D. SELECT CustomerID, COUNT(*) FROM Orders WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY CustomerID HAVING COUNT(*) > 3 ORDER BY COUNT(*) DESC;

Solution

1. Step 1: Filter orders in 2023

   Use WHERE with OrderDate between '2023-01-01' and '2023-12-31' to select orders in 2023.

2. Step 2: Group by CustomerID and filter groups

   GROUP BY CustomerID groups orders per customer; HAVING COUNT(*) > 3 filters customers with more than 3 orders.

3. Step 3: Sort results

   ORDER BY COUNT(*) DESC sorts customers by number of orders from highest to lowest.

4. Final Answer:

   SELECT CustomerID, COUNT(*) FROM Orders WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY CustomerID HAVING COUNT(*) > 3 ORDER BY COUNT(*) DESC; -> Option D

5. Quick Check:

   Filter dates, group, HAVING filter, then ORDER BY count desc [OK]

Hint: Use HAVING to filter groups after GROUP BY [OK]

Common Mistakes:

• Using WHERE to filter aggregates instead of HAVING
• Incorrect date filtering syntax
• Using >= 3 instead of > 3 for count condition