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

Need a 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

Need a 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

Need a 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

Need a hint?

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