Consider the following car rental agency database.
Customers(CID, first_name, Last_Name, Address)
Vehicle(VID, Mileage, Location, Size, Transmission)
Reservations(CID,VID, Start_Date, End_Date)
Note :
->Vehicle.transmission can have two values ‘manual’ and ‘automatic’.
->Vehicle.size can have following values. ‘compact’, ‘mid-size’, ‘full-size’, ‘premium’ and ‘luxury’. The default size is compact.
Create customers table
CREATE TABLE Customers
(
CID varchar2(5) PRIMARY KEY,
FIRST_NAME varchar2(20),
LAST_NAME varchar2(15),
ADDRESS varchar2(30)
);
Create vehicle table
CREATE TABLE Vehicle
(
VID varchar2(5) PRIMARY KEY,
MILEAGE NUMBER(7,2),
LOCATION varchar2(20),
VSIZE varchar2(30) CHECK (VSIZE IN('COMPACT','MID-SIZE','FULL-SIZE','PREMIUM','LUXURY')),
TRANSMISSION varchar2(10) CHECK (TRANSMISSION IN('MANUAL','AUTOMATIC'))
);
Create reservation table
CREATE TABLE Reservation
(
CID varchar2(5) REFERENCES Customers(CID),
VID varchar2(5) REFERENCES Vehicle(VID),
START_DATE DATE,
END_DATE DATE,
PRIMARY KEY(CID,VID)
);
Description / definition of customer table
DESC CUSTOMERS;
Output :
Name Null? Type
CID NOT NULL VARCHAR2(5)
FIRST_NAME VARCHAR2(20)
LAST_NAME VARCHAR2(15)
ADDRESS VARCHAR2(30)
Description / definition of vehicle table
DESC VEHICLE;
Output :
Name Null? Type
VID NOT NULL VARCHAR2(5)
MILEAGE NUMBER(7,2)
LOCATION VARCHAR2(20)
VSIZE VARCHAR2(30)
TRANSMISSION VARCHAR2(10)
Description / definition of reservation table
DESC RESERVATION;
Output :
Name Null? Type
CID NOT NULL VARCHAR2(5)
VID NOT NULL VARCHAR2(5)
START_DATE DATE
END_DATE DATE
Insert statements
INSERT STATEMENT FOR CUSTOMER TABLE
INSERT INTO Customers
(CID,FIRST_NAME,LAST_NAME,ADDRESS) VALUES ('101','KRUNAL','PATEL','MANINAGAR,AHMD');
INSERT INTO Customers
(CID,FIRST_NAME,LAST_NAME,ADDRESS) VALUES ('102','BHAVESH','MODI','VADAJ,AHMD');
INSERT INTO Customers
(CID,FIRST_NAME,LAST_NAME,ADDRESS) VALUES ('103','DARSHAN','DERASARI','HIMANTNAGAR');
INSERT INTO Customers
(CID,FIRST_NAME,LAST_NAME,ADDRESS) VALUES ('104','VISHAL','DAVE','ISSANPUR,AHMD');
INSERT INTO Customers
(CID,FIRST_NAME,LAST_NAME,ADDRESS) VALUES ('105','SAGAR','SHAH','VATVA,AHMD');
INSERT STATEMENT FOR VEHICLE TABLE
INSERT INTO Vehicle (VID,MILEAGE,LOCATION,VSIZE,TRANSMISSION)
VALUES ('V-101','70','AHMD','COMPACT','AUTOMATIC');
INSERT INTO Vehicle (VID,MILEAGE,LOCATION,VSIZE,TRANSMISSION)
VALUES ('V-102','50','SURAT','COMPACT','AUTOMATIC');
INSERT INTO Vehicle (VID,MILEAGE,LOCATION,VSIZE,TRANSMISSION)
VALUES ('V-103','10','AHMD','MID-SIZE','MANUAL');
INSERT INTO Vehicle (VID,MILEAGE,LOCATION,VSIZE,TRANSMISSION)
VALUES ('V-104','30','AHMD','MID-SIZE','AUTOMATIC');
INSERT INTO Vehicle (VID,MILEAGE,LOCATION,VSIZE,TRANSMISSION)
VALUES ('V-105','15','VADODARA','FULL-SIZE','AUTOMATIC');
INSERT INTO Vehicle (VID,MILEAGE,LOCATION,VSIZE,TRANSMISSION)
VALUES ('V-106','20','AHMD','LUXURY','AUTOMATIC');
INSERT INTO Vehicle (VID,MILEAGE,LOCATION,VSIZE,TRANSMISSION)
VALUES ('V-107','50','AHMD','LUXURY','MANUAL');
INSERT STATEMENT FOR RESERVATION TABLE
INSERT INTO Reservation
(CID,VID,START_DATE,END_DATE) VALUES ('101','V-101','10-JAN-2001','10-FEB-2005');
INSERT INTO Reservation
(CID,VID,START_DATE,END_DATE) VALUES ('102','V-102','12-MAR-2001','10-JUN-2006');
INSERT INTO Reservation
(CID,VID,START_DATE,END_DATE) VALUES ('103','V-103','15-FEB-1999','09-SEP-2005');
INSERT INTO Reservation
(CID,VID,START_DATE,END_DATE) VALUES ('105','V-105','15-FEB-2003','09-SEP-2005');
Display records of customer table
SELECT * FROM CUSTOMERS;
Output :
CID FIRST_NAME LAST_NAME ADDRESS
101 KRUNAL PATEL MANINAGAR,AHMD
102 BHAVESH MODI VADAJ,AHMD
103 DARSHAN DERASARI HIMANTNAGAR
104 VISHAL DAVE ISSANPUR,AHMD
105 SAGAR SHAH VATVA,AHMD
Display records of vehicle table
SELECT * FROM VEHICLE;
Output :
VID MILEAGE LOCATION VSIZE TRANSMISSI
V-101 70 AHMD COMPACT AUTOMATIC
V-102 50 SURAT COMPACT AUTOMATIC
V-103 10 AHMD MID-SIZE MANUAL
V-104 30 AHMD MID-SIZE AUTOMATIC
V-105 15 VADODARA FULL-SIZE AUTOMATIC
V-106 20 AHMD LUXURY AUTOMATIC
V-107 50 AHMD LUXURY MANUAL
Display records of reservation table
SELECT * FROM RESERVATION;
Output :
CID VID START_DAT END_DATE
101 V-101 10-JAN-01 10-FEB-05
102 V-102 12-MAR-01 10-JUN-06
103 V-103 15-FEB-99 09-SEP-05
105 V-105 15-FEB-03 09-SEP-05
Display vehicles which are reserved for maximum times
SELECT * FROM VEHICLE WHERE VID =
(
SELECT VID FROM RESERVATION WHERE ROUND((END_DATE-START_DATE)/365) =
(SELECT MAX(ROUND((END_DATE-START_DATE)/365)) AS "MAXIMUM TIME"FROM RESERVATION)
);
Output :
VID MILEAGE LOCATION VSIZE TRANSMISSI
V-103 10 AHMD MID-SIZE MANUAL
Display vehicles size is the most preferred.
SELECT VSIZE FROM VEHICLE WHERE MILEAGE = (SELECT MAX(MILEAGE) FROM VEHICLE);
Output :
VSIZE
COMPACT
Find location and total mileage of all vehicles specific to each respective location.
SELECT LOCATION,SUM(MILEAGE) FROM VEHICLE GROUP BY LOCATION;
Output :
OCATION SUM(MILEAGE)
HMD 110
URAT 50
ADODARA 15
Find the locations that have at least one vehicle with manual transmission that has lower mileage than any luxury vehicle at that location.
SELECT VID,MILEAGE,TRANSMISSION FROM VEHICLE WHERE TRANSMISSION = 'MANUAL' AND MILEAGE < (SELECT MILEAGE FROM VEHICLE WHERE VSIZE = 'LUXURY');
Output :
VID MILEAGE TRANSMISSION
V-103 10 MANUAL
Find the last names of people who have at least one reservation at a location such that the number of reservations at that location is less than the number of vehicles at that location with automatic transmission.
SELECT COUNT(VID),LOCATION FROM VEHICLE GROUP BY LOCATION NOT IN (SELECT COUNT(VID),LOCATION FROM VEHICLE GROUP BY LOCATION);
Find average mileage of vehicles for each location, which has at least five vehicles.
SELECT AVG(MILEAGE),COUNT(VID),LOCATION FROM VEHICLE GROUP BY LOCATION HAVING COUNT(VID) >= 5;
Output :
AVG(MILEAGE) COUNT(VID) LOCATION
36 5 AHMD
Find names of customers whose lastname starts with ‘S’ and who has reserved more vehicles than the customer with CID as 101.
SELECT LAST_NAME,FIRST_NAME FROM CUSTOMERS WHERE LAST_NAME like 'S%';
Delete all the reservations for customer whose last name starts with ‘S’.
DELETE FROM RESERVATION WHERE CID IN (SELECT CID FROM CUSTOMERS WHERE LAST_NAME LIKE 'S%');
Output :
RESERVATION TABLE BEFORE 'DELETE' OPERATION :=====>
CID VID START_DAT END_DATE
101 V-101 10-JAN-01 10-FEB-05
102 V-102 12-MAR-01 10-JUN-06
103 V-103 15-FEB-99 09-SEP-05
105 V-105 15-FEB-03 09-SEP-05
RESERVATION TABLE AFTER 'DELETE' OPERATION :=====>
CID VID START_DAT END_DATE
101 V-101 10-JAN-01 10-FEB-05
102 V-102 12-MAR-01 10-JUN-06
103 V-103 15-FEB-99 09-SEP-05
Find the customers who have reserved vehicles from all the locations.
SELECT COUNT(LOCATION) FROM VEHICLE GROUP BY LOCATION;