Oracle-27-set Operations (intersection, set, difference) & subquery of a single row sub-query

First, set operation

1.UNION : the set operation.

Syntax structure:

sql>Select Table 1, column 1, table 1, column 2 from table 1 Union select Table 2 column 1, table 2, column 2 from table 2;

Table 1, column 1 and table 1, column 2 is the two columns from Table 1, table 2, column 1 and table 2 column 2 is from table 2 two columns, it should be noted that if the Union is preceded by n columns, Then there must be n columns, that is, the number of columns before and after the Union must be the same. and the column name of the query result is named after the name of the n column in the Union (example 1).

2.INTERSECT : Intersection operation.

Syntax structure:

sql>Select empno, Enmaefrom empintersect select empno, ename from emp where sal > 1500;

Intersect front is the EMP table full check, followed by the EMP table has a choice of query, then the intersection is to filter out the EMP table to meet the sal>1500 conditions of employee information.

3.MINUS : Find out the difference between two given sets.

Syntax structure:

Sql>selecte mpno, ename from emp minus select Empno,ename from emp where sal > 1500;

The same part is removed.

Example 1: Practice set Operation Union.

Solution: Current Student table data:

Current Course table data:

Take Sno, Sname,cno, CNAME, and set:

If the number of columns is different before and after the union, an error occurs:

Example 2: Practice intersection operation intersect.

Solution: First look at the student table and the course table:

Take the two intersections:

Because there is no intersection, "unselected rows" is displayed.

Example 3: Continuation of the intersection operation intersect.

Solution:

Intersect front is the student table full check, the back is found sno> ' s005 ' data, then before and after the intersection, the results as shown.

If this is the intersection for the student table and the SC table:

where SC table data:

The intersection operation did not result, because the intersect Front column name and the following column name to be the same , in this case, in front of Sno and Sname, behind Sno and CNO, not the same, So the intersection has no result.

Example 4: Practice differential set operation minus.

Solution: For student table, shaving sno< ' s005 ' section:

Second, sub-query

To the current location The description query statement contains only one SELECT statement.

1. The basic types of subqueries:

(1) Single-line subquery: No results are returned to external SQL statements, or only one row is returned;

A single-line subquery can be used in the ①where clause, where the subquery may be placed in the WHERE clause of another query;(Summary:a single-line subquery is available after thewhere keyword)

For example: Query employee information that is less than the average salary of the employee.

Sql>select empno, ename from emp where sal <(select AVG (SAL) from EMP);

A single-line subquery can be used in the ②having clause. (Summary: A single subquery is available after having the keyword)

For example: Find out which positions have a higher average salary than the average employee.

Sql>select job, AVG (SAL) from EMP Group by job

2 have avg (sal) >(select AVG (SAL) from EMP);

A single-line subquery can be used in the ③FROM clause, which is called an inline view. (Summary: One- line subquery available after the FROM keyword)

For example: query which employees pay higher than the average salary of the position, that is, a staff is IT industry, its salary is 15000 more than the average salary of the IT industry 8000, then this a staff to find out, if B employees wages 1200 less than their industry average wage 1500, Then the query results do not include B employees ...

Sql>select ename, Sal, B.C from emp A,(select Job,avg (SAL) c from EMP Group by job) B where a.sal > B.C and A.job =b.job;

Where A,b,c is an alias, (select Job, avg (SAL) c from EMP Group by Job) is a subquery that finds out the average salary for each industry, and where to judge A.sal is the employee's salary, B.C is the industry average wage.

(2) Multiline subquery: returns one or more rows to an external SQL statement;

( 3 ) Multi-column sub-query;

( 4 ) Correlated sub-queries;

( 5 ) nested subqueries.

Example 5: Practice subquery (corresponds to: two-(1)-①)

Solution: Based on the existing student table, the student's number and name are less than the average age of the entire student.

, where there is a subquery that queries the average age in the current student table, and then if the average age of sage<, matches the criteria, is displayed in the query results.

The subquery behind the where is the query for use by the external select.

"Note" The following syntax error:

because the aggregate function is to be determined by the Select The statement shows the result, so direct use is wrong.

Example 6: Practice subquery (corresponds to: two-(1)-②)

Solution: The average score of certain courses is queried, which is higher than the average of all students ' grades.

The following instructions are available for the commands in:

(1) GroupBy CNO is the same set of data in the table according to CNO, that is, the same course is a group;

(2) The HAVING clause uses a subquery, i.e. (SELECTAVG (score) from SC) to find the mean value of all data in the SC table score, that is, the sharing of all students ' scores;

(3) Select query to the average of each course AVG (score), go to have clause to determine whether to meet the conditions, if the conditions are met in the query results display.

the "Remember" subquery cannot contain a sort, that is, cannot contain Group by clause.

Oracle-27-set Operations (intersection, set, difference) & subquery of a single row sub-query