SQL 练习/仓库

关系模式

练习

1. 选择所有仓库。

点击查看解决方案

 SELECT * FROM Warehouses;

2. 选择所有价值大于 150 美元的箱子。

点击查看解决方案

 SELECT * FROM Boxes 
 WHERE Value > 150;

3. 选择所有箱子中所有不同的内容。

点击查看解决方案

 SELECT DISTINCT Contents 
 FROM Boxes;

4. 选择所有箱子的平均价值。

点击查看解决方案

 SELECT AVG(Value) 
 FROM Boxes;

5. 选择仓库代码和每个仓库中箱子的平均价值。

点击查看解决方案

  SELECT Warehouse, AVG(Value)
  FROM Boxes
  GROUP BY Warehouse;

6. 与上一练习相同，但仅选择那些箱子平均价值大于 150 的仓库。

点击查看解决方案

  SELECT Warehouse, AVG(Value)
  FROM Boxes
  GROUP BY Warehouse
  HAVING AVG(Value) > 150;

7. 选择每个箱子的代码，以及箱子所在的城市名称。

点击查看解决方案

  SELECT Boxes.Code, Location
  FROM Warehouses INNER JOIN Boxes 
    ON Warehouses.Code = Boxes.Warehouse;

8. 选择仓库代码，以及每个仓库中的箱子数量。可选地，考虑到有些仓库是空的（即，箱子数量应该显示为零，而不是从结果中省略仓库）。

点击查看解决方案

 /* Not taking into account empty warehouses */
 SELECT Warehouse, COUNT(*)
 FROM Boxes
 GROUP BY Warehouse;

 /* Taking into account empty warehouses */
 SELECT Warehouses.Code, COUNT(Boxes.Code)
 FROM Warehouses LEFT JOIN Boxes
   ON Warehouses.Code = Boxes.Warehouse
 GROUP BY Warehouses.Code;

9. 选择所有饱和仓库的代码（如果仓库中的箱子数量大于仓库的容量，则该仓库饱和）。

点击查看解决方案

 SELECT Code
   FROM Warehouses
   WHERE Capacity <
   (
     SELECT COUNT(*)
     FROM Boxes
     WHERE Warehouse = Warehouses.Code
   );

/* Alternate method not involving nested statements */
  SELECT Warehouses.Code
  FROM Warehouses JOIN Boxes ON Warehouses.Code = Boxes.Warehouse
  GROUP BY Warehouses.code, Warehouses.Capacity
  HAVING Count(Boxes.code) > Warehouses.Capacity

10. 选择所有位于芝加哥的箱子的代码。

点击查看解决方案

 /* Without subqueries */
 SELECT Boxes.Code
 FROM Warehouses RIGHT JOIN Boxes
   ON Warehouses.Code = Boxes.Warehouse
 WHERE Location = 'Chicago';

 /* With a subquery */
 SELECT Code
   FROM Boxes
   WHERE Warehouse IN
   (
     SELECT Code
       FROM Warehouses
       WHERE Location = 'Chicago'
   );

11. 在纽约创建一个新的仓库，容量为 3 个箱子。

点击查看解决方案

 INSERT 
   INTO Warehouses
        (Location,Capacity)
 VALUES ('New York',3);

12. 创建一个新的箱子，代码为 "H5RT"，包含 "文件"，价值为 200 美元，位于仓库 2。

点击查看解决方案

 INSERT INTO Boxes
   VALUES('H5RT','Papers',200,2);

13. 将所有箱子的价值降低 15%。

点击查看解决方案

 UPDATE Boxes SET Value = Value * 0.85;

14. 对价值大于所有箱子平均价值的箱子应用 20% 的价值降低。

点击查看解决方案

UPDATE Boxes 
SET Boxes.value = Boxes.value * 0.8 
WHERE Boxes.code IN 
(
  SELECT * FROM 
       (
         SELECT Bx.code 
             FROM Boxes AS Bx
             WHERE Bx.value >  
                  (
                    SELECT AVG(B.value) 
                    FROM Boxes AS B
                   )
          ) AS Bxs
);

15. 删除所有价值低于 100 美元的箱子。

点击查看解决方案

 DELETE FROM Boxes WHERE Value < 100;

16. 从饱和仓库中删除所有箱子。

点击查看解决方案

 DELETE FROM Boxes 
  WHERE Warehouse IN 
  (
   SELECT * FROM 
     (
       SELECT Code
	 FROM Warehouses
	 WHERE Capacity <
           (
                SELECT COUNT(*)
		  FROM Boxes
		  WHERE Warehouse = Warehouses.Code
            )
      ) AS Bxs
  );

表创建代码

 CREATE TABLE Warehouses (
   Code INTEGER PRIMARY KEY NOT NULL,
   Location TEXT NOT NULL ,
   Capacity INTEGER NOT NULL 
 );
 
 CREATE TABLE Boxes (
   Code TEXT PRIMARY KEY NOT NULL,
   Contents TEXT NOT NULL ,
   Value REAL NOT NULL ,
   Warehouse INTEGER NOT NULL, 
   CONSTRAINT fk_Warehouses_Code FOREIGN KEY (Warehouse) REFERENCES Warehouses(Code)
 );

点击查看 MySQL 语法。

CREATE TABLE Warehouses (
   Code INTEGER NOT NULL,
   Location VARCHAR(255) NOT NULL ,
   Capacity INTEGER NOT NULL,
   PRIMARY KEY (Code)
 );
CREATE TABLE Boxes (
    Code VARCHAR(255) NOT NULL,
    Contents VARCHAR(255) NOT NULL ,
    Value REAL NOT NULL ,
    Warehouse INTEGER NOT NULL,
    PRIMARY KEY (Code),
    FOREIGN KEY (Warehouse) REFERENCES Warehouses(Code)
 ) ENGINE=INNODB;

示例数据集

 INSERT INTO Warehouses(Code,Location,Capacity) VALUES(1,'Chicago',3);
 INSERT INTO Warehouses(Code,Location,Capacity) VALUES(2,'Chicago',4);
 INSERT INTO Warehouses(Code,Location,Capacity) VALUES(3,'New York',7);
 INSERT INTO Warehouses(Code,Location,Capacity) VALUES(4,'Los Angeles',2);
 INSERT INTO Warehouses(Code,Location,Capacity) VALUES(5,'San Francisco',8);
 
 INSERT INTO Boxes(Code,Contents,Value,Warehouse) VALUES('0MN7','Rocks',180,3);
 INSERT INTO Boxes(Code,Contents,Value,Warehouse) VALUES('4H8P','Rocks',250,1);
 INSERT INTO Boxes(Code,Contents,Value,Warehouse) VALUES('4RT3','Scissors',190,4);
 INSERT INTO Boxes(Code,Contents,Value,Warehouse) VALUES('7G3H','Rocks',200,1);
 INSERT INTO Boxes(Code,Contents,Value,Warehouse) VALUES('8JN6','Papers',75,1);
 INSERT INTO Boxes(Code,Contents,Value,Warehouse) VALUES('8Y6U','Papers',50,3);
 INSERT INTO Boxes(Code,Contents,Value,Warehouse) VALUES('9J6F','Papers',175,2);
 INSERT INTO Boxes(Code,Contents,Value,Warehouse) VALUES('LL08','Rocks',140,4);
 INSERT INTO Boxes(Code,Contents,Value,Warehouse) VALUES('P0H6','Scissors',125,1);
 INSERT INTO Boxes(Code,Contents,Value,Warehouse) VALUES('P2T6','Scissors',150,2);
 INSERT INTO Boxes(Code,Contents,Value,Warehouse) VALUES('TU55','Papers',90,5);