1. Learning content

DataWhale SQL team learning

Addition and subtraction of Table 2

What is set operation?
A set represents "the sum of all kinds of things" in the field of mathematics and a set of records in the field of database. Specifically, the execution results of tables, views and queries are a set of records, and the elements in them are each row in the table or query results. In standard SQL, union, intersect and excel are used to merge, intersect and difference the search results respectively. Operators such as union, intersect and excel used to perform set operations are called set operators

2.1 addition UNION of table

2.1.1 UNION

SQL statement: (represents the union of two sets)

SELECT product_id, product_name
  FROM Product
 UNION
SELECT product_id, product_name
  FROM Product2;

Note: UNION and other set operators usually remove duplicate records

2.1.2 UNION and OR predicate

SQL statement: (the two results are consistent)

-- use OR predicate
SELECT * 
  FROM Product 
 WHERE sale_price / purchase_price < 1.3 
    OR sale_price / purchase_price IS NULL;

**-- use UNION
SELECT * 
  FROM Product 
 WHERE sale_price / purchase_price < 1.3
 UNION
SELECT * 
  FROM Product 
 WHERE sale_price / purchase_price IS NULL;
**

2.1.3 UNION ALL

For set operations that contain duplicate rows, add ALL after UNION

2.2 MySQL 8.0 does not support INTERSECT

2.3 subtraction of difference set, complement set and table

2.3.1 MySQL 8.0 does not support excel operation

MySQL 8.0 does not support table subtraction operator excel. However, with the help of the NOT IN predicate, we can also realize the subtraction of the table.

Exists only in the Product table but not in the Product2 table:

-- use IN Implementation method of clause
SELECT * 
  FROM Product
 WHERE product_id NOT IN (SELECT product_id 
                            FROM Product2)

2.3.2 INTERSECT AND and predicates

For two query results of the same table, their INTERSECT can actually connect the search conditions of the two queries with AND predicate equivalently.

2.4 symmetry difference

The symmetry difference between two sets A and B refers to the set composed of elements belonging only to A or B. The intersection of two sets can be regarded as the intersection of two sets, and the symmetry difference between the two sets is removed.
However, in MySQL 8.0, the of two tables or query results cannot be obtained directly. Therefore, it is not suitable to use the above idea to calculate the symmetry difference. Fortunately, there are difference set operations that can be used. It can be seen intuitively that the symmetry difference of two sets is equal to A-B and B-A, so this idea can be used to calculate the symmetry difference in practice.

Use the symmetry difference between the Product table and the Product2 table to query which products are in only one table
SQL statement:

-- use NOT IN Implement the difference set of two tables
SELECT * 
  FROM Product
 WHERE product_id NOT IN (SELECT product_id FROM Product2)
UNION
SELECT * 
  FROM Product2
 WHERE product_id NOT IN (SELECT product_id FROM Product)

3. Join

Time is a problem. The grammar rules will not be sorted out for the time being and will be supplemented later.
For details, refer to:
DataWhale SQL team learning
MySQL -- set operation

4 exercise questions

4.1

Find out the basic information of products with a price higher than 500 in product and product2.
SQL statement:

select * 
from product
where sale_price > 500
union
select * 
from product2
where sale_price > 500;

Operation results:

4.2

With the help of the implementation of symmetric difference, find the intersection of product and product2.
SQL statement:

select * 
from (select * from product 
			union 
			select * from product2) as p
where product_id not in
			(SELECT product_id FROM product
			 WHERE product_id NOT IN (SELECT product_id FROM product2)
			 UNION
       		 SELECT product_id FROM product2
			 WHERE product_id NOT IN (SELECT product_id FROM product));

Operation results:

4.3

Which stores sell the highest priced goods in each category?
SQL statement:

select p1.shop_id,p1.shop_name,p1.quantity,
			 p2.product_id,p2.product_name,p2.product_type,p2.sale_price,
			 mp.max_price as 'The highest selling price of this kind of goods is'
from shopproduct as p1
inner join product as p2
	on p1.product_id = p2.product_id
inner join (select product_type ,max(sale_price)as max_price
						from product
						group by product_type
						)as mp
on mp.product_type = p2.product_type
and p2.sale_price = mp.max_price;

Operation results:

4.4

Use the inner link and the associated sub link to query the highest selling price of each category of goods.
Inner link SQL statement: (consistent with the above question)

select p.product_id,p.product_id,p.product_type,p.sale_price,
			 mp.max_price as 'Maximum price of such goods'
from product as p
inner join(select product_type,max(sale_price) as max_price
					 from product
					 group by product_type)as mp
on p.product_type = mp.product_type
and p.sale_price = mp.max_price;		 

Operation results:

Associated subquery SQL statement:

select p.product_id,p.product_type,p.sale_price,
			 mp.max_price as 'Maximum price of such goods'
from product as p,
		 (select product_type,max(sale_price) as max_price
		  from product
			group by product_type) as mp
where p.product_type = mp.product_type
and p.sale_price = mp.max_price;

4.5

Use the associated sub query: in the product table, retrieve the product_id, produc_name, slae_price, and sort according to the selling price of goods from low to high, and sum the selling prices cumulatively.
SQL statement:

SELECT	p.product_id, p.product_name, p.sale_price,
				(select sum(sale_price) from product as p1
				 where p.sale_price > p1.sale_price
				 or(p.sale_price=p1.sale_price)
				 )as 'Cumulative summation'
from product as p
order by sale_price;   

Operation results:

Exercise reference:
Tianchi Longzhu SQL training camp daily task4 punch in