Common SQL statements

Group by clause
Group by [ALL] gyoup_by_expression [,... n] [WITH {CUBE | ROLLUP}]
CUBE: in addition to the columns specified BY the group by clause, the returned rows are grouped. the returned results are sorted by the first condition column of the group, sorted by the second condition column, and so on. the statistical row includes the statistics of various combinations of columns specified BY the group by clause.
ROLLUP: Unlike CUBE, this option is sensitive to the column sequence in the group by clause. It returns only the statistical rows of the columns specified BY the first grouping condition, changing the column order will change the returned results.

COMPUTE clause
The COMPUTE clause generates a summary data row at the end of the query result,
The syntax is as follows:
COMPUTE {AVG | COUNT | MAX | MIN | SUM} (expression)} [,... n] [BY expression [,... n]

AVG | COUNT | MAX | MIN | SUM
These functions ignore NULL values and the DISTINCT option cannot be used here
Expression: Specifies the name of the column to be counted. This column must be included in the SELECT list and cannot use aliases.
The Data Types of TEXT, NTEXT, and IMAGE cannot be used in the COMPUTE clause.
BY expression: generate the rows of classification statistics in the query results. If this option is used, the order by clause must be used. expression is the subset or complete set of order_by_expression in the corresponding order by clause.
Example:
Select employee name, department, salary from employee data table order by Department compute sum (salary) by department
Note: When adding the by keyword and Its grouping field, this grouping field must be used as the sorting condition at the same time; otherwise, an error occurs in the query.

UNION clause
Merging columns in two tables is not feasible. to merge query results using the UNION operator, two basic principles must be followed: the written and column order of the columns must be consistent in all queries; data type must be compatible
Note:
1. The field names in the result set obtained using the UNION clause are the same as those in the select statement result set before the UNION operator.
2. By default, the UNION operator will delete duplicate records from the final result set. If you want the final result set to retain ALL records, you must use the ALL keyword.
3. When the UNION operator is used, a separate select statement cannot contain its own order by or compute clause.
4. Only one order by or compute clause can be used after the last select statement. This clause applies to the final combination result set.
5. If you want to group query results and filter several having clauses After grouping, you must specify the group by and having clauses in a separate select statement.

Cross join
Format: Data Table 1 cross jion data table 2
Cartesian product of Table 1 and Table 2

Multi-Value Comparison ALL
Multi-Value Comparison: multiple rows and one column
The comparison between the parent query and multi-value subquery requires ALL.
The scalar value s is greater than that in the return set R of the subquery, and s> all r is True.
ALL indicates ALL
> ALL, <ALL, <= ALL,> = ALL, <> ALL
<> ALL is equivalent to not in.
Example: Find the youngest student
Select * from student where sage <all (slect sage from student)

Multi-Value Comparison Some/Any
Some/Any is required for compiling the parent query between multi-value subqueries.
The scalar value s is larger than one in the return set R of the subquery.
S> Some R is True or
S> Any R is True
> Some, <some, <= some,> = some, <> some
= Some is equivalent to in, <> some is not equivalent to not in
For example, find out the students not of the minimum age
Select * from student where sage> some (select dage fromstudent)

Subquery ---- Exists judgment
Exists + subquery is used to determine whether the subquery returns tuples.
For example, list the student ID and name of the student who has taken the CO1 course.
Select sno, sname from student where exists (select * from SC where SC. sno = student. sno and cno = 'co1 ')