Question

Tables

CUSTOMER

CustomerID	FirstName	LastName	Address	Phone	LicenseNo
1	David	Hacker	101 Yammba road, Rockhampton	0749008877	089 777 123
2	Tony	Morrison	98 South street, Melbourne	0490787772	088777555
3	Issac	Newton	90 Heaven road, Sydney	0299001122	099 776 123
4	James	Farrell	101 St Lucia Garden, Brisbane	0733099000	090 566 777
5	David	Land	345 Illinois road, Brisbane	0739000554	456 000 999
6	Peter	Garry	201 South port road, Gold Coast	0745676766	234 090 767

RENTAL

RentalID	CustomerID	RegoNo	StartDate	EndDate	Cost
1	4	NAK455	1/07/2019	3/07/2019	$105.00
2	4	QWA321	5/07/2019	7/07/2019	$180.00
3	1	QWA321	2/06/2019	5/06/2019	$240.00
4	2	LLP677	15/06/2019	21/06/2019	$280.00
5	3	SUN909	15/07/2019	18/07/2019	$140.00
6	3	NAK455	2/08/2019	5/08/2019	$140.00

CAR

RegoNo	MakeAndModel	NumOfSeats	ManufacturedYear	CategoryID
ABC455	Toyota Camry	5		1
LLP677	Toyota Hilux	4		2
NAK455	Toyota Corolla	4	2017	1
PAK561	Nissan Navara	5	2017	3
QWA321	VW Caravelle	9	2017	3
SOU320	GM Cardillac	7	2011	4
SUN909	VW Passat	5	2000	1
QLD101	Volvo XC60	5	2017	5

VEHICLE_CATEGORY

CategoryID	CategoryName	DailyHireRate
1	sedan	$35.00
2	ute	$40.00
3	minivan	$60.00
4	limos	$450.00
5	suv	$55.00

Note that:

• You are asked to provide a general solution to each request. If the database contents change, each of your queries should continue to answer the information requested correctly.
• Simple queries are preferred; if your queries are unnecessarily complex you may lose marks.
• For the given sample data, your queries should be able to generate the same data and column names as shown in the result table for each request.
• You are not required to sort the results in any order unless requested.
• State any assumption that you make to clarify your understanding of the information request.

Provide appropriate queries for the following requirements

List the details of cars that are Toyota sedan. The details include registration number, make and model, the number of seats, manufactured year, category ID, and category name.

<SQL_QUERY>

Which cars have never been rented out? List the details that include the car’s registration number, make and model, number of seats, manufactured year, category as well as the daily hire rate.

<SQL_QUERY>

Which cars have been rented out more than once? Show the car’s

registration number, make and model, manufactured year, and the number of rentals.

<SQL_QUERY>

Find out the rental activities that have the most expensive cost. This includes the customer names, car registration numbers, make and models, the start hire dates, end hire dates and the costs. Note: You will need to use an aggregate function.        

<SQL_QUERY>

How many times of rental activity were from each customer? Show the customer name, phone, and the number of rentals. Order the list so that the customers who rented the most appear first.

<SQL_QUERY>

Answer 1

Here are the SQL queries:

-- List the details of cars that are Toyota sedan. The details include registration number, make and model, the number of seats, manufactured year, category ID, and category name.

select RegoNo, MakeAndModel, NumOfSeats, ManufacturedYear, c.CategoryId, v.CategoryName
        from CAR c inner join VEHICLE_CATEGORY v ON c.CategoryID = v.CategoryID
        WHERE MakeAndModel LIKE "Toyota%"
        
-- Which cars have never been rented out? List the details that include the car’s registration number, make and model, number of seats, manufactured year, category as well as the daily hire rate.
select RegoNo, MakeAndModel, NumOfSeats, ManufacturedYear, v.CategoryName, v.DailyHireRate
        from CAR c inner join VEHICLE_CATEGORY v ON c.CategoryID = v.CategoryID
        WHERE RegoNo not in (select RegoNo from RENTAL)
        
-- Which cars have been rented out more than once? Show the car’s
registration number, make and model, manufactured year, and the number of rentals.
select c.RegoNo, MakeAndModel, ManufacturedYear, count(*) as NoRentals
        from CAR c inner join RENTAL r on c.RegoNo = r.RegoNo
        GROUP BY RegoNo
        HAVING count (*) > 1
        
-- Find out the rental activities that have the most expensive cost. This includes the customer names, car registration numbers, make and models, the start hire dates, end hire dates and the costs. Note: You will need to use an aggregate function.  
select FirstName + " " + LastName as CustomerName, c.RegoNo, MakeAndModel, StartDate, EndDate, Cost
        from CAR c inner join RENTAL r on c.RegoNo = r.RegoNo
                INNER JOIN CUSTOMER u ON u.CustomerID = r.CustomerID
                WHERE Cost = (select MAX(cost) from RENTAL)
                
-- How many times of rental activity were from each customer? Show the customer name, phone, and the number of rentals. Order the list so that the customers who rented the most appear first.
select FirstName + " " + LastName as CustomerName, Phone, COUNT(*) as NoRentals
        from Customer c INNER JOIN RENTAL r ON c.CustomerID = r.CustomerID
        GROUP BY CustomerID

In case of any doubts or questions, please ask via Comments.