COMPUTER SCIENCE 2210 · STRUCTURED PRACTICE
A computer hardware store manages stock using a single database table named PRODUCT_STOCK. The structure and sample records of the table are shown in the diagram.
Write an SQL query to display the ProductName and UnitPrice of all products in the 'Audio' category with a UnitPrice less than 100.00, sorted in ascending order of UnitPrice.
State the exact output generated when the following SQL query is executed on the PRODUCT_STOCK table: SELECT ProductCode, ProductName, StockQuantity FROM PRODUCT_STOCK WHERE StockQuantity <= ReorderLevel AND Category = 'Computing';
Write an SQL query using an aggregate function to calculate the total number of stock items (sum of StockQuantity) currently held across all products in the 'Storage' category.
A shop clerk attempts to write an SQL query to retrieve all products in either the 'Audio' or 'Computing' categories with a UnitPrice greater than 50.00. The clerk writes: SELECT ProductName FROM PRODUCT_STOCK WHERE Category = 'Audio' AND Category = 'Computing' AND UnitPrice > 50.00;
Explain why this query returns zero records.
Write the corrected WHERE clause for this query.
Total: 12 marks