Car retail

Try me

Problem definition

You need to design a database for Daniel Larusso´s car dealership. Larusso´s company is divided into departments. Larusso would like to store the Departments’ name, and identifier. Larusso also wants to store the following information about his employees: First name and last name, phone number, salary, and hiring date. You also want to know in which department they work and which is their manager. Each employee has an identifier. Larusso also wants to store the following information about the company´s customers: First name and last name, phone number, email, and preferred contact method. For each car the dealership sells, you are instructed to store the model, the status of the car (either if the car is ready to be delivered, in the workshop (working), or the company is waiting for the arrival of the car. You also need to keep track of which customer bought it, and which employee made the sell.

Solution

The image below depicts a possible solution:

In the solution, there is a table for each of the entities identified in the problem definition (departments, employees, cars, customers, and sales). Managers are implemented using a self-reference to the table employee. This is allowed in SQL, although it can generate potential circular references issues (references creating a loop).

Try it yourself

You can find the car retail database in the following link.

Data Analysis Queries

Try to build the following queries to analyse data

1. List All Cars Sold by a Specific Employee

2. Get the total Sales and average Sale Price of the cars sold by every employee

3. Write a query to obtain customers’ contact preferences and their purchases

4. Obtain the department-wise employee count and average Salary

Data Analysis Queries (solved)

Try to build the following queries to analyse data

1. List All Cars Sold by a Specific Employee

This query retrieves all the cars sold by a specific employee, including the model, status, and total cost. The function CONCAT is used to get the full name of the employee in a column. Replace 1 with other ID if you would like ot see data from another employee.

SELECT
    e.id,
    CONCAT(e.FName, " ", e.LNAME) as full_name,
    c.Model as car_model,
    c.Status as status,
    c.TotalCost as total_cost
FROM Cars c
LEFT JOIN Employees e ON c.EmployeeId = e.Id
WHERE e.Id = 1;  -- Replace with other employee ID

2. Get the total Sales and average Sale Price of the cars sold by every employee

This query provides a summary of the total sales and average sale price per employee, we only filter out the values of cars sold.

SELECT
    e.Id, CONCAT(e.FName, " ", e.LName) as full_name,
    SUM(c.TotalCost) AS TotalSales,
    AVG(c.TotalCost) AS AverageSalePrice
FROM Employees e
LEFT JOIN Cars c ON e.Id = c.EmployeeId
WHERE c.Status = 'Sold'
GROUP BY e.Id;

3. Write a query to obtain customers’ contact preferences and their purchases This query lists all customers, their preferred contact methods, and details of the cars they purchased. We just use a JOIN clause to join the tables Customers and cCrs.

SELECT cu.FName, cu.LName, cu.PreferredContact, c.Model, c.Status, c.TotalCost
FROM Customers cu
LEFT JOIN Cars c ON cu.Id = c.CustomerId;

4. Obtain the department-wise employee count and average Salary

This query provides the number of employees and average salary in each department.

SELECT d.Name, COUNT(e.Id) AS NumberOfEmployees, AVG(e.Salary) AS AverageSalary
FROM Departments d
JOIN Employees e ON d.Id = e.DepartmentId
GROUP BY d.Id;