[Study Notes] Alibaba Cloud Tianchi Dragon Ball Project SQL Training Camp - Task03: Complex Query Methods - Views, Subqueries, Functions, etc.

s

This note is the learning content of Alibaba Cloud Tianchi Dragon Ball Project SQL Training Camp

The link is: https://tianchi.aliyun.com/specials/promotion/aicampsql

 

Practice Questions - Part 1

3.1

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

  • Condition 1: The unit sales price is 1,000 yen or more.
  • Condition 2: The registration date is September 20, 2009.
  • Condition 3: Contains three columns: product name, unit sales price, and registration date.

The result of executing a SELECT statement on this view is shown below.

SELECT * FROM ViewPractice5_1;

Results of the

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

code show as below

CREATE VIEW ViewPractice5_1 as(
SELECT product_name,sale_price,regist_date
from product
where sale_price >= 1000 and regist_date = '2009-09-20'

3.2

Insert the following data into the view ViewPractice5_1 created in Exercise 1. What will be the result?

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

The answer is as follows: The inserted data does not meet the filter requirements of the view and therefore does not exist in the view, but exists within the original data (product)

3.3

Please write a SELECT statement based on the following results, where the sale_price_all column is the average selling price of all products.

product_id | product_name | product_type | sale_price | sale_price_all
------------+-------------+--------------+------------+---------------------
0001       | T shirt         | clothing         | 1000       | 2097.5000000000000000
0002       | hole punch        | Office Supplies      | 500        | 2097.5000000000000000
0003       | sports T shirt       | clothing          | 4000      | 2097.5000000000000000
0004       | kitchen knife          | Kitchenware      | 3000       | 2097.5000000000000000
0005       | pressure cooker        | Kitchenware      | 6800       | 2097.5000000000000000
0006       | fork          | Kitchenware      | 500        | 2097.5000000000000000
0007       | grater        | Kitchenware       | 880       | 2097.5000000000000000
0008       | ballpoint pen        | Office Supplies       | 100       | 2097.5000000000000000

code show as below:

SELECT
	product_id,	product_name,product_type,sale_price,
	(SELECT avg(sale_price) from product) as sale_price_all
FROM
	product
GROUP BY product_id
ORDER BY product_id

3.4

Write an SQL statement based on the conditions in Exercise 1 to create a view (named AvgPriceByType) containing the following data.

product_id | product_name | product_type | sale_price | avg_sale_price
------------+-------------+--------------+------------+---------------------
0001       | T shirt         | clothing         | 1000       |2500.0000000000000000
0002       | hole punch         | Office Supplies     | 500        | 300.0000000000000000
0003       | sports T shirt        | clothing        | 4000        |2500.0000000000000000
0004       | kitchen knife          | Kitchenware      | 3000        |2795.0000000000000000
0005       | pressure cooker         | Kitchenware     | 6800        |2795.0000000000000000
0006       | fork          | Kitchenware      | 500         |2795.0000000000000000
0007       | grater         | Kitchenware     | 880         |2795.0000000000000000
0008       | ballpoint pen         | Office Supplies     | 100         | 300.0000000000000000

Hint: The key here is the avg_sale_price column. Different from Exercise 3, what needs to be calculated here is the average selling price of each commodity type. This is the same result as using a correlated subquery. That is, the column can be created using a correlated subquery. The question is where to use this correlated subquery.

code show as below:

SELECT
	product_id,
	product_name,
	product_type,
	sale_price,
	( SELECT AVG( sale_price ) 
      FROM product AS p2 
      WHERE p1.product_type = p2.product_type 
      GROUP BY p2.product_type ) AS avg_sale_price 
FROM
	product AS p1
  • Practice Questions - Part II

3.5

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

Answer: I also learned after completing the question 3.6 that the results of this question may not all become NULL, depending on the situation. If the arithmetic function sum\avg is used, it does not affect the inclusion of null. If it becomes a predicate, the true results are all NULL

3.6

Execute the following two SELECT statements on the product table used in this chapter. What kind of results can you get?

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

search result:

 

 

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

search result:

 

3.7

The products in the product table in Exercise 6.1 are classified as follows according to the sales unit price ( sale_price).

  • Low-end goods: The unit price is less than 1,000 yen (T-shirts, office supplies, forks, graters, ballpoint pens)
  • Mid-range goods: The unit price is more than 1,001 yen and less than 3,000 yen (chopping knife)
  • High-end products: The unit price is more than 3,001 yen (sports T-shirts, pressure cookers)

Please write a SELECT statement that counts the number of products included in the above product categories, and the result is as follows.

code show as below:

SELECT
	sum( CASE WHEN a.price_fengdang = 'low_price' THEN 1 ELSE 0 END ) AS low_price,
	sum( CASE WHEN a.price_fengdang = 'mid_price' THEN 1 ELSE 0 END ) AS mid_price,
	sum( CASE WHEN a.price_fengdang = 'high_price' THEN 1 ELSE 0 END ) AS high_price 
FROM
	(
	SELECT
		product_name,
		product_type,
		sale_price,
		( CASE WHEN sale_price <= 1000 THEN 'low_price' WHEN sale_price <= 3000 THEN 'mid_price' ELSE 'high_price' END ) AS price_fengdang 
	FROM
		product 
	) a

Results of the

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

Tags: Database SQL

Posted by [e]r!k on Wed, 25 May 2022 06:53:14 +0300