1 / 14

Introduction to Database Management Systems

Introduction to Database Management Systems. Dr. Adam Anthony Fall 2012. Lecture Overview. A few SQL basic odds & ends Set Operations Dealing with nulls. Renaming. Attributes and relations can be renamed for utility and cleverness:

calais
Download Presentation

Introduction to Database Management Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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?

More Related