Question

Consider the database of a car rental company that contains three tables drivers, cars and reservation tables.

Drivers:                                           Reservation:                              Cars:

Dno	Dname	age		Dno	Cno	Day		Cno	Cmake	Color
22	Dustin	45		22	101	10/10		101	BMW	Blue
29	Brutus	33		22	102	10/10		102	VW	Red
31	Lubber	55		22	103	10/8		103	OPEL	Green
32	Andy	25		22	104	10/7		104	FIAT	Red
58	Rusty	35		31	102	11/10
64	Horatio	35		31	103	11/6
71	Zorba	16		31	104	11/12
74	Horatio	35		64	101	9/5
85	Art	25		64	102	9/8
95	Bob	63		74	103	9/8
23	Alice	15		23	104	9/11

1. Write DDL statements to create the tables

Drivers(Dno, Dname, age)

Reservation(Dno, Cno, Day)

Cars(Cno, Cmake, Color)

Where:

• no field could be empty except the age of the Driver.
• The company does not own more than one car from one maker     
• the colors of the cars should not be other than the following group (blue, white, red , green)

2. Write Queries for the following:
   1. Insert the sample data shown above into the tables

3. Write Queries for the following:
   1. Find the names of drivers who have reserved car no 103.
   2. Find the names of Drivers who have reserved red or Green cars.( use IN, UNION, =SOME/=ALL, and JION    each in separate query)For this segment, 4 responses are required, using the operations shown in parentheses
   3. Find the driver Dno of drivers with age over 20 who have not reserved a read car.
   4. Find the names of drivers who have reserved all cars.
   5. List the name of the drivers along with the day they rent cars in descending order
   6. List the name of the drivers whom rent more than 3 times along with the number of the times.
   7. Change the age of Alice to 18 years old
   8. Delete the information of the drivers younger than 20 years old

Answer 1

Creation table command has been written by following various constraint given in the question. That no field will be empty except age of the driver and company does not own more than one car from one makers and the colour of the car should not be other than blue, white, Red and Green.

Below is the Create command for all the tables with the inserted data has given in the tables.

TABLE: DRIVERS

Create table Drivers(Dno char(3) NOT NULL , Dname Varchar(25) NOT NULL, Age int);

Insert into Drivers values("22", "Dustin", 45);

Insert into Drivers values("29", "Brutus", 33);

Insert into Drivers values("31", "Lubber", 55);

Insert into Drivers values("32", "Andy", 25);

TABLE: RESERVATIONS

Create table Reservation(Dno char(3) NOT NULL, Cno char(3) NOT NULL, Day Char(10));

Insert into Reservation values("22", "101", "10/10");

Insert into Reservation values("22", "102", "10/10");

Insert into Reservation values("22", "103", "10/8");

Insert into Reservation values("22", "104", "10/7");

TABLE: CARS

Create table Cars(Cno char(3) NOT NULL UNIQUE, Cmake Varchar(25) NOT NULL UNIQUE, color Varchar(25));

Insert into Cars values("101", "BMW", "Blue");

Insert into Cars values("102", "VW", "Red");

Insert into Cars values("103", "OPEL", "Green");

Insert into Cars values("104", "FIAT", "Red");

Ans.a) Select Dname from Drivers LEFT JOIN Reservation ON Drivers.Dno=Reservation.Dno where Reservation.Cno="103";

LEFT join is used in order to join two tables DRIVERS and RESERVATION with their respective joining fields in which Dno of DRIVERS table is associated with the Dno of RESERVATION table along with the condition that Car no refers to 103

Ans.b) Select Dname from Drivers LEFT JOIN Reservation ON Drivers.Dno=Reservation.Dno LEFT JOIN Cars ON Reservation.Cno=Cars.Cno where Cars.color="Red" or Cars.color="Green";

LEFT join is used in order to join two tables DRIVERS and RESERVATION and CARS table with their respective joining fields in which Dno of DRIVERS table is associated with the Dno of RESERVATION table and Cno of RESERVATION table associated with Cno of Cars Table along with the condition that colour may be Red or Green.

Select Dname from Drivers LEFT JOIN Reservation ON Drivers.Dno=Reservation.Dno LEFT JOIN Cars ON Reservation.Cno=Cars.Cno where Cars.color="Red";

UNION

Select Dname from Drivers LEFT JOIN Reservation ON Drivers.Dno=Reservation.Dno LEFT JOIN Cars ON Reservation.Cno=Cars.Cno where Cars.color="Green";

LEFT join is used in order to join two tables DRIVERS and RESERVATION and CARS table with their respective joining fields in which Dno of DRIVERS table is associated with the Dno of RESERVATION table and Cno of RESERVATION table associated with Cno of Cars Table along with the condition that colour may be Red or Green by using UNION OPERATOR.

Select Dname from Drivers LEFT JOIN Reservation ON Drivers.Dno=Reservation.Dno LEFT JOIN Cars ON Reservation.Cno=Cars.Cno where Cars.color IN("Red","Green");

LEFT join is used in order to join two tables DRIVERS and RESERVATION and CARS table with their respective joining fields in which Dno of DRIVERS table is associated with the Dno of RESERVATION table and Cno of RESERVATION table associated with Cno of Cars Table along with the condition that colour may be Red or Green by using IN operator.

Ans.c)Select Drivers.Dno from Drivers LEFT JOIN Reservation ON Drivers.Dno=Reservation.Dno LEFT JOIN Cars ON Reservation.Cno=Cars.Cno where Age>20 AND Cars.color!="Red";

LEFT join is used in order to join two tables DRIVERS and RESERVATION and CARS table with their respective joining fields in which Dno of DRIVERS table is associated with the Dno of RESERVATION table and Cno of RESERVATION table associated with Cno of Cars Table along with the condition that age must be smaller than 20 and color will be red.

Ans.d). Select Dname from Drivers LEFT JOIN Reservation ON Drivers.Dno=Reservation.Dno LEFT JOIN Cars ON Reservation.Cno=Cars.Cno where Cars.Cno IN("101","102","103","104");

LEFT join is used in order to join two tables DRIVERS and RESERVATION and CARS table with their respective joining fields in which Dno of DRIVERS table is associated with the Dno of RESERVATION table and Cno of RESERVATION table associated with Cno of Cars Table along with the condition that Cno must lie in the list in 101,102,103, and 104.

Ans.e)Select Dname,Day from Drivers LEFT JOIN Reservation ON Drivers.Dno=Reservation.Dno LEFT JOIN Cars ON Reservation.Cno=Cars.Cno ORDER BY Day Desc;

LEFT join is used in order to join two tables DRIVERS and RESERVATION and CARS table with their respective joining fields in which Dno of DRIVERS table is associated with the Dno of RESERVATION table and Cno of RESERVATION table associated with Cno of Cars Table along with the sorting on the basis of Day field in descending order.

Ans.f)Select Dname, COUNT(*) from Drivers LEFT JOIN Reservation ON Drivers.Dno=Reservation.Dno GROUP BY Dname HAVING COUNT(*)>3;

LEFT join is used in order to join two tables DRIVERS and RESERVATION table with their respective joining fields in which Dno of DRIVERS table is associated with the Dno of RESERVATION table in which day lies with more than 3 times for a particular group of Dname using group by and having clause for condition.

Ans.g)UPDATE Drivers Set Age= 18 where Dname="Alice";

By using UPDATE command, the age of Alice change from 15 to 18.

Ans.h)DELETE from Drivers where Age<20;

DELETE the overall entity/records of the driver whose age lies less than 20 years.

DELETE keyword is used to delete the record in a table.

DROP keyword is used to delete the overall table and DROP keyword with ALTER command is used to DROP the particular column/field of the table.