源站網網絡技術 2021-04-07

SQL語句練習-倉庫

創建表

倉庫表

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

預置數據

倉庫信息

INSERT INTO Warehouses
VALUES (1, 'Chicago', 3),
       (2, 'Chicago', 4),
       (3, 'New York', 7),
       (4, 'Los Angeles', 2),
       (5, 'San Francisco', 8);

箱子信息

INSERT INTO Boxes
VALUES ('0MN7', 'Rocks', 180, 3),
       ('4H8P', 'Rocks', 250, 1),
       ('4RT3', 'Scissors', 190, 4),
       ('7G3H', 'Rocks', 200, 1),
       ('8JN6', 'Papers', 75, 1),
       ('8Y6U', 'Papers', 50, 3),
       ('9J6F', 'Papers', 175, 2),
       ('LL08', 'Rocks', 140, 4),
       ('P0H6', 'Scissors', 125, 1),
       ('P2T6', 'Scissors', 150, 2),
       ('TU55', 'Papers', 90, 5);

題目

選擇所有倉庫
選擇值大於$ 150的所有箱子
在所有箱子中選擇所有不同的內容
選擇所有箱子的平均值
選擇倉庫代碼和每個倉庫中箱子的平均值
與上一練習相同，但隻選擇那些平均值大於150的倉庫
選擇每個箱子的代碼以及箱子所在城市的名稱
選擇倉庫代碼以及每個倉庫中的箱子數量。可選，考慮到一些倉庫是空的（即，箱子數應該顯示為零，而不是從結果中省略倉庫）
選擇飽和的所有倉庫的代碼（如果倉庫中的箱子數大於倉庫的容量，則倉庫已飽和）
選擇位於芝加哥的所有箱子的代碼
在New York新建一個倉庫，可容納3個箱子
創建一個新箱子，代碼為H5RT，包含價值為200的Papers，位於倉庫2中
將所有箱子的價值減少15％
對價值大於所有箱子平均值的箱子應用20％的減值
移除價值低於100的所有箱子
從飽和倉庫中取出所有箱子

答案

選擇所有倉庫

select *
from warehouses;

選擇值大於$ 150的所有箱子

SELECT *
FROM Boxes
WHERE Value > 150;

在所有箱子中選擇所有不同的內容

SELECT DISTINCT Contents
FROM Boxes;

選擇所有箱子的平均值

SELECT AVG(Value)
FROM Boxes;

選擇倉庫代碼和每個倉庫中箱子的平均值

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

與上一練習相同，但隻選擇那些平均值大於150的倉庫

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

選擇每個箱子的代碼以及箱子所在城市的名稱

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

選擇倉庫代碼以及每個倉庫中的箱子數量。可選，考慮到一些倉庫是空的（即，箱子數應該顯示為零，而不是從結果中省略倉庫）

/* 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;

選擇飽和的所有倉庫的代碼（如果倉庫中的箱子數大於倉庫的容量，則倉庫已飽和）

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
HAVING Count(Boxes.Code) > Warehouses.Capacity;

選擇位於芝加哥的所有箱子的代碼

/* 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'
      );

在New York新建一個倉庫，可容納3個箱子

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

創建一個新箱子，代碼為H5RT，包含價值為200的Papers，位於倉庫2中

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

將所有箱子的價值減少15％

UPDATE Boxes
SET Value = Value * 0.85;

對價值大於所有箱子平均值的箱子應用20％的減值

UPDATE Boxes
SET Value = Value * 0.80
WHERE Value > (SELECT AVG(Value) FROM (SELECT * FROM Boxes) AS X);

移除價值低於100的所有箱子

DELETE
FROM Boxes
WHERE Value < 100;

從飽和倉庫中取出所有箱子

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

數據