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

《数据库系统原理与设计实验教程实验.doc》由会员分享，可在线阅读，更多相关《数据库系统原理与设计实验教程实验.doc（14页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、实验一 简单查询在订单数据库中，完成如下的查询：(1) 查询所有业务部门的员工姓名、职称、薪水。select employeeName,headShip,salaryfrom employeewhere department=业务科(2) 查询名字中含有“有限”的客户姓名和所在地。select CustomerName,addressfrom Customerwhere CustomerName like %有限%(3) 查询出姓“王”并且姓名的最后一个字为“成”的员工。select *from employeewhere employeeName like 王%成没有结果(4) 查询住址中含

2、有上海或南昌的女员工，并显示其姓名、所属部门、职称、住址，其中性别用“男”和“女”显示。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(

3、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

4、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) 选取编号界于“C”和“C”的客户编号、客户名称、客户地址。select Cu

5、stomerNo,CustomerName,addressfrom Customerwhere CustomerNo between C and C(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) 在订单主表中查询订单金额大于“E业

6、务员在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

7、salerNo=E AND orderDate= )(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 b.productNo=c.productNo and productName=52倍速光驱) and custome

8、rNo in (select customerNo from OrderMaster a,OrderDetail b,Product c where a.orderNo=b.orderNo and b.productNo=c.productNo and productName=17寸显示器) (14) 查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。Select b.employeeName,b.sex,b.department,b.headShipfrom Employee a,Employee bwhere a.department=b.department and a.em

9、ployeeName=陈诗杰(15) 查询每种商品的商品编号、商品名称、订货数量和订货单价。SELECT a.productNo,productName,quantity,priceFROM Product a, OrderDetail bWHERE a.productNo=b.productNoORDER BY productName,price(16) 查询单价高于400元的商品编号、商品名称、订货数量和订货单价。SELECT a.productNo,productName,quantity,priceFROM Product a, OrderDetail bWHERE a.product

10、No=b.productNo AND price400ORDER BY productName(17) 分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价，并分析比较检索的结果。左外连接命令：SELECT a.productNo,productName,quantity,priceFROM Product a LEFT OUTER JOIN OrderDetail b ON a.productNo=b.productNoWHERE price400Select a.productNo , a.productName , b.quantity,b

11、.priceFrom OrderDetail As b left JOIN Product As a ON (a.productNo=b.productNo) and price400Order by a.productNo右外连接命令：SELECT a.productNo,productName,quantity,priceFROM Product a RIGHT OUTER JOIN OrderDetail b ON a.productNo=b.productNoWHERE price400Select a.productNo , a.productName , b.quantity,b.

12、priceFrom OrderDetail As b RIGHT JOIN Product As a ON (a.productNo=b.productNo) and price400Order by a.productNo全连接命令：SELECT a.productNo,productName,quantity,priceFROM Product a FULL OUTER JOIN OrderDetail b ON a.productNo=b.productNoWHERE price400Select a.productNo , a.productName , b.quantity,b.pr

13、iceFrom OrderDetail As b full JOIN Product As a ON (a.productNo=b.productNo) and price400Order by a.productNo(18)分别使用左外连接、右外连接、完整外部连接查询客户编号、客户名称、订货金额和订单编号，并分析比较检索的结果。SELECT a.customerNo,customerName,orderSum,orderNoFROM Customer a left OUTER JOIN OrderMaster b ON a.customerNo=b.customerNoSELECT a.cu

14、stomerNo,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表左外连接可能会出现空值，但是右连接一定不会出现空值，全外连接与左

15、外连接一样的结果。 (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,Produc

16、t dWHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo AND c.productNo=d.productNoORDER 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) 使用左外连接查找每个客

17、户的客户编号、名称、订货日期、订单金额，其中订货日期不要显示时间，日期格式为“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的销售情况，要求显示相应的销售员的

18、姓名、性别，销售日期、销售数量和金额，其中性别用“男”、“女”表示。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 c.productNo=d.productNo AND productName=16M DRAM(22)

19、查找每个人的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期。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) 查询客户姓名为“客户丙”所

20、购货物的“客户名称”、“定单金额”、“定货日期”和“电话号码”。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=

21、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.

22、address LIKE %上海%(26) 实验问题连接操作类型有哪些？分析外连接在现实应用中的意义。答：连接运算包括内连接（等值连接，非等值连接，自然连接）外连接（左外连接，右外连接，全外连接），自表连接。外连接的意义：对于存在多对多的关系的两个关系，将关联不上的部分信息也能显示出来。在现实中对于数据库的数据初始化很有用。查询表可以用在什么地方？使用查询表要注意哪些地方？答：查询表用在from子句之后，必须为查询表设置一个元组变量名，用该名表示查询表。查询表是执行过程中产生的表，执行完后其表自动丢失。子查询也可以认为是一个查询表，但是不需要为他指定元组变量名。分析条件BETWEEN . AN

23、D、AND、OR等关键字的使用方法。答：BETWEEN . AND是范围查询，可用于查询属性值在某一个范围内的元组，NOT BETWEEN .AND 可用于查询属性值不在某个范围内的元组，BETWEEN后是属性的下限值，AND后是属性的上限值。AND、OR是逻辑查询，AND实现逻辑与运算，要注意的是在逻辑运算中，不可以对同一个属性进行逻辑“与”的等值运算OR实现逻辑或运算。用BETWEEN.AND能实现的查询，也可以用AND来达到。同样，用NOT BETWEENAND能实现的查询，也可以用OR来实现。请总结SQL语句中的单表查询语句的使用方法。查询所有列SELECT * FROM 表名2.查询

24、指定列SELECT 字段列表 FROM 表名3. 消除重复元组：SELECT Distinct 字段列表 FROM 表名4.查询经过计算的列SELECT 【字段及字段表达式】 FROM 【表名】5. 指定查询结果的查询SELECT TOP n PERCENT 【字段列表】 FROM 【表名】6.给属性取别名：SELECT 【字段名】1 as A FROM 【表名】：SELECT A=字段名1 FROM 【表名】：SELECT 字段名1 A FROM 【表名】7.选择查询SELECT 【字段名列表】 FROM 【表名】 WHERE 【条件表达式】条件表达式运算符：比较运算：=,=,!,!=,逻辑

25、查询：or,AND,not范围查询：between AND,not between AND集合查询：IN,not IN字符匹配查询：ike,not LIKE空值查询：is null,is not null8.排序查询SELECT 【字段名1】，【字段名2】 FROM 【表名】 WHERE 【条件表达式】 ORDER BY 【字段表达式1】，【字段表达式2】 ASCDESC9.分组查询SELECT 字段名列表 FROM 表名 WHERE 条件表达式 GROUP BY 字段名 SELECT 字段名列表 FROM 表名 WHERE条件表达式GROUP BY 字段名 HAVING 筛选表达式10.聚合查询SELECT count（ALL| DISTINCT*|字段名列表） FROM 表名 WHERE 条件表达式SELECT sum| avg| max| min (ALL| DISTINCT） FROM 表名WHERE 条件表达式分析哪几种情况需要使用自表连接。当同表中的某个元组变量与表中的另外的元组变量相关时，可以使用自表连接，从而得到一些特殊的数据或者是说带特定条件的数据。自表连接一般是用来判断并筛选单一表中的一些数据。