SQL经典练习：电脑商店

落日映苍穹つ 2023-10-14 08:07 30阅读 0赞

### 表结构 ###

本文使用的表结构如下：

![5ef1aeefdba640c391e80008422bb1f2.png][]

![截屏2023-03-02 下午5.27.32.png][2023-03-02 _5.27.32.png]

以下是创建表的语句：

-- 厂商表
    CREATE TABLE Manufacturers (
      Code INTEGER NOT NULL PRIMARY KEY, -- 编号，主键
      Name VARCHAR(255) NOT NULL, -- 名称
    );
    
    -- 产品表
    CREATE TABLE Products (
      Code INTEGER NOT NULL PRIMARY KEY, -- 编号，主键
      Name VARCHAR(255) NOT NULL, -- 名称
      Price DECIMAL NOT NULL, -- 价格
      Manufacturer INTEGER NOT NULL, -- 厂商编号
      FOREIGN KEY (Manufacturer) REFERENCES Manufacturers(Code) -- 外键，引用厂商表
    );
    
    -- 测试数据
    INSERT INTO Manufacturers(Code,Name) 
    VALUES (1,'Sony'),
           (2,'Creative Labs'),
           (3,'Hewlett-Packard'),
           (4,'Iomega'),
           (5,'Fujitsu'),
           (6,'Winchester');
    
    INSERT INTO Products(Code,Name,Price,Manufacturer) 
    VALUES (1,'Hard drive',240,5),
           (2,'Memory',120,6),
           (3,'ZIP drive',150,4),
           (4,'Floppy disk',5,6),
           (5,'Monitor',240,1),
           (6,'DVD drive',180,2),
           (7,'CD drive',90,2),
           (8,'Printer',270,3),
           (9,'Toner cartridge',66,3),
           (10,'DVD burner',180,2);

### 练习题 ###

#### 查看全部产品的名称 ####

SELECT Name FROM Products;

#### 查看全部产品的名称和价格 ####

SELECT Name, Price FROM Products;

#### 查看价格小于等于 200 的产品名称 ####

SELECT Name FROM Products WHERE Price <= 200;

#### 查看价格位于 60 到 120 之间的产品 ####

/* 使用 AND 运算符*/
    SELECT * FROM Products
    WHERE Price >= 60 AND Price <= 120;
    
    /* 使用 BETWEEN 运算符*/
    SELECT * FROM Products
    WHERE Price BETWEEN 60 AND 120;

#### 查看全部产品的名称和价格，价格以分为单位显示 ####

SELECT Name, Price * 100 AS PriceCents FROM Products;

#### 计算全部产品的平均价格 ####

SELECT AVG(Price) FROM Products;

#### 计算厂商编号为 2 的所有产品的平均价格 ####

SELECT AVG(Price) FROM Products WHERE Manufacturer=2;

#### 统计价格大于等于 180 的产品数量 ####

SELECT COUNT(*) FROM Products WHERE Price >= 180;

#### 查看价格大于等于 180 的产品名称和价格，按照价格降序、名称升序排序 ####

SELECT Name, Price 
    FROM Products
    WHERE Price >= 180
    ORDER BY Price DESC, Name;

#### 查看全部产品以及它们的厂商信息 ####

SELECT * FROM Products, Manufacturers
    WHERE Products.Manufacturer = Manufacturers.Code;

#### 查看全部产品的名称、价格以及厂商的名称 ####

SELECT Products.Name, Price, Manufacturers.Name
    FROM Products 
    JOIN Manufacturers
    ON Products.Manufacturer = Manufacturers.Code;

#### 统计每个厂商的平均产品价格，显示厂商编号 ####

SELECT AVG(Price), Manufacturer
    FROM Products
    GROUP BY Manufacturer;

#### 统计每个厂商的平均产品价格，显示厂商名称 ####

SELECT AVG(Price), Manufacturers.Name
    FROM Products 
    JOIN Manufacturers
    ON Products.Manufacturer = Manufacturers.Code
    GROUP BY Manufacturers.Name;

#### 查看平均产品价格大于等于 150 的厂商名称 ####

SELECT AVG(Price), Manufacturers.Name
    FROM Products 
    JOIN Manufacturers
    ON Products.Manufacturer = Manufacturers.Code
    GROUP BY Manufacturers.Name
    HAVING AVG(Price) >= 150;

#### 查看最便宜的产品名称和价格 ####

SELECT Name, Price
    FROM Products
    ORDER BY price ASC
    LIMIT 1;
    
    /* 如果存在多个最便宜的产品，嵌套子查询可以返回多条记录 */
    SELECT Name, Price
    FROM Products
    WHERE Price = (SELECT MIN(Price) FROM Products);

#### 查看每个厂商及其最昂贵的产品名称和价格 ####

SELECT A.Name, A.Price, F.Name
    FROM Products A 
    JOIN Manufacturers F
    ON A.Manufacturer = F.Code
    AND A.Price =
         (
           SELECT MAX(A.Price)
           FROM Products A
           WHERE A.Manufacturer = F.Code
         );

#### 查看平均产品价格大于 145 并且至少生产 2 种产品的厂商名称 ####

SELECT m.Name, Avg(p.price) AS p_price, COUNT(p.Manufacturer) AS m_count
    FROM Manufacturers m, Products p
    WHERE p.Manufacturer = m.code
    GROUP BY m.Name
    HAVING Avg(p.price) >= 150 AND COUNT(p.Manufacturer) >= 2;

#### 增加一个新的产品 ####

新产品的信息如下：名称为 Loudspeakers，价格为 70，厂商编号为 2。

INSERT INTO Products( Code, Name , Price , Manufacturer)
      VALUES ( 11, 'Loudspeakers' , 70 , 2 );

#### 将编号为 8 的产品更名为“Laser Printer” ####

UPDATE Products
    SET Name = 'Laser Printer'
    WHERE Code = 8;

#### 将全部产品打九折 ####

UPDATE Products
    SET Price = Price - (Price * 0.1);

#### 将价格大于等于 120 的产品打九折 ####

UPDATE Products
    SET Price = Price - (Price * 0.1)
    WHERE Price >= 120;

[5ef1aeefdba640c391e80008422bb1f2.png]: https://img-blog.csdnimg.cn/5ef1aeefdba640c391e80008422bb1f2.png
[2023-03-02 _5.27.32.png]: https://storage.qfedu.com/twxt/1677749283382.%E6%88%AA%E5%B1%8F2023-03-02%20%E4%B8%8B%E5%8D%885.27.32.png