Photo AI
Perfect Eyes is an optician that has branches throughout Scotland - Scottish Highers Computing Science - Question 11 - 2022
Question 11
Perfect Eyes is an optician that has branches throughout Scotland. It uses a relational database consisting of three linked tables to store data about customers, opt... show full transcript
Worked Solution & Example Answer:Perfect Eyes is an optician that has branches throughout Scotland - Scottish Highers Computing Science - Question 11 - 2022
Step 1
Complete the design of a query to produce this output.
Answer
- Field(s) and calculation(s): forename, surname, referralDate, specialist
- Tables(s): Referral, Customer
- Search criteria: specialist LIKE 'Eye Clinic%' AND referralDate LIKE '%/04/2022'
- Grouping: (None required)
- Sort order: referralDate DESC
Step 2
Step 3
Using 'AvgPointsQuery', complete the SQL statement to display the customers who have more than the average loyalty points.
Answer
SELECT forename, surname, loyaltyPoints, opticianName
FROM Customer, Optician, AvgPointsQuery
WHERE Customer.opticianID = Optician.opticianID AND loyaltyPoints > AveragePoints
ORDER BY loyaltyPoints DESC;