SQL HELP !

Homework #5 Joins

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 BidDate 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.

Preference

Operators Vehicles RLTES PK OperatorlD PK VehiclelD PK RunlD SeniorityNumber FirstName Manufacturer Model Year FK1 OperatorlD BidDate PK SchedulelD FK1 RunID FK2 VehiceID Trips FK1 SchedulelD StartTime