Skip to content
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
FORENAME
SURNAME
1
John
Smith
There are no rows in this table
1
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
FORENAME
SURNAME
ADULTTICKET
CHILDTICKET
CONCESSIONTICKET
1
John
Smith
8
2
3
There are no rows in this table
1
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
FORENAME
SURNAME
ADULTTICKET*5.5
CHILDTICKET*2
CONCESSIONTICKET*1.5
1
John
Smith
44
4
4.5
There are no rows in this table
1
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
FORENAME
SURNAME
(ADULTTICKET*5.5+CHILDTICKET*2+CONCESSIONTICKET*1.5)
1
John
Smith
52.5
There are no rows in this table
1
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
FORENAME
SURNAME
Tax (£)
1
John
Smith
52.5
There are no rows in this table
1
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
maxChild
1
8
There are no rows in this table
1
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
FORENAME
SURNAME
CHILDTICKET
1
Kim
Pettigrew
8
2
Tahir
Baqri
8
There are no rows in this table
2
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.