Question $3.$ Consider the table created by the following SQL statement:

CREATE TABLE timetable $($

orig $,$ char$(3),--$ code of the origin station

dest $,$ char$(3),--$ code of the destination station

$--$ departure time expressed as minutes elapsed since $00$:$00$

dep smallint CHECK $($ dep$0$ AND dep

$--$ arrival time expressed as minutes elapsed since $00$:$00$

arr smallint CHECK $($ arr$0$ AND arr

fare numeric $(6,2),--$ ticket price

CHECK $($ arr $>$ dep AND orig dest $),$

PRIMARY KEY $($orig$,$ dest, dep, arr$)$

;

$($a$)$ Using only standard constructs we have seen in class, write an SQL query

to find all connections with exactly $1$ stop and such that:

the origin and final destination are distinct stations;

there is no cheaper direct route $($or no direct route at all$)$;

the connection time is at least $10$ and no more than $30$ minutes.

Return the codes of the origin, stopover, and destination stations $($for the

overall trip$),$ the total travel time $($including connection time$),$ and the total

fare. If there is more than one connection that satisfies the requirements,

they must all be included in the answer.

A correct query is worth $4$ marks; $2$ additional marks are awarded if the

query has at most one level of nesting and does not use aggregation, outer

joins, set$/$bag operations, CASE and WITH.

$($b$)$ Can the query in Question $3($a$)$ output rows with multiplicity greater than

one? If yes, provide an instance witnessing that this is the case; otherwise,

explain why such a situation cannot occur.