Unformatted text preview:

SQL - Part 2SELECT Statement OverviewExample RelationsExample Relation InstancesBut First Join RevisitedMore Join PracticeOrdering Result DataAggregate Queries and FunctionsAggregate FunctionsAggregate Function ExampleGROUP BY ClauseGROUP BY ExampleGROUP BY Clause RulesHAVING ClauseHAVING ExampleGROUP BY/HAVING ExampleGROUP BY/HAVING Example (2)GROUP BY ExamplesMulti-Attribute ExampleGROUP BY Practice QuestionsConceptual Evaluation ProcessConceptual Evaluation LessonsConceptual Evaluation ProblemConceptual Evaluation Problem (cont)Query Formulation ProcessCritical QuestionsEfficiency ConsiderationsSQL - Part 2Much of the material presented in these slides was developed by Dr. Ramon Lawrence at the University of IowaSELECT Statement Overview SELECT <list of column expressions> FROM <list of tables and join operations> WHERE <list of logical expressions for rows> GROUP BY <list of grouping columns> HAVING <list of logical expressions for groups> ORDER BY <list of sorting specifications>Expression: combination of columns, constants, operators, and functionsExample RelationsRelations:Emp (eno, ename, bdate, title, salary, supereno, dno)Proj (pno, pname, budget, dno)Dept (dno, dname, mgreno)WorksOn (eno, pno, resp, hours)Foreign keys:Emp: Emp.supereno to Emp.eno, Emp.dno to Dept.dnoProj: Proj.dno to Dept.dnoDept: Dept.mgreno to Emp.enoWorksOn: WorksOn.eno to Emp.eno, WorksOn.pno to Proj.pnoExample Relation InstancesBut First Join RevisitedSELECT *FROM emp, deptWHERE emp.dno=dept.dno;• Natural Join Style:How many columns in the output table?SELECT * FROM emp NATURAL JOIN dept;• Cross Product Style:How many columns in the output table?• Alternative SQL92 styles:How many columns in each output table?SELECT * FROM emp INNER JOIN dept USING (dno);SELECT *FROM emp INNER JOIN dept ON emp.dno = dept.dno;More Join PracticeRelational database schema:Return a list of all department names, the names of the projects of that department, and the name of the manager of each department.Return the names of all projects and the names of the employees who have worked on each project.Return the names of all employees who are supervisors.emp (eno, ename, bdate, title, salary, supereno, dno)proj (pno, pname, budget, dno)dept (dno, dname, mgreno)workson (eno, pno, resp, hours)Ordering Result DataThe query result returned is not ordered on any attribute by default. We can order the data using the ORDER BY clause:SELECT ename, salary, bdateFROM empWHERE salary > 30000ORDER BY salary DESC, ename ASC;'ASC' sorts the data in ascending order, and 'DESC' sorts it in descending order. The default is 'ASC'.The order of sorted attributes is significant. The first attribute specified is sorted on first, then the second attribute is used to break any ties, etc.NULL is normally treated as less than all non-null values.Aggregate Queries and FunctionsSeveral queries cannot be answered using the simple form of the SELECT statement. These queries require a summary calculation to be performed. Examples:What is the maximum employee salary?What is the total number of hours worked on a project?How many employees are there in department 'D1'?To answer these queries requires the use of aggregate functions. These functions operate on a single column of a table and return a single value.Aggregate FunctionsThe five basic aggregate functions are:COUNT - returns the # of values in a columnSUM - returns the sum of the values in a columnAVG - returns the average of the values in a columnMIN - returns the smallest value in a columnMAX - returns the largest value in a columnNotes:COUNT, MAX, and MIN apply to all types of fields, whereas SUM and AVG apply to only numeric fields.Except for COUNT(*) all functions ignore nulls. COUNT(*) returns the number of rows in the table.Use DISTINCT to eliminate duplicates.Aggregate Function ExampleReturn the number of employees and their average salary.SELECT COUNT(eno) AS numEmp, AVG(salary) AS avgSalaryFROM emp;Aggregate functions are often most useful when combined with the GROUP BY clause. The GROUP BY clause groups the tuples based on the values of the attributes specified.When used in combination with aggregation functions, the result is a table where each tuple consists of unique values for the group by attributes and the result of the aggregate functions applied to the tuples of that group.GROUP BY ClauseGROUP BY ExampleFor each employee title, return the number of employees with that title, and the minimum, maximum, and average salary.SELECT title, COUNT(eno) AS numEmp,MIN(salary) as minSal,MAX(salary) as maxSal, AVG(salary) AS avgSalFROM empGROUP BY title;Result:GROUP BY Clause RulesThere are a few rules for using the GROUP BY clause:1) A column name cannot appear in the SELECT part of the query unless it is part of an aggregate function or in the list of group by attributes.Note that the reverse is true: a column can be in the GROUP BY without being in the SELECT part.2) Any WHERE conditions are applied before the GROUP BY and aggregate functions are calculated.HAVING ClauseThe HAVING clause is applied AFTER the GROUP BY clause and aggregate functions are calculated.It is used to filter out entire groups that do not match certain criteria.HAVING ExampleReturn the title and number of employees of that title where the number of employees of the title is at least 2.SELECT title, COUNT(eno) AS numEmpFROM empGROUP BY titleHAVING COUNT(eno) >= 2;Result:GROUP BY/HAVING ExampleFor employees born after December 1, 1965, return the average salary by department where the average is > 40,000.SELECT dname, AVG(salary) AS avgSalFROM emp NATURAL JOIN deptWHERE emp.bdate > DATE ’1965-12-01'GROUP BY dnameHAVING AVG(salary) > 40000;Step #1: Perform Join and Filter in WHERE clauseGROUP BY/HAVING Example (2)Step #2: GROUP BY on dnameStep #3: Calculate aggregate functionsStep #4: Filter groups using HAVING clauseGROUP BY ExamplesReturn the average budget per project:SELECT AVG(budget)FROM proj;Return the average # of hours worked on each project:SELECT pno, AVG(hours)FROM worksonGROUP BY pno;Return the departments that have projects with at least 2 'EE's working on them:SELECT proj.dno, COUNT(*)FROM proj, workson, empWHERE emp.title = 'EE' and workson.eno=emp.enoand workson.pno = proj.pnoGROUP BY proj.dnoHAVING COUNT(*) >=2;Multi-Attribute ExampleReturn the employee number,


View Full Document

UNCA CSCI 343 - SQL notes

Download SQL notes
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view SQL notes and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view SQL notes 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?