A warehouse uses a database table called STOCK to track inventory. ItemCode Description Supplier QuantityInStock ReorderLevel UnitCost WH001 Steel Bolt M8 M...

(a) SQL query for items below reorder level: [2]

SELECT *
FROM STOCK
WHERE QuantityInStock < ReorderLevel

This compares two fields within the same record. From the data, only WH002 (Rubber Washer: quantity 80, reorder level 200) satisfies the condition where 80 < 200.

(b) SQL INSERT statement for a new item: [3]

INSERT INTO STOCK (ItemCode, Description, Supplier,
    QuantityInStock, ReorderLevel, UnitCost)
VALUES ('WH006', 'Nylon Strap', 'SealParts',
    400, 150, 0.30)

All six values must be provided in the correct order matching the field list. String values are enclosed in single quotes; numeric values are written directly.

(c) SQL query for total stock value per supplier: [3]

SELECT Supplier, SUM(QuantityInStock * UnitCost)
FROM STOCK
GROUP BY Supplier

The expression QuantityInStock * UnitCost calculates the value for each item, and SUM totals these across each supplier group.

Working:

• MetalCo: (500 x 0.15) + (250 x 1.20) + (30 x 4.50) = 75 + 300 + 135 = 510
• SealParts: (80 x 0.08) + (150 x 0.05) = 6.40 + 7.50 = 13.90

(d) Two fields where a range check should be applied: [2]

1. QuantityInStock: Must be >= 0 (cannot have negative stock).
2. UnitCost: Must be > 0 (an item cannot have a zero or negative cost).

(e) SQL query to count total records: [2]

SELECT COUNT(*)
FROM STOCK

COUNT(*) counts all rows in the table regardless of any NULL values. This would return 5 for the given data.

(f) SQL query for average per category with a minimum group size: [3]

SELECT Supplier, AVG(UnitCost)
FROM STOCK
GROUP BY Supplier
HAVING COUNT(*) > 2

GROUP BY groups records by Supplier, AVG calculates the mean UnitCost within each group, and HAVING filters to show only groups with more than 2 records. In this data, only MetalCo (3 items) would appear; SealParts (2 items) would be excluded.

(g) SQL query to update records matching a condition: [2]

UPDATE STOCK
SET QuantityInStock = 0
WHERE QuantityInStock < ReorderLevel

The UPDATE statement modifies existing records. SET specifies the new value, and WHERE determines which records are affected. Without a WHERE clause, all records would be updated.

(h) Two advantages of using a database over a flat file: [2]

1. Data can be queried using SQL to retrieve specific records quickly without reading the entire file, making searches efficient even with large datasets.
2. Multiple users can access and update the data simultaneously with access controls, preventing conflicts and restricting sensitive information to authorized personnel.

(i) SQL query for top 5 results sorted descending: [1]

SELECT *
FROM STOCK
ORDER BY UnitCost DESC
LIMIT 5

ORDER BY UnitCost DESC sorts from highest to lowest cost. LIMIT 5 restricts the output to the first 5 records. This is useful for finding the most expensive items.