Introduction to Database Management Systems

1. Introduction to Database Management Systems Dr. Adam Anthony Fall 2012

2. Lecture Overview • A few SQL basic odds & ends • Set Operations • Dealing with nulls

3. Renaming • Attributes and relations can be renamed for utility and cleverness: • SELECT ID, name, salary/12 AS monthly_salaryFROM instructor • Find the names of all instructors who have a higher salary than some instructor in ‘Comp. Sci’. • SELECT DISTINCT T. nameFROM instructor AST, instructor ASSWHERET.salary > S.salaryANDS.dept_name = ‘Comp. Sci.’

4. Ordering • List in alphabetic order the names of all instructors SELECT DISTINCT nameFROM instructorORDER BY name • We may specify desc for descending order or asc for ascending order, for each attribute; ascending order is the default. • Example: ORDER BYnameDESC • Can sort on multiple attributes • Example: ORDER BY dept_nameASC, name DESC

5. Practice Query 1: Join with Problems • Find the ID’s of all students who were taught by an instructor named Einstein. Remove duplicates in the result.

6. Textbook University Schema Diagram

7. Set Operations • Remember, a relation is theoretically a SET (as in discrete math) of TUPLES • Union, Intersection, Set-Difference • Used to combine two queries that have IDENTICAL ATTRUBUTE SETS! • Example sets: • All course ID’s for courses taught in Fall 2009 • All course ID’s for courses taught in Spring 2010

8. Union Operation • Combines result of two queries, eliminates duplicates: • Find course ID’s for courses that ran in Fall 2009 or in Spring 2010 (selectcourse_idfrom section where sem = ‘Fall’ and year = 2009)union(selectcourse_idfrom section where sem = ‘Spring’ and year = 2010)

9. Intersect Operation • Finds all non-duplicate tuples in common between two queries • Find courses that ran in both Fall 2009 and Spring 2010 (selectcourse_idfrom section where sem = ‘Fall’ and year = 2009)intersect(selectcourse_idfrom section where sem = ‘Spring’ and year = 2010)

10. Except Operation • EXCEPT keyword does set-difference: gives all tuples in the first query, but removes anything that appears in the second • Probably the most-used of the three operations because a boolean version is tricky • Find courses that ran in Fall 2009 but not in Spring 2010 (selectcourse_idfrom section where sem = ‘Fall’ and year = 2009)except(selectcourse_idfrom section where sem = ‘Spring’ and year = 2010)

11. Duplicates in Set Operations • To retain all duplicates use the corresponding multiset versions union all, intersect all and except all.Suppose a tuple occurs m times in r and n times in s, then, it occurs: • m + n times in r union all s • min(m,n) times in r intersect all s • max(0, m – n) times in r except all s

12. Null Values • It is possible for tuples to have a null value, denoted by null, for some of their attributes • null signifies an unknown value or that a value does not exist. • The result of any arithmetic expression involving null is null • Example: 5 + nullevaluates as null • The predicate is null can be used to check for null values. • Example: Find all instructors whose salary is null. select namefrom instructorwhere salary is null • can also use is not null, which can be very useful: • Example: Find all of a student’s grades select takes.* from students natural join takes where grade is not null

13. Null Values and Three Valued Logic • Any comparison with null returns unknown • Example: 5 < null or null <> null or null = null • Three-valued logic using the truth value unknown: • OR: (unknown or true) = true, (unknown or false) = unknown (unknown or unknown) = unknown • AND: (true and unknown) = unknown, (false and unknown) = false, (unknown and unknown) = unknown • NOT: (not unknown) = unknown • “P is unknown” evaluates to true if predicate P evaluates to unknown • Result of where clause predicate is treated as false if it evaluates to unknown

14. Set Operations Practice • Can we come up with our own examples?