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)
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:
INSERT INTO ViewPractice5_1 VALUES (' Knife ', 300, '2009-11-02');
The result is shown in the figure:
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:
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;