Skip to content
Gallery
FLIGHT_DATA
SQA Tasks
Extra Tasks
TABLE DATA
WILD CARD LIKE
More
Share
Explore
Extra Tasks

icon picker
Extension Task 2

task 1

List the customer name, routeID and departure location for the booking with the largest cost value. You will need 2 queries.
Task 2

⁠

task 2 (challenge)

a Using the booking table, list customerID and departure date showing the most recent flight for each date.
b Now include the earliest or first flight in the same query.
c Now calculate the interval between the two flight dates for each customer.
Display the total seat capacity used for each route with the highest total capacity first.
Include the total capacity, the departure point, arrival point and number of flights on the route
⁠
part 2a SQL
select customerid, min(departuredate) as "1st Flight"
from booking, flight
where booking.flightid = flight.flightid
group by customerid
part 2a sample output (there are more than 10 rows)
1st flight per customer
CUSTOMERID
1st Flight
1
GR01976
24-Mar-2018
2
GR01955
23-Mar-2018
3
GR01973
27-Mar-2018
4
GR01974
23-Mar-2018
5
GR01941
24-Mar-2018
6
GR01929
23-Mar-2018
7
GR01928
26-Mar-2018
8
GR01933
25-Mar-2018
9
GR01954
28-Mar-2018
10
GR01968
26-Mar-2018
There are no rows in this table
10
Count
⁠

⁠
part 2b SQL
select customerid, min(departuredate) as "1st Flight", max(departuredate) as "Latest Flight"
from booking, flight
where booking.flightid = flight.flightid
group by customerid
part 2b sample output (there are more than 10 rows)
first and latest flight per customer
CUSTOMERID
1st Flight
Latest Flight
1
GR01976
24-Mar-2018
30-Mar-2018
2
GR01955
23-Mar-2018
03-Apr-2018
3
GR01973
27-Mar-2018
30-Mar-2018
4
GR01974
23-Mar-2018
01-Apr-2018
5
GR01941
24-Mar-2018
24-Mar-2018
6
GR01929
23-Mar-2018
30-Mar-2018
7
GR01928
26-Mar-2018
01-Apr-2018
8
GR01933
25-Mar-2018
02-Apr-2018
9
GR01954
28-Mar-2018
02-Apr-2018
10
GR01968
26-Mar-2018
02-Apr-2018
There are no rows in this table
10
Count
⁠

⁠
part 2c SQL
select customerid, max(departuredate) - min(departuredate) as "Interval"
from booking, flight
where booking.flightid = flight.flightid
group by customerid
part 2c sample output (there are more than 10 rows)
Interval between first and last flight dates
CUSTOMERID
Interval
1
GR01976
6
2
GR01955
11
3
GR01973
3
4
GR01974
9
5
GR01941
0
6
GR01929
7
7
GR01928
6
8
GR01933
8
9
GR01954
5
10
GR01968
7
There are no rows in this table
10
Count
⁠
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.