今日SQL题：模拟库存管理系统中产品缺货预警
难度系数：🌟☆☆☆☆☆☆☆☆☆
1、题目要求(总共五题，每日一题)
- 找出每个仓库中库存低于再订货水平的产品，并按缺货数量排序。
- 显示仓库名称、产品名称、库存数量、再订货水平、缺货数量。
2、表和虚拟数据(全部执行，后面题目会用到
--仓库信息表
CREATE TABLE IM_Warehouses (WarehouseID INT PRIMARY KEY, -- 仓库IDWarehouseName NVARCHAR(100), -- 仓库名称Location NVARCHAR(100) -- 仓库位置
);--产品表
CREATE TABLE IM_Products (ProductID INT PRIMARY KEY, -- 产品IDProductName NVARCHAR(100), -- 产品名称Category NVARCHAR(50), -- 产品类别UnitCost DECIMAL(10, 2), -- 产品单价ReorderLevel INT -- 再订货水平
);--产品库存表
CREATE TABLE IM_Inventory (InventoryID INT PRIMARY KEY, -- 库存IDWarehouseID INT, -- 仓库IDProductID INT, -- 产品IDQuantity INT, -- 库存数量LastUpdated DATETIME, -- 最后更新时间FOREIGN KEY (WarehouseID) REFERENCES IM_Warehouses(WarehouseID),FOREIGN KEY (ProductID) REFERENCES IM_Products(ProductID)
);--订单表
CREATE TABLE IM_Orders (OrderID INT PRIMARY KEY, -- 订单IDOrderDate DATETIME, -- 订单日期CustomerID INT, -- 客户ID，引用IM_Customers表WarehouseID INT, -- 仓库ID，引用IM_Warehouses表FOREIGN KEY (WarehouseID) REFERENCES IM_Warehouses(WarehouseID)
);--订单明细表
CREATE TABLE IM_OrderDetails (OrderDetailID INT PRIMARY KEY, -- 订单详情IDOrderID INT, -- 订单ID，引用IM_Orders表ProductID INT, -- 产品ID，引用IM_Products表Quantity INT, -- 订单数量UnitPrice DECIMAL(10, 2), -- 产品单价FOREIGN KEY (OrderID) REFERENCES IM_Orders(OrderID),FOREIGN KEY (ProductID) REFERENCES IM_Products(ProductID)
);--客户表
CREATE TABLE IM_Customers (CustomerID INT PRIMARY KEY, -- 客户IDCustomerName NVARCHAR(100), -- 客户名称CustomerEmail NVARCHAR(100) -- 客户邮箱
);--模拟数据
INSERT INTO IM_Warehouses (WarehouseID, WarehouseName, Location) VALUES
(1, '京东智能仓储-北京库', '北京'),
(2, '阿里智能仓储-杭州库', '杭州'),
(3, '华为智能仓储-深圳库', '深圳'),
(4, '中兴智能仓储-成都库', '成都'),
(5, '顺丰智能仓储-广州库', '广州');INSERT INTO IM_Products (ProductID, ProductName, Category, UnitCost, ReorderLevel) VALUES
(1, '笔记本电脑', '电子产品', 1200.00, 50),
(2, '智能手机', '电子产品', 800.00, 100),
(3, '办公椅', '家具', 250.00, 30),
(4, '耳机', '配件', 150.00, 70),
(5, '显示器', '电子产品', 300.00, 20),
(6, '键盘', '配件', 80.00, 40),
(7, '鼠标', '配件', 40.00, 50),
(8, '打印机', '电子产品', 150.00, 10),
(9, '台灯', '家具', 90.00, 25),
(10, 'USB闪存', '配件', 20.00, 150);INSERT INTO IM_Customers (CustomerID, CustomerName, CustomerEmail) VALUES
(1, '客户A', 'customerA@example.com'),
(2, '客户B', 'customerB@example.com'),
(3, '客户C', 'customerC@example.com'),
(4, '客户D', 'customerD@example.com'),
(5, '客户E', 'customerE@example.com'),
(6, '客户F', 'customerF@example.com'),
(7, '客户G', 'customerG@example.com'),
(8, '客户H', 'customerH@example.com'),
(9, '客户I', 'customerI@example.com'),
(10, '客户J', 'customerJ@example.com');INSERT INTO IM_Inventory (InventoryID, WarehouseID, ProductID, Quantity, LastUpdated) VALUES
(1, 1, 1, 60, '2024-08-01'),
(2, 1, 2, 120, '2024-08-01'),
(3, 1, 6, 200, '2024-08-01'),
(4, 1, 9, 30, '2024-08-01'),
(5, 2, 1, 80, '2024-08-01'),
(6, 2, 3, 20, '2024-08-01'),
(7, 2, 7, 90, '2024-08-01'),
(8, 2, 10, 150, '2024-08-01'),
(9, 3, 4, 50, '2024-08-01'),
(10, 3, 5, 10, '2024-08-01'),
(11, 3, 8, 5, '2024-08-01'),
(12, 4, 2, 60, '2024-08-01'),
(13, 4, 5, 40, '2024-08-01'),
(14, 4, 6, 120, '2024-08-01'),
(15, 5, 3, 80, '2024-08-01'),
(16, 5, 7, 30, '2024-08-01'),
(17, 5, 8, 20, '2024-08-01');INSERT INTO IM_Orders (OrderID, OrderDate, CustomerID, WarehouseID) VALUES
(1, '2024-08-02', 1, 1),
(2, '2024-08-05', 2, 2),
(3, '2024-08-07', 3, 3),
(4, '2024-08-10', 4, 1),
(5, '2024-08-12', 5, 2),
(6, '2024-08-14', 6, 3),
(7, '2024-08-16', 7, 4),
(8, '2024-08-18', 8, 5),
(9, '2024-08-20', 9, 1),
(10, '2024-08-22', 10, 2);INSERT INTO IM_OrderDetails (OrderDetailID, OrderID, ProductID, Quantity, UnitPrice) VALUES
(1, 1, 1, 10, 1200.00),
(2, 1, 2, 20, 800.00),
(3, 2, 3, 15, 250.00),
(4, 2, 4, 5, 150.00),
(5, 3, 5, 2, 300.00),
(6, 3, 6, 8, 80.00),
(7, 4, 7, 5, 40.00),
(8, 4, 8, 1, 150.00),
(9, 5, 9, 3, 90.00),
(10, 5, 10, 10, 20.00),
(11, 6, 1, 2, 1200.00),
(12, 6, 3, 5, 250.00),
(13, 7, 4, 10, 150.00),
(14, 7, 6, 3, 80.00),
(15, 8, 5, 7, 300.00),
(16, 8, 9, 4, 90.00),
(17, 9, 2, 6, 800.00),
(18, 9, 10, 8, 20.00),
(19, 10, 7, 5, 40.00),
(20, 10, 8, 2, 150.00);
3、预期查询结果
