MIS3150 Data and Information Management Query Languages –SQL

1. MIS3150 Data and Information ManagementQuery Languages –SQL Arijit Sengupta

2. Structure of this semester MIS3150 1. Design 2. Querying 3. Advanced Topics 0. Intro 4. Applications Database Fundamentals Conceptual Modeling Query Languages Java DB Applications – JDBC Transaction Management Relational Model Advanced SQL Data Mining Normalization Newbie Users Designers Developers Professionals

3. Today’s Buzzwords • Query Languages • Formal Query Languages • Procedural and Declarative Languages • Relational Algebra • Relational Calculus • SQL • Aggregate Functions • Nested Queries

4. Objectives At the end of the lecture, you should • Get a formal as well as practical perspective on query languages • Have a background on query language basics (how they came about) • Be able to write simple SQL queries from the specification • Be able to look at SQL queries and understand what it is supposed to do • Be able to write complex SQL queries involving nesting • Execute queries on a database system

5. Set Theory Basics • A set: a collection of distinct items with no particular order • Set description: • { b | b is a Database Book} • {c | c is a city with a population of over a million} • {x | 1 < x < 10 and x is a natural number} • Most basic set operation: • Membership: x  S (read as x belongs to S if x is in the set S)

6. Other Set Operations • Addition, deletion (note that adding an existing item in the set does not change it) • Set mathematics: • Union R  S = { x | x  R or x  S} • Intersection R  S = { x | x R and x  S} • Set Difference R – S = { x | x  R and x  S} • Cross-product R x S = { <x,y> | x  R and y  S} • You can combine set operations much like arithmetic operations: R – (S  T) • Usually no well-defined precedence

7. Relational Query Languages • Query languages: Allow manipulation and retrieval of data from a database. • Relational model supports simple, powerful QLs: • Strong formal foundation based on logic. • Allows for much optimization. • Query Languages != programming languages! • QLs not expected to be “Turing complete”. • QLs not intended to be used for complex calculations. • QLs support easy, efficient access to large data sets.

8. Formal Relational Query Languages Two mathematical Query Languages form the basis for “real” languages (e.g. SQL), and for implementation: • Relational Algebra: More operational, very useful for representing execution plans. • Relational Calculus: Lets users describe what they want, rather than how to compute it. (Non-operational, declarative.) • Understanding Algebra & Calculus is key to understanding SQL, query processing!

9. Structured Query Language • Need for SQL • Operations on Data Types • Definition Manipulation • Operations on Sets • Declarative (calculus) vs. Procedural (algebra) • Evolution of SQL • SEQUEL ..SQL_92 .. SQL_93 • SQL Dialects Does SQL treat Relations as ‘Sets’?

10. Preliminaries • A query is applied to relation instances, and the result of a query is also a relation instance. • Schemasof input relations for a query are fixed (but query will run regardless of instance!) • The schema for the resultof a given query is also fixed! Determined by definition of query language constructs. • Positional vs. named-field notation: • Positional notation easier for formal definitions, named-field notation more readable. • Both used in SQL

11. Example Instances • Students, Registers, Courses relations for our examples. R1 C1 S2 S1

12. Relational Algebra • Basic operations: • Selection ( ) Selects a subset of rows from relation. • Projection ( ) Deletes unwanted columns from relation. • Cross-product( ) Allows us to combine two relations. • Set-difference ( ) Tuples in reln. 1, but not in reln. 2. • Union( ) Tuples in reln. 1 and in reln. 2. • Additional operations: • Intersection, join, division, renaming: Not essential, but (very!) useful. • Since each operation returns a relation, operationscan be composed! (Algebra is “closed”.)

13. Projection • Deletes attributes that are not in projection list. • Schema of result contains exactly the fields in the projection list, with the same names that they had in the (only) input relation. • Projection operator has to eliminate duplicates! (Why??) • Note: real systems typically don’t do duplicate elimination unless the user explicitly asks for it. (Why not?)

14. Vertical Slices Algebra: projection <A1,A2,...Am> (R) • Projection • Specifying Elements • No Specification • List all information about Students • select * • from STUDENT; • (Student) • Conditional • List IDs, names, and addresses of all students • selectStudentID, name, address • from STUDENT; • StudentID, name, address(Student)

15. Does SQL treat Relations as ‘Sets’? What are the different salaries we pay to our employees? select salary from EMPLOYEE; OR is the following better? select DISTINCT salary from EMPLOYEE;

16. Selection • Selects rows that satisfy selection condition. • No duplicates in result! (Why?) • Schema of result identical to schema of (only) input relation. • Result relation can be the input for another relational algebra operation! (Operatorcomposition.)

17. Horizontal Slices Algebra: selection or restriction (R) • Restriction • Specifying Conditions • Unconditional • List all students • select * • from STUDENT; • (Student) • Conditional • List all students with GPA > 3.0 • select * • from STUDENT • where GPA > 3.0; • GPA > 3.0(Student)

18. Specifying Conditions List all students in ... select * from STUDENT where city in (‘Boston’,’Atlanta’); List all students in ... select * from STUDENT where zip not between 60115 and 60123;

19. Pattern Matching ‘%’ any string with n characters, n>=0 ‘_’ any single character. x exact sequence of string x. List all CIS courses. select * from COURSE where course# like‘CIS%’; List all CIS 3200 level courses. select * from COURSE where course# like ? ;

20. Missing or Incomplete Information • List all students whose address or telephone number is missing: • select * • from STUDENT • where Address is null or GPA is null;

21. Horizontal and Vertical Query: List all student ID, names and addresses who have GPA > 3.0 and date of birth before Jan 1, 1980. select StudentID, Name, Address from STUDENT where GPA > 3.0 and DOB < ‘1-Jan-80’ order by Name DESC; Algebra: StudentID,name, address (GPA > 3.0 and DOB < ‘1-Jan-80’(STUDENT)) Calculus: {t.StudentID, t.name, t.address | t  Student t.GPA > 3.0  t.DOB < ‘1-Jan-80’} Order by sorts result in descending (DESC) order. Note: The default order is ascending (ASC) as in: order by Name;

22. Union, Intersection, Set-Difference • All of these operations take two input relations, which must be union-compatible: • Same number of fields. • `Corresponding’ fields have the same type. • What is the schema of result?

23. Union • List students who live in Atlanta or GPA > 3.0 • select StudentID, Name, DOB, Address • from STUDENT • where Address = ‘Atlanta’ • union • select StudentID, Name, DOB, Address • from STUDENT • where GPA > 3.0; • Can we perform a Union on any two Relations ?

24. Union Compatibility • Two relations, A and B, are union-compatible • if • A and B contain a same number of attributes, and • The corresponding attributes of the two have the same domains • Examples • CIS=Student (ID: Did; Name: Dname; Address: Daddr; Grade: Dgrade); • Senior-Student (SName: Dname; S#: Did; Home: Daddr; Grade: Dgrade); • Course (C#: Dnumber; Title: Dstr; Credits: Dnumber) • Are CIS-Student and Senior-Student union compatible? • Are CIS-Student and Course union compatible? • What happens if we have duplicate tuples? • What will be the column names in the resulting Relation?

25. B A Union, Intersect, Minus select CUSTNAME, ZIP from CUSTOMER where STATE = ‘MA’ UNION select SUPNAME, ZIP from SUPPLIER where STATE = ‘MA’ ORDER BY 2; select CUSTNAME, ZIP from CUSTOMER where STATE = ‘MA’ INTERSECT select SUPNAME, ZIP from SUPPLIER where STATE = ‘MA’ ORDER BY 2; select CUSTNAME, ZIP from CUSTOMER where STATE = ‘MA’ MINUS select SUPNAME, ZIP from SUPPLIER where STATE = ‘MA’ ORDER BY 2; B A A B A

26. Cross-Product • Each row of S1 is paired with each row of R1. • Result schema has one field per field of S1 and R1, with field names `inherited’ if possible. • Conflict: Both S1 and R1 have a field called sid. • Renaming operator:

27. Joins • Condition Join: • Result schema same as that of cross-product. • Fewer tuples than cross-product, might be able to compute more efficiently • Sometimes called a theta-join.

28. Joins • Equi-Join: A special case of condition join where the condition c contains only equalities. • Result schema similar to cross-product, but only one copy of fields for which equality is specified. • Natural Join: Equijoin on all common fields.

29. Solution 1: • Solution 2: • Solution 3: Find names of students who have taken course #103

30. Connecting/Linking Relations • List information about all students and the classes they are taking Student Class What can we use to connect/link Relations? Join: Connecting relations so that relevant tuples can be retrieved.

31. R1 R2 Join Cartesian Product Student: 30 tuples Class: 4 tuples Total Number of Tuples in the Cartesian Product. ? (match each tuple of student to every tuple of class) Select tuples having identical Student Ids. Expected number of such Tuples: Join Selectivity

32. Join Forms R1 R2 select s.*, c.* from STUDENT s, CLASS c where s.StudentID = c. SID; • General Join Forms • Equijoin • Operator Dependent • Natural Join • Outer Join • Left • Right • Full R1 R2 = x > y <> ... select s.*, c.* from STUDENT s, CLASS c where s.StudentID = c.SID (+);

33. A more efficient solution: Find names of students who have taken a CIS course • Information about departments only available in Courses; so need an extra join: • A query optimizer can find this given the first solution!

34. Find students who have taken an MIS or a CS course • Can identify all MIS or CS courses, then find students who have taken one of these courses: • Can also define Temp1 using union! (How?) • What happens if is replaced by in this query?

35. Find students who have taken a CIS and an ECI Course • Previous approach won’t work! Must identify students who have taken CIS courses, students who have taken ECI courses, then find the intersection (note that sid is a key for Students):

36. Relational Calculus • Comes in two flavours: Tuple relational calculus (TRC) and Domain relational calculus(DRC). • Calculus has variables, constants, comparison ops, logical connectives and quantifiers. • TRC: Variables range over (i.e., get bound to) tuples. • DRC: Variables range over domain elements (= field values). • Both TRC and DRC are simple subsets of first-order logic. • Expressions in the calculus are called formulas. An answer tuple is essentially an assignment of constants to variables that make the formula evaluate to true.

37. TRC: Find students with GPA > 3.7 who have taken a CIS Course DRC:

38. DRC: TRC: Find students who have taken all CIS courses How will you do this with Relational Algebra?

39. Monotonic and Non-Monotonic Queries • Monotonic queries: queries for which the size of the results either increase or stay the same as the size of the inputs increase. The result size never decreases • Non-monotonic queries: queries for which it is possible that the size of the result will DECREASE when the size of the input increases • Examples of each? • Which of the algebra operations is non-monotonic? • What does this signify?

40. Summaries and Aggregates Calculate the average GPA select avg. (GPA) from STUDENT, Find the lowest GPA select min (GPA) as minGPA from STUDENT, How many CIS majors? select count (StudentId) from STUDENT where major=‘CIS’; Discarding duplicates select avg (distinct GPA) STUDENT where major=‘CIS’ (is this above query correct?)

41. Aggregate Functions • COUNT (attr) - a simple count of values in attr • SUM (attr) - sum of values in attr • AVG (attr) - average of values in attr • MAX (attr) - maximum value in attr • MIN (attr) - minimum value in attr • Take effect after all the data is retrieved from the database • Applied to either the entire resulting relation or groups • Can’t be involved in any query qualifications (where clause) • Would the following query be permitted? • select StudentId • from STUDENT • where GPA = max (GPA);

42. Grouping Results Obtained • Show all students enrolled in each course. • select cno, StudentID • from REGISTRATION • group bycno; Is this grouping OK? • Calculate the average GPA of students by county. • select county, avg (GPA) as CountyGPA • from STUDENT • group bycounty; • Calculate the enrollment of each class. • select cno, year , term, count (StudentID) as enroll • from REGISTRATION • group by cno, year, term;

43. Selections on Groups • Show all CIS courses that are full. • select cno, count (StudentID) • from REGISTRATION • group by cno • having count (StudentID) > 29;

44. Grouping Results after Join • Calculate the average GPA of each class select course#, avg (GPA) from STUDENT S, CLASS C where S.StudentID = C.SID group by course#,

45. Nesting Queries SELECT attribute(s) FROM relation(S) WHERE attr [not] {in | comparison operator | exists } ( query statement(s) ); List names of students who are taking “BA201” select Name from Student where StudentID in ( select StudentID from REGISTRATION where course#=‘BA201’);

46. Sub Queries List all students enrolled in CIS courses select name from STUDENT where StudentId in (select StudentId from REGISTRATION where cno like ‘CIS%’); List all courses taken by Student (Id 1011) select cname from COURSE where cnum = any (select cno from REGISTRATION where StudentId = 1011);

47. Sub Queries Who received the highest grade in CIS 8140 select StudentId from REGISTRATION where cnum = ‘CIS 8140’ and grade >=all (select grade from REGISTRATION where cno = ‘CIS 8140’); List all students enrolled in CIS courses. select name from STUDENT S where exists (select * from REGISTRATION where StudentId = S.StudentId and cno like ‘CIS%’);

48. View 1 View 2 View N Base Relation 1 Base Relation 2 Relational Views • Relations derived from other relations. • Views have no stored tuples. • Are useful to provide multiple user views. • What level in the three layer model do views belong? • Which kind of independence do they support?

49. View Creation Create View view-name [ ( attr [ , attr ] ...) ] AS subquery [ with check option ] ; DROP VIEW view-name; • Create a view containing the student ID, Name, Age and GPA for those who are qualified to take 300 level courses, i.e., GPA >=2.0.

50. View Options • With Check Option enforces the query condition for insertion or update To enforce the GPA >=2.0 condition on all new student tuples inserted into the view • A view may be derived from multiple base relations Create a view that includes student IDs, student names and their instructors’ names for all CIS 300 students.