any column can be used in select and where clauses

Info iconThis preview shows page 1. Sign up to view the full content.

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

Unformatted text preview: n can be used in SELECT and WHERE clauses. • But refers to only one tuple value at a time select-from-where can produce a one-column table that contains only one tuple. Thus queries can also be nested in SELECT and WHERE clauses Example: Which film(s) had the highest budget? 14 SELECT * FROM Film WHERE budget = ( SELECT MAX(budget) FROM Film); USING IN FOR MEMBERSHIP TEST Comparison operator IN • Why use DISTINCT in this example? 15 • Compares value v with a set (or bag) of values V • Evaluates to TRUE if v is one of the elements in V • Allows any relation in WHERE clause USING IN (CONT’D.) Use tuples of values in comparisons 16 • Requires parentheses NESTED 1-COLUMN QUERIES Use other comparison operators to compare a single value v • = ANY (or = SOME) operator • Returns TRUE if the value v is equal to some value in the set V • Equivalent to IN • Also available for >, >=, <, <=, and <> • >= ALL operator 17 • Returns TRUE if the value v is greater than or equal to every value in the set V • Equivalent to =(SELECT MAX(…)…) • Also available for =, >, <, <=, and <> CORRELATED NESTED QUERIES Correlated nested query • Evaluated once for each tuple in the outer query Such queries are easiest to understand (and write correctly) if all...
View Full Document

Ask a homework question - tutors are online