Semester Examinations
01) Please prepare the My SQL Database and SQL Queries with screenshot of the below SQL queries results. Use the relations CUSTOMER, RENTAL, CAR, and VEHICLE_CATEGORY provided below. These relations describe the activities in which customers hire cars from a car hire service firm. The relevant data on customers, cars, and rental activities are stored in a database.
Relations
CUSTOMER(CustomerID, FirstName, LastName, Address, Phone, LicenseNo)
CAR(RegoNo, MakeAndModel, ManufacturedYear, NumOfSeats, CategoryID)
XXXXXXXXXXForeign Key (CategoryID) References Vehicle_Category
VEHICLE_CATEGORY(CategoryID, CategoryName, DailyHireRate)
RENTAL(RentalID, CustomerID, RegoNo, StartDate, EndDate, Cost)
XXXXXXXXXXForeign Key (CustomerID) References Custome
XXXXXXXXXXForeign Key (RegoNo) References Ca
Note: The following E-R diagram may assist you to understand the relations as
XXXXXXXXXXdescribed as above.
CUSTOMERCustomerIDFirstNameLastNameAddressPhoneLicenseNoCARRegoNoMake&ModelNumOfSeatsManufacturedYearVEHICLE_CATEGORYCategoryIDCategoryNameDaily hire rateRENTALRentalIDStartDateEndDate[Cost]MakeRentBelong tod
Tables
CUSTOMER
CustomerID
FirstName
LastName
Address
Phone
LicenseNo
1
David
Hacke
101 Yammba road, Rockhampton
XXXXXXXXXX
XXXXXXXXXX
2
Tony
Mo
ison
98 South street, Melbourne
XXXXXXXXXX
XXXXXXXXXX
3
Issac
Newton
90 Heaven road, Sydney
XXXXXXXXXX
XXXXXXXXXX
4
James
Fa
ell
101 St Lucia Garden, Brisbane
XXXXXXXXXX
XXXXXXXXXX
5
David
Land
345 Illinois road, Brisbane
XXXXXXXXXX
XXXXXXXXXX
6
Pete
Ga
y
201 South port road, Gold Coast
XXXXXXXXXX
XXXXXXXXXX
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
ManufacturedYea
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 co
ectly.
· Simple queries are prefe
ed; 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.
Question 1 XXXXXXXXXX2 marks)
List the details of cars that are Toyota sedans. The details include registration number, make and model, the number of seats, manufactured year, category ID, and category name.
RegoNo
MakeAndModel
NumOfSeats
ManufacturedYea
CategoryID
CategoryName
ABC455
Toyota Camry
5
1
sedan
NAK455
Toyota Corolla
4
2017
1
sedan
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
ManufacturedYea
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
(2 marks)
Which cars have been rented out more than once? Show the car’s
egistration number, make and model, manufactured year, and the number of rentals.
RegoNo
MakeAndModel
ManufacturedYea
NumOfRental
NAK455
Toyota Corolla
2017
2
QWA321
VW Caravelle
2017
2
Question 4
(2 marks)
Find out all rental activities that have the costs higher than the average cost. This includes the customer names, car registration numbers, make and models, the start hire dates, end hire dates and the costs.
FirstName
LastName
RegoNo
MakeAndModel
StartDate
EndDate
Cost
David
Hacke
QWA321
VW Caravelle
02/06/2019
05/06/2019
$240.00
Tony
Mo
ison
LLP677
Toyota Hilux
15/06/2019
21/06/2019
$280.00
Question 5
(2 marks)
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
Fa
ell
XXXXXXXXXX
2
Issac
Newton
XXXXXXXXXX
2
Tony
Mo
ison
XXXXXXXXXX
1
David
Hacke
XXXXXXXXXX
1
Question 6
(2 marks)
Define a stored procedure with a parameter that represents the category name of a car. When the stored procedure is called, the value of parameter is passed to the procedure. The procedure will display the details of all cars with the value of parameter passed. For example, if the value of category name is ‘sedan’, when the stored procedure is executed, it will display the following result:
RegoNo
MakeAndModel
NumOfSeats
ManufacturedYea
CategoryName
DailyHireRate
ABC455
Toyota Camry
5
sedan
$35.00
NAK455
Toyota Corolla
4
2017
sedan
$35.00
SUN909
VW Passat
5
2000
sedan
$35.00
4