SQL EXERCISE

Each query in this exercise relies on a single table, but may require returning specific rows (the WHERE clause); specific groups (GROUP BY, HAVING); using specific order (ORDER BY); with calculations (SELECT, WHERE).

Using Select

Complete the following queries.

1. The following queries are worth one point each.

A) Display all 2005 trips departing from the Olympia Transit Center sorted by departure time.

B) Display all trips for route 15 sorted by effective date, starting location and start time.

C) Display all September 2004 trips which start before 8:00 sorted by starting location, start time, and route number.

D) Display the full name (as one field) and hire date of all operators hired during 2002.

2. The flowing queries are worth two points each.

E) Display the elapsed trip time for all trips.

F) Display route number, start location end location (one field), and start time for trips departing between 8:00am and 4:00pm in January 2005.

G) Calculate the length of service in years for all operators (difference between hire date and the current date).

3. The following queries are worth three points each.

H) How many 2005 trips start or end in Yelm?

I) Display the total number of operators hired each year.

J) Display the number of operators hired in each year where more than one operator was hired.

K) How many operators have worked for more than three years?

L) What is the total elapsed time for all September 2004 trips serving Westfield Shopping Mall?