16、查找销售总额少于1000元的销售员编号、姓名和销售额
| select emp_no,emp_name,d.sale_sum from employee a, (select sale_id,sale_sum from (select sale_id, sum(tot_amt) as sale_sum from sales group by sale_id ) b where b.sale_sum <1000 ) d where a.emp_no=d.sale_id |
17、查找至少销售了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额
| select a.cust_id,cust_name,b.prod_id,prod_name,d.qty,d.qty*d.unit_price from customer a, product b, sales c, sale_item d where a.cust_id=c.cust_id and d.prod_id=b.prod_id and c.order_no=d.order_no and a.cust_id in ( select cust_id from (select cust_id,count(distinct prod_id) prodid from (select cust_id,prod_id from sales e,sale_item f where e.order_no=f.order_no) g group by cust_id having count(distinct prod_id)>=3) h ) |
18、查找至少与世界技术开发公司销售相同的客户编号、名称和商品编号、商品名称、数量和金额
| select a.cust_id,cust_name,d.prod_id,prod_name,qty,qty*unit_price from customer a, product b, sales c, sale_item d where a.cust_id=c.cust_id and d.prod_id=b.prod_id and c.order_no=d.order_no and not exists (select f.* from customer x ,sales e, sale_item f where cust_name='世界技术开发公司' and x.cust_id=e.cust_id and e.order_no=f.order_no and not exists ( select g.* from sale_item g, sales h where g.prod_id = f.prod_id and g.order_no=h.order_no and h.cust_id=a.cust_id) ) |
19、查找表中所有姓刘的职工的工号，部门，薪水
| select emp_no,emp_name,dept,salary from employee where emp_name like '刘%' |
20、查找所有定单金额高于2000的所有客户编号
| select cust_id from sales where tot_amt>2000 |
21、统计表中员工的薪水在4000-6000之间的人数
| select count(*)as 人数 from employee where salary between 4000 and 6000 |
22、查询表中的同一部门的职工的平均工资，但只查询"住址"是"上海市"的员工
| select avg(salary) avg_sal,dept from employee where addr like '上海市%' group by dept |
23、将表中住址为"上海市"的员工住址改为"北京市"
| update employee set addr like '北京市' where addr like '上海市' |
24、查找业务部或会计部的女员工的基本信息。
| select emp_no,emp_name,dept from employee where sex='F'and dept in ('业务','会计') |
25、显示每种产品的销售金额总和，并依销售金额由大到小输出。
| select prod_id ,sum(qty*unit_price) from sale_item group by prod_id order by sum(qty*unit_price) desc |