SQL经典练习：电脑商店

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;


## 写在最后

**在结束之际，我想重申的是，学习并非如攀登险峻高峰，而是如滴水穿石般的持久累积。尤其当我们步入工作岗位之后，持之以恒的学习变得愈发不易，如同在茫茫大海中独自划舟，稍有松懈便可能被巨浪吞噬。然而，对于我们程序员而言，学习是生存之本，是我们在激烈市场竞争中立于不败之地的关键。一旦停止学习，我们便如同逆水行舟，不进则退，终将被时代的洪流所淘汰。因此，不断汲取新知识，不仅是对自己的提升，更是对自己的一份珍贵投资。让我们不断磨砺自己，与时代共同进步，书写属于我们的辉煌篇章。**


需要完整版PDF学习资源私我