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

1.DDL Commands for creating table.

CREATE TABLE Drivers(Dno INT(10) NOT NULL ,

Dname CHAR(50) NOT NULL ,

age INT(5)

PRIMARY KEY (Dno));

CREATE TABLE Reservation(Dno INT(10) NOT NULL ,

Cno INT(10) NOT NULL,

Day DATE NOT NULL

PRIMARY KEY ( Dno,Cno ),

FOREIGN KEY (Dno) REFERENCES Drivers (Dno),

FOREIGN KEY (Cno) REFERENCES Cars (Cno);

CREATE TABLE Cars(Cno INT (10) NOT NULL,

Cmake CHAR(10),

Color CHAR(20) CHECK (Color = 'blue' OR 'white' OR 'red' OR 'green'),

Primary Key(Cno));

2. Insert data.

[I'm writing the query for one entry in each table.]

>INSERT INTO Drivers(Dno,Dname,age) VALUES(22,"Dustin",45);

>INSERT INTO reservation(Dno,Cno,Day) VALUES(22,101,"10/10");

>INSERT INTO Cars(Cno,Cmake,Color) VALUES(101,"BMW","blue");

3.   Query for names of drivers who have reserved car no 103.

>>  SELECT Drivers.Dname FROM Drivers LEFT JOIN Reservation ON Drivers.Dno=Reservation.Dno WHERE Reservation.Cno= "103" ;

4. Query for names of Drivers who have reserved red or green cars.

>>  SELECT Drivers.Dname FROM Drivers LEFT JOIN Reservation ON Drivers.Dno=Reservation.Dno WHERE Reservation.Cno IN (SELECT Cars.Cno FROM Cars LEFT JOIN Reservation ON Drivers.Dno=Reservation.Dno WHERE Cars.Color ="red" OR "green");

5. Query for driver Dno of drivers with age over 20 who have not reserved a red car.

>> SELECT Drivers.Dname FROM Driver WHERE Driver.age>20 AND Dno EXIST IN (SELECT * FROM Reseravtion INNER JOIN Cars ON Reservation.Cno=Cars.cno WHERE NOT Cars.Color ="red" );

6 Query for names of drivers who have reserved all cars.

>> SELECT Dname FROM Drivers WHERE Dno = ALL (SELECT Dno FROM Reseravtion );

7. Query for changing the age of Alice to 18 years old.

>> UPDATE Drivers SET age = '18' WHERE Dno. = "23";

8. Query for name of the drivers whom rent more than 3 times along with the number of the times.

>> SELECT Drivers.Dname, Reservation.day FROM Drivers JOIN Reservation ON Drivers.Dno=Reservation.Dno WHERE COUNT(Cno)>3;

9. Query for getting the name of the drivers along with the day they rent cars in descending order

>>   SELECT Drivers.Dname FROM Drivers INNER JOIN Reservation ON Drivers.Dno = Reservation.Dno
ORDER BY Reservation.Day DESC;

10. Query for deleting the information of the drivers younger than 20 years old.

>> DELETE FROM Drivers WHERE age<20 ;