SQL2008-隨堂練習(3)

1.在資料表 emp中,找出每一種職務(job)的總人數、平均薪資、最高薪資、最低薪資及總薪資，並依總薪資做降冪排列如下(P198)

select        job,count(*),avg(sal) as average,max(sal) as highest,min(sal) as lowest,sum(sal) as total

from emp

group by job

order by sum(sal) desc;

2.在資料表customer中,找出每一州(state)的每一城市(city)的總人數(P199)

select state,city,count(*) as 'count(*)'

from customer

group by state,city

3.在資料表item中,找出每張訂單上訂購多少種產品及該張訂單的總金額，

只要顯示訂單總金額超過5000的訂單，並依訂單總金額做降冪排列(P203)

select ordid, count(*) as 'count(*)' , sum(itemtot) as 'sum(itemtot)'

from item

group by ordid

having sum(itemtot) > 5000

order by sum(itemtot) desc;

4.找出每一個業務員及其客戶的資料如下(P213)

select empno, ename, job, custid, name, repid

from emp as a , customer as b

where a.empno = b.repid and job='salesman'

order by empno;

go

select empno, ename, job, custid, name, repid

from emp as a join customer as b

on a.empno = b.repid

where job='salesman'

order by empno;