CS 425 – Database Organization - 4 - Chapter 4: Introduction to SQL （2）

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