1 / 16

Sub-Selects & Table Joins - PowerPoint PPT Presentation

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.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

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.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

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.

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

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

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

The Sub Query Concept operation the same way you can select rows in a Select operation. In the Select operation you used the

• 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.

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

Sub Query Example operation the same way you can select rows in a Select operation. In the Select operation you used the

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

Example Sub-Query 2 with Results operation the same way you can select rows in a Select operation. In the Select operation you used the

Types of Sub Queries operation the same way you can select rows in a Select operation. In the Select operation you used the

• 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.

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

• 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

Example SQL SELECT Using an Equi-Join)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

Use of Table Aliases Equi-Join)

• 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

Joining Three or More Tables Equi-Join)

• 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;

Joining Three or More Tables (Continued) Equi-Join)

• 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';