Adopt from
https://www6.software.ibm.com/dw/education/dm/dm0608cao3/section12.html
https://www6.software.ibm.com/dw/education/dm/dm0608cao3/section13.html
https://www6.software.ibm.com/dw/education/dm/dm0608cao3/section15.html
http://msdn.microsoft.com/zh-tw/library/ms191255.aspx

UNION

query_expression UNION | INTERSECT | EXCEPT [ALL] query_expression
[ORDER BY order_list];
使用 EXCEPT，下列查詢會傳回 EXCEPT 運算元左邊查詢結果有、但右邊查詢沒有的任何相異值。

query_expression - Any join or nonjoin query expression, as defined in the SQL Reference Guide .

If the ORDER BY clause is used, the values must reference columns from the select list of the first query expression. Using column names from the second query expression results in an error.

ORDER BY 1, 2

Example UNION query

		SELECT store_name AS store, store_type AS size,
		       state, SUM(dollars) AS sales
		FROM     aroma.period p
		    JOIN aroma.sales s ON p.perkey = s.perkey
		    JOIN aroma.store r ON r.storekey = s.storekey
		WHERE   store_type = 'Medium'
		    AND year = 2005
		    AND week = 52
		GROUP BY store_name, store_type, state

		UNION

		SELECT store_name AS store, store_type AS size,
		       state, SUM(dollars)
		FROM     aroma.period p
		    JOIN aroma.sales s ON p.perkey = s.perkey
		    JOIN aroma.store r ON r.storekey = s.storekey
		WHERE   store_type = 'Large'
		    AND year = 2005
		    AND week = 52
		GROUP BY store_name, store_type, state
		ORDER BY 2, 1;

Example query (UNION ALL)

		SELECT city
		FROM aroma.store
		UNION ALL
		SELECT hq_city AS city
		FROM aroma.market
		ORDER BY city;

Using UNION ALL if you want to repeat values that occur more than once in the list of unioned objects. For example, you may want to count the total number of objects in each case.

Adopt from
https://www6.software.ibm.com/dw/education/dm/dm0608cao3/section10.html

Set functions operate on all rows of a result table or on groups of rows defined by a GROUP BY clause. For example, you can group the sales for each market and calculate the respective sum, maximum, and minimum values.

Syntax of the GROUP BY clause

		SELECT column name(s)
		FROM table name(s)
		[WHERE search_condition]
		[GROUP BY group_list]
		[ORDER BY order_list];

Using Multiple groups

SELECT year, region, district, city, SUM(dollars) AS sales
FROM aroma.store a, aroma.sales b, aroma.product c,
     aroma.period d, aroma.market e
WHERE   a.storekey = b.storekey
    AND c.prodkey = b.prodkey
    AND c.classkey = b.classkey
    AND d.perkey = b.perkey
    AND e.mktkey = a.mktkey
    AND year IN (2004, 2005)
GROUP BY year, region, district, city
ORDER BY year, region, district, city;

Adopt from
https://www6.software.ibm.com/dw/education/dm/dm0608cao3/dm0608cao3-pdf.pdf
http://en.wikipedia.org/wiki/Join_(SQL)
http://www.1keydata.com/tw/sql/sqljoins.html

Table Aliases
SELECT date, dollars
FROM aroma.period a, aroma.sales b
WHERE a.perkey=b.perkey
AND a.month='JAN'
AND a.year=2006;

Simple Join
SELECT prod_name, store_name, day, dollars
FROM aroma.promotion a, aroma.product b,
     aroma.period c, aroma.store d, aroma.sales e
WHERE       a.promokey = e.promokey
    AND    b.prodkey = e.prodkey
    AND    b.classkey = e.classkey
    AND    c.perkey = e.perkey
    AND    d.storekey = e.storekey
    AND     prod_name LIKE 'Easter%'
    AND     day IN ('SA', 'SU')
    AND     promo_type = 900
    AND     year = 2005;

Self-joins
The tables being joined in a query do not need to be distinct; you can join any table to itself as long as you give each table reference a different name. Self-joins are useful for discovering relationships between different columns of data in the same table. 

不同的 SQL JOIN
除了我们在上面的例子中使用的 INNER JOIN（内连接），我们还可以其他几种连接。
下面列出了您可以使用的 JOIN 类型，以及它们之间的差异。

• JOIN: 如果表中有至少一个匹配，则返回行
• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN: 只要其中一个表中存在匹配，就返回行

Innter Join and Outer Join
In most cases, tables are joined according to search conditions that find only the rows with matching values; this type of join is known as an inner join. In some cases, however, decision-support analysis requires outer joins, which retrieve both matching and non-matching rows, which express, for example, a greater-than or less-than relationship.

An outer join operation returns all the rows returned by an inner join plus all the rows from one table that do not match any row from the other table. An outer join can be left, right, or full, depending on whether rows from the left, right, or both tables are retained. The first table listed in the FROM clause is referred to as the left table and the second as the right table. For all three types of outer join, NULLs are used to represent empty columns in rows that do not match.

FROM table_1 LEFT|RIGHT|FULL OUTER JOIN table_2
    ON table_1.column = table_2.column

Set Functions
 SUM(expression) - Calculates the sum of all the values in expression.
 SUM(DISTINCT expression) - Calculates the sum of distinct values in expression.
 AVG(expression) - Calculates the average of all the values in expression.
 AVG(DISTINCT expression) - Calculates the average of distinct values in expression.
 MAX(expression) - Determines the maximum value in expression.
 MIN(expression) - Determines the minimum value in expression.
 COUNT(*) - Counts the number of rows returned.
 COUNT(expression) - Counts the number of non-null values in expression.
 COUNT(DISTINCTexpression) - Counts the number of distinct non-null values in expression.

You can replace expression with any column name or numeric expression. Each function, except COUNT(*), ignores NULL values when calculating the returned aggregate value.

SELECT SUM(dollars) as Dol_Sales, AVG(dollars) as Avg_Sales,
MAX(dollars) as Max_Sales, MIN(dollars) as Min_Sales, COUNT(*) as Qty
FROM aroma.store a, aroma.sales b, aroma.product c, aroma.period d
WHERE   a.storekey = b.storekey
    AND c.prodkey = b.prodkey
    AND c.classkey = b.classkey
    AND d.perkey = b.perkey
    AND prod_name LIKE 'Lotta Latte%'
    AND year = 2005
    AND city LIKE 'Los Ang%';

as change the field name

Adopt from
https://www6.software.ibm.com/dw/education/dm/dm0608cao2/index.html

CONNECT TO aromadb USER userid USING password

SELECT perkey, sum(dollars)
FROM aroma.sales
WHERE perkey < 50
GROUP BY perkey
HAVING sum(dollars) > 8000
ORDER BY perkey;

SELECT mktkey, hq_city, hq_state, district, region FROM aroma.market

SELECT DISTINCT district, region
FROM aroma.market;

WHERE clause is case-sensitive.

<> not equal
= equal

A simple condition can be expressed with the following SQL comparison predicates:

SELECT hq_city, hq_state, district
FROM aroma.market
WHERE district IN ('Chicago', 'New York', 'New Orleans');

Wildcard characters
With the LIKE predicate and the two wildcard characters, the percent sign (%) and the underscore (_), you can also express conditions that match a portion of a character string (a substring).
SELECT district, hq_city
FROM aroma.market
WHERE district LIKE 'Min%';

AS Clause
SELECT district, hq_city AS City
FROM aroma.market
WHERE district LIKE 'Min%';
Result