please i need a Sql queries for: *I\'m using oracle sql developer 11g* 1)List th

Question

please i need a Sql queries for: *I'm using oracle sql developer 11g*

1)List those employees who have moved their employment within the company. The output must include the Employee Name, Starting Date, Leaving Date, Depot, Rent point, Job Title.
2) List the License plat no, Make, Model, Date of last service, Year Made, Odometer reading for each van held at all the depots.
3) For each rent point, list the number of vehicles that are currently on rent.

------------------------------------------------------------------

The whole file structure and not sure 100% if the whole file is correct

--------------------------------------------------------------

drop table vehicle cascade constraints;
drop table customer cascade constraints;
drop table employee cascade constraints;
drop table car cascade constraints;
drop table van cascade constraints;
drop table rentAgreement cascade constraints;
drop table depot cascade constraints;
drop table rent_point cascade constraints;

CREATE TABLE vehicle
(vPlateNo CHAR(7),
make varchar2 (15),
model char(12),
maxPassengers NUMBER (2),
vYearMade DATE,
topSpeed NUMBER (3),
odometerValue NUMBER (10),
dateoflastservice DATE,
cost NUMBER (7),
onLease Char(3),
CONSTRAINT vehicle_vPlateNo_pk PRIMARY KEY (vPlateNo));

CREATE TABLE customer
(customerId CHAR (7),
Fname VARCHAR2 (10),
Lname VARCHAR2 (10),
cAddress VARCHAR2 (20),
CONSTRAINT customer_customerId_pk PRIMARY KEY (customerId));

CREATE TABLE employee
(empID CHAR (5),
Fname char(10),
Lname char(10),
starting_date DATE,
leaving_date DATE,
works_at char(10),
CONSTRAINT employee_empId_pk PRIMARY KEY (empId));

CREATE TABLE car
(vPlateNo CHAR(7),
class char(10) NOT NULL,
--rental_period VARCHAR2 (15),
--car_charges VARCHAR2 (11),
CONSTRAINT car_vPlateNo_pk PRIMARY KEY (vPlateNo));

CREATE TABLE van
(vPlateNo CHAR(7),
seats NUMBER (2) NOT NULL,
CONSTRAINT van_vPlateNo_pk PRIMARY KEY (vPlateNo));

CREATE TABLE rentAgreement
(vPlateNo CHAR(7),
customerID CHAR(7),
model CHAR(12),
rentDuration Number(3),
rentFee Number(5),
restrictions VARCHAR2 (15),
CONSTRAINT rentAgreement_vPlateNo_pk PRIMARY KEY (vPlateNo),
CONSTRAINT rentAgreement_CustomerID_fk FOREIGN KEY (customerID)
REFERENCES customer (customerID));

CREATE TABLE depot
(depID CHAR(7),
depLocation Char (2),
empID Char(5),
vPlateNo CHAR(7),
CONSTRAINT depot_depID_pk PRIMARY KEY (depID),
CONSTRAINT depot_vPlateNo_fk FOREIGN KEY (vPlateNo)
REFERENCES vehicle (vPlateNo));

CREATE TABLE rent_point
(rentPointID char(6),
vPlateNo CHAR(7),
customerID CHAR (7),
CONSTRAINT rent_point_rentPointID_pk Primary Key (rentPointID),
CONSTRAINT rent_point_customerID_fk FOREIGN KEY (customerID)
REFERENCES customer (customerID));

INSERT INTO vehicle VALUES ('PLN0001', 'Lamborgini','Mercelago', 3, '01-OCT-2015', 160, 45000, '01-OCT-2016', 654000,'Yes');
INSERT INTO vehicle VALUES ('PLN0002', 'Porche','911', 2,'22-FEB-2011', 170, 77999, '22-FEB-2012', 564000,'Yes');
INSERT INTO vehicle VALUES ('PLN0003', 'Pagoni','Zonda', 4, '30-DEC-2002', 190, 54326, '30-DEC-2003', 990000,'Yes');
INSERT INTO vehicle VALUES ('PLN0004', 'KIA','Rio', 5,'04-DEC-2003', 244, 46367, '04-DEC-2004', 464646,'Yes');
INSERT INTO vehicle VALUES ('PLN0005', 'FORD','GT', 10,'15-NOV-2013', 214, 56423, '15-NOV-2014', 876880,'Yes');
INSERT INTO vehicle VALUES ('PLN0006', 'Buggati','Veron', 6,'16-MAY-2015', 199, 46643, '16-MAY-2016', 999610,'No');
INSERT INTO vehicle VALUES ('PLN0007', 'MAZDA','CX5', 8,'31-JUL-2016', 235, 35126, '31-JUL-2017', 452220,'No');
INSERT INTO vehicle VALUES ('PLN0008', 'TOYOTA','Vios', 4,'14-Jan-2002', 137, 64377, '14-Jan-2003', 703330,'No');
INSERT INTO vehicle VALUES ('PLN0009', 'BMW','M5', 12,'30-SEP-2017', 201, 11256, '30-SEP-2018', 723100,'No');
INSERT INTO vehicle VALUES ('PLN0010', 'Ferrari','Spider', 4,'25-OCT-2009', 300, 13653, '17-SEP-2018', 911000,'No');

INSERT INTO customer VALUES ('CNO001', 'Dwayne', 'Ashton', 'Jalan PJU 3/20');
INSERT INTO customer VALUES ('CNO002', 'Daniel', 'Ashton', 'Jalan PJU 3/21');
INSERT INTO customer VALUES ('CNO003', 'Samantha', 'Parker', 'Jalan PJU 3/22');
INSERT INTO customer VALUES ('CNO004', 'Amjad', 'Halabi', 'Jalan PJU 3/23');
INSERT INTO customer VALUES ('CNO005', 'Hazim', 'AlSaidawi', 'Jalan PJU 3/24');
INSERT INTO customer VALUES ('CNO006', 'Eltayeb', 'Adam', 'Jalan PJU 3/25');
INSERT INTO customer VALUES ('CNO007', 'Joe', 'Anvil', 'Jalan PJU 3/26');
INSERT INTO customer VALUES ('CNO008', 'Felix', 'Rich', 'Jalan PJU 3/27');
INSERT INTO customer VALUES ('CNO009', 'Adam', 'Sandler', 'Jalan PJU 3/28');
INSERT INTO customer VALUES ('CNO010', 'Samer', 'Shinigami', 'Jalan PJU 3/29');

INSERT INTO employee VALUES ('NO001', 'Johan', 'Tiger', '02-FEB-2010', '03-MAR-2011', 'Depot');
INSERT INTO employee VALUES ('NO002', 'Sami', 'Koleb', '03-JAN-2011', '04-JAN-2012', 'Depot');
INSERT INTO employee VALUES ('NO003', 'Nami', 'Yatsura', '04-JUL-2012', '05-OCT-2013', 'Depot');
INSERT INTO employee VALUES ('NO004', 'Jack', 'Heng', '05-NOV-2013', '06-DEC-2014', 'Depot');
INSERT INTO employee VALUES ('NO005', 'George', 'Claw', '06-DEC-2014', '', 'Depot');
INSERT INTO employee VALUES ('NO006', 'William', 'Feng', '07-JAN-2015', '', 'RentPoint');
INSERT INTO employee VALUES ('NO007', 'Yamen', 'Kabir', '08-SEP-2016', '', 'RentPoint');
INSERT INTO employee VALUES ('NO008', 'Noah', 'Nelson', '20-APR-2013', '', 'RentPoint');
INSERT INTO employee VALUES ('NO009', 'Julia', 'Robert', '25-MAR-2015', '', 'RentPoint');
INSERT INTO employee VALUES ('NO010', 'Mary', 'Winter', '18-OCT-2014', '', 'RentPoint');

INSERT INTO car VALUES ('PLN0001','Standard');
INSERT INTO car VALUES ('PLN0003','Luxury');
INSERT INTO car VALUES ('PLN0005','Standard');
INSERT INTO car VALUES ('PLN0007','Executive');
INSERT INTO car VALUES ('PLN0009','Executive');

INSERT INTO van VALUES ('PLN0002',8);
INSERT INTO van VALUES ('PLN0004',7);
INSERT INTO van VALUES ('PLN0006',6);
INSERT INTO van VALUES ('PLN0008',8);
INSERT INTO van VALUES ('PLN0010',12);

INSERT INTO rentAgreement VALUES ('PLN0001','CNO001','Mercelago', 27, 1500 , 'None');
INSERT INTO rentAgreement VALUES ('PLN0002','CNO002', '911', 1, 1600,'Return-By-9PM');
INSERT INTO rentAgreement VALUES ('PLN0003','CNO003','Zonda',30,1700, 'None');
INSERT INTO rentAgreement VALUES ('PLN0004','CNO004','Rio',14,1800, 'In-Town-Only');
INSERT INTO rentAgreement VALUES ('PLN0005','CNO005','GT',3,1900, 'None');

INSERT INTO depot VALUES ('DEP001','KL', 'NO001', 'PLN0006');
INSERT INTO depot VALUES ('DEP002','SG', 'NO002', 'PLN0007');
INSERT INTO depot VALUES ('DEP003','SB', 'NO003', 'PLN0008');
INSERT INTO depot VALUES ('DEP004','CJ', 'NO004', 'PLN0009');
INSERT INTO depot VALUES ('DEP005','AM', 'NO005', 'PLN0010');

INSERT INTO rent_point VALUES ('RP001','PLN0001','CNO001');
INSERT INTO rent_point VALUES ('RP002','PLN0002','CNO002');
INSERT INTO rent_point VALUES ('RP003','PLN0003','CNO003');
INSERT INTO rent_point VALUES ('RP004','PLN0004','CNO004');
INSERT INTO rent_point VALUES ('RP005','PLN0005','CNO005');

commit;

Explanation / Answer

1.

SELECT e.Fname, e.Lname, e.starting_date, e.leaving_date, d.depotLocation, r.rentPoint, e.works_at
FROM depot d
JOIN employee e ON e.empID = d.empID
JOIN rentPoint r ON r.vPlateNo = d.vPlateNo;

2.

SELECT v.vPlateNo, v.make, v.model, v.dateOfLastService, v.vYearMade, v.odometerValue
FROM vehicle v
JOIN van n ON n.vPlateNo = v.vPlateNo
JOIN depot d ON d.vPlateNo = v.vPlateNo;

3.

SELECT COUNT (v.vPlateNo)
FROM vehicle v
JOIN rent_agreement r ON r.vPlateNo = v.vPlateNo
JOIN rent_Point p ON p.vPlateNo = v.vPlateNo;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.