CREATE TABLE Warehouse ( WarehouseID INT PRIMARY KEY, Name VARCHAR(50), Address VARCHAR(100), Telephone VARCHAR(20), Capacity INT ); CREATE TABLE Staff ( StaffID INT PRIMARY KEY, Name VARCHAR(50), Gender CHAR(2), Birthday DATE, JobType VARCHAR(20), WarehouseID INT, FOREIGN KEY (WarehouseID) REFERENCES Warehouse(WarehouseID) ); CREATE TABLE Steel ( SteelID INT PRIMARY KEY, Name VARCHAR(50), Variety VARCHAR(50), Spec VARCHAR(50) ); CREATE TABLE Supplier ( SupplierID INT PRIMARY KEY, Name VARCHAR(50), Address VARCHAR(100), Telephone VARCHAR(20), Contact VARCHAR(50) ); CREATE TABLE SupplierProduct ( SupplierID INT, SteelID INT, Price FLOAT, PRIMARY KEY (SupplierID, SteelID), FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID), FOREIGN KEY (SteelID) REFERENCES Steel(SteelID) ); CREATE TABLE Deposit ( SteelID INT, WarehouseID INT, Num INT PRIMARY KEY (SteelID, WarehouseID), FOREIGN KEY (SteelID) REFERENCES Steel(SteelID), FOREIGN KEY (WarehouseID) REFERENCES Warehouse(WarehouseID) ); CREATE TABLE SalesRecord ( SalesID INT PRIMARY KEY, StaffID INT, SteelID INT, WarehouseID INT, Quantity INT, SalesDate DATE, FOREIGN KEY (StaffID) REFERENCES Staff(StaffID), FOREIGN KEY (SteelID) REFERENCES Steel(SteelID), FOREIGN KEY (WarehouseID) REFERENCES Warehouse(WarehouseID), );

这是一个典型的关系型数据库的例子，共有6个表。Warehouse表记录仓库的信息，包括仓库ID、名称、地址、电话和容量；Staff表记录员工的信息，包括员工ID、姓名、性别、出生日期、职位和所属仓库；Steel表记录钢材的信息，包括钢材ID、名称、品种和规格；Supplier表记录供应商的信息，包括供应商ID、名称、地址、电话和联系人；SupplierProduct表记录供应商所提供的钢材信息，包括供应商ID、钢材ID和价格；Deposit表记录钢材在仓库中的存储情况，包括钢材ID、仓库ID和数量；SalesRecord表记录销售记录，包括销售ID、员工ID、钢材ID、仓库ID、数量和销售日期。这些表之间通过外键关联起来，形成一个完整的数据模型，可以进行复杂的数据查询和分析。

相关问题

CREATE TABLE B ( Bno INT(10) PRIMARY KEY, Bname VARCHAR(50), Bauthor VARCHAR(50), Bprice DECIMAL(10, 2), Pname VARCHAR(50), Pdate DATE ); CREATE TABLE C ( Cno INT(10) PRIMARY KEY, Cname VARCHAR(50), Csex VARCHAR(10), Cbirthday DATE, Caddress VARCHAR(100), Cmobile VARCHAR(20) ); CREATE TABLE S ( Sno INT(10) PRIMARY KEY, Bno INT(10), Cno INT(10), Scount INT(10), Ssum DECIMAL(10, 2), Sdate DATE, FOREIGN KEY (Bno) REFERENCES B(Bno), FOREIGN KEY (Cno) REFERENCES C(Cno) ); -- 创建库存表BW： CREATE TABLE BW ( Bno INT(10), Wno INT(10), num INT(10), PRIMARY KEY (Bno, Wno), FOREIGN KEY (Bno) REFERENCES B(Bno), FOREIGN KEY (Wno) REFERENCES W(Wno) ); -- 创建仓库表W： CREATE TABLE W ( Wno INT(10) PRIMARY KEY, Wname VARCHAR(50), Waddress VARCHAR(100) );给出上述表的几组数据并给出插入语句

给出表B的数据和插入语句：

| Bno | Bname | Bauthor | Bprice | Pname | Pdate |
| --- | ----------| ----------| ------ | --------| ----------|
| 101 | Book A | Author A | 50.00 | Publisher A | 2021-01-01 |
| 102 | Book B | Author B | 80.00 | Publisher B | 2021-02-02 |
| 103 | Book C | Author C | 60.50 | Publisher C | 2021-03-03 |

插入语句：

INSERT INTO B (Bno, Bname, Bauthor, Bprice, Pname, Pdate) VALUES
(101, 'Book A', 'Author A', 50.00, 'Publisher A', '2021-01-01'),
(102, 'Book B', 'Author B', 80.00, 'Publisher B', '2021-02-02'),
(103, 'Book C', 'Author C', 60.50, 'Publisher C', '2021-03-03');

给出表C的数据和插入语句：

| Cno | Cname | Csex | Cbirthday | Caddress | Cmobile |
| --- | --------| -----| ----------| ----------------| ----------|
| 201 | Customer A | Male | 1990-01-01 | Address A, City A | 1234567890 |
| 202 | Customer B | Female | 1995-02-02 | Address B, City B | 2345678901 |
| 203 | Customer C | Male | 1985-03-03 | Address C, City C | 3456789012 |

插入语句：

INSERT INTO C (Cno, Cname, Csex, Cbirthday, Caddress, Cmobile) VALUES
(201, 'Customer A', 'Male', '1990-01-01', 'Address A, City A', '1234567890'),
(202, 'Customer B', 'Female', '1995-02-02', 'Address B, City B', '2345678901'),
(203, 'Customer C', 'Male', '1985-03-03', 'Address C, City C', '3456789012');

给出表S的数据和插入语句：

| Sno | Bno | Cno | Scount | Ssum | Sdate |
| --- | --- | --- | ------ | ------ | ----------|
| 301 | 101 | 201 | 2 | 100.00 | 2021-04-01 |
| 302 | 102 | 202 | 1 | 80.00 | 2021-05-02 |
| 303 | 103 | 203 | 3 | 181.50 | 2021-06-03 |

插入语句：

INSERT INTO S (Sno, Bno, Cno, Scount, Ssum, Sdate) VALUES
(301, 101, 201, 2, 100.00, '2021-04-01'),
(302, 102, 202, 1, 80.00, '2021-05-02'),
(303, 103, 203, 3, 181.50, '2021-06-03');

给出表W的数据和插入语句：

| Wno | Wname | Waddress |
| --- | -----------| -----------------|
| 1 | Warehouse A| Address A, City A |
| 2 | Warehouse B| Address B, City B |
| 3 | Warehouse C| Address C, City C |

插入语句：

INSERT INTO W (Wno, Wname, Waddress) VALUES
(1, 'Warehouse A', 'Address A, City A'),
(2, 'Warehouse B', 'Address B, City B'),
(3, 'Warehouse C', 'Address C, City C');

给出表BW的数据和插入语句：

| Bno | Wno | num |
| --- | --- | --- |
| 101 | 1 | 20 |
| 101 | 2 | 30 |
| 102 | 2 | 50 |
| 103 | 3 | 10 |

插入语句：

INSERT INTO BW (Bno, Wno, num) VALUES
(101, 1, 20),
(101, 2, 30),
(102, 2, 50),
(103, 3, 10);

本题目要求编写SQL语句， 查询’上海’的仓库编号及其获得的订单数量和产品总价。 CREATE TABLE `employee` ( `Eid` varchar(10), --职工编号 `EName` varchar(30), --职工姓名 `Wno` varchar(10), --所在仓库 `Salary` int(11) --职工工资 ); CREATE TABLE `orders` ( `OrdNo` int(11), --订单编号 `Sid` varchar(10), --供应商编号 `Eid` varchar(10), --职工编号 `Pid` varchar(20), --商品编号 `Price` decimal(10,2), --价格 `QTY` int(11), --订购数量 `ordDate` date --订单日期 ); CREATE TABLE `warehouse` ( `Wno` varchar(10), --仓库编号 `City` varchar(20), --所在城市 `Size` int(11), --面积 );

SELECT warehouse.Wno, COUNT(orders.OrdNo) AS OrderCnt, SUM(orders.Price * orders.QTY) AS ProductTotalPrice
FROM warehouse
INNER JOIN employee ON warehouse.Wno = employee.Wno
INNER JOIN orders ON employee.Eid = orders.Eid
WHERE warehouse.City = '上海'
GROUP BY warehouse.Wno;