sql2 - 1 Introduction to SQL Multirelation Queries...

Info iconThis preview shows pages 1–13. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 1 Introduction to SQL Multirelation Queries Subqueries Slides are reused by the approval of Jeffrey Ullman’s 2 Our Running Example ◆ All our SQL queries will be based on the following database schema. ◗ Underline indicates key attributes. Beers(name , manf) Bars(name , addr, license) Drinkers(name , addr, phone) Likes(drinker , beer ) Sells(bar , beer , price) Frequents(drinker , bar ) 3 Meaning of Single-Relation Query ◆ Begin with the relation in the FROM clause. ◆ Apply the selection indicated by the WHERE clause. ◆ Apply the extended projection indicated by the SELECT clause. 4 Operational Semantics --- General ◆ Think of a tuple variable visiting each tuple of the relation mentioned in FROM. ◆ Check if the “current” tuple satisfies the WHERE clause. ◆ If so, compute the attributes or expressions of the SELECT clause using the components of this tuple. 5 Complex Conditions in WHERE Clause ◆ Boolean operators AND, OR, NOT. ◆ Comparisons =, <>, <, >, <=, >=. ◗ And many other operators that produce boolean-valued results. 6 Patterns ◆ A condition can compare a string to a pattern by: ◗ <Attribute> LIKE <pattern> or <Attribute> NOT LIKE <pattern> ◆ Pattern is a quoted string with % = “any string”; _ = “any character.” 7 Example : LIKE ◆ Using Drinkers(name, addr, phone) find the drinkers with exchange 555: SELECT name FROM Drinkers WHERE phone LIKE ’%555-_ _ _ _’; 8 NULL Values ◆ Tuples in SQL relations can have NULL as a value for one or more components. ◆ Meaning depends on context. Two common cases: ◗ Missing value : e.g., we know Joe’s Bar has some address, but we don’t know what it is. ◗ Inapplicable : e.g., the value of attribute spouse for an unmarried person. 9 Comparing NULL’s to Values ◆ The logic of conditions in SQL is really 3-valued logic: TRUE, FALSE, UNKNOWN. ◆ Comparing any value (including NULL itself) with NULL yields UNKNOWN. ◆ A tuple is in a query answer iff the WHERE clause is TRUE (not FALSE or UNKNOWN). 10 Three-Valued Logic ◆ To understand how AND, OR, and NOT work in 3-valued logic, think of TRUE = 1, FALSE = 0, and UNKNOWN = ½. ◆ AND = MIN; OR = MAX, NOT( x ) = 1-x . ◆ Example : TRUE AND (FALSE OR NOT(UNKNOWN)) = MIN(1, MAX(0, (1 - ½ ))) = MIN(1, MAX(0, ½ )) = MIN(1, ½ ) = ½. 11 Surprising Example ◆ From the following Sells relation: bar beer price Joe’s Bar Bud NULL SELECT bar FROM Sells WHERE price < 2.00 OR price >= 2.00; UNKNOWN UNKNOWN UNKNOWN 12 Reason: 2-Valued Laws != 3-Valued Laws ◆ Some common laws, like commutativity of AND, hold in 3-valued logic....
View Full Document

Page1 / 72

sql2 - 1 Introduction to SQL Multirelation Queries...

This preview shows document pages 1 - 13. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online