Question

this question is based on tables listed below:

CUSTOMER

CustNo	Custname	Phone
100	Smith	999-1111
200	Jones	222-3456
300	Bruce	123-9999
400	Tan	888-4321

CAR

RegistrationNo	Make
9AA 321	Holden
6KZ 516	Datsun
8ZZ 999	Ford
7YT 555	Holden

RENTAL

RentalNo	CustNo	Registration	DateRented
1	200	9AA 321	7-Oct-15
2	300	6KZ 516	7-Oct-15
3	200	8ZZ 999	18-Oct-15
4	100	6KZ 516	12-Nov-15
5	300	9AA 321	14-Nov-15

Give SQL statements for each of the following:

a. list all the cars rented by customer Smith since 1 November 2015.

b. list each make of car, and the total number of cars of that make.

c. list the registration number of all the cars that have never been rented.

d. change the phone number of customer jones to 999-1234.

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

Question a:

SQL Query :

select RENTAL.Registration,Make from RENTAL,CAR,CUSTOMER
where RENTAL.RegistrationNo=CAR.RegistrationNo and
RENTAL.CustNo=CUSTOMER.CustNo and Custname='Smith' and
DateRented > '1-Nov-15';

Explanation :

• Above sql query will join three tables RENTAL,CAR,CUSTOMER
• and will return the details.

***********************

Question b:

SQL Query :

select Make,count(RegistrationNo) as 'total number of cars' from RENTAL
group by Make;

Explanation :Above sql query will count the number of cars by make.

***********************

Question c:

SQL Query :

select RegistrationNo from Car where RegistrationNo not in
(select RegistrationNo from RENTAL);

Explanation :Above sql query will list all the RegistrationNo that never been rented.

***********************

Question d:

SQL Query :

update CUSTOMER set Phone='999-1234' where Custname='Jones';

Explanation :Above sql query will update the phone number.

***********************

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.