SQL2008-隨堂練習(4)

1.找出605號訂單的訂購日期、客戶的姓名、聯絡電話、訂購的產品及訂購的數量如下(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 i.ordid = o.ordid and o.ordid=605;

2.找出滯銷的產品(不曾被訂購的產品)如下(P226)

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;

3.找出同一天進公司的員工如下(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

order by a.empno;

4.在資料表ord中找出客戶JOCKSPORTS曾下過的訂單如下P(246)

select ordid,orderdate, custid, total

from ord

where custid = ( select custid from customer

                 where name = 'Jocksports' );

5.在資料表item中找出曾經訂過產品 'ACE TENNIS NET' 的訂單如下(P246)

select ordid, prodid, qty, actualprice

from item

where prodid= ( select prodid from product

                where descrip = 'ACE TENNIS NET'  ) ;