Sub-Selects & Table Joins

1. Sub-Selects & Table Joins CIS 310

2. 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. Examples: 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

3. 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. Example: SELECT State, Avg(Total_Miles), COUNT(Total_Miles) FROM FREQUENT_FLYER 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

4. 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. Example: SELECT State, AVG(Total_Miles), COUNT(Total_Miles) FROM FREQUENT_FLYER 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

5. The Sub Query Concept • A sub query is a query that is nested inside the where clause of another query. • The sub query is executed first and its resulting value or values is/are treated as if they were literal constants when the outer query is executed. • A sub query can contain another sub query so that we can nest sub queries to any desired depth. • A sub query allows you to break a complex retrieval problem into parts. • “First I will retrieve a set of information X, and then I will use this result to find another set of information Y. • e.g, I want to retrieve a list of FREQUENT FLYERs whose Cur_Year_Miles value is more than the average Cur_Year_Miles across all FREQUENT FLYERs. First I need to find the average of Cur_Year_Miles, andthencompare each FREQUENT FLYER’s Cur_Year_Milesto the average.

6. For the example of the previous slide, 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

7. Sub Query Example

8. Lets assume that we want to look at all 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

9. Example Sub-Query 2 with Results

10. Types of Sub Queries • A sub query can return a single value - single row • Most where clause operators can be used with this type of sub query • In the previous example, the sub query returned a single value. • A sub query can return multiple rows • Can only be used with where clause operators designed to accept a list of items, such as the IN operator. (ANY and ALL operators which are described in your text also work here) • A sub query can return values from multiple columns • Can only be used with a special where clause option which allows combining columns and will not be described here.

11. 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. Example: Select Flight_no, Orig, Dest from FLIGHT where Origin (Select Apt_Code from AIRPORT where Elevation > 2000); Results: FLIGHT_NO ORIG DEST ---------------------- ---- ---- 101 FLG PHX 210 FLG LAX 606 ABQ PHX Sub Query Example 2

12. Fundamentals of SQL Select Statements with Table Joins (THE Equi-Join) • Joins in a relational database are based upon linking logically related data across tables • Where the logical link is recorded by repeating the PRIMARY KEY of the Parent Table as a FOREIGN KEY in the Child Table SELECT col_1, col_2, … FROM table1, table2, … WHERE table1_key = matching_table2_key [AND … (more join conditions or selection conditions as needed)] • Attributes appearing in more than 1 table must be identified by Table.column • Abbreviated aliases are often used for table names

13. Example SQL SELECT Using an Equi-Join • For each TICKET, AirWest would like to see theFlight_NO, the Orig, and the Destof the FLIGHT associated with the ticket, as well as the Flight_date, Itinerary_Noand Seat of the TICKET. • To get this we must join the Flight and TICKET tables: 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

14. Use of Table Aliases • We can follow each table name with a character (or set of chars.) which serves as an alias for that table name and can be used throughout the SQL Query. • EXAMPLE: 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

15. Joining Three or More Tables • To join 3 or more tables, we must simply include an appropriate where clause for each relationship needed to link the set of tables involved. • The where clause always equates the primary key column of the one side table with the parallel foreign key column of the many side table of the relationship. For example to join the three tables shown below: 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;

16. Joining Three or More Tables (Continued) • We must include tables in our SELECT statement (and apply the appropriate where clauses for joining them) if: • They contain columns, that we wish to display (in our column-list) or, • 1 or more columns from the table appear in selection conditions for the select statement, • E.G. I want a list of the Names of PASSENGERs who have a TICKET whose Flight_Dateis after 10-JAN-2014. • Or, tables needed in the SELECT statement have no direct relationship and I must include the table to build a path to relate the requested data. • E.G. I want a list of the Names of all PASSENGERs who have flown on FLIGHTs whose Origin is FLG. • In this example the data I want to retrieve is in the PASSENGER table. However, the selection criteria I am using is in the FLIGHT table, so it must be included. Since there is no direct linkage between these two tables, I must also include the TICKET table to provide a path to properly link PASSENGERs and FLIGHTs together. • The SELECT Statement would be: • SELECT P.Pass_Name FROM PASSENGER P, TICKET T, FLIGHT F • WHERE P.Itinerary_No = T.Itinerary_No • AND T.Flight_No= F.Flight_No • AND Orig = 'FLG';