user3186023 user3186023 - 7 months ago 11
SQL Question

SQL Using COUNT

I'm doing past papers for my database exam, and I've come across the following question:




Given the schema:


  • Station(name, city) (name is key)

  • Train(number, day, depart_station, arrive_station, depart_time, arrive_time) (number and day are a composite key, and day in {Monday, Tuesday, ... , Sunday}.

  • Service(train number, train_type, food_served, first_class) (train_number is key and first_class is "yes" or "no".



The query is:

Find stations from which at least 20 trains with first-class service depart for Edinburgh every week on weekdays.

My answer to this is:

WITH fstClass AS (
SELECT *
FROM Train T, Service S
WHERE T.number = S.train_number AND T.arrive_station = "Edinburgh"
AND S.first_class = 'yes'
AND T.day LIKE 'S%'
)
SELECT f.depart_station
FROM fstclass f
GROUP by f.depart_airport
HAVING COUNT(*) >= 20;


I'm debating with my friend whether this is right, and I personally don't see what could be wrong with it. So any insight on whether this seems correct and/or better ways about doing this query would be much appreciated. Are temporary tables a good way to do SQL queries?

Dai Dai
Answer

The subquery can be eliminated, and HAVING used:

SELECT
    Trains.departingStation,
    COUNT(*) AS numberOfTrains
FROM
    Trains
    INNER JOIN Services ON Trains.number = Services.number
WHERE
    arrivalStation = 'Edinburgh'
    AND
    Services.hasFirstClassService = 1
    AND
    day NOT IN ( 'Saturday', 'Sunday' )
GROUP BY
    departingStation
HAVING
    COUNT(*) >= 20
Comments