5/19 SQL2008 隨堂練習

--隨堂練習(P219)

select o.ordid , orderdate , NAME ,PHONE ,PRODID ,QTY
from ORD as o , CUSTOMER as c , ITEM as i
where o.CUSTID = c.CUSTID and
 o.ORDID = i.ORDID and
 o.ORDID ='605'
order by ORDID 
go

--隨堂練習 Join on 寫法(P219)

select o.ordid , orderdate , NAME ,PHONE ,PRODID ,QTY
from ORD as o join CUSTOMER as c on o.CUSTID = c.CUSTID
 join ITEM as i on o.ORDID = i.ORDID
where o.ORDID ='605'
order by ORDID 
go

--隨堂練習(P226) about 外部連結

select p.prodid ,DESCRIP ,ORDID , i.PRODID ,QTY
from PRODUCT as p left join ITEM as i
on p.PRODID = i.PRODID
where ORDID is null
order by p.PRODID
go

--隨堂練習(P229) about 自我連結

select a.empno ,a.ename ,a.hiredate ,b.empno ,b.ename ,b.hiredate
from emp as a , emp as b
where a.hiredate = b.hiredate and a.empno < b.empno
go

--隨堂練習Join on 寫法(P229)

select a.empno ,a.ename ,a.hiredate ,b.empno ,b.ename ,b.hiredate
from emp as a join emp as b on a.hiredate = b.hiredate 
where a.empno < b.empno
go


--隨堂練習(P246-1) about 子查詢

select ordid , ORDERDATE ,CUSTID ,TOTAL
from ord
where CUSTID = ( select CUSTID
from CUSTOMER
where NAME = 'Jocksports' )
go

--隨堂練習連結寫法(P246-1) about 子查詢

select ordid , ORDERDATE ,a.CUSTID ,TOTAL
from ord as a, CUSTOMER as b
where a.CUSTID = b.CUSTID and NAME='Jocksports'
go

--隨堂練習(P246-2) about 子查詢

select ORDID ,PRODID ,QTY ,ACTUALPRICE
from ITEM
where PRODID = (select PRODID 
from PRODUCT
where DESCRIP ='ace tennis net' )
go

--隨堂練習連結寫法(P246-2) about 子查詢

select ORDID ,a.PRODID ,QTY ,ACTUALPRICE
from ITEM as a , PRODUCT as b
where a.PRODID =b.PRODID and DESCRIP='ace tennis net' 
go