task03-More complex query-learning and Practice

1 Learning

1.1 View

Create a view:

CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type ;

Get:

Modify View Code

ALTER VIEW productSum
    AS
        SELECT product_type, sale_price
          FROM Product
         WHERE regist_date > '2009-09-11';

Update View Code:

UPDATE productsum
   SET sale_price = '5000'
 WHERE product_type = 'Office Supplies';

Delete View Code

DROP VIEW productSum;

1.2 Subquery

Scalar quantum query: Returns a column of a specific row in a table.
Purpose: 1. Query out the commodities whose sales unit price is higher than the average sales unit price.
(2) Query out which item has the latest registration date
The codes for querying goods whose prices are greater than the average are as follows:

SELECT product_id, product_name, sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product);

The result is as follows:

Associate subqueries: Connect the internal and external queries to filter the data through some flags.
Example: Select a commodity whose average unit price is higher than that of each commodity type
The code is as follows:

SELECT product_type, product_name, sale_price
  FROM product AS p1
 WHERE sale_price > (SELECT AVG(sale_price)
   FROM product AS p2
                      WHERE p1.product_type =p2.product_type
   GROUP BY product_type);

The output is:

1.3 Function

Get the current date

SELECT CURRENT_DATE;

Get:

Get the current date and time:

SELECT CURRENT_TIMESTAMP;

1.4 predicate

Functions that return true values
(TRUE / FALSE / UNKNOWN)

2 Exercises

Original table:

2.1 Create a view (named ViewPractice5_1) that meets the following three criteria. Use the product (commodity) table as the reference table, assuming that the table contains eight rows of data in the initial state.

Condition 1: Sales unit price is greater than or equal to 1000 yen.
Condition 2: Registration date is September 20, 2009.
Conditions 3: Include three columns: commodity name, sales unit price and registration date.
Solution code:

CREATE VIEW viewpractice5_1
(product_name,sale_price,regist_date)
AS
SELECT product_name,sale_price,regist_date
FROM product
WHERE sale_price >= 1000 and regist_date='2009-09-20';

The output is as follows:

2.2 What will be the result of inserting the following data into the view ViewPractice5_1 created in Exercise 1?

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

The result is shown in the figure:

2.3 Write a SELECT statement based on the results below, where sale_price_all is listed as the average unit price for all goods sold.

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 Pension       | 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       | Wipe board        | Kitchenware       | 880       | 2097.5000000000000000
0008       | Ball pen        | Office Supplies       | 100       | 2097.5000000000000000

The code is as follows:

select product_id, product_name, product_type, sale_price, 
(select avg(sale_price) from product) 
as sale_price_all 
from product;

The result is as follows:

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

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 Pension        | 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       | Wipe board         | Kitchenware     | 880         |2795.0000000000000000
0008       | Ball pen         | Office Supplies     | 100         | 300.0000000000000000

Tip: The key is the avg_sale_price column. Unlike Exercise 3, what you need to calculate here is the average sales unit price for each commodity type. This is the same as what you get with an associated subquery. That is, the column can be created with an associated subquery. The question is where this associated subquery should be used.
(the join condition defined in the association subquery is having, not where)
The code is as follows:

create view avgpricebytype 
(product_id, product_name, product_type, sale_price,
 avg_sale_price) 
as
	select product_id, product_name, product_type, sale_price,
    (select avg(sale_price) from product as p2
		group by product_type
			having p2.product_type=p1.product_type) 
	as avg_sale_price from product as p1;

The results are as follows:

When a 2.5 operation or function contains NULL, all results will become NULL? (Judgment Question)

Wrong! Not necessarily

2.6 What are the results of executing the following two SELECT statements on the product (commodity) table used in this chapter?

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

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


The value of purchase_price is null and the null comparison result is always null, i.e. (null = null) = null, returning a result that is not TRUE or FALSE and therefore does not return a row where purchase_price is null.

2.6 Categorize the items in the product (commodities) table in Exercise 3.6 according to the sales_price as follows.

Low-end goods: sales unit price is less than 1000 yen (T-shirts, office supplies, forks, dishboard, ballpoint pen)
Medium-grade commodities: sales unit price is more than 1001 yen and less than 3000 yen (kitchen knife)
High-end goods: sales unit price is above RMB 3001 (sports T-shirts, pressure cooker)
Write a SELECT statement that counts the number of items in the above categories as shown below.

results of enforcement

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

select 
	count(case when sale_price <= 1000 then sale_price end) as low_price,
    count(case when sale_price between 1001 and 3000 then sale_price end) as mid_price,
    count(case when sale_price >= 3001 then sale_price end) as high_price
    from product;

Tags: Javascript Database MySQL SQL

Posted on Sun, 19 Sep 2021 16:58:09 -0400 by zang8027