Skip to content

Inventory Management Queries

Beginner ~15 min

What you'll build

A SQL script that creates a products table and a set of INSERT, UPDATE, and SELECT queries to manage inventory in a warehouse.

Goals

  • Create a products table with stock tracking
  • Use INSERT to add new products
  • Use UPDATE to modify stock levels
  • Use SELECT with WHERE to find low-stock items

Prerequisites

  • CREATE TABLE syntax
  • INSERT, UPDATE, and SELECT basics
  • WHERE clause filtering

Steps

  1. 1

    Create the products table

    Create a products table with columns for id, name, price, stock quantity, and a reorder threshold.

    Code
    CREATE TABLE products (
      id              SERIAL PRIMARY KEY,
      name            TEXT NOT NULL,
      price           NUMERIC(10,2) NOT NULL CHECK (price > 0),
      stock           INTEGER NOT NULL DEFAULT 0,
      reorder_level   INTEGER NOT NULL DEFAULT 10
    );

    Expected result

    The products table is created with a CHECK constraint on price.

  2. 2

    Add products

    Insert several products with different stock levels and reorder thresholds.

    Code
    INSERT INTO products (name, price, stock, reorder_level) VALUES
      ('Widget', 9.99, 50, 10),
      ('Gadget', 19.99, 5, 15),
      ('Gizmo', 14.99, 0, 20);

    Expected result

    Three products are inserted with varying stock levels.

  3. 3

    Update stock after a sale

    Simulate selling 10 Widgets by updating the stock. Use UPDATE with a WHERE clause to target the right product.

    Code
    UPDATE products
    SET stock = stock - 10
    WHERE name = 'Widget';

    Expected result

    Widget stock decreases from 50 to 40.

  4. 4

    Find low-stock products

    Write a query to find all products where stock is at or below the reorder level. These need reordering.

    Code
    SELECT name, stock, reorder_level
    FROM products
    WHERE stock <= reorder_level
    ORDER BY stock ASC;

    Expected result

    Gizmo (0 stock) and Gadget (5 stock) are returned as low-stock items.

  5. 5

    Add indexes to optimize queries

    Create indexes on frequently queried columns to improve query performance. Indexes are the primary means of database performance optimization, but be mindful of write overhead.

    Code
    -- Create indexes on frequently filtered columns
    CREATE INDEX idx_users_email ON users(email);
    CREATE INDEX idx_orders_user_id ON orders(user_id);
    CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
    
    -- Verify the index is used
    EXPLAIN SELECT * FROM orders WHERE user_id = 42;

    Expected result

    EXPLAIN output shows an index scan using idx_orders_user_id instead of a full table scan.

Try it yourself

What you learned

You practiced the core CRUD operations (INSERT, UPDATE, SELECT) on a real-world inventory scenario, including using CHECK constraints and filtering with WHERE.

Related Resources

Related Lessons

Related Glossary

Related Glossary

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors

← Back to topic