For all days between 2003-04-01 and 2003-04-07 find the number of trips from Rostov.
Result set: date, number of trips.
the database consist on ;
The database schema consists of 4 tables:
Company(ID_comp, name)
Trip(trip_no, id_comp, plane, town_from, town_to, time_out, time_in)
Passenger(ID_psg, name)
Pass_in_trip(trip_no, date, ID_psg, place)
The Company table contains IDs and names of the airlines transporting passengers. The Trip table contains information on the schedule of flights: trip (flight) number, company (airline) ID, plane type, departure city, destination city, departure time, and arrival time. The Passenger table holds IDs and names of the passengers. The Pass_in_trip table contains data on flight bookings: trip number, departure date (day), passenger ID and her seat (place) designation during the flight. It should be noted that
scheduled flights are operated daily; the duration of any flight is less than 24 hours; town_from <> town_to;
all time and date values are assumed to belong to the same time zone;
departure and arrival times are specified with one minute precision;
there can be several passengers bearing the same first name and surname (for example, Bruce Willis);
the seat (place) designation consists of a number followed by a letter; the number stands for the row, while the letter (a – d) defines the seat position in the grid (from left to right, in alphabetical order;
connections and constraints are shown in the database schema below.
CREATE TABLE #Company (
ID_comp int NOT NULL,
name varchar(100) NOT NULL
)
CREATE TABLE #Trip (
trip_no int NOT NULL,
id_comp int NOT NULL,
plane int NOT NULL,
town_from varchar(50) NOT NULL,
town_to varchar(50) NOT NULL,
time_out datetime NULL,
time_in datetime NULL
)
CREATE TABLE #Passenger (
ID_psg bigint NOT NULL,
name varchar(100) NOT NULL
)
CREATE TABLE #Pass_in_trip (
trip_no int NOT NULL,
date date NOT NULL,
ID_psg bigint NOT NULL,
place varchar(5) NULL
)
;WITH cte_trip_dates AS (
SELECT * FROM ( VALUES('20030401'),('20030402'),('20030403'),('20030404'),('20030405'),
('20030406'),('20030407')) AS dates(date)
)
SELECT ctd.date AS Dt, COUNT(t.town_from) AS Qty
FROM cte_trip_dates ctd
LEFT OUTER JOIN #Pass_in_trip pit ON pit.date = ctd.date
LEFT OUTER JOIN #Trip t ON t.trip_no = pit.trip_no AND t.town_from = 'Rostov'
GROUP BY ctd.date
ORDER BY Dt
The use of LEFT OUTER JOINs so that all the dates are always present, even if no matching trip is found.
The COUNT() being of a column in the last LEFT JOINed table, to make sure a row is counted only if there's an actual trip for that date. For example, COUNT(*) would always add one for the date, even if no trip was taken.
SELECT A.DT,
( SELECT COUNT(DISTINCT TP.TRIP_NO) FROM
PASS_IN_TRIP TP, TRIP T
WHERE TP.TRIP_NO = T.TRIP_NO AND TP.DATE = A.DT AND T.town_from = 'Rostov') AS QTY
FROM (
SELECT CAST('2003-04-01 00:00:00.000' AS DATETIME) AS DT
UNION
SELECT CAST('2003-04-02 00:00:00.000' AS DATETIME) AS DT
UNION
SELECT CAST('2003-04-03 00:00:00.000' AS DATETIME) AS DT
UNION
SELECT CAST('2003-04-04 00:00:00.000' AS DATETIME) AS DT
UNION
SELECT CAST('2003-04-05 00:00:00.000' AS DATETIME) AS DT
UNION
SELECT CAST('2003-04-06 00:00:00.000' AS DATETIME) AS DT
UNION
SELECT CAST('2003-04-07 00:00:00.000' AS DATETIME) AS DT) A
This was the most correct query for that execerise i was stuck there for a week
