FLIGHT_DATA

Explore

SQA Tasks

Task B

Database documentation showing entities and attributes.

⁠

Advice

Before starting to develop your code you should have a look at the data to help get an idea of the meaning and purpose of the requirements.

You could simply select * from each table in turn. If you save the code then you can quickly review the data for each table.

⁠

1b(i)

John Smith, Customer ID - GR01932, has asked for a copy of the tax he has paid on flight QH182. The tax for a booking is calculated as follows:

adults pay £5.50

children pay £2.00

concessions pay £1.50

Implement the SQL statement that will produce an output with the headings.

⁠

Advice

This query might be tricky to complete without some preparation so

Notice that the result is based on one flight for one customer.

Step one is to begin the query to simply return that customer

select forename, surname

from booking, customer

where booking.customerid = customer.customerid

and customer.customerid = 'GR01932'

and flightid = 'QH182'

step 1

step 1

FORENAME

SURNAME

John

Smith

There are no rows in this table

Count

⁠

Next you can see that the costs depend on the number of adults, children and concession tickets.

select forename, surname, adultticket, childticket, concessionticket

from booking, customer

where booking.customerid = customer.customerid

and customer.customerid = 'GR01932'

and flightid = 'QH182'

step 2

step 2

FORENAME

SURNAME

ADULTTICKET

CHILDTICKET

CONCESSIONTICKET

John

Smith

There are no rows in this table

Count

⁠

Next the numbers in the row can be used to calculate the tax paid for each type of ticket by introducing the relevant payments.

select forename, surname, adultticket*5.5, childticket*2, concessionticket*1.5

from booking, customer

where booking.customerid = customer.customerid

and customer.customerid = 'GR01932'

and flightid = 'QH182'

step 3

step 3

FORENAME

SURNAME

ADULTTICKET*5.5

CHILDTICKET*2

CONCESSIONTICKET*1.5

John

Smith

4.5

There are no rows in this table

Count

⁠

Now the individual costs can simply be added up:

select forename, surname, (adultticket*5.5 + childticket*2 + concessionticket*1.5)

from booking, customer

where booking.customerid = customer.customerid

and customer.customerid = 'GR01932'

and flightid = 'QH182'

step 4

step 4

FORENAME

SURNAME

(ADULTTICKET*5.5+CHILDTICKET*2+CONCESSIONTICKET*1.5)

John

Smith

52.5

There are no rows in this table

Count

⁠

Finally use an alias to display the correct column heading

select forename, surname, (adultticket*5.5 + childticket*2 + concessionticket*1.5) as "Tax (£)"

from booking, customer

where booking.customerid = customer.customerid

and customer.customerid = 'GR01932'

and flightid = 'QH182'

step 5

step 5

FORENAME

SURNAME

Tax (£)

John

Smith

52.5

There are no rows in this table

Count

⁠

1b(ii)

The airline wishes to identify the customer(s) who made a booking with the greatest number of children.

Implement two SQL statements that will find the forename and surname of the customer(s) who made a booking with the greatest number of children.

⁠

Advice

This is a two-stage query which will need a view.

First you have to discover the maximum number of children, then you can use it to restrict the bookings to only the rows that match the maximum.

step 1

test a query to return the largest number of children i a booking

select max(childticket) as "maxChild"

from booking

step 2.1

step 2.1

maxChild

There are no rows in this table

Count

⁠

step 2

create a view base on the query

create view "childBooking" as

select max(childticket) as "maxChild"

from booking

step 3

Use the view to to restrict the rows in the booking table to only those with the maximum number of child tickets

Leave in the number of childtickets to check the answer and then remove it from the final version

select forename, surname, booking.childTicket

from customer, booking, "childBooking"

where customer.customerid = booking.customerid

and "childBooking"."maxChild" = booking.childTicket

step 2.3

step 2.3

FORENAME

SURNAME

CHILDTICKET

Kim

Pettigrew

Tahir

Baqri

There are no rows in this table

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.