Question

Question 1                                                                                   

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.

RegoNo	MakeAndModel	NumOfSeats	ManufacturedYear	CategoryID	CategoryName
ABC455	Toyota Camry	5		1	sedan
NAK455	Toyota Corolla	4	2017	1	sedan

Question 2                                                                                  

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.

RegoNo	MakeAndModel	NumOfSeats	ManufacturedYear	Category	DailyHireRate
ABC455	Toyota Camry	5		sedan	$35.00
PAK561	Nissan Navara	8	2017	minivan	$60.00
QLD101	Volvo XC60	5	2018	suv	$55.00
SOU320	GM Cardillac	7	2011	limos	$450.00

Question 3                                                                                    

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.

RegoNo	MakeAndModel	ManufacturedYear	NumOfRental
NAK455	Toyota Corolla	2017	2
QWA321	VW Caravelle	2017	2

Question 4                                                                                  

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.

FirstName	LastName	RegoNo	MakeAndModel	StartDate	EndDate	Cost
Tony	Morrison	LLP677	Toyota Hilux	15/06/2019	21/06/2019	$280.00

Question 5                                                                                   

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.

FirstName	LastName	Phone	NumbOfRental
James	Farrell	0733099000	2
Issac	Newton	0299001122	2
Tony	Morrison	0490787772	1
David	Hacker	0749008877	1

Answer 1

Question 1                                                                                    (2 marks)

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.

RegoNo	MakeAndModel	NumOfSeats	ManufacturedYear	CategoryID	CategoryName
ABC455	Toyota Camry	5		1	sedan
NAK455	Toyota Corolla	4	2017	1	sedan

NOTE : Table name is not given for any table, hence 'TABLE_NAME' is used as name of the table for all questions.

ANSWER

SELECT * FROM TABLE_NAME WHERE CategoryName='sedan' AND MakeandModel LIKE %T;

Question 2                                                                                   (2 marks)

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.

RegoNo	MakeAndModel	NumOfSeats	ManufacturedYear	Category	DailyHireRate
ABC455	Toyota Camry	5		sedan	$35.00
PAK561	Nissan Navara	8	2017	minivan	$60.00
QLD101	Volvo XC60	5	2018	suv	$55.00
SOU320	GM Cardillac	7	2011	limos	$450.00

In above table the field NumOf Rental is not present, Hence the above question cannot be answered.

Question 3                                                                                    (2 marks)

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.

RegoNo	MakeAndModel	ManufacturedYear	NumOfRental
NAK455	Toyota Corolla	2017	2
QWA321	VW Caravelle	2017	2

SELECT * FROM TABLE_NAME WHERE NumOfRental > 1;

Question 4                                                                                   (2 marks)

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.

FirstName	LastName	RegoNo	MakeAndModel	StartDate	EndDate	Cost
Tony	Morrison	LLP677	Toyota Hilux	15/06/2019	21/06/2019	$280.00

In the above table there is only cost present and from this it is unable to find the most expensive cost.

QUESTION 5

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.

FirstName	LastName	Phone	NumbOfRental
James	Farrell	0733099000	2
Issac	Newton	0299001122	2
Tony	Morrison	0490787772	1
David	Hacker	0749008877	1

SELECT * FROM TABLE_NAME ORDER BY NumOfRental DESC;

NOTE :: In all the 5 questions all the field are asked to display, hence * is  used.