0%）
select emp_name ,salary ,case
when salary<40000 then salary*0.0
when salary between 40000 and 49999 then salary*0.05
when salary between 50000 and 59999 then salary*0.07
when salary >=60000 then salary*0.10
end price
from employee
-- 查询公司中所有姓名有三个字的员工信息
select * from employee where rtrim(emp_name) like '___'
-- 生成公司销售的明细表 要求表中需要表现的信息为（定单号，销售员姓名，销售产品，供伙商名称，销售金额）
select b.order_no,emp_name,prod_name,sup_name,toa_amt
from sale_item a right join sales b on a.order_no=b.order_no
join product c on c.prod_id = a.prod_id join supply d on d.sup_id = a.sup_id
join employee e on e.emp_no=b.sale_id
-- 在采购明细表中查询 同类产品在不同时间进货差价 超过200元的产品及供货商名称
select product.prod_name,s1.sup_name,a.unit_price,a.pur_date,s2.sup_name,b.unit_price,b.pur_date
from pur_item a join pur_item b on a.pur_no=b.pur_no and a.unit_price - b.unit_price>200
join supply s1 on s1.sup_id=a.sup_id join supply s2 on s1.sup_id=s2.sup_id
join product on product.prod_id=a.prod_id where a.pur_date>b.pur_date
-- 查询在同一天进入公司的员工信息
select * from employee a join employee b on a.date_hired=b.date_hired
where a.emp_no>b.emp_no
--查询公司所有客户在公司的定货情况
select * from sales
right join customer on sales.cus_id=customer.cus_id
-- 查询由公司女业务员所接回的定单
select emp_name,order_no,cus_id,sale_id,toa_amt,order_date,ship_date,invoice_no,o_static from sales a
join employee b on a.sale_id=b.emp_no
where b.sex='F'
-- 查询公司中姓名相同的员工并按照员工编号显示员工信息 ******
select * from employee a
join employee b on a.emp_name=b.emp_name
where a.emp_no order by a.emp_no
-- 查询公司中目前业绩还没有超过2万的业务员 ********
select emp_name from sales
left join employee on emp_no=sale_id
group by emp_name
having sum(toa_amt)<20000
select * from sales
-- 查询仓库中还没有销售过的产品信息
select * from stock a
left join sale_item c on c.prod_id=a.prod_id
where c.order_no is null
select * from sale_item
select * from product
select * from stock
-- 查询公司员工的平均年龄
select avg(datediff(yy,birthday,getdate()))'平均年龄'
from employee
-- 查询没有在公司订购产品的客户名单
select cus_name from customer a
left join sales b on a.cus_id=b.cus_id
where b.order_no is null
select * from customer
select * from sales
-- 按照供货商来统计公司的销售榜
select sup_name,isnull (sum(qty*unit_price),0) s from sale_item
right join supply on supply.sup_id=sale_item.sup_id
group by sup_name
order by s desc
====================================================================================
2013.12.02 晚
|