This note is the learning content of Alibaba cloud Tianchi Longzhu plan SQL training camp. The link is: https://tianchi.aliyun.com/specials/promotion/aicampsql

preface

This note is the learning content of Alibaba cloud Tianchi Longzhu plan SQL training camp. The link is: https://tianchi.aliyun.com/specials/promotion/aicampsql

1. Part I exercise questions and answers

Exercise 1

Create a view that meets the following three conditions (the view name is ViewPractice5_1). Using the product table as the reference table, it is assumed that the table contains the items in the initial state
8 rows of data.

• Condition 1: the sales unit price is greater than or equal to 1000 yen.
• Condition 2: the registration date is September 20, 2009.
• Condition 3: it includes three columns: commodity name, sales unit price and registration date. The result of the SELECT statement on the view is as follows.

SELECT * FROM ViewPractice5_1;

results of enforcement

product_name | sale_price | regist_date
--------------+------------+------------
T shirt         | 　 1000    | 2009-09-20
 kitchen knife          |    3000    | 2009-09-20

answer:

CREATE VIEW ViewPractice5_1 AS
SELECT product_name, sale_price, regist_date
FROM product
WHERE sale_price >= 1000
AND regist_date = '2009-09-20';

Exercise 2

Add viewpractice5 to the view created in exercise 1_ What results will be obtained by inserting the following data into 1?

INSERT INTO ViewPractice5_1 VALUES (' knife ', 300, '2009-11-02');

Answer: if you execute the previous statement, an error will be reported. This is because when the view inserts data, the original table will also insert data. When the original table data is inserted, it is not satisfied with the constraints, so an error will be reported. (because
The original table of ViewPractice5_1 has three fields with a NOT NULL constraint).

This is the original table structure:

+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| product_id | char(4) | NO | PRI | NULL | |
| product_name | varchar(100) | NO | | NULL | |
| product_type | varchar(32) | NO | | NULL | |
| sale_price | int | YES | | NULL | |
| purchase_price | int | YES | | NULL | |
| regist_date | date | YES | | NULL | |
+----------------+--------------+------+--

Exercise 3

Write a SELECT statement based on the following results, where sale_price_all is the average selling unit price of all goods.

product_id | product_name | product_type | sale_price | sale_price_all
------------+-------------+--------------+------------+---------------------
0001       | T shirt         | clothes         | 1000       | 2097.5000000000000000
0002       | Punch        | Office Supplies      | 500        | 2097.5000000000000000
0003       | motion T Shirt       | clothes          | 4000      | 2097.5000000000000000
0004       | kitchen knife          | kitchenware      | 3000       | 2097.5000000000000000
0005       | pressure cooker        | kitchenware      | 6800       | 2097.5000000000000000
0006       | Fork          | kitchenware      | 500        | 2097.5000000000000000
0007       | Dish cleaning board        | kitchenware       | 880       | 2097.5000000000000000
0008       | ball pen        | Office Supplies       | 100       | 2097.5000000000000000

answer:

SELECT product_id,
product_name,
product_type,
sale_price,
(SELECT AVG(sale_price) FROM product) AS sale_price_all
FROM product;

Exercise 4

Please write an SQL statement according to the conditions in exercise 1 and create a view (named AvgPriceByType) containing the following data.

product_id | product_name | product_type | sale_price | avg_sale_price
------------+-------------+--------------+------------+---------------------
0001       | T shirt         | clothes         | 1000       |2500.0000000000000000
0002       | Punch         | Office Supplies     | 500        | 300.0000000000000000
0003       | motion T Shirt        | clothes        | 4000        |2500.0000000000000000
0004       | kitchen knife          | kitchenware      | 3000        |2795.0000000000000000
0005       | pressure cooker         | kitchenware     | 6800        |2795.0000000000000000
0006       | Fork          | kitchenware      | 500         |2795.0000000000000000
0007       | Dish cleaning board         | kitchenware     | 880         |2795.0000000000000000
0008       | ball pen         | Office Supplies     | 100         | 300.0000000000000000

Tip: the key is AVG_ sale_ The price column. Different from exercise 3, what needs to be calculated here is the average sales unit price of each commodity type. This is the same as using the associated subquery. That is, the column can be created using an associated subquery. The question is where the associated subquery should be used.

answer:

CREATE VIEW AvgPriceByType AS
SELECT product_id,
 product_name,
 product_type,
 sale_price,
 (SELECT AVG(sale_price)
 FROM product p2
 WHERE p1.product_type = p2.product_type
 GROUP BY p1.product_type) AS avg_sale_price
FROM product p1;

2. Part II exercise questions and answers

Exercise 5

When an operation or function contains NULL, the result will all become NULL? (judgment question)

Answer: correct

Exercise 6

What results can be obtained by executing the following two SELECT statements on the product table used in this chapter?

①

SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000);

Answer: the query statement only takes out purchase_price is not a commodity of 500, 2800 or 5000.

②

SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);

Answer: zero records are returned because the parameter of NOT IN cannot contain NULL. Otherwise, the query result is usually empty.

Exercise 7

Classify the items in the product table in exercise 6.1 as follows according to the sales_price.

• Low grade goods: the sales unit price is less than 1000 yen (T-shirts, office supplies, forks, kitchen cleaning boards, ball point pens)
• Middle grade goods: the sales unit price is more than 1001 yen and less than 3000 yen (kitchen knife)
• High grade goods: the sales unit price is more than 3001 yen (Sports T-shirt, pressure cooker)
  Please write a SELECT statement to count the quantity of goods contained in the above commodity categories. The results are as follows.

results of enforcement

low_price | mid_price | high_price
----------+-----------+------------
        5 |         1 |         2

SELECT SUM(CASE WHEN sale_price <= 1000 THEN 1 ELSE 0 END) AS low_price,
SUM(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN 1 ELSE 0 END) AS mid_price,
SUM(CASE WHEN sale_price >= 3001 THEN 1 ELSE 0 END) AS high_price
FROM product;