SQL subquery Instance

Introduction to SQL subquery Examples:

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 this subquery. In the simplest form of a subquery, the subquery is rendered in a where or a having child of another SQL statement.

List the sales points whose sales targets exceed the quota synthesis of each sales person.

SELECT City

From offices

WHERE TARGET > (SELECT SUM (QUOTA)

From Salesreps

WHERE rep_offices = OFFICE)

SQL subqueries generally appear as part of a WHERE clause or a HAVING clause. In the WHERE clause, they help you select the individual records that are rendered in the query results. In the HAVING clause, they moderators select the group of records to render in the query results.

The difference between a subquery and an actual SELECT statement:

In common usage, a subquery must generate a data field as its query result. This means that a subquery almost always has a selection in its select clause.

The ORDER BY clause cannot be specified in a subquery, and the subquery results are used internally by the query and are always invisible to the user, so it makes no sense to sort them.

The field names that are rendered in the subquery may refer to the fields of the table in the main query.

In most implementations, a word query cannot be a union of several different select statements, and it allows only one select.

Subqueries in a Where

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

List 10% of sales people whose quotas are less than the sales target of the whole company.

SELECT NAME

From Salesreps

WHERE QUOTA < (. 1 * (SELECT SUM (TARGET)) from offices)

(a subquery generates a value that is used to test a search condition.) )

List the sales points whose sales targets exceed the quota totals for each salesperson.

SELECT City

From offices

WHERE TARGET > (SELECT SUM (QUOTA)

From Salesreps

WHERE Rep_office = OFFICE)

(Execution Description: The main query obtains data from the offices table, where clause chooses which point of sale to include in the query results.) SQL uses the test conditions in the WHERE clause to record each record in the offices table, where clause compares the value of the Target field in the current record with the value produced by the subquery. To test the Taeget value, SQL executes the subquery to find the sum of the quotas for the salesperson in the current point of sale. A subquery produces a number where the clause compares this number to the target value and selects or excludes the current point of sale based on the comparison. )

(When the DBMS checks for search conditions in a subquery, the field values in the external reference are extracted from the current record detected by the main query.) )

Subquery search Criteria

* subquery Comparison test = <> < <= > >= (in this type of test, the subquery must produce a value of the appropriate data type, that is, it must produce a query result record that contains only one field in the query result record.) If a query produces more than one record or more than one field, the time is meaningless, and SQL reports an error. If the subquery does not produce a record or produces a null value, the comparison test returns NULL.

* Subquery Group member test (in)

* Presence Test (EXISTS)

* Qualifying comparison test any of the

Subqueries and Links

Many queries written by subqueries can also be written as multiple-table queries or joins.

List the names of the salespeople (table 1) who work at the sales point in the western Region (table 2).

SELECT Name,age

From Salesreps

WHERE Rep_office in (SELECT OFFICE

From offices

WHERE REGION = ' Western ')

SELECT Name,age

From Salesreps,offices

WHERE Rep_office = OFFICE

and REGION = ' Western '

Next database update

List the names and ages of salespeople who sold more than the average quota.

SELECT Name,age

From Salesreps

WHERE QUOTA > (SELECT Age (QUOTA)

From Salesreps)

(In this example, the internal query is a rollup query, the external query is not, so you cannot combine two queries into one connection.) Subquery in the Having query

When a subquery is rendered in the HAVING clause, it works as part of the selection of the group of records performed by the HAVING clause.

Lists the products that are produced for ACI, and the average order size achieved exceeds the total average order size of the sales person.

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 that are produced for ACI, with an average order size of at least as large as the total average order size of the sales staff.

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)