Return to Assignments 

Answers to Assignment 2 based on Car Rental Database 

1. Retrieve all customers who currently have car rental bookings. Display their customer number, name and address together with the rental start date.

Selectc.cust_no,c.cust_name,

c.address,b.date_rent_start

from customers c, bookings b

where c.cust_no=b.cust_no;

2. Retrieve all customers who have booked a car in-group 'A1' and display the customer number, model, registration, rate per day, rate per mile and date reserved.

select cust.cust_no, car.model_name, car.registration, cg.rate_per_day, cg.rate_per_mile, b.date_reserved

 

from customers cust,cars car, cargroups cg, bookings b

 

where cust.cust_no = b.cust_no

and b.registration=car.registration

and car.car_group_name=cg.car_group_name

and car.car_group_name='A1'

 

3. Retrieve the registration, group, model, rate per day, date reserved, date rental start, customer number, customer name and address for all customer bookings taken since February 1 1992.

select b.registration, car.Car_group_name, car.model_name, cg.rate_per_day, b.date_rent_start, b.cust_no, cust.cust_name, cust.address

from bookings b, cargroups cg, customers cust, cars car

where cust.cust_no = b.cust_no

and b.registration=car.registration

and car.car_group_name=cg.car_group_name

and b.date_reserved>#2/1/92#;

4. Retrieve model, registration, rate per day from the cars table, for those cars whose daily rate is less than the daily rate for model 'P944 T'.

select car.model_name, car.registration, cg.rate_per_day

from cars car, cargroups cg

where car.car_group_name = cg.car_group_name and cg.rate_per_day <

(select distinct cg2.rate_per_day

from cargroups cg2, cars car2

where cg2.car_group_name=car2.car_group_name

and car2.model_name = 'P944 T')

5. Select customer number, name, town, from the customers table for customers who have had their bookings taken by ' jane b'.

select cust_no, cust_name, Town from customers where cust_no in

(select cust_no from bookings

where reserved_by='jane b')

6. Retrieve model, registration, rental start date, customer number and town for all bookings made by customer who live in London, Bristol or Birmingham.

select car.model_name, b.registration, b.date_rent_start, b.cust_no, cust.town

from cars car, bookings b, customers cust

where car.registration=b.registration

and b.cust_no=cust.cust_no

and cust.cust_no in

(select cust_no from customers

where town='London'

or town = 'Bristol'

or town = 'Birmingham')

7. Write a SELECT statement to find the car, which has the cheapest daily rate.

select car.model_name, car.registration, car.car_group_name, cg.rate_per_day

from cars car, cargroups cg

where car.car_group_name=cg.car_group_name

and cg.rate_per_day =

(select min(rate_per_day) from cargroups)

8. Write a SELECT statement to return the number of car groups in the cars table. Do not include duplicates.

select count(*)

from cargroups

where car_group_name in

(select distinct car_group_name from cars)

 

9. Write a SELECT statement to return the total number of customers who have rented during 1989. Use the HAVING clause to exclude those customers who did not charge the rental to their account.

select count(*)

from bookings

where date_rent_start>=#1/1/89#

and date_rent_start<=#12/31/89#

group by pay_method

having pay_method = 'C';

 

10. Write a SELECT statement, which returns just the models, groups and maintenance intervals of the cars in the cars table. The rows should be returned in ascending order of car group and descending order of maintenance interval.

select model_name, car_group_name, maint_int

from models

order by car_group_name asc, maint_int desc;

11. Write a statement which creates a view of the bookings table to include customer number, car group, model, date reserved, reserved by, date rental start, rental period and pay method.

create view MyView as

select b.cust_no, c.car_group_name, c.model_name, b.date_reserved, b.reserved_by, b.date_rent_start, b.rental_period, b.pay_method

from bookings b, cars c

where b.registraion=c.registration;

 

12. Select date bought for all cars in-group A2, display the date in a form similar to ' the 10^th day of January, 1992'.

 Return to Assignments