SQL Learning Note 4--nested subqueries (bottom)

5) Sub-query in the FROM clause

A subquery expression is allowed in the FROM clause because any SQL Select-from-where expression returns a relationship.

Consider the query "find out the average wage for teachers in those departments with a average wage of more than $42000", before we use the HAVING clause to write the query.

Now we don't have a HAVING clause, as follows:

Select Dept_name,avg_salary

From (select Dept_name,avg (Salary) as Avg_salary

From instructor

Group BY Dept_name)

where avg_salary>42000;

We can also use the AS clause to give a name to this subquery result relationship and rename the attribute. As follows:

Select Dept_name, Avg_salary

From (select Dept_name,avg (Salary)

From instructor

Group BY Dept_name)

As Dept_avg (dept_name,avg_salary)/*as Common occasions * *

where avg_salary>42000;

Another example is that if we want to find out the largest system of total wages in all the departments, there is nothing in this having clause. But the from nested subqueries can be easily written:

Select Max (tot_salary)

From (select Dept_name,sum (Salary)

From instructor

Group by Dept_name) as Dept_total (dept_name,tot_salary);

Note: Not all SQL implementations support nesting subqueries in the FROM clause.

The FROM clause nesting does not allow related variables from other relationships from the FROM clause to be used.

6) with clause

The WITH clause provides a way to define a temporary relationship. This definition is valid only for queries that contain a with clause.

Consider the query "find the system with the largest budget value." As follows

With Max_budget (value) as

(select MAX (Budget)

From department)

Select Budget

From Department,max_budget

where Department.budget=max_budget.value;

The WITH clause defines the temporary relationship max_budget, which is then used.

7) Standard Quantum query

SQL allows subqueries to appear anywhere that an expression that returns a single value can occur, as long as the subquery returns only a single tuple of a single property, called a scalar subquery. It can appear in the Select, where, and having clauses.

Consider the query "lists all the departments and the number of teachers they have:

Select Dept_name, (select COUNT (*)

From instructor

where department.dept_name = Instructor.dept_name)

As Num_instructors

From department;

SQL Learning Note 4--nested subqueries (bottom)