跳到内容

SQL 练习/电脑商店

Add links
来自维基教科书，开放的书本，为开放的世界

关系模式

[编辑 | 编辑源代码]

请注意，给定的数据类型是 SQLite 数据类型。

PK 和 FK 分别代表主键和外键。

练习

[编辑 | 编辑源代码]

1. 选择商店中所有产品的名称。

点击查看解决方案
SELECT Name FROM Products;


2. 选择商店中所有产品的名称和价格。

点击查看解决方案
SELECT Name, Price FROM Products;


3. 选择价格小于或等于 $200 的产品的名称。

点击查看解决方案
SELECT Name FROM Products WHERE Price <= 200;


4. 选择价格在 $60 到 $120 之间的产品。

点击查看解决方案
 /* With AND */
 SELECT * FROM Products
   WHERE Price >= 60 AND Price <= 120;
 
 /* With BETWEEN */
 SELECT * FROM Products
   WHERE Price BETWEEN 60 AND 120;


5. 以美分显示名称和价格（即，价格必须乘以 100）。

点击查看解决方案
 /* Without AS */
 SELECT Name, Price * 100 FROM Products;
 
 /* With AS */
 SELECT Name, Price * 100 AS PriceCents FROM Products;


6. 计算所有产品的平均价格。

点击查看解决方案
 SELECT AVG(Price) FROM Products;


7. 计算制造商代码等于 2 的所有产品的平均价格。

点击查看解决方案
 SELECT AVG(Price) FROM Products WHERE Manufacturer=2;


8. 计算价格大于或等于 $180 的产品数量。

点击查看解决方案
 SELECT COUNT(*) FROM Products WHERE Price >= 180;


9. 选择所有价格大于或等于 $180 的产品名称和价格，并首先按价格（降序），然后按名称（升序）排序。

点击查看解决方案
   SELECT Name, Price 
     FROM Products
    WHERE Price >= 180
 ORDER BY Price DESC, Name;


10. 选择产品的所有数据，包括每个产品的制造商的所有数据。

点击查看解决方案
 /* Without LEFT JOIN */
 SELECT * FROM Products, Manufacturers
   WHERE Products.Manufacturer = Manufacturers.Code;

 /* With LEFT JOIN */
 SELECT *
   FROM Products LEFT JOIN Manufacturers
   ON Products.Manufacturer = Manufacturers.Code;


11. 选择所有产品的产品名称、价格和制造商名称。

点击查看解决方案
 /* Without INNER JOIN */
 SELECT Products.Name, Price, Manufacturers.Name
   FROM Products, Manufacturers
   WHERE Products.Manufacturer = Manufacturers.Code;

 /* With INNER JOIN */
 SELECT Products.Name, Price, Manufacturers.Name
   FROM Products INNER JOIN Manufacturers
   ON Products.Manufacturer = Manufacturers.Code;


12. 选择每个制造商产品的平均价格，仅显示制造商的代码。

点击查看解决方案
  SELECT AVG(Price), Manufacturer
    FROM Products
GROUP BY Manufacturer;


13. 选择每个制造商产品的平均价格，显示制造商的名称。

点击查看解决方案
 /* Without INNER JOIN */
 SELECT AVG(Price), Manufacturers.Name
   FROM Products, Manufacturers
   WHERE Products.Manufacturer = Manufacturers.Code
   GROUP BY Manufacturers.Name;
 
 /* With INNER JOIN */
 SELECT AVG(Price), Manufacturers.Name
   FROM Products INNER JOIN Manufacturers
   ON Products.Manufacturer = Manufacturers.Code
   GROUP BY Manufacturers.Name;


14. 选择其产品平均价格大于或等于 $150 的制造商的名称。

点击查看解决方案
 /* Without INNER JOIN */
 SELECT AVG(Price), Manufacturers.Name
   FROM Products, Manufacturers
   WHERE Products.Manufacturer = Manufacturers.Code
   GROUP BY Manufacturers.Name
   HAVING AVG(Price) >= 150;
 
 /* With INNER JOIN */
 SELECT AVG(Price), Manufacturers.Name
   FROM Products INNER JOIN Manufacturers
   ON Products.Manufacturer = Manufacturers.Code
   GROUP BY Manufacturers.Name
   HAVING AVG(Price) >= 150;


15. 选择最便宜产品的名称和价格。

点击查看解决方案
 SELECT name,price
  FROM Products
  ORDER BY price ASC
  LIMIT 1

/* With a nested SELECT */
/* WARNING: If there is more than one item with the cheapest price it will select them both */
 SELECT Name, Price
   FROM Products
   WHERE Price = (SELECT MIN(Price) FROM Products);


16. 选择每个制造商的名称，以及其最昂贵产品的名称和价格。

点击查看解决方案
 /* With a nested SELECT and without INNER JOIN */
   SELECT A.Name, A.Price, F.Name
   FROM Products A, Manufacturers F
   WHERE A.Manufacturer = F.Code
     AND A.Price =
     (
       SELECT MAX(A.Price)
         FROM Products A
         WHERE A.Manufacturer = F.Code
     );
 
 /* With a nested SELECT and an INNER JOIN */
   SELECT A.Name, A.Price, F.Name
   FROM Products A INNER JOIN Manufacturers F
   ON A.Manufacturer = F.Code
     AND A.Price =
     (
       SELECT MAX(A.Price)
         FROM Products A
         WHERE A.Manufacturer = F.Code
     );


17. 选择每个平均价格高于 $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 , p.Manufacturer
HAVING Avg(p.price) >= 150 and COUNT(p.Manufacturer) >= 2;


18. 添加一个新产品：扬声器，$70，制造商 2。

点击查看解决方案
 INSERT INTO Products( Code, Name , Price , Manufacturer)
  VALUES ( 11, 'Loudspeakers' , 70 , 2 );


19. 将产品 8 的名称更新为“激光打印机”。

点击查看解决方案
 UPDATE Products
   SET Name = 'Laser Printer'
   WHERE Code = 8;


20. 对所有产品应用 10% 的折扣。

点击查看解决方案
 UPDATE Products
   SET Price = Price - (Price * 0.1);


21. 对价格大于或等于 $120 的所有产品应用 10% 的折扣。

点击查看解决方案
 UPDATE Products
   SET Price = Price - (Price * 0.1)
   WHERE Price >= 120;


表格创建代码

[编辑 | 编辑源代码]
CREATE TABLE Manufacturers (
	Code INTEGER PRIMARY KEY NOT NULL,
	Name CHAR(50) NOT NULL 
);

CREATE TABLE Products (
	Code INTEGER PRIMARY KEY NOT NULL,
	Name CHAR(50) NOT NULL ,
	Price REAL NOT NULL ,
	Manufacturer INTEGER NOT NULL 
		CONSTRAINT fk_Manufacturers_Code REFERENCES Manufacturers(Code)
);

请注意，此处提供的语法适用于 SQLite 系统。作者已在 sqlite3 上对其进行了测试。

该代码也在 SQL Server 2017 上进行了测试。

另请注意，主键字段上的 NOT NULL 约束在语义上是冗余的，但在 SQLite 中是语法上的必要条件。

点击查看 MySQL 语法。
CREATE TABLE Manufacturers (
  Code INTEGER,
  Name VARCHAR(255) NOT NULL,
  PRIMARY KEY (Code)   
);

CREATE TABLE Products (
  Code INTEGER,
  Name VARCHAR(255) NOT NULL ,
  Price DECIMAL NOT NULL ,
  Manufacturer INTEGER NOT NULL,
  PRIMARY KEY (Code), 
  FOREIGN KEY (Manufacturer) REFERENCES Manufacturers(Code)
) ENGINE=INNODB;

示例数据集

[编辑 | 编辑源代码]
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);
取自 "https://wikibooks.cn/w/index.php?title=SQL_Exercises/The_computer_store&oldid=4392730"
华夏公益教科书