Database Management (DBAS32100) - Case, Grouping, & Rollups PDF

Database Management (DBAS32100) sheridancollege.ca  CASE, CUBE, AND ROLLUP Instructor: Sivasakthi J Email: [email protected] Course: DBAS32100 sheridancollege.ca CONDITIONAL EXPRESSION - CA...

Database Management (DBAS32100) sheridancollege.ca  CASE, CUBE, AND ROLLUP Instructor: Sivasakthi J Email: [email protected] Course: DBAS32100 sheridancollege.ca CONDITIONAL EXPRESSION - CASE Used to change the projected (returned) values based on a condition sheridancollege.ca  PRACTICE CASE (USE HR DATABASE ) A 10 Min Return Employee ID, First name, Last name and salary category. Use HR Schema Salary category is defined as following:  High if salary > 10,000  Medium if salary > 6,000 and salary 6000 THEN 'Medium' ELSE 'Low' END AS salary_category FROM hr.employees; sheridancollege.ca SQL RECAP sheridancollege.ca  AGGREGATE FUNCTIONS There are many aggregate function, but the basic ones are:  COUNT()  SUM()  AVG()  MIN()  MAX() Aggregate functions work across multiple rows in a table and they return one single value. Example: SELECT COUNT(employee_id), SUM(salary), ROUND(AVG(salary), 2) FROM hr.employees; sheridancollege.ca  GROUP BY  GROUP BY clause makes calculations for each unique combination of column values and returns a value for each combination.  That's the reason why you must include in the GROUP BY clause all the selected columns, except for calculated values of the aggregate functions: Example: What is the number of employees in each department: SELECT department_name, COUNT(*) AS "No. of Employees“ FROM hr.employees e LEFT OUTER JOIN hr.departments d ON e.department_id= d.department_id GROUP BY department_name; sheridancollege.ca  HAVING CLAUSE  HAVING clause restricts the number of rows returned by the select statement, based on a condition  It works similarly to the WHERE clause  HAVING is applied after the grouping and can be used to filter the values based on the calculations done by aggregate functions  WHERE cannot be applied on values returned by an aggregate function. Example: SELECT department_name, COUNT(*) AS "No. of Employees“ FROM hr.employees e LEFT OUTER JOIN hr.departments d ON e.department_id = d.department_id GROUP BY department_name HAVING COUNT(*) > 5; sheridancollege.ca GROUPING OPERATIONS sheridancollege.ca  ADVANCED GROUPING  GROUP BY returns values for each unique combination of columns in the select clause  In many cases we would like to have not only aggregated values for each combination (subgroups) but also for any other combination of column values Example you may want to see  The total number of locations in the hr database, which can be done with a simple grouping  The number of locations for each country  The number of countries for each region  Oracle contains two extensions to GROUP BY which can be used in such case: ROLLUP and CUBE sheridancollege.ca  ROLLUP  The ROLLUP operation is usually used to produce subtotal values when we have  groups within groups.  The example shows the total number of locations for each country and region  The missing values are NULL  The COUNT(*) shows the subtotal of the column with NULL. SELECT region_name, country_name, COUNT(*) FROM hr.regions r INNER JOIN hr.countries c ON r.region_id = c.region_id GROUP BY ROLLUP (region_name, country_name) ORDER BY 1,2; sheridancollege.ca SOLUTION sheridancollege.ca https://docs.oracle.com/cd/F49540_01/DOC/server.815/a68003/rollup_c.htm SELECT * FROM S3.ITEM; sheridancollege.ca  GROUPING In the following slides you will be working with Item table. Below is how the table looks Subclasses of the Items are Item Class. 5 classes composed of items are of Class A has other items. For Item class A and subclass A1 example Item 2 Color 3 items are and A2. is Composed of of class B Class B Cas Items 1 and 3 Subclass B1 and B2 sheridancollege.ca Note. Item table is located in s3 Schema so you need to prefix the table name with s3  AGGREGATE FUNCTIONS This Statement will make the calculations based on all the rows in Items table Aggregate functions performs a calculation on as election set. Aggregate function returns only one value and it can be used to answer questions like How Many students have GPA above 4, or what the maximum grade in the class. sheridancollege.ca  GROUPING Group by function makes calculations for every unique value of a column or collection of columns and it can answer questions like what is the average student GPA for every program or what is the number of students in every class This will make the calculations for every Class sheridancollege.ca  GROUPING BY MULTIPLE COLUMNS It will create groups based on the possible combination of values between the columns Here you get 4 results because category A has two colours and category b has two colors sheridancollege.ca  RESTRICTIONS WITH GROUPING Where select the rows that will participate in the calculation Where select the rows that will participate in the calculation Having makes selection on the result of the calculation Having makes selection on the result of the calculation sheridancollege.ca  CUBE o CUBE operation is used to produce cross-tabulation values o For each element in the select statement, cube returns all the possible combinations by substituting NULL for each element. o For example if we have an element with a value 1 and another with a value 2, the possible combinations will be: 1 and 2 1 and NULL NULL and 2 sheridancollege.ca  GROUPING FUNCTIONS Oracle also contains three grouping functions: GROUPING() GROUPING_ID() GROUPING SETS They are user to:  Facilitate different combinations of groupings  Easily identify groups and their subgroups sheridancollege.ca  ADVANCED GROUPING - CUBE Cube will calculate the aggregate function for 1- the whole selection Group by the combination of class 2- every column in the group by and color clause Group by class 3- all the possible combinations Group by color between the columns in the group Aggregate for the entire selection by clause Note: your results will not be ordered this way if you run this statement. You will see how to order the result in a later slide sheridancollege.ca  MORE EXAMPLE ON CUBES You can make cubes more Group by the possible combination of class , color, and manufacturing interesting by adding more year groups Group by the possible combination of class and color Group by the possible combination of class and manufacturing year Group by class Group by the possible combination of color and manufacturing year Group by color Group by Manufacturing Year sheridancollege.ca Aggregate for the entire selection  SORTING RESULTS GROUPING_ID function will return an integer for every group set and you can use this integer to sort the result so every group set will be together. In this example GROUPING_ID returns 0 When I_Class and I_Color are not null (Calculation on the combination of the two columns is performed) 1 When I_Class is not null and I_Color is null (Calculation on the first column is performed) 2 When I_Class is null and I_Color is not null (Calculation on the second column is performed) 3 When I_Class and I_Color are nulls (Calculation on the whole result set is performed) sheridancollege.ca  ACTIVITY A Create a SQL using set operations (Union, Intersects, and Except) that will return the exact same result as the query in slide 17 sheridancollege.ca  CUBES You can use conditions to represent the aggregation groups in one column GROUPING function will return 1 if the value in the column is not null and 0 if the value is null GROUPING function indicates whether the column in a GROUP BY list is aggregated or not. This function can be used only on a single column. Grouping returns 1 if the data is aggregated across the specified column or 0 for not aggregated in the result set. sheridancollege.ca  ROLLUP ROLLUP returns aggregates for groups resulting from the possible combinations of the two columns then it will aggregate the result based on grouping by the first column. it is a good fit for columns that implies Counts for every subclass of A hierarchy where a group is part of another group. For example get Rollup the counts to A Counts for every subclass of B average sale by day, month , and year Rollup the counts to B Rollup the counts to the entire ROLLUP produce subtotal values when selection we have groups within groups. sheridancollege.ca  ACTIVITY (USE HR DATASET) A Activity 5 min What is the difference between the result of the two select statements. Which one is more appropriate? sheridancollege.ca  GROUP SETS Group sets will group the column based on each set in the group set. A set could be a column or more sheridancollege.ca  ACTIVITY (USE HR DATASET) A Create a report that shows Job title, department name and average salary of employees for each department, Job, specific Job in a department and overall (for the whole company). Create a report that shows the average salary of employees by month and year of hire date sheridancollege.ca