Sub-Selects & Table Joins. CIS 310. Aggregate functions in SQL are operations that summarize a number of rows from a table, view, or join operation into one value. Examples include: sum, avg for average, count, min for minimum, and max for maximum, and stddev for standard deviation.
Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
Aggregate functions in SQL are operations that summarize a number of rows from a table, view, or join operation into one value. Examples include: sum, avg for average, count, min for minimum, and max for maximum, and stddev for standard deviation.
Select min(Total_Miles) from FREQUENT_FLYER
Obtains the lowest Total_Miles amount for all FREQUENT_FLYERS
Select count(Total_Miles) from FREQUENT_FLYER
Obtains a count of the number of rows with (non NULL) Total_Miles values in the FREQUENT FLYER table
Select avg(Total_Miles) from FREQUENT_FLYER
Obtains the average of Total_Miles across all rows in the FREQUENT FLYER table (NULLs not included)AGGREGATE Function Concepts
The group by clause allows you to use group functions on a group of rows that have the same value for a specific attribute.
Typical examples include: grouping data is by department, or gender, or date.
Using the group by will give you a result (summary) for each distinct value of the group by attribute. If you did a group by on gender you would get a summary on male and a summary on female. If you did a group by on date you would get a summary for each date value that is in the database.
SELECT State, Avg(Total_Miles), COUNT(Total_Miles)
GROUP BY State;
The result of this statement will be the average of Total_Miles and number of Frequent Flyers from each State.
If you include an non aggregate attribute (like State) in the column list and do not include it in a group by clause you will get an error because you would be combining aggregate and detailed data.Group-By Clause
You can select from the output (result rows) of a Group By operation the same way you can select rows in a Select operation. In the Select operation you used the Where clause. To apply selection criteria to group results you use a Having Clause.
SELECT State, AVG(Total_Miles), COUNT(Total_Miles)
GROUP BY State
HAVING COUNT(Total_Miles) >= 3
This would display summary results for all States where there are 3 or more FREQUENT FLYERs in that state.Group By with Selection
For the example of the previous slide, The operation the same way you can select rows in a Select operation. In the Select operation you used the first part of the operation would be to get the average Cur_Year_Miles across all FREQUENT FLYERSs. We could do that in one SQL query and then use the result to construct another SQL query, but we can also write the query as one query where the average calculation is obtained in the sub query. Note that the sub query is in parenthesis.
Select F_Name, L_Name, Cur_Year_Milesfrom FREQUENT_FLYER
where Cur_Year_Miles> (Select avg(Cur_Year_Miles)
from FREQUENT_FLYER);Sub Query Example
Lets assume that we want to look at all the operation the same way you can select rows in a Select operation. In the Select operation you used the Frequent Flyers from the State of AZ whose Cur_Year_Miles are more than the average Cur_Year_Milesof Frequent Flyers from the state of AZ.
Note that parallel WHERE clause conditions are needed in the outer and sub-selects. We want to compare the Cur_Year_Miles of Frequent Flyers from AZ with the average Cur_Year_Miles of Frequent Flyers from that state.
Select F_Name, L_Name, Cur_Year_Miles from FREQUENT_FLYER
where State = 'AZ' and Cur_Year_Miles > (Select avg(Cur_Year_Miles)
from FREQUENT_FLYER WHERE State = 'AZ');
The row selection of the WHERE clause is performed first, prior to performing any aggregation or computation, thus only Frequent Flyers from AZ are included in the computed average.
This also applies to queries with GROUP BY and HAVING clauses. WHERE clause selection is preformed first, then the aggregation and grouping operations are performed on the remaining rows.Sub Query 2 – with WHERE Clause
As noted on the previous slide, sub queries can be used to obtain a list of values that another query can use to control its results. For example lets assume that we are interested in obtaining the flights that originate in airports where the elevation is greater than 1000 feet. This list can be solved using a join, but it can also be solved with a sub query. First, we get a list of the codes for airports over 2000 feet in the sub query, then we apply that list in the outer query to retrieve data from all flights whose origin is in the list.
Select Flight_no, Orig, Dest from FLIGHT where Origin (Select Apt_Code from AIRPORT
where Elevation > 2000);
FLIGHT_NO ORIG DEST
---------------------- ---- ----
101 FLG PHX
210 FLG LAX
606 ABQ PHXSub Query Example 2
SELECT col_1, col_2, … FROM table1, table2, …
WHERE table1_key = matching_table2_key
[AND … (more join conditions or selection conditions as needed)]
select FLIGHT.Flight_No, Orig, Dest, Flight_Date, Itinerary_no, Seat
from FLIGHT, TICKET
where FLIGHT.Flight_no= TICKET.Flight_No;
Flight_No is in both tables so we must specify a tablename
Table names separated by commas
Must set primary key of FLIGHT table = foreign key of TICKET
select F.Flight_No, Orig, Dest, Flight_Date, Itinerary_no, Seat
from FLIGHT F, TICKET T
where F.Flight_no = T.Flight_No;
Since cust_no in both tables we must indicate which table to use
c and s are table aliases
Table aliases used here
Flight(Flight_No, Orig, Dest, . . .)
PASSENGER(Itinerary_No, Pass_Name,. . .)
TICKET(Itinerary_No,Flight_CNo, Flight_Date, Seat, Fare_Charged)
SELECT * FROM FLIGHT F, TICKET T, PASSENGER P
WHERE F.Flight_No= T.Flight_No
AND T.Itinerary_no= P.Itinerary_No;