Inventory Management Queries
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
Create the products table
Create a
productstable with columns for id, name, price, stock quantity, and a reorder threshold.CodeCREATE 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
Add products
Insert several products with different stock levels and reorder thresholds.
CodeINSERT 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
Update stock after a sale
Simulate selling 10 Widgets by updating the stock. Use UPDATE with a WHERE clause to target the right product.
CodeUPDATE products SET stock = stock - 10 WHERE name = 'Widget';Expected result
Widget stock decreases from 50 to 40.
- 4
Find low-stock products
Write a query to find all products where stock is at or below the reorder level. These need reordering.
CodeSELECT 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
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
- Insert, Update, Delete
Learn data modification operations.
- WHERE Clause
Learn to filter query results.
Related Glossary
- Constraint
Data integrity rules.
Related Glossary
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
- Constraint
Data integrity rules.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.