1. STARTING WITH 子句 相當于like (**%);
2. order by 子句
3. group by 子句
4. having 子句
SQL> select team,avg(sickleave),avg(annualleave) from orgchart
2 group by team
3 having avg(sickleave)>25 and avg(annualleave)<20;
TEAM AVG(SICKLEAVE) AVG(ANNUALLEAVE)
--------------- -------------- ----------------
RESEARCH 27 14.5
MARKETING 28.33333333333 15.3333333333333
SQL> select * from orgchart;
NAME TEAM SALARY SICKLEAVE ANNUALLEAVE
--------------- --------------- ------------ --------- -----------
ADAMS RESEARCH 34000.00 34 12
WILKES MARKETING 31000.00 40 9
STOKES MARKETING 36000.00 20 19
MEZA COLLECTIONS 40000.00 30 27
MERRICK RESEARCH 45000.00 20 17
RICHARDSON MARKETING 42000.00 25 18
FURY COLLECTIONS 35000.00 22 14
PRECOURT PR 37500.00 24 24
8 rows selected
SQL> select team,avg(sickleave),avg(annualleave) from orgchart
2 group by team having count (team)>1;
TEAM AVG(SICKLEAVE) AVG(ANNUALLEAVE)
--------------- -------------- ----------------
COLLECTIONS 26 20.5
RESEARCH 27 14.5
MARKETING 28.33333333333 15.3333333333333
SQL> select team,avg(salary)from orgchart
2 group by team having team in ('PR','RESEARCH');
TEAM AVG(SALARY)
--------------- -----------
PR 37500
RESEARCH 39500
5. 兩者結合使用：
SQL> select * from orgchart order by name desc;
NAME TEAM SALARY SICKLEAVE ANNUALLEAVE
--------------- --------------- ------------ --------- -----------
WILKES MARKETING 31000.00 40 9
STOKES MARKETING 36000.00 20 19
RICHARDSON MARKETING 42000.00 25 18
PRECOURT PR 37500.00 24 24
MEZA COLLECTIONS 40000.00 30 27
MERRICK RESEARCH 45000.00 20 17
FURY COLLECTIONS 35000.00 22 14
ADAMS RESEARCH 34000.00 34 12
8 rows selected
SQL> select payee,sum(amount)total,count(payee)number_written from checks
2 group by payee having sum(amount) > 50;
PAYEE TOTAL NUMBER_WRITTEN
------------------------------------ ---------- --------------
Ma Bell 350.32 2
Reading R.R. 245.34 1
Local Utilities 98 1
Joes Stale$ Dent 150 1
SQL> select payee,sum(amount) total,count(payee)number_written
2 from checks where amount >= 100 group by payee
3 having sum(amount) > 50;
PAYEE TOTAL NUMBER_WRITTEN
------------------------------------ ---------- --------------
Ma Bell 350.32 2
Reading R.R. 245.34 1
Joes Stale$ Dent 150 1
where 子句與order by子句常在對單行進行處理時用到；
group by 和 having子句常用在對數據進行匯總操作上。