Logo 
Search:

Oracle Articles

Submit Article
Home » Articles » Oracle » DatabaseRSS Feeds

Car rental agency database

Posted By: Audris Schmidt     Category: Oracle     Views: 17494

Article that create car rental agency database with alter, insert, select, update, delete etc...

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;

  
Share: 


Didn't find what you were looking for? Find more on Car rental agency database Or get search suggestion and latest updates.

Audris Schmidt
Audris Schmidt author of Car rental agency database is from Frankfurt, Germany.
 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
Jaxon Smith from Singapore Comment on: Jun 24
When and by whom was this article about car rentals written? Don't people already know about spreadsheets?
car booking Singapore http://cars.sgbooking.com/

Jaxon Smith from Singapore Comment on: Jun 17
I have explain so many article of this site in which some of them were very interesting and inspiring.This article has good title with good description.

David John from India Comment on: Nov 30
One should rent a car from a reputed company. With the help of this you will invest with the reliable source.

car rental in singapore
http://sg.sixt.com/

View All Comments