数据库系统原理设计实验教程实验1.docVIP

实验一 简单查询 在订单数据库中，完成如下的查询： (1) 查询所有业务部门的员工姓名、职称、薪水。 select employeeName,headShip,salary from employee where department=业务科 (2) 查询名字中含有“有限”的客户姓名和所在地。 select CustomerName,address from Customer where CustomerName like %有限% (3) 查询出姓“王”并且姓名的最后一个字为“成”的员工。 select * from employee where employeeName like 王%成 没有结果 (4) 查询住址中含有上海或南昌的女员工，并显示其姓名、所属部门、职称、住址，其中性别用“男”和“女”显示。 select employeeName,department,headship,address, sex= Case sex when Mthen 男 when Fthen 女 end from employee where address like%上海% or address like %南昌% and sex=F (5) 在订单明细表OrderDetail中挑出销售金额大于等于10000元的订单。 select orderNo from OrderDetail group by orderNo having sum(quantity*price)=10000 (6) 选取订单金额最高的前10%的订单数据。 SELECT TOP 10 PERCENT orderNo FROM Orderdetail GROUP BY orderNo ORDER BY sum(quantity*price) DESC (7) 查询出职务为“职员”或职务为“科长”的女员工的信息。 select * from employee where (headship=职员 or headship=科长) and sex=F (8) 查找定单金额高于8000的所有客户编号。 1）查询总金额高于8000元的客户编号 select CustomerNo from OrderMaster a,Orderdetail b where a.orderNo=b.orderNo group by CustomerNo having sum(quantity*price)8000 2）查找定单金额高于8000的所有客户编号 select CustomerNo from OrderMaster where orderNo in ( select orderNo from OrderDetail group by orderNo having sum(quantity*price)8000 ) (9) 选取编号界于“和“的客户编号、客户名称、客户地址。 select CustomerNo,CustomerName,address from Customer where CustomerNo between and (11) 找出同一天进入公司服务的员工。 Select a.employeeNo,a.employeeName,b.employeeNo,b.employeeName from Employee a,Employee as b where a.employeeNo!=b.employeeNo and a.employeeNameb.employeeName and (a.hireDate=b.hireDate) (12) 在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。 1）首先计算订单主表的订单金额 update OrderMaster set orderSum=totalSum from OrderMaster a,(select orderNo,sum(quantity*price) totalSum from OrderDetail group by orderNo) b where a.orderNO=b.orderNo 2） SEL