Basic query and sorting

Keywords: SQL

This article learned from Dataware SQL tutorial

SELECT & WHERE

SELECT: SELECT the necessary data from the table

Query: the process of matching query / query and selecting necessary data through SELECT

SELECT "Field name" FROM "Table name";

WHERE: selectively extract necessary data

SELECT "Field name" 
FROM "Table name" 
WHERE "condition";

For example:

SELECT sale_price
FROM product
WHERE product_name='trousers';

Some SQL rules

1, An asterisk (*) represents all columns

SELECT * FROM <Table name>；

2, Line breaks can be used freely in SQL without affecting statement execution (but empty lines cannot be inserted)

3, DISTINCT can be used in a SELECT statement to remove duplicate rows

SELECT DISTINCT product_type FROM product;

4, Single line notes: –; Multiline comment: / **/

5, Use IS NULL operator when selecting NULL record; The IS NOT NULL operator is used when selecting records that are not NULL

-- selection NULL Record of
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NULL;
-- Select not as NULL Record of
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NOT NULL;

6, SQL statements can use the AS keyword to set aliases for columns (double quotes are required when using Chinese)

SELECT product_id     As id,
       product_name   As name,
       purchase_price AS "Purchase unit price"
FROM product;

7, SQL statements can use arithmetic expressions

SELECT product_name, sale_price, sale_price * 2 AS "sale_price x2"
FROM product;

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

8, You can use AND, OR, NOT operators

-- By using parentheses OR Operator before AND Operator execution
SELECT product_name, product_type, regist_date
FROM product
WHERE product_type = 'Office Supplies'
AND ( regist_date = '2009-09-11' OR regist_date = '2009-09-20');

9, Note that because of the existence of NULL in SQL, its logical operation is called ternary logic. In short, no matter AND OR, if there is an uncertainty, it is uncertain.

Exercises

1, 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.

SELECT product_name, regist_date From product
WHERE regist_date >= '2009-4-28';

Note: in SQL, you can use >, <, > =, < = to compare and select dates,

2, Please state the returned results when executing the following three SELECT statements on the product table.

-- 1
SELECT *
FROM product
WHERE purchase_price = NULL;
-- 2
SELECT *
FROM product
WHERE purchase_price <> NULL;
-- 3
SELECT *
FROM product
WHERE product_name > NULL;

Return all the NULL purchase_price columns in the product table;
Returns all columns in the product table whose purchase_price column is not NULL;
Error is reported, NULL cannot be compared;

3, Please write a SELECT statement and SELECT the records from the product table that meet the conditions of "office supplies and kitchenware with a profit of more than 100 yen after 10% discount on the sales unit price". The query results should include the product_name column, the product_type column and the profit after 10% discount on the sales unit price (the alias is set to profit).

Tip: a 10% discount on the sales unit price can be obtained by multiplying the value of saleprice column by 0.9, and the profit can be obtained by subtracting the value of purchase_price column from this value.

SELECT product_name, product_type, sale_price * 0.9 - purchase_price AS profit
FROM product
WHERE sale_price * 0.9 > 100 AND (product_type='Office Supplies' OR product_type='kitchenware');

Aggregate functions in SQL

The functions used for summary in SQL are called aggregate functions. The following five are the most commonly used aggregate functions:

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

-- Calculate the number of rows of all data (including NULL)
SELECT COUNT(*)
  FROM product;
-- calculation NULL Rows of data other than
SELECT COUNT(purchase_price)
  FROM product;
-- Calculate the total value of sales unit price and purchase unit price
SELECT SUM(sale_price), SUM(purchase_price) 
  FROM product;
-- Calculate the average value of sales unit price and purchase unit price
SELECT AVG(sale_price), AVG(purchase_price)
  FROM product;
-- MAX and MIN It can also be used for non numeric data
SELECT MAX(regist_date), MIN(regist_date)
  FROM product;
-- Calculate the number of data rows after removing duplicate data
SELECT COUNT(DISTINCT product_type)
 FROM product;

Some common rules

The result of the COUNT function varies according to the parameters. COUNT(*) will get the number of data rows containing NULL, while COUNT(< column name >) will get the number of data rows other than NULL.
The aggregate function excludes NULL. The exception to COUNT(*) does not exclude NULL.
The MAX/MIN function is applicable to almost all data type columns. The SUM/AVG function is only applicable to numeric type columns.

Group summary and sorting

GROUP BY: used for group summary

For example, we know the sales of all stores in a shopping mall (calculated by SUM). If we want to know the individual sales of each store, we need to use GROUP BY, as follows

SELECT Store_Name, SUM(Sales) 
FROM Store_Information 
GROUP BY Store_Name;

The returned result is the total sales of each store.

Writing position of GROUP BY:

SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

The first three items are used to filter data. GROUP BY processes the filtered data, for example:

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

The above code is used to count the number of clothes with different purchase prices. I understand that the statistical result will contain NULL

When using aggregate functions and GROUP BY clauses, common errors are:

Columns other than the aggregate key are written in the SELECT clause of the aggregate function

When using aggregate functions such as COUNT, 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).
Use the alias of the column in the GROUP BY clause

Aliases can be specified in the SELECT clause through AS, but they cannot be used in GROUP BY. In DBMS, the SELECT clause is executed after the GROUP BY clause.
Use aggregate functions in WHERE

The reason is that the aggregate function is used on the premise that the result set has been determined and WHERE is still in the process of determining the result set, so contradictions will cause errors. If you want to specify conditions, you can use the aggregate function in the SELECT, HAVING and ORDER BY clauses.

HAVING: filter groups

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

The following is an example of a typical error:

-- Wrong form (because product_name Not included in GROUP BY Aggregate key (in)
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
HAVING product_name = 'ball pen';

ORDER BY: sort the results in order

-- In descending order DESC
SELECT product_id, product_name, sale_price, purchase_price
FROM product
ORDER BY sale_price DESC;
-- Ascending sort is the default
SELECT product_id, product_name, sale_price, purchase_price
FROM product
ORDER BY sale_price, product_id;

Note that when the column name used for sorting contains NULL, NULL will be summarized at the beginning or end.

When too many SQL commands are used, the order of command execution becomes particularly important. When using the HAVING clause, the order of SELECT statements is:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

The sequence is logical and not difficult to understand

Exercises

1, Point out grammatical errors

SELECT product_id, SUM(product_name)
FROM product 
GROUP BY product_type 
WHERE regist_date > '2009-09-01';

Columns other than aggregate keys are written in the SELECT clause of the aggregate function, and product_type is not a column in the SELECT clause;

In addition, the order of GROUP BY and WHERE is wrong.

2, 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.

product_type | sum  | sum 
-------------+------+------
clothes         | 5000 | 3300
 Office Supplies      |  600 | 320

SELECT product_type, SUM(sale_price), SUM(purchace_price)
FROM product 
WHERE sale_price > purchace_price*1.5;

3, 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.

Answer: order by register_date;

Posted by schris403 on Thu, 18 Nov 2021 08:01:38 -0800

Programmer Group

Basic query and sorting

SELECT & WHERE

Some SQL rules

Exercises

Aggregate functions in SQL

Group summary and sorting

GROUP BY: used for group summary

HAVING: filter groups

ORDER BY: sort the results in order

Exercises

Hot Keywords