City Subway System

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).sql

Some stations are in 「Zone 1」; the rest are in 「Zone 2」.
Customers take journeys from one train station to another. The cost of a journey depends
on which zones the customer travels throughapp

Journeys entirely within zone 1, or entirely within zone 2, cost $1.
Journeys that start in one zone and end in another cost $2.
Journeys that start in zone 2 on one line, travel through the city,and end in zone 2 on another line, cost $3.
Data Model
The following ER diagram describes the database schema which has been implemented.ide

Setup Script

To setup the database in your SQLServer server, download the file subway_ddl.sql from
http://192.168.8.222 and run it . This script creates the schema and database tables and
populates them with data. If it is your own computer, you will want to create a new schema
to host the tables.ui

The SQL queries required

Over the page are listed 10 questions for you to answer. Write one SQL statement per
question. Do not use views or temporary tables to answer the questions. UNION is allowed.
Where it would improve readability, order your output and use aliases. Format large
numbers and fractions appropriately.
Beside each question is a maximum mark which reflects the difficulty of that question.
Your work will be assessed on the correctness and simplicity of the queries that you write.
(A query that produces correct output but is more complex than it needs to be, for example
joining more tables than is necessary, may not achieve full marks, even if it produces the
correct results.)3d

How many usernames contain the letter A? (5)
What is the average journey length, in seconds? (5)
List the stations on the Eastern line (excluding City),in outbound order. (5)
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. (10)
List the details of each journey, along with its cost. (10)
List the station ids, along with the number of journeys that started or stopped at each
station. (10)
List the journeys that ended at the last station on the line.(the station with the highest
sequence number for a line) (15)
For each journey, show how many stations it passed through.(Count the end station
but not the start station.) (15)
List the usernames of customers who have travelled on all lines. (20)rest

KEY：

--1
select COUNT(*) from customer
where username like '%A%'
--2
select AVG(DATEDIFF(second,startTime,endTime))
from journey
--3
select station.name from station inner join line
on station.line=line.id
where line.name='eastern' and sequence>0
order by sequence
--4
select top 1 DATENAME(WEEKDAY,startTime),COUNT(*) from journey
group by DATENAME(WEEKDAY,startTime)
order by COUNT(*) desc
--5
select ID,name from station where id not in(
select distinct startStation from journey
union
select distinct endStation from journey)
--6
select journey.*,case when s1.zone='1' and s2.zone='1'
OR s1.zone='2' and s2.zone='2' and s1.line=s2.line then '$1'
when s1.zone!=s2.zone then '$2'
when s1.zone='2' and s2.zone='2' and s1.line!=s2.line then '$3'
end cost
from journey inner join station s1 on journey.startStation=s1.id
inner join station s2 on journey.endStation=s2.id

--7
select j1.sid,sc,ec from
(select journey.startStation sid,COUNT(*) sc from journey
group by journey.startStation) as j1
inner join
(select journey.endStation sid,COUNT(*) ec from journey
group by journey.endStation) as j2
on j1.sid=j2.sid
--8
select * from journey where endStation in(
    select id from station s1 join
(select line,MAX(sequence) seq from station
where line!=0 group by line    ) as s2 on s1.sequence=s2.seq
and s1.line=s2.line
)
--9
select journey.*, case when s1.line=s2.line then
abs(s1.sequence-s2.sequence) when s1.line!=s2.line
then s1.sequence+s2.sequence end num
from journey inner join station s1 on journey.startStation=s1.id
inner join station s2 on journey.endStation=s2.id
--10
select username from customer
where not exists(
    select * from line
    where id!=0 and not exists(
        select * from station where exists
        (
        select * from journey
        where (journey.startStation=station.id
         or journey.endStation=station.id)
         and line.id=station.line
         and journey.customer=customer.id
        )
    )
)