3.txt [一](ppt 199) 在資料表 emp中,找出每一種職務(job)的總人數、平均薪資、最高薪資、最低薪資及總薪資 ，並依總薪資做降冪排列如下 +-----------+----------+-----------+----------+--------+-------+ | job | count(*) | average | hightest | lowest | total | +-----------+----------+-----------+----------+--------+-------+ | MANAGER | 3 | 2758.3333 | 2975 | 2450 | 8275 | | ANALYST | 2 | 3000.0000 | 3000 | 3000 | 6000 | | SALESMAN | 4 | 1400.0000 | 1600 | 1250 | 5600 | | PRESIDENT | 1 | 5000.0000 | 5000 | 5000 | 5000 | | CLERK | 4 | 1037.5000 | 1300 | 800 | 4150 | +-----------+----------+-----------+----------+--------+-------+ Ans: select
job,count(*) ,avg(sal) as ,max(sal) as ,min(sal) as ,sum(sal) as
average highest lowest total
from emp group by job order by sum(sal) desc; [二](ppt 200) 在資料表customer中,找出每一州(state)的每一城市(city)的總人數 +-------+--------------+----------+ | state | city | count(*) | +-------+--------------+----------+ | CA | BELMONT | 1 | | CA | BURLINGAME | 2 | | CA | CUPERTINO | 1 | | CA | PALO ALTO | 1 | | CA | REDWOOD CITY | 1 | | CA | SANTA CLARA | 1 | | CA | SUNNYVALE | 1 | | MN | HIBBING | 1 | +-------+--------------+----------+ 8 rows in set (0.01 sec) Ans: select state,city,count(*) as 'count(*)' from customer group by state,city [三](ppt 204) 在資料表item中,找出每張訂單上訂購多少種產品及該張訂單的總金額， 只要顯示訂單總金額超過5000的訂單，並依訂單總金額做降冪排列 +-------+----------+--------------+ | ordid | count(*) | sum(itemtot) | +-------+----------+--------------+ | 617 | 10 | 46370.00 | | 614 | 3 | 23940.00 | | 605 | 6 | 8324.00 | | 613 | 4 | 6400.00 | | 612 | 4 | 5860.00 | +-------+----------+--------------+ Ans: select ordid, count(*) as 'count(*)' , sum(itemtot) as 'sum(itemtot)' from item group by ordid having sum(itemtot) > 5000 order by sum(itemtot) desc;
第 1 頁