跳到內容

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://wikibook.tw/w/index.php?title=SQL_Exercises/The_computer_store&oldid=4392730"
華夏公益教科書