SQL HOMEWORK HELP !

Given relationship diagram

Table given

Requirements

Complete the following queries. "Natural join" refers to fields compared in the WHERE

clause.

a. Display the Run ID and all fields from the trips table for each trip using an inner-join.

b. Display the vehicle name used for each trip, and all fields from the trip table, using a natural join.

c. Display each Operator and the vehicles that he/she drives using an inner-join (operator name, manufacturer, model, vehicle id).

d. Display the count of trips for each January 2005 run using a natural join (test the Bid Date field in Runs).

e. Calculate the number of trips Vehicle #3 made in September 2004's schedule using an inner-join (you may need to refer to assignment #1 for the correct vehicle, depending

on identity values) (use the Effective Date in Trips).

f. List any routes that have more than one bus operating trips grouped by Effective Date.

g. Calculate the number of Operators who start at or arrive at Lacey in January 2005 (use Effective Date in Trips).

h. Insert a new vehicle (CityCruiser, 35 foot, 1996); create a list of buses and trip details, including all buses.

i. Display the Operator name, vehicle model, start location, and start time for all trips starting after 9:00 am in September 2004.

Turn in this file with your SQL code and screen prints.

Operators Runs Vehicles PK OperatorlD PK RunID PK VehicleID 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 EffectiveDate Operators Runs Vehicles PK OperatorlD PK RunID PK VehicleID 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 EffectiveDate