SQL語句練習-電腦商店

創建表

• 商店表

create table manufacturers
(
    id   integer primary key,  -- 商店ID
    name varchar(100) not null -- 商店名稱
);

• 商品表

create table products
(
    id            integer primary key,                  --商品ID
    name          varchar(100) not null,                --商品名稱
    price         decimal      not null,                --商品價格
    manufacturer integer references manufacturers (id)  --關聯商店
);

預置數據

• 商店信息

insert into manufacturers
values (1, '索尼'),
       (2, '華碩'),
       (3, '惠普'),
       (4, '英特爾'),
       (5, '富士通'),
       (6, '金士頓');

• 商品信息

insert into products
values (1, '硬盤', 240, 5),
       (2, '內存', 120, 6),
       (3, 'CPU', 150, 4),
       (4, 'U盤', 5, 6),
       (5, '監控', 240, 1),
       (6, 'DVD驅動器', 180, 2),
       (7, 'CD驅動器', 90, 2),
       (8, '打印機', 270, 3),
       (9, '墨粉盒', 66, 3),
       (10, 'DVD刻錄機', 180, 2);

題目

1. 選擇商店中所有產品的名稱
2. 選擇商店中所有產品的名稱和價格
3. 選擇價格小於或等於200的產品名稱
4. 選擇價格在60到120之間的所有產品
5. 選擇名稱和價格，但是價格必須乘以100
6. 計算所有產品的平均價格
7. 計算製造商ID等於2的所有產品的平均價格
8. 計算價格大於或等於180的產品數量
9. 選擇價格大於或等於180的所有產品的名稱和價格，然後按價格降序排序，然後按名稱升序排序
10. 選擇產品中的所有數據，包括每個產品製造商的所有數據
11. 選擇所有產品的產品名稱，價格和製造商名稱
12. 選擇每個製造商產品的平均價格，僅顯示製造商的代碼
13. 選擇每個製造商產品的平均價格，顯示製造商的名稱
14. 選擇產品的平均價格大於或等於150美元的製造商名稱
15. 選擇最便宜產品的名稱和價格
16. 選擇每個製造商的名稱以及最昂貴產品的名稱和價格
17. 添加新產品：音箱, 價格：70, 製造商ID：2
18. 將產品id為8的名稱更新為激光打印機
19. 對所有產品享受10％的折扣
20. 對價格大於或等於120美元的所有產品申請10％的折扣

答案

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

select name, price * 100 as price
from products;

6. 計算所有產品的平均價格

select avg(price)
from products;

7. 計算製造商ID等於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 INNER JOIN */
select *
from products as p,
     manufacturers as m
where p.manufacturer = m.id;

/* With INNER JOIN */
select *
from products as p
         inner join manufacturers m on p.manufacturer = m.id;

11. 選擇所有產品的產品名稱，價格和製造商名稱

/* Without INNER JOIN */
select p.name, p.price, m.name
from products as p,
     manufacturers as m
where p.manufacturer = m.id;

/* With INNER JOIN */
SELECT p.Name, p.Price, m.Name
FROM products as p
         INNER JOIN manufacturers as m
                    ON p.Manufacturer = m.id;

12. 選擇每個製造商產品的平均價格，僅顯示製造商的代碼

select avg(price), manufacturer
from products
group by manufacturer;

13. 選擇每個製造商產品的平均價格，顯示製造商的名稱

/* Without INNER JOIN */
select avg(price), m.name
from products as p,
     manufacturers as m
where p.manufacturer = m.id
group by m.name

/* With INNER JOIN */
select avg(price), m.name
from products as p
         inner join manufacturers m on p.manufacturer = m.id
group by m.name

14. 選擇產品的平均價格大於或等於150美元的製造商名稱

/* Without INNER JOIN */
select avg(Price), m.name
from products as p,
     manufacturers as m
where p.manufacturer = m.id
group by m.name
having avg(Price) >= 150;

/* With INNER JOIN */
SELECT AVG(Price), m.Name
FROM Products as p
         INNER JOIN manufacturers as m
                    ON p.Manufacturer = m.id
GROUP BY m.Name
HAVING AVG(Price) >= 150;

15. 選擇最便宜產品的名稱和價格

SELECT name, price
FROM Products
ORDER BY price ASC
LIMIT 1;

/* With a nested SELECT */
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.id
  AND A.Price =
      (
          SELECT MAX(A.Price)
          FROM Products A
          WHERE A.manufacturer = F.id
      );

/* 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.id
                        AND A.Price =
                            (
                                SELECT MAX(A.Price)
                                FROM Products A
                                WHERE A.Manufacturer = F.id
                            );

17. 添加新產品：音箱, 價格：70, 製造商ID：2

insert into products
values (11, '音箱', 70, 2);

18. 將產品id為8的名稱更新為激光打印機

update products
set name='激光打印機'
where id = 8;

19. 對所有產品享受10％的折扣

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

20. 對價格大於或等於120美元的所有產品申請10％的折扣

update products
set price=price - (price * 0.1)
where price >= 120;