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);