06

06 — Practice solutions

← Questions

These are independently written explanations, not an official marking scheme.

06A

SELECT c.Name, o.CollectionDate
FROM Customer AS c
JOIN Orders AS o ON o.CustomerID = c.CustomerID
WHERE o.Quantity >= 3
ORDER BY o.CollectionDate DESC;

Using > wrongly excludes quantity 3. Adding DISTINCT might wrongly collapse different orders with the same name/date.

Reasoning

One row per qualifying order, with two output columns. The required name is in Customer and the date/quantity in Orders, so join through CustomerID. “At least” includes3; “latest first” means descending date. No aggregation or DISTINCT is needed.

06B

SELECT CustomerID, SUM(Quantity) AS TotalQuantity
FROM Orders
GROUP BY CustomerID
HAVING SUM(Quantity) > 10
ORDER BY TotalQuantity DESC;

WHERE Quantity > 10 would filter individual orders before summing, answering a different question.

Reasoning

One row per customer, not per order. Sum all that customer’s quantities before deciding whether the total exceeds10. A customer with orders of 6 and 5 qualifies even though neither individual order exceeds10.

06C

def change_phone(connection, customer_id, phone):
    connection.execute(
        "UPDATE Customer SET Phone = ? WHERE CustomerID = ?",
        (phone, customer_id)  # Match parameter order to the two placeholders.
    )
    connection.commit()  # Persist the successful change as requested.

Text preserves leading zeroes and possible + prefixes. Binding values prevents them from being interpreted as SQL syntax and handles quoting safely. The function presumes the caller supplies the validated phone and an existing customer; the question did not require returning a row count.

Reasoning

Two values change the statement’s behaviour: the new phone and the customer to target. Bind both as values. The question explicitly asks to commit, so merely executing an update is incomplete. This practises the taught SQL programming workflow.

06D

SELECT c.CustomerID, COUNT(o.OrderID) AS OrderCount
FROM Customer AS c
LEFT JOIN Orders AS o
    ON o.CustomerID = c.CustomerID
    AND o.CollectionDate >= '2026-09-01'
    AND o.CollectionDate < '2026-10-01'
GROUP BY c.CustomerID
ORDER BY c.CustomerID;

Why: the date condition restricts matched orders while preserving all customers. Moving it into WHERE discards unmatched rows. COUNT(o.OrderID) gives zero for a customer with no qualifying order; COUNT(*) would count the preserved row.

06E

PRAGMA foreign_keys = ON;
CREATE TABLE Orders (
    OrderID INTEGER PRIMARY KEY,
    CustomerID INTEGER NOT NULL,
    CollectionDate TEXT NOT NULL,
    Quantity INTEGER NOT NULL CHECK (Quantity > 0),
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

Enable foreign-key enforcement on each relevant connection, before starting the transaction. NOT NULL rejects missing values; CHECK enforces the positive range. Under the stated input contract, integer quantities are supplied. SQLite’s ordinary type affinity is not a universal strict type validator.