When the CarSale table was originally designed, it was suggested that a compound key could have been used - Scottish Highers Computing Science - Question 12 - 2022
Question 12
When the CarSale table was originally designed, it was suggested that a compound key could have been used.
Explain why a compound key would not have been suitable f... show full transcript
Worked Solution & Example Answer:When the CarSale table was originally designed, it was suggested that a compound key could have been used - Scottish Highers Computing Science - Question 12 - 2022
Step 1
Explain why a compound key would not have been suitable for the CarSale table.
96%
114 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
A compound key may not be suitable for the CarSale table because the 'saleID' is designed to uniquely identify each record. Additionally, the primary keys from the other two tables (Customer and SalesPerson) would not uniquely identify a car sale. Furthermore, until a sale occurs, there would be no customer number or sales person number associated, making it necessary for record values to be filled. Since a compound key cannot allow empty fields, it would not be adequately functional in this context.
Step 2
Complete the design of a query that will display the information as shown above.
99%
104 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
Field(s) and calculation(s):
year, MIN(askingPrice) AS Cheapest Price
Tables(s):
CarSale
Search criteria:
year=2017 OR year=2018 AND sold = 'No'
Grouping:
year
Sort order:
year ASC
Step 3
Write the SQL statement that would create this list.
96%
101 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
SELECT carReg, salesPersonRef, askingPrice, soldPrice,
(askingPrice - soldPrice) AS PriceDifference
FROM CarSale, SalesPerson
WHERE CarSale.salesPersonRef = SalesPerson.salesPersonRef
AND sold = 'Yes';
Step 4
Write the SQL statement that would make these changes.
98%
120 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
UPDATE CarSale
SET askingPrice = askingPrice * 1.10
WHERE mileage <= 10000;
Join the Scottish Highers students using SimpleStudy...
