5/18 SQL2008 隨堂練習

--隨堂練習1(P198)


select job ,COUNT(*) as '總人數' , AVG(sal) as avg , MAX(SAL) as highest ,MIN(sal) lowest,SUM(sal) as total


from emp


group by job


order by SUM(sal) desc


go


--隨堂練習2(P199)


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


from CUSTOMER


group by STATE ,city


go

--隨堂練習3(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


go


--隨堂練習4(P213)


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


from emp as e ,CUSTOMER as c


where e.empno=c.REPID and job='salesman'


order by empno


go


--練習4 JOIN ON版


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


from emp as e Join CUSTOMER as c


on e.empno=c.REPID


where job='salesman'


order by empno


go