Essential storage keeps sign-in and learning features working. Google Analytics measures visits; Microsoft Clarity records interactions to help us improve usability. Both are optional and stay off until you choose. Read our privacy policy.
Restock a sku without a duplicate row | SQL exercise | SovranCode
SQLintermediate 40 min
CODING EXERCISE
Restock a sku without a duplicate row
A receiving scan may insert a new sku or add to stock if the sku already exists. Apply one INSERT ... ON CONFLICT so PEN-2 gains 5 units and INK-4 is created at 5. Then return every sku and stock ordered by sku.
Requirements
Keep the original products rows; do not DELETE them.
Upsert using INSERT INTO products (sku, stock) with two incoming rows: PEN-2 and INK-4, each stock 5.
ON CONFLICT(sku) DO UPDATE SET stock = products.stock + excluded.stock.
Return sku and stock for every product.
Order by sku ascending.
Do not write a separate UPDATE for pens.
Example
Input
Keep products, then upsert both receiving rows and select the catalog.
PRIMARY KEY on sku is the conflict target. excluded.stock is the incoming 5. For PEN-2 the row exists, so stock becomes 2 + 5 = 7. For INK-4 there is no row, so the INSERT stands at 5. One statement covers both receiving outcomes, which is the point of an upsert: the scanner does not need to SELECT first. A plain INSERT of PEN-2 would fail uniqueness. A blind UPDATE would miss new ink. Notebooks and mugs are untouched. ORDER BY sku puts INK-4 first alphabetically. SQLite uses ON CONFLICT; PostgreSQL often writes the same idea as INSERT ... ON CONFLICT or MERGE. Do not confuse this with REPLACE, which deletes the old row.
Why this pattern matters
Receiving cannot race a SELECT-then-INSERT. ON CONFLICT uses the primary key as the decision and excluded as the incoming shipment, so pens add and ink appears in one statement.
Solution walkthrough
INSERT PEN-2 and INK-4 each with stock 5.
On sku conflict, add excluded.stock to the stored stock.
Select every sku so mugs and notebooks prove they were not reset.
Common mistakes to avoid
REPLACE INTO, which deletes and reinserts and would set pens to 5 instead of 7.
DO UPDATE SET stock = excluded.stock, which overwrites instead of adding.
A second statement only for INK-4.
What this exercise teaches
Use a unique key as the conflict target
Add to existing stock with excluded
Insert a brand-new sku in the same statement
A PRACTICAL PLAN
Work through Restock a sku without a duplicate row with intent.
Translate the contract.Turn the requirements into a short checklist before editing your-solution.sql.
Use the example as evidence.Predict the result for the supplied input, then add one boundary case such as an empty value, a limit, or unexpected input.
Review the implementation.Compare your choices against solution.sql only after a real attempt.
EXERCISE FAQ
Before you move on.
What does “Restock a sku without a duplicate row” teach?
This intermediate SQL exercise focuses on Use a unique key as the conflict target, Add to existing stock with excluded, Insert a brand-new sku in the same statement. Its requirements define the exact behavior to implement before you write code.
How should I validate this SQL solution?
Start with the displayed example input and expected output, then test a boundary case suggested by the requirements. Open your-solution.sql and select Run code. The browser creates a fresh isolated SQLite database, executes the complete script, and compares its result rows with the expected report.
When should I open the reference solution?
Attempt Restock a sku without a duplicate row first. Then open the read-only solution file to compare the contract, edge-case handling, and implementation choices—not simply to copy the final code.
