Photo by rawpixel on UnsplashIntro
Steve runs a top-end car showroom but his data analyst has just quit and left him without his crucial insights. Can you analyse the following data to provide him with all the answers he requires?
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');
Questions
- What are the details of all cars purchased in the year 2022 ?
select distinct 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;
- What is the total number of cars sold by each salesperson?
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;
- What is the total revenue generated by each salesperson?
select salespersons.name,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;
Benson Kimingiri Wangui
Software Engineer
My professional interests include Web Development, Gamming Development using Pycharm and Web Scrapping Using Python.