SQL HOMEWORK Homework #6 – Subqueries Complete the following queries. a) Use sub
ID: 3729323 • Letter: S
Question
SQL HOMEWORK
Homework #6 – Subqueries
Complete the following queries.
a) Use subqueries to answer the following questions
Which drivers begin trips at Olympia with an Effective Date in January 2005?
Do any drivers use more than one bus on their run where the Bid Date is in September 2004?
Which drivers make trips that last longer than 45 minutes?
Which trips are operated by Phantoms and have an Effective Date in 2005?
b) Answer the same questions using inner- or natural joins (where clause is used for the join).
c) Display trips with an Effective Date in 2005 made by Operators with less than three years on the job.
Reference
Operators Runs Vehicles PK OperatorlD PK RunID PK VehiclelD SeniorityNumber FirstName LastName Manufacturer Model Year PurchaseDate FK1 OperatorlD BidDate Schedule PK SchedulelD FK1 RunlD FK2 VehiclelD Trips PK TriplD FK1 SchedulelD StartLocation StartTime EndLocation EndTime EffectiveDateExplanation / Answer
--1 Which drivers begin trips at Olympia with an Effective Date in January 2005?
SELECT FirstName FROM Operators
WHERE OperatorID IN (SELECT OperatorID FROM Runs
WHERE RunID IN (SELECT RunID FROM Schedule
WHERE ScheduleID IN (SELECT ScheduleID FROM Trips
WHERE StartLocation ='Olympia' AND EffectiveDate BETWEEN '01-01-2005' AND '31-01-2015'/*Please provide EffectiveDate based on Data*/)))
--2 Do any drivers use more than one bus on their run where the Bid Date is in September 2004?
SELECT FirstName,OperatorID FROM Operators
WHERE OperatorID IN (SELECT OperatorID FROM Runs
WHERE BIDDate BETWEEN '01-08-2004' AND '01-30-2004'
GROUP BY OpratorID HAVING COUNT (RunID) > 1)
--3 Which drivers make trips that last longer than 45 minutes?
SELECT FirstName FROM Operators
WHERE OperatorID IN (SELECT OperatorID FROM Runs
WHERE RunID IN (SELECT RunID FROM Schedule
WHERE ScheduleID IN (SELECT ScheduleID FROM Trips
GROUP BY ScheduleID HAVING TIMEDIFF(EndTime,StartTime) > '00:45:00')));
--4 Which trips are operated by Phantoms and have an Effective Date in 2005?
SELECT FirstName FROM Operators
WHERE FirstName = 'Phantoms'/*Assuming operated FirstName is Phantoms. Please provide correct based on operated data*/ AND OperatorID IN (SELECT OperatorID FROM Runs
WHERE RunID IN (SELECT RunID FROM Schedule
WHERE ScheduleID IN (SELECT ScheduleID FROM Trips
WHERE EffectiveDate BETWEEN '01-01-2005' AND '31-01-2015'/*Please provide EffectiveDate based on Data*/)))
--Answer the same questions using inner- or natural joins
--5
SELECT o.FirstName FROM Operators o INNER JOIN Runs r ON o.OperatorID=r.OperatorID
INNER JOIN Schedule s ON r.RunID=s.RunID
INNER JOIN Trips t ON s.ScheduleID=t.ScheduleID
WHERE t.StartLocation ='Olympia' AND t.EffectiveDate BETWEEN '01-01-2005' AND '31-01-2015';
--6
SELECT o.FirstName,r.OperatorID FROM Operators o INNER JOIN Runs r ON o.OperatorID=r.OperatorID
WHERE r.BIDDate BETWEEN '01-08-2004' AND '01-30-2004'
GROUP BY r.OperatorID HAVING COUNT (RunID) > 1;
--7
SELECT o.FirstName FROM Operators o INNER JOIN Runs r ON o.OperatorID=r.OperatorID
INNER JOIN Schedule s ON r.RunID=s.RunID
INNER JOIN Trips t ON s.ScheduleID=t.ScheduleID
GROUP BY t.ScheduleID HAVING TIMEDIFF(t.EndTime,t.StartTime) > '00:45:00';
--8
SELECT o.FirstName FROM Operators o INNER JOIN Runs r ON o.OperatorID=r.OperatorID
INNER JOIN Schedule s ON r.RunID=s.RunID
INNER JOIN Trips t ON s.ScheduleID=t.ScheduleID
WHERE o.FirstName = 'Phantoms' AND t.EffectiveDate BETWEEN '01-01-2005' AND '31-01-2015';
--c
SELECT t.tripID,r.OperatorID FROM Runs r
INNER JOIN Schedule s ON r.RunID=s.RunID
INNER JOIN Trips t ON s.ScheduleID=t.ScheduleID
WHERE t.EffectiveDate BETWEEN '01-01-2005' AND '31-12-2015' AND r.BID < '01-01-2002'
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.