SQL Real-World Projects: Inventory Management System

Create Inventory Tables

Design and create SQL tables for Products, Suppliers, and StockMovements with appropriate keys and constraints.

Expected output: SQL CREATE TABLE statements that define the inventory schema.

Hint: Focus on primary keys, foreign keys, and data types suitable for each column.

Write Stock Level Query

Write an SQL query to display the current stock level for all products.

Expected output: A query that returns product names and their current stock quantities.

Hint: Use SUM and GROUP BY on the StockMovements table joined with Products.

I would create a StockMovements table with columns for MovementID (primary key), ProductID (foreign key), Quantity (positive for stock in, negative for stock out), MovementType (e.g., IN or OUT), and Date to record when the movement occurred.

I would sum the Quantity column in the StockMovements table grouped by ProductID, joining with the Products table to get product names.

Transactions ensure that multiple related database operations, such as updating stock and recording sales, are completed atomically, preventing partial updates that could lead to inconsistent stock data.