Clause Order The last example under grouping above shows the correct order of clauses when many SQL clauses are included in the same statement. These clauses must always be specified in this order—the clauses cannot be rearranged. For example, you cannot designate an Order By clause before the Where clause. Select Display name and salary From of person with highest Where salary (using order by) Group by With Rollup Display name and salary Having of union member with Order by lowest salary. (not with Rollup) Sub-Queries Display name and salary of person with highest Sub-queries allow you to use the results of one query as a salary. criteria for another. For this unit, the sub-query must return a single value. Display name an salary of In the next unit, sub-queries will return multiple fields union member with lowest and multiple rows. salary. Example: Display the student(s) with the highest GPA Display destination and cost per day for any trip Select LastName, FirstName, GPA From tblStudent over the average cost per Where GPA= (Select Max(GPA) From tblStudent); day sorted by cost per day, highest to lowest. Display the city and avg select destination, max(avgcostperday) from (select destination, cost with the highest sum(expenses)/sum(duration) as AvgCostPerDay average cost per day. from tbltrips group by destination) as tablename; Note: can be done using Note subquery in clause. Limit 1 (end of statement) From required in subquery but Limit is MySQL, not As tablename standard Select * for all employees who work in the Coloma plant using a subquery to determine plant
Clause Order The last example under grouping above shows the correct order of clauses when many SQL clauses are included in the same statement. These clauses must always be specified in this order—the clauses cannot be rearranged. For example, you cannot designate an Order By clause before the Where clause. Select From Where Group by With Rollup Having Order by (not with Rollup) Display name and salary of person with highest salary (using order by) Display name and salary of union member with lowest salary. Sub-Queries Sub-queries allow you to use the results of one query as a criteria for another. For this unit, the sub-query must return a single value. In the next unit, sub-queries will return multiple fields and multiple rows. Example: Display the student(s) with the highest GPA Select LastName, FirstName, GPA From tblStudent Where GPA= (Select Max(GPA) From tblStudent); select destination, max(avgcostperday) from (select destination, sum(expenses)/sum(duration) as AvgCostPerDay from tbltrips group by destination) as tablename; Note subquery in From clause. As tablename required in subquery Display name and salary of person with highest salary. Display name an salary of union member with lowest salary. Display destination and cost per day for any trip over the average cost per day sorted by cost per day, highest to lowest. Display the city and avg cost with the highest average cost per day. Note: can be done using Limit 1 (end of statement) but Limit is MySQL, not standard Select * for all employees who work in the Coloma plant using a subquery to determine plant