Sub-Selects & Table Joins

1. Sub-Selects & Table Joins CIS 310

2. The ROUND and TRUNC Single Row Functions – For Numeric Data • The form of the command is simply: ROUND(variable, #_of_digits) or TRUNC(variable, #_of_digits) • Where ROUND rounds off to the nearest value and truncate gives the nearest value that the variable exceeds. • Assume Unit_Weight has a value of 26.65 • ROUND(Unit_Weight,1) -> 26.7 • TRUNC(Unit_Weight,1) -> 26.6 , • ROUND(Unit_Weight) -> 27 nearest whole # assumed here • ROUND(Unid_Weight,-1) -> 30 can round to 10s, 100s, etc.

3. The TRUNC Function with Dates • For Dates, the TRUNC function has the following form: TRUNC(variable, ‘date_format’) • Where the date_format characters indicate the level to truncate to, e.g • Assume Ord_Date has a value of ‘06-Oct-14’ • TRUNC(Ord_Date, ‘MON’) -> ‘01-Oct-14’ • TRUNC(Ord_Date, ‘YYYY’) -> ‘01-Jan-14’

4. 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(Standard_price) from PRODUCT Obtains the lowest price for all products Select count(Standard_price) from PRODUCT Obtains a count of the number of rows with standard_price values (non NULL) in the PRODUCT table Select avg(unit_price) from PRODUCT Obtains the average of standard_price across all rows in the PRODUCT table (NULLs not included) AGGREGATE Function Concepts

5. 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 Prod_Category, Avg(standard_price), count(standard_price) FROM PRODUCT GROUP BY Prod_Category; The result of this statement will be the average Standard_Price and number of products in each product category. If you include an non aggregate attribute (like Prod_Category) 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

6. 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 Prod_Category, Avg(Standard_Price), count(Standard_Price) FROM PRODUCT Group By Prod_Category HAVING count(Standard_Price) > 2 This would display summary results for all product categories where there are more than two products in the category. Group By with Selection

7. 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 Products whose unit_weight is more than the average unit_weight. First I need to find the average of unit_weight, then compare each product’s unit_weight to the average.

8. Lets assume that we want to look at all the PRODUCTs whose Unit_Weight is more than the average Unit_Weight of all PRODUCTs. The first part of the operation would be to get the average Unit_Weight of PRODUCTs. We could do that in one SQL query and then use the result to construct another SQL query. 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 Prod_Code, Prod_Descrip, Unit_Weight from PRODUCT where Unit_Weight > (Select avg(unit_weight) from PRODUCT); Sub Query

9. Sub Query Example

10. Lets assume that we want to look at all the PRODUCTs whose Unit_Weight is more than the average Unit_Weight of PRODUCTs in the ‘STO’ product category. Note that parallel WHERE clause conditions are needed in the outer and sub-selects. We want to compare the weight of each PRODUCT in the STO category with the average weight of PRODUCTs in that category. Select Prod_Code, Prod_Descrip, Unit_Weight from PRODUCT WHERE Prod_Category = ‘STO’ AND Unit_Weight > (Select avg(unit_weight) from PRODUCT WHERE Prod_Category = ‘STO’); The row selection of the WHERE clause is performed first, prior to performing any aggregation or computation, thus only PRODUCTs in the STO category 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 – with WHERE Clause

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

12. 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 Names of all CUSTOMERs who have placed an order with Apex with a total bill greater than $5000. First, we get a list of the customer numbers associated with SALEs whose sale_tot_bill is greater than $5,000 in the sub query (on the SALE table, then we apply that list in the outer query to retrieve the names of the CUSTOMERs. EXAMPLE: select f_name, l_name from CUSTOMER where cust_no IN (select cust_no FROM SALE WHERE sale_tot_bill > 5000); RESULT: F_NAME L_NAME ---------- ---------- Jerry Jones Chuck Lewis Mary Niles Sub Query Example 2

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

14. Example SQL SELECT Using an Equi-Join • For each Sale, APEX would like to see the name of the Customer we sold to, the Order number and date and the total billed. • To get this we must join the CUSTOMER and SALE tables select f_name, l_name, ord_no, ord_date, tot_bill from customer, sale where customer.cust_no = sale.cust_no;; Table names separated by commas Must set primary key of customer table = foreign key of sale

15. 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 c.cust_no, f_name, l_name, ord_no, ord_date, tot_bill from customer c, sale s where c.cust_no = s.cust_no; Since cust_no in both tables we must indicate which table to use c and s are table aliases Table aliases used here

16. 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: SALE(Ord_No, Ord_Date, . . .) PRODUCT(Prod_Code, Prod_Descrip, Prod_Category. . .) ITEM_SOLD(Ord_No, Prod_Code, Qty_Ord, . . .) SELECT * FROM SALE S, ITEM_SOLD I, PRODUCT P WHERE S.Ord_No= I.Ord_No AND I.Prod_Code= P.Prod_Code;

17. 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 students who have a MEMBERSHIP whose join_date is after 16-JUN-14. • 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 Prod_Code, Prod_Descripand Prod_Categoryof all PRODUCTs purchased on September 14th,2014 . • In this example the data I want to retrieve is in the PRODUCT table. However, the selection criteria I am using is in the SALE table, so it must be included. Since there is no direct linkage between these two tables, I must also include the ISEM table to provide a path to properly link products and sales together. • The SELECT Statement would be: • SELECT P.Prod_Code, Prod_Descrip, Prod_CategoryFROM PRODUCT P, ITEM_SOLD I, SALE S • WHERE P.Prod_Code= I.PROD_CODE • AND I.Ord_No= S.Ord_No • AND Ord_Date= ’14-SEP-2014’;