Question

create a DDL MYSQL

create a database where a user can sell their vehicle, lease a vehicle, finance a cart, or donate their current vehicle.

Also we'll have a adminstrator that will have access to the customers and vehicles.

Answer 1

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