淮海工学院计算机工程学院实验报告书课程名:《数据库原理及应用》题目:T-SQL高级应用班级:软件132学号:***********名:***一.目的与要求1.掌握复杂查询的使用方法;2.掌握多表连接的方法;3.掌握SELECT语句在多表查询中的应用。
4.掌握数据汇总、外连接查询的使用方法;5.掌握事务定义的一般方法二.实验内容1.给出教材14-16的程序运行结果。
2.编写程序,查询“1033”班的最高分的学生的学号、姓名、班号、课程号和分数。
3.编写程序,创建一个新表,包含所有学生的姓名、课程名和分数,并以姓名排序。
4.编写程序,输出每个班最高分的课程名和分数。
5.在上次实验建立的factory数据库的基础上,完成如下各题:●删除factory数据库上各个表之间建立的关系;●显示各职工的工资记录和相应的工资小计。
●按性别和部门名的所有组合方式列出相应的平均工资。
●在worker表中使用以下语句插入一个职工记录:insert into worker values(20,’陈立’,‘女’,’55/03/08’,1,’75/10/10’,4),在depart表中使用以下语句插入一个部门记录:insert into depart values(5,’设备处’)。
再对worker和depart表进行全外连接显示职工的职工号,姓名和部门名。
然后删除这两个插入的记录。
●显示最高工资的职工的职工号、姓名、部门名、工资发放日期和工资。
●显示最高工资的职工所在的部门名。
●显示所有平均工资低于全部职工平均工资的职工的职工号和姓名。
●先显示worker表中的职工人数,开始一个事务,插入一个职工记录,再显示worker表中的职工人数,回滚该事务,最后显示worker表中的职工人数。
三.实验步骤删除factory数据库上各个表之间建立的关系的操作步骤如下:①启动SQL Server管理控制器。
②在“对象资源管理器”中展开LCB-PC服务器节点。
③展开“数据库”节点。
④选中,将其展开。
⑤展开“数据库关系图”节点。
⑥选中dbo.Diagram_1,右击,在出现的快捷菜单中选择“修改”命令,如图5.1所示。
⑦在数据库关系图中,选择表示要从关系图中删除的关系的连接线(对于两条连线均进行⑦~⑨的操作)。
⑧右击关系线,从快捷菜单中选择“从数据库中删除关系”命令。
⑨出现一个消息框,提示确认删除。
单击“是”按钮。
⑩在出现的对话框中单击“是”按钮保存所做的修改。
这样就将worker表和depart表以及worker表和salary表之间的关系删除了。
四.测试数据与实验结果第1-14题图第1-15题图第1-16题图第2题图第3题图第4题图图5.1第5-2题图第5-3题图第5-4题图第5-5题图第5-6题图第5-7题图第5-8题图第5-9题图第5-10题图五.结果分析与实验体会在FROM子句中指定连接条件,有助于将这些连接条件与WHERE子句中的其他搜索条件分开,指定连接时建议使用这种方法。
如果某个表只出现在子查询中而不出现在外部查询中,那么该表中的列就无法包含在输出中(外部查询的选择列表)。
使用EXISTS引入的子查询在以下几方面与其他子查询略有不同:EXISTS关键字前面没有列名,常量或其他表达式。
由EXISTS引入的子查询的选择列表通常都是由星号(*)组成。
由于只是测试是否存在符合子查询中指定条件的行,所以不必列出列名。
六.源码1use schoolselect 学号,课程号,分数from scorewhere 学号 in(103,105)order by 学号compute avg(分数) by 学号go2use schoolgoselect student.班号,course.课程名,avg(score.分数)as'平均分'from student,course,scorewhere student.学号=score.学号 and course.课程号=score.课程号group by student.班号,course.课程名 with cubego3use schoolgobegin transaction Mytraninsert into teachervalues('999','张英','男','1960/03/05','教授','计算机系')save transaction Mytraninsert into teachervalues('888','胡丽','男','1982/08/04','副教授','电子工程系')rollback transaction Mytrancommit transactiongoselect * from teachergodelete teacher where 编号='999'go4use schoolselect student.学号,student.姓名,student.班号,score.课程号,score.分数from student,scorewhere 分数 in(select max(分数)from scorewhere 班号='1033' and student.学号=score.学号)5USE schoolgoSELECT s.姓名,c.课程名,sc.分数INTO studFROM student s,course c,score scWHERE s.学号=sc.学号 and c.课程号=sc.课程号 and sc.分数 is not null ORDER BY s.姓名SELECT * FROM studGo6USE schoolGOSELECT 班号,课程名,MAX(分数) 分数FROM (SELECT s.学号,s.姓名,s.班号,c.课程名,sc.分数FROM student s,course c,score scWHERE s.学号=sc.学号 AND c.课程号=sc.课程号 AND 分数 IS NOT NULL) T GROUP BY 班号,课程名ORDER BY 班号GO7USE factoryGOSELECT worker.职工号,worker.姓名,salary.工资FROM worker,salaryWHERE worker.职工号=salary.职工号ORDER BY worker.职工号,worker.姓名COMPUTE SUM(salary.工资) BY worker.职工号GO8USE factoryGOSELECT worker.性别,depart.部门名,AVG(salary.工资) AS '平均工资'FROM worker,depart,salaryWHERE worker.职工号=salary.职工号 AND worker.部门号=depart.部门号GROUP BY worker.性别,depart.部门名 WITH CUBEGO9USE factoryGOINSERT INTO worker VALUES('20','陈立','女','55/03/08',1,'75/10/10',4)GOINSERT INTO depart VALUES(5,'设备处')GOSELECT worker.职工号,worker.姓名,depart.部门名FROM worker FULL JOIN departON(worker.部门号=depart.部门号)ORDER BY worker.职工号GODELETE FROM worker WHERE 职工号='20'GODELETE FROM depart WHERE 部门号=5GO10USE factoryGOSELECT worker.职工号,worker.姓名,depart.部门名,salary.日期,salary.工资FROM worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号AND salary.工资=(SELECT MAX(工资)FROM salary)GO11USE factoryGOSELECT 部门名FROM departWHERE 部门号=(SELECT 部门号FROM workerWHERE 职工号=(SELECT 职工号FROM salaryWHERE 工资=(SELECT MAX(工资)FROM salary)) )GO12USE factoryGOSELECT 职工号,姓名FROM workerWHERE 职工号 IN(SELECT 职工号FROM salaryGROUP BY 职工号HAVING AVG(工资)<(SELECT AVG(工资) FROM salary))GO13USE factoryGOSET NOCOUNT ON--声明变量DECLARE @dname char(10)--声明游标DECLARE d_cursor CURSORFOR SELECT 部门名FROM departWHERE 部门号=(SELECT 部门号FROM workerWHERE 职工号=(SELECT 职工号FROM salaryWHERE 工资=(SELECT MAX(工资)FROM salary)))--打开游标OPEN d_cursor--提取第一行数据FETCH NEXT FROM d_cursor INTO @dname--打印表标题PRINT '部门名'PRINT '---------'WHILE @@FETCH_STATUS = 0BEGIN--打印一行数据PRINT @dname--提取下一行数据FETCH NEXT FROM d_cursor INTO @dname END--关闭游标CLOSE d_cursor--释放游标DEALLOCATE d_cursorGO14USE factoryGOSET NOCOUNT ON--声明变量DECLARE @no int,@name char(10)--声明游标DECLARE w_cursor CURSORFOR SELECT 职工号,姓名FROM workerWHERE 职工号 IN(SELECT 职工号FROM salaryGROUP BY 职工号HAVING AVG(工资)<(SELECT AVG(工资) FROM salary))--打开游标OPEN w_cursor--提取第一行数据FETCH NEXT FROM w_cursor INTO @no,@name--打印表标题PRINT '职工号姓名'PRINT '-----------------'WHILE @@FETCH_STATUS = 0BEGIN--打印一行数据PRINT CAST(@no AS char(8))+@name--提取下一行数据FETCH NEXT FROM w_cursor INTO @no,@nameEND--关闭游标CLOSE w_cursor--释放游标DEALLOCATE w_cursorGO15USE factoryGODECLARE @num intSELECT @num=COUNT(*) FROM workerPRINT '原职工人数:'+CAST(@num AS CHAR(3))GODECLARE @num intBEGIN TRANSACTION --启动事务--插入一个职工记录INSERT INTO worker VALUES(20,'陈立','女','55/03/08',1,'75/10/10',4) PRINT '插入一个职工记录'SELECT @num=COUNT(*) FROM workerPRINT '职工人数:'+CAST(@num AS CHAR(3))ROLLBACK TRANSACTION --回滚事务GOPRINT '回滚事务'DECLARE @num intSELECT @num=COUNT(*) FROM workerPRINT '职工人数:'+CAST(@num AS CHAR(3))GO。