Group by cust_id,prod_id
Order by cust_id,prod_id
44、 查詢訂購(gòu)了三種以上產(chǎn)品的訂單號(hào)
Select order_no
from sale_item
Group by order_no
Having count(*)>3
45、 查詢訂購(gòu)的產(chǎn)品至少包含了訂單3號(hào)中所訂購(gòu)產(chǎn)品的訂單
Select distinct order_no
From sale_item a
Where order_no<>'3'and not exists (
Select * from sale_item b where order_no ='3' and not exists
(select * from sale_item c where c.order_no=a.order_no and c.prod_id=b.prod_id))
46、 在sales表中查找出訂單金額大于"E0013業(yè)務(wù)員在1996/11/10這天所接每一張訂單的金額"的所有訂單，并顯示承接這些訂單的業(yè)務(wù)員和該訂單的金額
Select sale_id,tot_amt from sales
where tot_amt>all(select tot_amt
from sales
where sale_id='E0013' and order_date='1996-11-10')
47、 查詢末承接業(yè)務(wù)的員工的信息
Select *
From employee a
Where not exists
(select * from sales b where a.emp_no=b.sale_id)
48、 查詢來(lái)自上海市的客戶的姓名，電話、訂單號(hào)及訂單金額
Select cust_name,tel_no,order_no,tot_amt
From customer a ,sales b
Where a.cust_id=b.cust_id and addr='上海市'
49、 查詢每位業(yè)務(wù)員各個(gè)月的業(yè)績(jī)，并按業(yè)務(wù)員編號(hào)、月份降序排序
Select sale_id,month(order_date), sum(tot_amt)
from sales
group by sale_id,month(order_date)
order by sale_id,month(order_date) desc
50、 求每種產(chǎn)品的總銷售數(shù)量及總銷售金額，要求顯示出產(chǎn)品編號(hào)、產(chǎn)品名稱，總數(shù)量及總金額，并按產(chǎn)品號(hào)從小到大排列
Select a.prod_id,prod_name,sum(qty),sum(qty*unit_price)
From sale_item a,product b
Where a.prod_id=b.prod_id
Group by a.prod_id,prod_name
Order by a.prod_id
51、 查詢總訂購(gòu)金額超過(guò)'C0002'客戶的總訂購(gòu)金額的客戶號(hào)，客戶名及其住址
Select cust_id, cust_name,addr
From customer
Where cust_id in (select cust_id from sales
Group by cust_id
Having sum(tot_amt)>
(Select sum(tot_amt) from sales where cust_id='C0002'))
52、 查詢業(yè)績(jī)最好的的業(yè)務(wù)員號(hào)、業(yè)務(wù)員名及其總銷售金額
select emp_no,emp_name,sum(tot_amt)
from employee a,sales b
where a.emp_no=b.sale_id
group by emp_no,emp_name
having sum(tot_amt)=
(select max(totamt)
from (select sale_id,sum(tot_amt) totamt
from sales
group by sale_id) c)
53、 查詢每位客戶所訂購(gòu)的每種產(chǎn)品的詳細(xì)清單，要求顯示出客戶號(hào)，客戶名，產(chǎn)品號(hào)，產(chǎn)品名，數(shù)量及單價(jià)
select a.cust_id, cust_name,c.prod_id,prod_name,qty, unit_price
from customer a,sales b, sale_item c ,product d
where a.cust_id=b.cust_id and b.order_no=c.order_no and c.prod_id=d.prod_id
54、 求各部門的平均薪水，要求按平均薪水從小到大排序
select dept,avg(salary)
from employee
group by dept
order by avg(salary)
