SQL subquery instance

SQL subquery instance

SQL subquery instance introduction:

A subquery is a query within a query. The results of the subquery are used by the DBMS to determine the results of the advanced query that contains the subquery. In the simplest form of a subquery, The subquery is displayed in the WHERE or HAVING sub-bureau of another SQL statement.

List the sales points whose sales targets exceed the quota of each salesperson.

SELECT CITY

FROM OFFICES

Where target> (select sum (QUOTA)

FROM SALESREPS

WHERE REP_OFFICES = OFFICE)

SQL subqueries are generally used as part of the WHERE clause or HAVING clause. In the WHERE clause, they help select the records that are presented in the query results. In the HAVING clause, the moderators select the record groups displayed in the query results.

The difference between the subquery and the actual SELECT statement:

In common usage, a data field must be generated for a subquery as its query result. This means that a subquery has almost always a selection item in its SELECT clause.

The order by clause cannot be specified in the subquery. The subquery results are used internally in the query, which is invisible to users. Therefore, sorting the subquery results is meaningless.

The field name displayed in the subquery may reference the fields of the table in the primary query.

In most implementations, word queries cannot be the UNION of several different SELECT statements, and only one SELECT statement is allowed.

Subquery in WHERE

Subqueries are most commonly used in the WHERE clause of SQL statements.

List the sales staff whose quota is less than 10% of the company's sales target.

SELECT NAME

FROM SALESREPS

Where quota <(. 1 * (select sum (TARGET) from offices)

(The value generated by the subquery to test the search condition .)

List the sales points in which the company's sales targets exceed the total quota of each salesperson.

SELECT CITY

FROM OFFICES

Where target> (select sum (QUOTA)

FROM SALESREPS

WHERE REP_OFFICE = OFFICE)

(Execution Description: The Master query retrieves data from the OFFICES table, and the WHERE clause selects the sales point included in the query results. SQL uses the test conditions in the WHERE clause to scan records in the OFFICES table one by one. The WHERE clause compares the value of the TARGET field in the current record with the value generated by the subquery. To test the TAEGET value, run the SQL subquery to find the total quota of sales personnel in the current sales point. A subquery generates a number. The WHERE clause compares the number with the TARGET value to select or exclude the current sales point based on comparison .)

(When the DBMS checks the search conditions in the subquery, the Field Values in the external reference are extracted from the current record detected by the primary query .)

Subquery search criteria

* Subquery comparison test = <> <=> = (in this test, subquery must generate a value of the appropriate data type, that is, it must generate a query result record, which contains only one field. If a query generates multiple records or fields for a long time, the SQL statement reports an error. If a subquery does not generate a record or a NULL value, NULL is returned for the comparison test ).

* Sub-query group member test (IN)

* EXISTS)

* Limited qualitative comparison test ANY ALL

Subquery and link

Many subqueries can also be written as multi-table queries or connections.

Name of the salesperson who works at a sales point in the western region (table 1) (table 2 ).

Select name, AGE

FROM SALESREPS

WHERE REP_OFFICE IN (SELECT OFFICE

FROM OFFICES

Where region = 'west ')

Select name, AGE

From salesreps, OFFICES

WHERE REP_OFFICE = OFFICE

And region = 'west'

Next database update

Lists the names and ages of sales personnel whose sales volume exceeds the average quota.

Select name, AGE

FROM SALESREPS

Where quota> (select age (QUOTA)

From salesreps)

(In this example, an internal query is a summary query, but an external query is not. Therefore, you cannot combine the two queries into a connection .) Subquery in HAVING Query

When a subquery is displayed in a HAVING clause, it is used as part of the selection of record groups executed by the HAVING clause.

List the sales personnel who have obtained an average order size for products produced by ACI that exceeds the total average order size.

Select name, AVG (AMOUNT)

From salesreps, ORDERS

WHERE EMPL_NUM = REP

And mfr = 'aci'

GROUP BY NAME

Having avg (AMOUNT)> (select avg (AMOUNT)

From orders)

List the products produced for ACI. The average order size obtained is at least as large as the total average order size.

Select name, AVG (AMOUNT)

From salesreps, ORDERS

WHERE EMPL_NUM = REP

And mfr = 'aci'

Group by name, EMPL_NUM

Having avg (AMOUNT)> = (select avg (AMOUNT)

FROM ORDERS

Where rep = EMPL_NUM)