1 operator

1.1 arithmetic operators

meaning	operator
addition	+
subtraction	-
multiplication	*
division	/

1.2 comparison operators

operator	meaning
=	Equal to
<>	And ~ are not equal
>=	Greater than or equal to
>	greater than
<=	Less than or equal to
<	less than

2 exercise part 1 - query

2.1 writing the product table

The SQL code is as follows:

CREATE TABLE product
(product_id    CHAR(4)      NOT NULL,
product_name   VARCHAR(100) NOT NULL,
product_type   VARCHAR(32)  NOT NULL,
sale_price     INTEGER      DEFAULT 0,
purchase_price INTEGER ,
regist_date    DATE ,
PRIMARY KEY (product_id)); 

INSERT INTO product VALUES('0001', 'T shirt', 'clothes', 1000, 500, '2009-09-20');
INSERT INTO product VALUES('0002', 'Punch', 'Office Supplies', 500, 320, '2009-09-11');
INSERT INTO product VALUES('0003', 'motion T Shirt', 'clothes', 4000, 2800, NULL);
INSERT INTO product VALUES('0004', 'kitchen knife', 'kitchenware', 3000, 2800, '2009-09-20');
INSERT INTO product VALUES('0005', 'pressure cooker', 'kitchenware', 6800, 5000, '2009-01-15');
INSERT INTO product VALUES('0006', 'Fork', 'kitchenware', 500, NULL, '2009-09-20');
INSERT INTO product VALUES('0007', 'Dish cleaning board', 'kitchenware', 880, 790, '2008-04-28');
INSERT INTO product VALUES('0008', 'ball pen', 'Office Supplies', 100, NULL, '2009-11-11');

The results are as follows:

2.2 query statement

Write an SQL statement and select the goods with "registration date (register after April 28, 2009)" from the product table. The query result should include two columns: product name and register_date.
SQL code:

select product_name,regist_date
 from product
where 	not regist_date = '2009-01-15'
    and not regist_date = '2008-04-28';

Query results:

2.3 several incorrect query formats of null

Please state the results returned when the following three SELECT statements are executed on the product table

SELECT *
  FROM product
 WHERE purchase_price = NULL;

SELECT *
  FROM product
 WHERE purchase_price <> NULL;

SELECT *
  FROM product
 WHERE product_name > NULL;

None of the above three query results can get any records. The results are as follows:

reason:
When selecting NULL records, you need to use the IS NULL operator in the conditional expression. When you want to select records that are not NULL, you need to use the IS NOT NULL operator in the conditional expression.

2.4 conditional query statement + operator

The SELECT statement can take out the goods whose "sales price is more than 500 yen higher than purchase price" from the product table. Please write two SELECT statements that can get the same result. The execution results are as follows.

The SQL statement is as follows:

SELECT product_name, sale_price, purchase_price
  FROM product
 WHERE sale_price-purchase_price >= 500;
 
SELECT product_name, sale_price, purchase_price
  FROM product
 WHERE sale_price-500 >= purchase_price;

SELECT product_name, sale_price, purchase_price
  FROM product
 WHERE sale_price >= purchase_price +500;

2.5 condition query + query results are set to another column

SQL code:

SELECT product_name, product_type,sale_price*0.9-purchase_price as profit
  FROM product
 WHERE sale_price*0.9-purchase_price>100
  and (product_type='Office Supplies' or product_type='kitchenware');

The results are as follows:

3 aggregation & sorting

3.1 aggregate function

COUNT: calculate the number of records (rows) in the table
SUM: calculate the total value of the data in the value column in the table
AVG: calculate the average value of the data in the value column in the table
MAX: find the maximum value of data in any column in the table
MIN: find the minimum value of data in any column in the table

3.2 GROUP BY statement under where condition

SELECT purchase_price, COUNT(*)
  FROM product
 WHERE product_type = 'clothes'
 GROUP BY purchase_price;

There are strict requirements for the writing order of GROUP BY clauses. Failure to comply with the requirements will lead to the failure of normal SQL execution. The current order of clauses is:
1 SELECT → 2. FROM → 3. WHERE → 4. GROUP BY
The first three items are used to filter data, and GROUP BY processes the filtered data

3.3 using HAVING to get a specific group

-- number
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING COUNT(*) = 2;

3.4 ORDER BY sorting

-- Descending order
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY sale_price DESC;
-- Multiple sort keys
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY sale_price, product_id;
-- When the column name used for sorting contains NULL When, NULL It will be summarized at the beginning or end.
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY purchase_price;

Question: the alias defined in the SELECT clause cannot be used in the GROUP BY clause, but it can be used in the ORDER BY clause. Why can't it be used in GROUP BY and can it be used in ORDER BY?
This is because when SQL uses the HAVING clause, the order of SELECT statements is:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
The execution ORDER of SELECT is after the GROUP BY clause and before the ORDER BY clause, that is, when it is in ORDER
When using an alias in BY, you already know the existence of the alias set in SELECT, but you don't know the existence of the alias when using an alias in GROUP BY, so you can't use it in ORDER
Aliases can be used in BY, but not in GROUP BY

4 exercise part 2 - aggregation & sorting

4.1 please point out all syntax errors in the following SELECT statement.

SELECT product_id, SUM(product_name)
--book SELECT There is an error in the statement.
  FROM product 
 GROUP BY product_type 
 WHERE regist_date > '2009-09-01';

Solution:

• SUM is used to calculate the total value of the data in the numerical column in the table. The product_name column is not numerical column data.
• Punctuation marks, such as brackets, commas, semicolons, use full angle, and half angle should be used.
• The order of GROUP BY and WHERE is reversed, and a semicolon is not required after the WHERE statement.
• If a column name appears in the SELECT clause, it can only be the column name specified in the GROUP BY Clause (that is, the aggregate key).

4.2 HAVING filters aggregation groups

Please write a SELECT statement to find the product category whose total sales unit price (sales_price column) is 1.5 times greater than the total purchase unit price (purchase_price column). The execution results are as follows.

SQL code:

SELECT product_type,sum(sale_price),sum(purchase_price)
  FROM product 
 GROUP BY product_type
 HAVING sum(sale_price) > 1.5*sum(purchase_price);

4.3 ORDER BY sorting

Previously, we used the SELECT statement to SELECT all records in the product table. At that time, we used the order by clause to specify the sorting order, but now we can't remember how to specify it. Please think about the contents of the order by clause according to the following execution results.

SQL code:

SELECT *
  FROM product
 ORDER BY if(isnull(regist_date),0,1),regist_date DESC, sale_price;

reference resources: DataWhale SQL team learning