数据库实验报告一SQL查询.doc

(1) 查询员工的姓名、职务和薪水。 select employeename,employeeno,salary from Employee (2) 查询名字中含有“有限”的客户名称和所在地。 select customerno,address from customer where customername like %有限% (3) 查询出姓“张”并且姓名的最后一个字为“梅”的员工。 select * from employee where employeename like 张_梅 (4) 查询住址中含有“上海”或“南昌”的女员工，并显示其姓名、所属部门、职称、住址、出生日期和性别，其中如果为空，显示select employeename,department,headship,address,birthday=case birthday when null then不详 end,sex=case sex when Mthen 男 when Fthen 女 end from employee where address like %上海% or address like %南昌%(5) 查询出职务为“职员”或职务为“科长”的女员工的信息。 select * from employee where headship like 职员 or headship like 科长 (6) 选取编号不在间的客户编号、客户名称、客户地址。 select customerno,customername,address from customer where customerno not between and (7) 在表OrderMaster中挑出销售金额大于等于5000元的订单。 先统计订单主表中的订单金额，使用命令： update OrderMaster set orderSum=sum2 from OrderMaster a,(select orderNo,sum(quantity*price) sum2 from OrderDetail group by orderNo) b where a.orderNo=b.orderNo select a.orderno,b.sum2 from OrderMaster a,(select orderNo,sum(quantity*price) sum2 from OrderDetail group by orderNo) b where a.orderNo=b.orderNo and b.sum2=5000 (8) 选取订单金额最高的前10%的订单数据。 select top 10 percent a.orderno,b.sum2 from OrderMaster a,(select orderNo,sum(quantity*price) sum2 from OrderDetail group by orderNo) b where a.orderNo=b.orderNo (9) 计算出一共销售了几种商品。 select count(*)商品总数 from(select orderno from orderdetail group by orderno)a (10) 计算OrderDetail表中每种商品的销售数量、平均销售单价和总销售金额，并且依据销售金额由大到小排序输出。 select productno,sum(quantity)销售数量,avg(price)平均价格,sum(quantity*price)销售金额 from orderdetail group by productno order by sum(quantity*price) desc (11) 按客户编号统计每个客户2008年2月的订单总金额。 select customerno,sum(ordersum) from ordermaster where year(orderdate)=2008 and month(orderdate)=2 group by customerno (12) 统计至少销售了10件以上的商品编号和销售数量。 select productno,sum(quantity)sum