41、Null Values and Aggregates
① Total all salaries
select sum (salary )
from instructor
- Above statement ignores null amounts
- Result is null if there is no non-null amount
② All aggregate operations except count(*) ignore tuples with null values on the aggregated attributes
③ What if collection has only null values?
- count returns 0
- all other aggregates return null
42、Empty Relations and Aggregates
① What if the input relation is empty
② Conventions:
◦ sum: returns null
◦ avg: returns null
◦ min: returns null
◦ max: returns null
◦ count: returns 0
43、Example Query
① Find courses offered in Fall 2009 and in Spring 2010
select distinct course_id
from section
where semester = ’Fall’ and year= 2009 and
course_id in (select course_id
from section
where semester = ’Spring’ and year= 2010);
② Find courses offered in Fall 2009 but not in Spring 2010
select distinct course_id
from section
where semester = ’Fall’ and year= 2009 and
course_id not in (select course_id
from section
where semester = ’Spring’ and year= 2010);
44、Query Features Recap - Syntax
① An SQL query is either a Select-from-where block or a set operation
② An SQL query block is structured like this:
SELECT [DISTINCT] select_list
[FROM from_list]
[WHERE where_condition]
[GROUP BY group_by_list]
[HAVING having_condition]
[ORDER BY order_by_list]
③ Set operations
[Query Block] set_op [Query Block]
set_op: [ALL] UNION | INTERSECT | EXCEPT
45、Query Semantics
① Compute FROM clause
◦ Compute cross product of all items in the FROM clause
◦ Relations: nothing to do
◦ Subqueries: use this algorithm to recursively compute the result of subqueries first
◦ Join expressions: compute the join
② Compute WHERE clause
- For each tuple in the result of 1. evaluate the WHERE clause condition
③ Compute GROUP BY clause
- Group the results of step 2. on the GROUP BY expressions
④ Compute HAVING clause
- For each group (if any) evaluate the HAVING condition
46、Syntax - SELECT
① SELECT [DISTINCT [ON (distinct_list)]] select_list
② select_list
<1>List of projection expressions
[expr] [AS name]
<2>expr
Expression over attributes, constants, arithmetic operators,
functions, CASE-construct, aggregation functions
③ distinct_list
List of expressions
④ Examples:
◦ SELECT DISTINCT ON (a % 2) a FROM r;
◦ SELECT substring(a, 1,2) AS x FROM r;
◦ SELECT CASE WHEN a = 2 THEN a ELSE null END AS b FROM r;
◦ SELECT a = b AS is_a_equal_to_b FROM r;
47、Syntax – FROM (cont.)
Examples (relation r with attributes a and b):
◦ SELECT * FROM r;
◦ SELECT * FROM r AS g(v,w);
◦ SELECT * FROM r x;
◦ SELECT * FROM r NATURAL JOIN s, t;
48、Syntax - WHERE
① WHERE where_condition
② where_condition: A boolean expression over
◦ Attributes
◦ Constants: e.g., true, 1, 0.5, ‘hello’
◦ Comparison operators: =, <, >, IS DISTINCT FROM, IS NULL, …
◦ Arithmetic operators: +,-,/,%
◦ Function calls
◦ Nested subquery expressions
③ Examples
◦ SELECT * FROM r WHERE a = 2;
◦ SELECT * FROM r WHERE NOT(a = 2 OR a = 3);
49、Syntax – GROUP BY
① GROUP BY group_by_list
② group_by_list
◦ List of expressions
◦ Expression over attributes, constants, arithmetic operators,
functions, CASE-construct, aggregation functions
③ Examples:
SELECT sum(a), b FROM r GROUP BY b;
◦ SELECT sum(a), b, c FROM r GROUP BY b, c;
◦ SELECT sum(a), b/2 FROM r GROUP BY b/2;
50、Syntax – HAVING
① HAVING having_condition
② having_condition
- Like where_condition except that expressions over attributes have either to be in the GROUP BY clause or are aggregated
③ Examples:
◦ SELECT sum(a), b FROM r GROUP BY b HAVING sum(a) > 10;
◦ SELECT sum(a), b FROM r GROUP BY b HAVING sum(a) + 5 > 10;
◦ SELECT sum(a), b FROM r GROUP BY b HAVING true;
◦ SELECT sum(a), b FROM r GROUP BY b HAVING count(*) = 50;
◦ SELECT b FROM r GROUP BY b HAVING sum(a) > 10;
51、Syntax – ORDER BY
① ORDER BY or