SQL Challenge 1 - Steve's Car Showroom

Steve's Car Showroom Steve’s Car Showroom

In this SQL project, we delve into the fascinating world of data analysis, a crucial tool for informed decision-making. By transforming the raw data retrieved from Steve’s Car Showroom into valuable insights, we will drive his business towards success. Let’s go!

Problem Statement

Steve runs a top-end car showroom, but his data analyst has just quit and left him without his crucial insights. Can you analyze the following data to provide him with all the answers he requires?

Tables

Here are the tables you will be using:

Steve’s Car Showroom Datasets
Steve’s Car Showroom Datasets

Schema SQL

CREATE TABLE cars (
car_id INT PRIMARY KEY,
make VARCHAR(50),
type VARCHAR(50),
style VARCHAR(50),
cost INT
);
--------------------
INSERT INTO cars (car_id, make, type, style, cost)
VALUES (1, 'Honda', 'Civic', 'Sedan', 30000),
(2, 'Toyota', 'Corolla', 'Hatchback', 25000),
(3, 'Ford', 'Explorer', 'SUV', 40000),
(4, 'Chevrolet', 'Camaro', 'Coupe', 36000),
(5, 'BMW', 'X5', 'SUV', 55000),
(6, 'Audi', 'A4', 'Sedan', 48000),
(7, 'Mercedes', 'C-Class', 'Coupe', 60000),
(8, 'Nissan', 'Altima', 'Sedan', 26000);
--------------------

CREATE TABLE salespersons (
salesman_id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
city VARCHAR(50)
);
--------------------
INSERT INTO salespersons (salesman_id, name, age, city)
VALUES (1, 'John Smith', 28, 'New York'),
(2, 'Emily Wong', 35, 'San Fran'),
(3, 'Tom Lee', 42, 'Seattle'),
(4, 'Lucy Chen', 31, 'LA');
--------------------

CREATE TABLE sales (
sale_id INT PRIMARY KEY,
car_id INT,
salesman_id INT,
purchase_date DATE,
FOREIGN KEY (car_id) REFERENCES cars(car_id),
FOREIGN KEY (salesman_id) REFERENCES salespersons(salesman_id)
);
--------------------
INSERT INTO sales (sale_id, car_id, salesman_id, purchase_date)
VALUES (1, 1, 1, '2021-01-01'),
(2, 3, 3, '2021-02-03'),
(3, 2, 2, '2021-02-10'),
(4, 5, 4, '2021-03-01'),
(5, 8, 1, '2021-04-02'),
(6, 2, 1, '2021-05-05'),
(7, 4, 2, '2021-06-07'),
(8, 5, 3, '2021-07-09'),
(9, 2, 4, '2022-01-01'),
(10, 1, 3, '2022-02-03'),
(11, 8, 2, '2022-02-10'),
(12, 7, 2, '2022-03-01'),
(13, 5, 3, '2022-04-02'),
(14, 3, 1, '2022-05-05'),
(15, 5, 4, '2022-06-07'),
(16, 1, 2, '2022-07-09'),
(17, 2, 3, '2023-01-01'),
(18, 6, 3, '2023-02-03'),
(19, 7, 1, '2023-02-10'),
(20, 4, 4, '2023-03-01');

Entity Relationship Diagram

Questions

Click here to follow along and answer the questions:

  1. What are the details of all cars purchased in the year 2022?

    SELECT 
        cars.car_id,
        cars.make,
        cars.type,
        cars.style,
        cars.cost,
        sales.purchase_date
    FROM cars
    JOIN sales ON cars.car_id = sales.car_id
    WHERE YEAR(purchase_date) = 2022;
    

  2. What is the total number of cars sold by each salesperson?

    SELECT 
        salespersons.salesman_id,
        salespersons.name,
        salespersons.age,
        salespersons.city,
        COUNT(sales.sale_id) AS total_cars_sold
    FROM salespersons
    JOIN sales ON salespersons.salesman_id = sales.salesman_id
    GROUP BY salespersons.salesman_id;
    

  3. What is the total revenue generated by each salesperson?

    SELECT 
        salespersons.salesman_id,
        salespersons.name,
        salespersons.age,
        salespersons.city,
        SUM(cars.cost) AS total_revenue
    FROM salespersons
    JOIN sales ON salespersons.salesman_id = sales.salesman_id
    JOIN cars ON sales.car_id = cars.car_id
    GROUP BY salespersons.salesman_id;
    

  4. What are the details of the cars sold by each salesperson?

    SELECT DISTINCT
        sales.salesman_id,
        salespersons.name,
        cars.make,
        cars.type,
        cars.style
    FROM cars
    JOIN sales ON sales.car_id = cars.car_id
    JOIN salespersons ON salespersons.salesman_id = sales.salesman_id
    GROUP BY sales.sale_id;
    

  5. What is the total revenue generated by each car type?

    SELECT 
        cars.car_id,
        cars.make,
        cars.type,
        cars.style,
        SUM(cars.cost) AS total_revenue_by_car
    FROM cars
    JOIN sales ON sales.car_id = cars.car_id
    GROUP BY cars.car_id;
    

  6. What are the details of the cars sold in the year 2021 by salesperson ‘Emily Wong’?

    SELECT 
        salespersons.name,
        cars.car_id,
        cars.make,
        cars.type,
        cars.style,
        cars.cost
    FROM cars
    JOIN sales ON sales.car_id = cars.car_id
    JOIN salespersons ON salespersons.salesman_id = sales.salesman_id
    WHERE YEAR(sales.purchase_date) = 2021 AND salespersons.name = 'Emily Wong';
    

  7. What is the total revenue generated by the sales of hatchback cars?

    SELECT 
        cars.style, 
        SUM(cars.cost) AS hatchback_total_revenue
    FROM cars
    JOIN sales ON sales.car_id = cars.car_id
    WHERE cars.style = 'Hatchback'
    GROUP BY cars.car_id;
    

  8. What is the total revenue generated by the sales of SUV cars in the year 2022?

    SELECT 
        cars.style, 
        SUM(cars.cost) AS SUV_total_revenue_2022
    FROM cars
    JOIN sales ON sales.car_id = cars.car_id
    WHERE cars.style = 'SUV' AND YEAR(purchase_date) = 2022;
    

  9. What is the name and city of the salesperson who sold the most number of cars in the year 2023?

    SELECT 
        salespersons.salesman_id,
        salespersons.name,
        salespersons.city,
        COUNT(sales.sale_id) AS total_cars_sold
    FROM salespersons
    JOIN sales ON salespersons.salesman_id = sales.salesman_id
    WHERE YEAR(sales.purchase_date) = 2023
    GROUP BY salespersons.salesman_id
    ORDER BY total_cars_sold DESC
    LIMIT 1;
    

  10. What is the name and age of the salesperson who generated the highest revenue in the year 2022?

    SELECT 
        salespersons.salesman_id,
        salespersons.name,
        salespersons.age,
        SUM(cars.cost) AS highest_revenue
    FROM salespersons
    JOIN sales ON salespersons.salesman_id = sales.salesman_id
    JOIN cars ON cars.car_id = sales.car_id
    WHERE YEAR(sales.purchase_date) = 2022
    GROUP BY salespersons.salesman_id
    ORDER BY highest_revenue DESC
    LIMIT 1;
    

Conclusion

This SQL project offers valuable insights into the world of data analysis, showcasing its significance as a vital tool for informed decision-making. Steve’s Car Showroom project informs us about cars purchased, salesperson performance, and revenue generation throughout the years. This is achieved by identifying popular cars, evaluating the sales team, and acknowledging high-earning salespeople. These insights are key to driving Steve’s Car Showroom towards success and prosperity by enabling data-driven decision-making and optimizing business operations.

Adopted form Steel Data.

John Michael Karanja
John Michael Karanja
Digital Skills Trainer | Freelance Writer & Tutor | Junior Data Analyst