Assignment: Database Systems and Information Modelling

The Case

You are the SQL programmer for a company that operates a (fictional) city subway system. Your job is to write SQL queries that answer questions posed by management.

The subway has three train lines, named "Northern", "Western" and "Eastern", each of which starts at the "City" station. Along each line are several stations. Each station has an id and a name, and a sequence number along the line. On a given line, station 0 is City, station 1 is the ‘first' station travelling outbound etc., until the station furthest from the city, which has the highest sequence number. The subway map in figure 1 shows the three lines and the stations on each line. Since the city station is on all lines, it is marked as being on line ‘0' (note that there is not an actual line 0).

1. How many usernames contain the letter A?

2. What is the average journey length, in seconds?

3. List the stations on the Eastern line (excluding City), in outbound order.

4. On which day of the week (Monday, Tuesday etc.) are the most journeys made?

5. List any stations at which no passenger has started or ended a journey. Show the station id and name.

6. List the details of each journey, along with its cost.

7. List the station ids, along with the number of journeys that started or stopped at each station.

8. List the journeys that ended at the last station on the line. (the station with the highest sequence number for a line)

9. For each journey, show how many stations it passed through. (Count the end station but not the start station.)

10. List the usernames of customers who have travelled on all lines.

Attachment:- Assignment.rar