Advanced SQL

1 .Chapter 5 6e & 8 5e: Complex SQL Prof. Steven A. Demurjian, Sr. Computer Science & Engineering Department The University of Connecticut 191 Auditorium Road, Box U-155 Storrs, CT 06269-3155 steve@engr.uconn.edu http://www.engr.uconn.edu/~steve (860) 486 - 4818 About one third of these slides are being used with the permission of Dr. Ling Lui, Associate Professor, College of Computing, Georgia Tech. About one-half of these slides have been adapted from the AWL web site for the textbook.

2 .Variety of Complex SQL Queries Nested Queries Grouping and Aggregation Order by and Having Views Sets Tuple Variable Other Complex Queries and Operations

3 .Recall Earlier Query 1 Query 1: Retrieve Name and Address of all Employees who work for the Research Department SELECT FNAME, MINIT, LNAME, ADDRESS, DNAME FROM EMPLOYEE, DEPARTMENT WHERE DNAME=Research AND DNUMBER=DNO What Action is Being Performed?

4 .Nested Queries SQL SELECT Nested Query is Specified within WHERE-clause of another Query (the Outer Query ) Query 1A: Retrieve the Name and Address of all Employees who Work for the Research Department SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME=Research ) Note: This Reformulates Earlier Query 1 (prior slide)

5 .Nested Queries SQL SELECT Nested Query is Specified within WHERE-clause of another Query (the Outer Query ) Query 1A: Retrieve the Name and Address of all Employees who Work for the Research Department SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME=Research ) Note: This Reformulates Earlier Query 1 (prior slide)

6 .Nested Queries SQL SELECT Nested Query is Specified within WHERE-clause of another Query (the Outer Query ) Query 1A: Retrieve the Name and Address of all Employees who Work for the Research Department SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME=Research ) Note: This Reformulates Earlier Query 1 (prior slide)

7 .Nested Queries SQL SELECT Nested Query is Specified within WHERE-clause of another Query (the Outer Query ) Query 1A: Retrieve the Name and Address of all Employees who Work for the Research Department SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME=Research ) Note: This Reformulates Earlier Query 1 (prior slide)

8 .Correlated Nested Queries When WHERE-clause of a Nested Query References an Attribute of a Relation Declared in the Outer Query Query 16: Retrieve the Name of each Employee who has a Dependent with the Same First Name as the Employee SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT WHERE ESSN=E.SSN AND E.FNAME=DEPENDENT_NAME) Note: This Differs Slightly from 16 in book. Inner Query Returns Relation a set of ESSNs for All Emps that have Dependents with the same FNAME

9 .How Does it Work? SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT WHERE ESSN=E.SSN AND E.FNAME=DEPENDENT_NAME ) Inner Query Returns Relation a set of ESSNs for All Emps that have Dependents with the same FNAME Alice Franklin Joy Abner John Alice Elizabeth Set with two Result: 123456789 333445555

10 .What Does Outer Query Do? SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT WHERE ESSN=E.SSN AND E.FNAME=DEPENDENT_NAME ) Returns the Employee Names for all elements In the set: 123456789 333445555 Returns FNAME LNAME John Smith Franklin Wong

11 .Query Equivalence Query 16: SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT WHERE ESSN=E.SSN AND E.FNAME=DEPENDENT_NAME) Query 16A: SELECT E.FNAME, E.LNAME FROM EMPLOYEE E, DEPENDENT D WHERE E.SSN=D.SSN AND E.FNAME=D.DEPENDENT_NAME

12 .EXISTS Nested Queries EXISTS checks Whether the Result of a Correlated Nested Query is Empty (contains no tuples) or not Query 16B: Retrieve the Name of each Employee who has a Dependent with the Same First Name as the Employee SELECT FNAME, LNAME FROM EMPLOYEE WHERE EXISTS (SELECT * FROM DEPENDENT WHERE SSN=ESSN AND FNAME=DEPENDENT_NAME) There is a analogous NOT EXISTS Inner Query Returns Is True if there is At least one match. Can there be 2 matches?

13 .NULLS in SQL Queries SQL Allows Queries that Check if a value is NULL (Missing or Undefined or not Applicable) SQL uses IS or IS NOT to compare NULLs since it Considers each NULL value Distinct from other NULL Values, so Equality Comparison is not Appropriate Query 18: Retrieve the names of all employees who do not have supervisors. SELECT FNAME, LNAME FROM EMPLOYEE WHERE SUPERSSN IS NULL Why Would Such a Capability be Useful? Downloading/Crossloading a Database Promoting a Attribute to PK/FK

14 .Aggregate Functions in SQL Queries Query 19: Find Maximum Salary, Minimum Salary, and Average Salary among all Employees SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEE Query 20: Find maximum and Minimum Salaries among Research Department Employees SELECT MAX(SALARY), MIN(SALARY) FROM EMPLOYEE, DEPARTMENT WHERE DNAME=Research AND DNUMBER=DNO What Does Query 22 Do? SELECT COUNT(*) FROM EMPLOYEE, DEPARTMENT WHERE DNAME=Research AND DNUMBER=DNO

15 .Grouping in SQL Queries In Many Cases, We Want to Apply the Aggregate Functions to Subgroups of Tuples in a Relation Each Subgroup of Tuples is Set of Tuples that Have the Same Value for the Grouping Attribute(s) Function is Applied to Each Subgroup Independently Query 24: For Each Department, Retrieve the DNO, Number of Employees, and Their Average Salary SELECT DNO, COUNT (*), AVG (SALARY) FROM EMPLOYEE GROUP BY DNO

16 .Grouping in SQL Queries Query 24: For Each Department, Retrieve the DNO, Number of Employees, and Their Average Salary SELECT DNO, COUNT (*), AVG (SALARY) FROM EMPLOYEE GROUP BY DNO EMPLOYEE tuples are Divided into Groups; each group has the Same Value for Grouping Attribute DNO COUNT and AVG functions are applied to each Group of Tuples Aeparately SELECT-clause Includes only the Grouping Attribute and the Functions to be Applied on each Tuple Group

17 .Results of Query 24: SELECT DNO, COUNT (*), AVG (SALARY) FROM EMPLOYEE GROUP BY DNO

18 .Joins and Grouping in SQL Queries A Join Condition can be used in Conjunction with Grouping Query 25: For each Project, Retrieve its Number, Name, and Number of Employees working on Project SELECT PNUMBER, PNAME, COUNT (*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME In this case, the Grouping and Functions are Applied after the Joining of the two Relations

19 .The HAVING Clause in SQL Queries In Some Cases, we want to retrieve values of Functions for only those Groups that Satisfy Certain Condition(s) The HAVING-clause is used for Specifying a Selection Condition on Groups (rather than Individual Tuples) Query 26: For each Project on which more than two employees work , Retrieve its Number, Name, and Number of Employees working on Project project SELECT PNUMBER, PNAME, COUNT (*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME HAVING COUNT (*) > 2

20 .Results of Query 26: After Applying the WHERE/GROUP BY Clauses Two Groups Not Selected Based on Having Constraint

21 .Results of Query 26: After Applying the HAVING Clause Condition 3 3 3 3

22 .Substring Comparison in SQL Queries In Regard to Strings, Most DBMSs Support SQL Queries for Exact, Near, and Starts with Matching LIKE is Used to Compare Partial Strings % (or *) Replaces an Arbitrary # of characters _ replaces a single arbitrary character Query 12: Retrieve all Employees whose Address is in Houston, Texas. SELECT FNAME, LNAME FROM EMPLOYEE WHERE ADDRESS LIKE %Houston,TX% Houston, TX can be anywhere within the ADDRESS VAR CHAR String

23 .Substring Comparison in SQL Queries The LIKE Operator Allows us to get Around the Fact that each Value is Considered Atomic and Indivisible SQL: Character String Attribute values are not Atomic Query 12A: Retrieve all employees who were born during the 1950s. SELECT FNAME, LNAME FROM EMPLOYEE WHERE BDATE LIKE __5_______ There are two “ _ ” before 5 and seven “ _ ” after 5

24 .Arithmetic Operations in SQL Queries Standard Arithmetic Operators +, -. *, and / can be Applied to Numeric Values in an SQL Query Result Query 13 : Show the Effect of Giving all Employees who work on the ProductX project a 10% raise. SELECT FNAME, LNAME, 1.1*SALARY FROM EMPLOYEE, WORKS_ON, PROJECT WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME=ProductX

25 .ORDER BY Clause in SQL Queries ORDER BY used to Sort the Tuples in a Query Result based on the Values of one or More Attribute(s) Query 15 : Retrieve a list of Employees and the Projects each works in, ordered by Dept., and within each Dept., alphabetically by Employee last name SELECT DNAME, LNAME, FNAME, PNAME FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT WHERE DNUMBER=DNO AND SSN=ESSN AND PNO=PNUMBER ORDER BY DNAME, LNAME Default is Ascending - Can be ASC/DESC as we’ll see in a Later Example

26 .ASC DESC Example Query 15 : Retrieve a list of Employees and the Projects each works in, ordered by Dept in alphabetical order, and within each Dept., alphabetically in reverse by Employee last name SELECT DNAME, LNAME, FNAME, PNAME FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT WHERE DNUMBER=DNO AND SSN=ESSN AND PNO=PNUMBER ORDER BY ASC DNAME , DESC LNAME

27 .SQL Support for Views Views are Part of the SQL DDL Abstracting from Conceptual to External Schema View Hides the Details One or More Tables in Conceptual Schema May be Combined (in Part) to Form a View Don’t Include FKs and Other Internal Attributes Typically, View is Formed by Join of Two or More Relations Utilizing FKs, PKs, etc. As a Result - View is Independent Once Formed - View Static/Unchangeable to Insulate User Applications from Conceptual Schema Similar in Concept/Intent to “Public Interface”

28 .Features of Views View Represents a Restricted Portion (Rows, Columns) of a Relation - External Schema in SQL View is Virtual Table View (Not Stored) and Must be Re-evaluated Every Time - Dynamic Like Relation, a View Can Be Deleted at Any Time Attributes Can Be Renamed in View Reasons for Views Security Increasing Application-Data Independence CREATE VIEW PQ(P#, SUMQTY) AS SELECT P#, SUM(QTY) FROM SP GROUP BY P#; SQL View Definition

29 .First View: Attribute Names are Inherited CREATE VIEW WORKS_ON1 AS SELECT FNAME, LNAME, PNAME, HOURS FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBER ; Second View: View attribute names are Aliased via a one-to-one Correspondence with the SELECT-clause CREATE VIEW DEPT_INFO (DEPT_NAME, NO_OF_EMPS, TOTAL_SAL) AS SELECT DNAME, COUNT (*), SUM (SALARY) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER=DNO GROUP BY DNAME ; View Definition in Ongoing Example