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

实验一 简单查询在订单数据库中，完成如下的查询：(1) 查询所有业务部门的员工姓名、职称、薪水。select employeeName,headShip,salaryfrom employeewhere department=业务科(2) 查询名字中含有“有限”的客户姓名和所在地。select CustomerName,addressfrom Customerwhere CustomerName like %有限%(3) 查询出姓“王”并且姓名的最后一个字为“成”的员工。select *from employeewhere employeeName like 王%成没有结果(4) 查询住址中含有上海或南昌的女员工，并显示其姓名、所属部门、职称、住址，其中性别用“男”和“女”显示。select employeeName,department,headship,address,sex= Case sex when Mthen 男 when Fthen 女 endfrom employeewhere address like%上海% or address like %南昌% and sex=F(5) 在订单明细表OrderDetail中挑出销售金额大于等于10000元的订单。select orderNofrom OrderDetailgroup by orderNohaving sum(quantity*price)=10000 (6) 选取订单金额最高的前10%的订单数据。SELECT TOP 10 PERCENT orderNoFROM OrderdetailGROUP BY orderNoORDER BY sum(quantity*price) DESC(7) 查询出职务为“职员”或职务为“科长”的女员工的信息。select *from employeewhere (headship=职员 or headship=科长) and sex=F(8) 查找定单金额高于8000的所有客户编号。 1）查询总金额高于8000元的客户编号select CustomerNofrom OrderMaster a,Orderdetail bwhere a.orderNo=b.orderNogroup by CustomerNohaving sum(quantity*price)8000 2）查找定单金额高于8000的所有客户编号 select CustomerNofrom OrderMaster where orderNo in ( select orderNo from OrderDetail group by orderNo having sum(quantity*price)8000 )(9) 选取编号界于“C20050001”和“C20050004”的客户编号、客户名称、客户地址。select CustomerNo,CustomerName,addressfrom Customerwhere CustomerNo between C20050001 and C20050004(11) 找出同一天进入公司服务的员工。Select a.employeeNo,a.employeeName,b.employeeNo,b.employeeNamefrom Employee a,Employee as bwhere a.employeeNo!=b.employeeNo and a.employeeNameb.employeeName and (a.hireDate=b.hireDate)(12) 在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。 1）首先计算订单主表的订单金额 update OrderMaster set orderSum=totalSumfrom OrderMaster a,(select orderNo,sum(quantity*price) totalSum from OrderDetail group by orderNo) bwhere a.orderNO=b.orderNo 2）SELECT *FROM OrderMasterWHERE orderSumany (SELECT orderSum FROM OrderMaster WHERE salerNo=E2005002 AND orderDate=20080109 )(13) 查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。Select customerNo,orderNo,orderSumfrom OrderMaster where customerNo in (select customerNo from OrderMaster a,OrderDetail b,Product c where a.orderNo=b.orderNo and ductNo=ductNo and productName=52倍速光驱) and customerNo in (select customerNo from OrderMaster a,OrderDetail b,Product c where a.orderNo=b.orderNo and ductNo=ductNo and productName=17寸显示器) (14) 查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。Select b.employeeName,b.sex,b.department,b.headShipfrom Employee a,Employee bwhere a.department=b.department and a.employeeName=陈诗杰(15) 查询每种商品的商品编号、商品名称、订货数量和订货单价。SELECT ductNo,productName,quantity,priceFROM Product a, OrderDetail bWHERE ductNo=ductNoORDER BY productName,price(16) 查询单价高于400元的商品编号、商品名称、订货数量和订货单价。SELECT ductNo,productName,quantity,priceFROM Product a, OrderDetail bWHERE ductNo=ductNo AND price400ORDER BY productName(17) 分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价，并分析比较检索的结果。左外连接命令：SELECT ductNo,productName,quantity,priceFROM Product a LEFT OUTER JOIN OrderDetail b ON ductNo=ductNoWHERE price400Select ductNo , ductName , b.quantity,b.priceFrom OrderDetail As b left JOIN Product As a ON (ductNo=ductNo) and price400Order by ductNo右外连接命令：SELECT ductNo,productName,quantity,priceFROM Product a RIGHT OUTER JOIN OrderDetail b ON ductNo=ductNoWHERE price400Select ductNo , ductName , b.quantity,b.priceFrom OrderDetail As b RIGHT JOIN Product As a ON (ductNo=ductNo) and price400Order by ductNo全连接命令：SELECT ductNo,productName,quantity,priceFROM Product a FULL OUTER JOIN OrderDetail b ON ductNo=ductNoWHERE price400Select ductNo , ductName , b.quantity,b.priceFrom OrderDetail As b full JOIN Product As a ON (ductNo=ductNo) and price400Order by ductNo(18)分别使用左外连接、右外连接、完整外部连接查询客户编号、客户名称、订货金额和订单编号，并分析比较检索的结果。SELECT a.customerNo,customerName,orderSum,orderNoFROM Customer a left OUTER JOIN OrderMaster b ON a.customerNo=b.customerNoSELECT a.customerNo,customerName,orderSum,orderNoFROM Customer a right OUTER JOIN OrderMaster b ON a.customerNo=b.customerNoSELECT a.customerNo,customerName,orderSum,orderNoFROM Customer a full OUTER JOIN OrderMaster b ON a.customerNo=b.customerNo从上述结果可知：若表a和表b做外连接，且b表是外码表，则a和b表左外连接可能会出现空值，但是右连接一定不会出现空值，全外连接与左外连接一样的结果。 (18) 查找每个员工的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期，其中性别使用“男”和“女”表示，日期使用“yyyy-mm-dd”格式显示。SELECT salerNo,employeeName, CASE sex WHEN MTHEN 男 WHEN FTHEN 女 END 性别, productName,quantity,price,orderSum, convert(char(10),orderDate,120) orderDateFROM Employee a,OrderMaster b,OrderDetail c,Product dWHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo AND ductNo=ductNoORDER BY salerNo(19) 查找在2008年3月中有销售记录的客户编号、名称和订单总额。SELECT a.customerNo,customerName,orderSumFROM OrderMaster a,Customer bWHERE year(orderDate)=2008 AND month(orderDate)=3 AND a.customerNo=b.customerNo (20) 使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额，其中订货日期不要显示时间，日期格式为“yyyy-mm-dd”，按客户编号排序，同一客户再按订单金额降序排序输出。SELECT a.customerNo,customerName,convert(char(10),orderDate,120) orderDate,orderSumFROM Customer a LEFT OUTER JOIN OrderMaster b ON a.customerNo=b.customerNoORDER BY a.customerNo,orderSum desc(21) 查找16M DRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用“男”、“女”表示。SELECT employeeName,CASE sex WHEN M THEN男 WHEN F THEN女 END sex, orderDate,quantity,quantity*price totalFROM Employee a,OrderMaster b,OrderDetail c,Product dWHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo AND ductNo=ductNo AND productName=16M DRAM(22) 查找每个人的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期。select a.employeeNo,a.employeeName,a.sex ,d.ProductName, quantity,price,price*quantity total,orderDatefrom Employee a,OrderMaster b,OrderDetail c,Product dwhere a.employeeNo=b.salerNo AND b.orderNo=c.orderNo AND c.ProductNo=d.ProductNo(23) 查询客户姓名为“客户丙”所购货物的“客户名称”、“定单金额”、“定货日期”和“电话号码”。SELECT CustomerName ,orderSum,orderDate,telephoneFROM Customer a,OrderMaster bWHERE a.CustomerNo=b.CustomerNo AND a.CustomerName=客户丙(24) 找出公司男业务员所接且订单金额超过2000元的订单号及订单金额。SELECT orderNo,orderSumFROM (SELECT orderNo,orderSum FROM OrderMaster a,Employee b WHERE a.salerNo=b.employeeNo AND b.sex=M) cWHERE ordersum2000或：SELECT orderNo,orderSumFROM OrderMaster a,Employee bWHERE a.salerNo=b.employeeNo AND b.sex=M and ordersum2000(25) 查询来自上海市的客户的姓名、电话、订单号及订单金额。SELECT CustomerName,telephone,orderNo,orderSumFROM Customer a,OrderMaster bWHERE a.CustomerNo=b.CustomerNo AND a.address LIKE %上海%(26) 实验问题连接操作类型有哪些？分析外连接在现实应用中的意义。答：连接运算包括内连接（等值连接，非等值连接，自然连接）外连接（左外连接，右外连接，全外连接），自表连接。外连接的意义：对于存在多对多的关系的两个关系，将关联不上的部分信息也能显示出来。在现实中对于数据库的数据初始化很有用。查询表可以用在什么地方？使用查询表要注意哪些地方？答：查询表用在from子句之后，必须为查询表设置一个元组变量名，用该名表示查询表。查询表是执行过程中产生的表，执行完后其表自动丢失。子查询也可以认为是一个查询表，但是不需要为他指定元组变量名。分析条件BETWEEN . AND、AND、OR等关键字的使用方法。答：BETWEEN . AND是范围查询，可用于查询属性值在某一个范围内的元组，NOT BETWEEN .AND 可用于查询属性值不在某个范围内的元组，BETWEEN后是属性的下限值，AND后是属性的上限值。AND、OR是逻辑查询，AND实现逻辑与运算，要注意的是在逻辑运算中，不可以对同一个属性进行逻辑“与”的等值运算OR实现逻辑或运算。用BETWEEN.AND能实现的查询，也可以用AND来达到。同样，用NOT BETWEENAND能实现的查询，也可以用OR来实现。请总结SQL语句中的单表查询语句的使用方法。查询所有列SELECT * FROM 表名2.查询指定列SELECT 字段列表 FROM 表名3. 消除重复元组：SELECT