实验五--数据查询——复杂查询
数据库实验 复杂查询
实验4 复杂查询一.实验目的：1.掌握SQL语句中的group by 子句。
2.掌握SQL语句中的子查询。
3.掌握SQL语句中的order by 子句。
二.实验内容：1.在自己的文件夹下建立一个“教学数据库”，表如下：2.S（学生表）C（课程表）SC（成绩表）S（学生表）SNO SN SEX AGE DEPT S1 赵亦女17 计算机S2 钱尔男18 信息S3 孙珊女20 信息S4 李思男21 自动化S5 周武男19 计算机S6 孙丽女20 自动化C（课程表）CNO CN CTC1 程序设计60C2 微机原理80C3 数字逻辑60C4 数据结构80C5 数据库60C6 编译原理60C7 操作系统60SC（成绩表）SNO CNO SCORES1 C1 90S1 C2 85S2 C5 57S2 C6 80S2 C7S2 C4 70S3 C1 75S3 C2 70S3 C4 85S4 C1 93S4 C2 85S4 C3 83S5 C2 893.利用SQL语句完成下列查询：(1)查询全体学生的学号、姓名、课程名和成绩。
(2)查询选修了课程的学生的人数。
(3)查询选修C1或C2且分数大于等于85分学生的姓名、课程名和成绩。
(4)查询所有姓孙的学生的学号和姓名。
(5)查询没有考试成绩的学生的学号、姓名和课程名。
(6)查询选修C1课程的最高分，显示课程号、课程名和最高分。
(7)查询每个学生的学号、姓名和平均成绩，按平均成绩的降序排列。
(8)查询比孙珊同学所选修课程最高成绩还高的学生的姓名、课程名和成绩。
(9)查询至少选修两门课程学生的学号、姓名和选课门数。
(10)查询考试成绩不及格学生的姓名、课程名和成绩。
实验5 数据查询
实验五数据查询目的和要求1．了解查询的概念和方法；2．掌握查询分析器的使用方法；3．掌握SELECT语句在单表查询中的应用；4．掌握复杂查询的使用方法；5．掌握多表连接的方法；6．掌握SELECT语句在多表查询中的应用。
内容和步骤一、单表查询1．简单查询打开查询分析器，从teacher表中分别检索出教师的所有信息，以及仅查询教工号、姓名和职称。
语句及查询结果如图5.1所示：`select * from teacherselect 教工号,姓名from teacher如要查询时改变列标题的显示，则从te acher表中分别检索出教师教工号、姓名、家庭住址信息并分别加上‘教师姓名’、‘教工号’、‘家庭住址’等标题信息，语句及查询结果如下：使用TOP关键字：分别从teacher中检索出前2条及前面67%的教师的信息。
语句及查询结果如下：select top 2 * from teacherselect top 67 percent * from teacher使用DISTINCT关键字：从teacher表中检索出教师的职称并且要求显示的职称不重复。
语句及查询结果如下：select distinct 职称from teacher6．用计算列：，为teacher表中架一个工资字段，并输入几个值，将teacher表中各教师的姓名、教工号及工资按95%发放的信息，第2条语句将工资按95%发放后列名该为‘预发工资’。
语句如下：select 教工号,姓名,工资*0.95 from teacherselect 教工号,姓名,工资*0.95 AS 预发工资from teacher7．使用ORDER BY子句对查询的结果进行排序使用ORDER BY语句可以对查询的结果进行排序，ASC、DESC分别是升序和降序排列的关键字，系统默认的是升序排列。
从teacher表中查询工资大于2800的教师的教工号、姓名，并按升序排列，语句及查询结果如下：select 教工号,姓名from teacherwhere 工资>2800 order by 工资ASC2．条件查询⑴使用关系运算符：从teacher表中查询出教工号小于2130的教师资料，语句如下：select * from teacher where 教工号<2130⑵使用BETWEEN AND谓词：从teacher表中查询出教工号界于2100和2130之间的教师资料，语句如下：select * from teacher where 教工号 between 2100 and 2130⑶使用IN谓词：从teacher表中查询出职称为“教授”或“副教授”的教师的教工号、教师姓名、职称及家庭住址，语句如下：select 教工号,姓名,家庭住址,职称 from teacherwhere 职称 in ('教授','副教授')⑷使用LIKE谓词：从teacher表中分别检索出姓伍的教师的资料，或者姓名的第2个字是寿或立的教师的资料，语句如下：select * from teacher where 姓名 like '伍%'select * from teacher where 姓名 like '_[寿，立]%'二、多表查询数据库各表中存放着不同的数据，用户经常需要用多个表中的数据来组合提炼出所需要的信息，如果一个查询需要对多个表进行操作，就称为关联查询，关联查询的结果集或结果表称为表之间的连接。
数据库实验报告 复杂数据查询
七、教师评语与成绩评定：
4
5
（19）查询所开课程的选修情况，包括没有任何学生选修的课程。 SELECT 学号, 课程名, 分数 FROM 成绩 A RIGHT OUTER JOIN 课程 B ON (A.课程编号=B.课程编号)
（20） 查询研究生与其导师的情况。 SELECT * FROM 研究生 A INNER JOIN 教师 B ON A.导师号= B.教工号 也可以写成等价的 SQL 语句： SELECT * FROM 研究生 A,导师 B WHERE A.导师号= B.教工号
（21）查询教师指导研究生的情况，包括不指导研究生的教师。
3
SELECT * FROM 导师 A LEFT OUTER JOIN 研究生 B ON (A.教工号= B.导师) 三、实验平台： Microsoft SQL Sever 四、设计流程： 1、进入查询分析器，参见进入查询分析器演示。 2、在查询分析器的命令编辑窗口输入 SQL 语句，点击执行键 ►，将执行 SQL 语 句。参见执行 SQL 语句的演示。 3、在调试程序过程中，可以用鼠标选择某些要执行的 SQL 语句，再点击执行键， 执行选择的语句。参见执行 SQL 语句的演示。 4、 注意：在各个表中插入数据的顺序，首先在父表中插入数据，然后在子表中 插入数据。另外，由于表的定义中包含完整性约束的定义，所以，当主码重 复，或者外码不是被参照表的有效值时，系统将拒绝插入的数据。 5、保存调试通过的 SQL 程序。 五、程序清单：
（11）根据学生、课程和成绩表，输出“计算机”课程的成绩单，包括姓名和 分数。 SELECT 姓名，分数 FROM 学生 A JOIN 成绩 B ON（A.学号=B.学号） JOIN 课程 C ON（B.课程编号=C.课程编号） WHERE C.课程名称=’计算机’
实验五-复杂查询-实验报告
实验五复杂查询1 实验目的与要求(1) 熟练掌握SQL语句的使用。
(2) 熟练使用SQL语句进行连接操作。
2 实验内容(1)在订单明细表中查询订单金额最高的订单。
select a.*from orderdetail a,ordermaster bwhere a.orderno=b.orderno and ordersum=(select max(ordersum)from ordermaster)(2)找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。
select productno 商品编号,orderno 订单编号,quantity 订货数量,quantity*price 订货金额from orderdetailwhere productno in(select productnofrom orderdetailgroup by productnohaving count(*)>=3)order by quantity desc(3)查找销售总额少于5000元的销售员编号、姓名和销售额。
select a.salerno 销售员编号,b.employeename 姓名,sum(ordersum)销售额from ordermaster a,employee bwhere a.salerno=b.employeenogroup by a.salerno,b.employeenamehaving sum(ordersum)<5000(4)找出目前业绩未超过5000元的员工，并按销售业绩的降序排序输出。
select salerno 销售员编号,sum(ordersum)销售业绩from ordermastergroup by salernohaving sum(ordersum)<=5000order by sum(ordersum)desc(5)查询订购的商品数量没有超过10个的客户编号和客户名称。
数据库实验5答案
实验五：数据库综合查询一、实验目的1.掌握SELECT语句的基本语法和查询条件表示方法；2.掌握查询条件种类和表示方法；3.掌握连接查询的表示及使用；4.掌握嵌套查询的表示及使用；5.了解集合查询的表示及使用。
二、实验环境已安装SQL Server企业版的计算机(120台)；具有局域网环境,有固定IP;三、实验学时2学时四、实验要求1.了解SELECT语句的基本语法格式和执行方法；2.了解连接查询的表示及使用；3.了解嵌套查询的表示及使用；4.了解集合查询的表示及使用；5.完成实验报告；五、实验内容及步骤1.利用Transact-SQL嵌套语句实现下列数据查询操作。
1) 查询选修了计算机体系结构的学生的基本信息。
select*from studentwhere Sno in(select Sno from coursewhere Cno in(select Cno from sc where Cname='计算机体系结构'))2) 查询年龄比李勇小的学生的学号和成绩。
select a.sno,grade from student a,coursewhere a.sno=course.sno and sage<(select sage from student where sname='李勇')3) 查询其他系中比系编号为‘D1’的学生中年龄最小者要大的学生的信息。
select*from student where dnum<>'D1'AND SAGE>(select min(sage)from student where dnum='D1')4) 查询其他系中比系编号为‘D3’的学生年龄都大的学生的姓名。
select*from student where dnum<>'D3'AND SAGE>all(selectsage from student where dnum='D3')5) 查询‘C1’课程的成绩高于70的学生姓名。
数据库 实验五
数据库实验五在学习数据库的过程中，实验是巩固理论知识、提高实践能力的重要环节。
本次实验五主要围绕数据库的某些关键操作和应用展开，通过实际动手操作，让我们对数据库的理解更加深入和全面。
本次实验的环境是常见的数据库管理系统，如 MySQL 或 SQL Server 等。
实验的目的是让我们熟练掌握数据库的查询、更新、插入和删除等基本操作，同时能够运用这些操作解决一些实际的问题。
实验的第一个任务是进行数据的查询操作。
查询是从数据库中获取所需信息的重要手段。
我们需要根据给定的条件，从数据表中筛选出符合要求的数据。
这就涉及到了使用 WHERE 子句来设定条件，以及各种运算符如等于（＝）、大于（＞）、小于（＜）、不等于（＜＞）等的运用。
同时，还需要掌握连接（JOIN）操作，将多个相关的数据表连接起来，以获取更全面和准确的信息。
例如，在一个学生成绩管理系统中，要查询某个班级中数学成绩大于 80 分的学生名单，就需要先从学生表中获取班级信息，再从成绩表中筛选出数学成绩符合条件的记录，最后通过学生学号将两个表连接起来，得到最终的结果。
接下来是数据的更新操作。
这包括对已有数据的修改和删除。
在进行更新操作时，必须格外小心，因为一旦操作失误，可能会导致数据的丢失或错误。
在修改数据时，同样要使用 WHERE 子句来指定要修改的记录。
例如，要将某个学生的数学成绩从 80 分修改为 90 分，就需要明确指定该学生的学号或其他唯一标识。
而删除数据则需要更加谨慎，通常建议先进行查询，确认要删除的记录准确无误后，再执行删除操作。
数据的插入操作也是实验的重要部分。
插入新的数据可以增加数据库的信息量。
在插入数据时，需要注意数据的类型和格式要与数据表的定义相匹配。
比如，一个学生信息表中，学号是整数类型，姓名是字符串类型，出生日期是日期类型，如果插入的数据类型不正确，就会导致插入失败。
在实验过程中，我也遇到了一些问题和挑战。
比如，在进行复杂的查询操作时，由于条件设置不当，导致查询结果不准确。
数据库实验报告 数据查询
FROM学生
（20）查询学生的总人数。
SELECT COUNT(*)AS人数
FROM学生
2、复杂查询
（21）如果要统计成绩表中所有选课的学生人数，即不管一名学生选了几门课程，都只计算一次
SELECT COUNT (DISTINCT (学号)) AS人数
（12）列出教工中比姜立伟工资低的姓名和工资。
SELECT R.姓名,R.工资
FROM教工R,教工S
WHERE R.工资<S.工资AND S.姓名='姜立伟'
（13）查出所有不是教授的教工姓名。
SELECT姓名
FROM教工
WHERE职称NOT LIKE '教授'
（14）检索工资在1000元到2000元范围内的职工信息。
SELECT *
FROM教工
WHERE职称='教授'
（3）列出教工表中的系编号并消除重复的元组。
SELECT DILeabharlann TINCT系编号FROM教工
（4）已知学分=学时/17，计算每一门课程的学分数。
SELECT课程名称,学时/17 AS学分
FROM课程
（5）显示教授的工资和提高10%的工资额。
SELECT姓名,工资,工资*1.1 AS '工资*1.1'
(32)统计无先修课的课程的学时总数；
(33)统计每位学生选修课程的门数、学分及其平均成绩；
(34)统计选修每门课程的学生人数及各门课程的平均成绩；
(35)找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排
列；
(36)查询选修了“1”或“2”号课程的学生学号和姓名；
实验五--数据查询——复杂查询
实验五数据查询——复杂查询一、实验目的1．掌握SQLServer查询语句的基本语法2．熟练使用SQL的Select语句对多表进行查询3．熟练掌握并运用SQLServer所提供的函数4．熟练使用SQL语句进行复杂的连接操作二、实验环境（实验的软件、硬件环境）硬件：PC机软件：SQL2000三、实验指导说明请复习相关的查询知识点并完成如下内容。
四、实验内容1．在订单数据库orderDB中，完成如下的查询：（1）用子查询查询员工“张小娟”所做的订单信息。
（2）查询没有订购商品的且在北京地区的客户编号，客户名称和邮政编码，并按邮政编码降序排序。
（3）查询订购了“32M DRAM”商品的订单编号，订货数量和订货单价。
（4）查询与员工编号“E2008005”在同一个部门的员工编号，姓名，性别，所属部门。
（5）查询既订购了P2*******商品，又订购了P2*******商品的客户编号，订单编号和订单金额（6）查询没有订购“52倍速光驱”或“17寸显示器”的客户编号，客户名称。
（7）查询订单金额最高的订单编号，客户姓名，销售员名称和相应的订单金额。
（8）查询订购了“52倍速光驱”商品的订购数量，订购平均价和订购总金额。
（9）查询订购了“52倍速光驱”商品且订货数量界于2~4之间的订单编号，订货数量和订货金额。
（10）在订单主表中查询每个业务员的订单数量（11）统计在业务科工作且在1973年或1967年出生的员工人数和平均工资。
（12）在订单明细表中统计每种商品的销售数量和金额，并按销售金额的升序排序输出。
（13）统计客户号为“C20050001”的客户的订单数，订货总额和平均订货金额（14）统计每个客户的订单数，订货总额和平均订货金额。
（15）查询订单中至少包含3种（含3种）以上商品的订单编号及订购次数，且订购的商品数量在3件（含3件）以上。
（16）查找订购了“32M DRAM”的商品的客户编号，客户名称，订货总数量和订货总金额。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
实验五数据查询——复杂查询一、实验目的1．掌握SQLServer查询语句的基本语法2．熟练使用SQL的Select语句对多表进行查询3．熟练掌握并运用SQLServer所提供的函数4．熟练使用SQL语句进行复杂的连接操作二、实验环境（实验的软件、硬件环境）硬件：PC机软件：SQL2000三、实验指导说明请复习相关的查询知识点并完成如下内容。
四、实验内容1．在订单数据库orderDB中，完成如下的查询：（1）用子查询查询员工“张小娟”所做的订单信息。
（2）查询没有订购商品的且在北京地区的客户编号，客户名称和邮政编码，并按邮政编码降序排序。
（3）查询订购了“32M DRAM”商品的订单编号，订货数量和订货单价。
（4）查询与员工编号“E2008005”在同一个部门的员工编号，姓名，性别，所属部门。
（5）查询既订购了P2*******商品，又订购了P2*******商品的客户编号，订单编号和订单金额（6）查询没有订购“52倍速光驱”或“17寸显示器”的客户编号，客户名称。
（7）查询订单金额最高的订单编号，客户姓名，销售员名称和相应的订单金额。
（8）查询订购了“52倍速光驱”商品的订购数量，订购平均价和订购总金额。
（9）查询订购了“52倍速光驱”商品且订货数量界于2~4之间的订单编号，订货数量和订货金额。
（10）在订单主表中查询每个业务员的订单数量（11）统计在业务科工作且在1973年或1967年出生的员工人数和平均工资。
（12）在订单明细表中统计每种商品的销售数量和金额，并按销售金额的升序排序输出。
（13）统计客户号为“C20050001”的客户的订单数，订货总额和平均订货金额（14）统计每个客户的订单数，订货总额和平均订货金额。
（15）查询订单中至少包含3种（含3种）以上商品的订单编号及订购次数，且订购的商品数量在3件（含3件）以上。
（16）查找订购了“32M DRAM”的商品的客户编号，客户名称，订货总数量和订货总金额。
（17）查询每个客户订购的商品编号，商品所属类别，商品数量及订货金额，结果显示客户名称，商品所属类别，商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出。
（18）按商品类别查询每类商品的订货平均单价在280元（含280元）以上的订货总数量，订货平均单价和订货总金额。
（19）查找至少有2次销售的业务员名称和销售日期。
（20）查询销售金额最大的客户名称和总货款额（21）查找销售总额小于5000元的销售员编号，姓名和销售额（22）查找至少订购了3种商品的客户编号，客户名称，商品编号，商品名称，数量和金额。
（23）查找同时订购了商品为“P2*******”和商品编号为“P2*******”的商品的客户编号，客户姓名，商品编号，商品名称和销售数量，按客户编号排序输出。
（24）计算每一商品每月的销售金额总和，并将结果首先按销售月份然后按订货金额降序排序输出。
（25）查询订购了“键盘”商品的客户姓名，订货数量和订货日期（26）查询每月订购“键盘”商品的客户名称。
（27）查询至少销售了5种商品的销售员编号，姓名，商品名称，数量及相应的单价，并按销售员编号排序输出。
（28）查询没有订购商品的客户编号和客户名称。
（29）查询至少包含了“世界技术开发公司”所订购的商品的客户编号，客户名称，商品编号，商品名称，数量和金额。
五、实验步骤请完成实验内容，并写出具体的实验步骤（1）用子查询查询员工“张小娟”所做的订单信息。
select orderMaster.*from employee,orderMaster where orderMaster.SaleNo=employeeNo and employeeName in(select employee.employeeName from employee where employeeName='张小娟')（2）查询没有订购商品的且在北京地区的客户编号，客户名称和邮政编码，并按邮政编码降序排序。
select distinct customer.CustomerNo,customer.customerName,customer.Zip from customer,orderMaster where customer.customerNo not in(select orderMaster.customerNo from orderMaster)and customer.addr='北京市'order by customer.Zip desc（3）查询订购了“32M DRAM”商品的订单编号，订货数量和订货单价。
select orderDetail.OrderNo,orderDetail.Qty,orderDetail.Price from orderDetail,product where orderDetail.ProductNo=product.ProductNo and ProductName='32M DRAM'（4）查询与员工编号“E2008005”在同一个部门的员工编号，姓名，性别，所属部门。
select employee.employeeNo,employee.employeeName,case employee.Sexwhen'M'then'男'when'F'then'女'end,employee.Department from employee where employee.Department=(select employee.Department from employee whereemployee.employeeNo='E2008005')（5）查询既订购了P2*******商品，又订购了P2*******商品的客户编号，订单编号和订单金额select customer.customerno,ordermaster.orderno,ordermaster.ordersum from ordermaster,customer,orderdetail where customer.customerno=ordermaster.customerno and ordermaster.orderno=orderdetail.orderno and orderdetail.productno='P2007002'intersectselect customer.customerno,ordermaster.orderno,ordermaster.ordersum from ordermaster,customer,orderdetail where customer.customerno=ordermaster.customerno and ordermaster.orderno=orderdetail.orderno and orderdetail.productno='P2005001'（6）查询没有订购“52倍速光驱”或“17寸显示器”的客户编号，客户名称。
select distinct customer.customerno,customer.customername from customer,orderdetail,ordermaster,product where orderdetail.orderno=ordermaster.orderno and ordermaster.customerno=customer.customerno and orderdetail.productno=product.productno and product.productname notin('52倍速光驱','17寸显示器')（7）查询订单金额最高的订单编号，客户姓名，销售员名称和相应的订单金额。
selectordermaster.orderno,customer.customername,employee.employeename,ord ermaster.ordersum from employee,ordermaster,customer where employee.employeeno=ordermaster.saleno and customer.customerno=ordermaster.customerno and ordermaster.ordersum=(select MAX (ordermaster.ordersum)from ordermaster)（8）查询订购了“52倍速光驱”商品的订购数量，订购平均价和订购总金额。
select SUM(orderdetail.qty)as'订购数量',A VG(ordermaster.ordersum)as'订购平均价',SUM(ordermaster.ordersum)as'订购总金额'from orderdetail,ordermaster,product where orderdetail.orderno=ordermaster.orderno and orderdetail.productno=product.productno and product.productname='52倍速光驱'（9）查询订购了“52倍速光驱”商品且订货数量界于~4之间的订单编号，订货数量和订货金额。
select orderdetail.orderno,orderdetail.qty,ordermaster.ordersum from ordermaster,orderdetail,product whereorderdetail.orderno=ordermaster.orderno and product.productno=orderdetail.productno and product.productname='52倍速光驱'and orderdetail.qty between 2 and 4（10）在订单主表中查询每个业务员的订单数量select ordermaster.saleno,COUNT(*)as'订单数量'from ordermaster group by saleno（11）统计在业务科工作且在年或年出生的员工人数和平均工资。