上海大学数据库上机作业上机练习.doc
上海大数据库数据库系统与应用上机习题* 第六部分、SQL高级应用要求掌握：熟练掌握T-SQL语言，了解事务处理的相关语句，学会用游标方式对数据库进行操作。一、 写出书上练习题10中第14、15、16、17题的结果，并上机验证。完成第20、21、22、23、24题14. 给出下列程序的执行结果USE schoolSELECT sno,cno,degreeFROM scoreWHERE sno IN (103,105)ORDER BY sno COMPUTE AVG(degree) BY snoGO15. 给出下列程序的执行结果USE schoolGOSELECT teacher.tname AS '教师',student.sclass AS '班号',AVG(score.degree) AS '平均分'FROM student,course,score,teacherWHERE student.sno=score.sno AND o=o AND course.tno=teacher.tnoGROUP BY teacher.tname,student.sclass WITH CUBE GO16. 给出下列程序的执行结果USE schoolGOBEGIN TRANSACTION Mytran -启动事务 INSERT INTO teacher VALUES(999,'张瑛','男','1960/03/05','教授','计算机系') -插入一个教师记录SAVE TRANSACTION Mytran -保存点 INSERT INTO teacher VALUES(888,'胡丽','男','1982/8/04','副教授','电子工程系')ROLLBACK TRANSACTION MytranCOMMIT TRANSACTIONGOSELECT * FROM teacher -查询教师表的记录GO DELETE teacher WHERE tno='999' -删除插入的记录GO17. 编写一个程序，查询最高分的课程名USE schoolSELECT cnameFROM course,scoreWHERE o=o AND degree=(SELECT MAX(degree) FROM score)二、 完成书上上机实验题51.1）进入企业管理器，展开数据库，单击“factory”，单击下方的“关系图”。 2）在数据库关系图中，选择要表示要从关系图中删除的关系的联接线。 3）右击关系线，并从快捷菜单中选择“从数据库中删除关系”。 4）出现一个消息框，提示确认删除。单击“是”按钮。2.USE factorySELECT worker.职工号,worker.姓名,salary.工资FROM worker,salaryWHERE worker.职工号=salary.职工号ORDER BY worker.职工号,worker.姓名COMPUTE SUM(salary.工资) BY worker.职工号3.USE factorySELECT worker.性别,depart.部门名,AVG(salary.工资) AS '平均工资'FROM worker,salary,depart WHERE worker.职工号=salary.职工号 AND worker.部门号=depart.部门号GROUP BY worker.性别,depart.部门名 WITH CUBEORDER BY worker.性别,depart.部门名4.USE factoryGOINSERT INTO worker VALUES(20,'陈立','女','55/03/08',1,'75/10/10',4)GO INSERT INTO depart VALUES(5,'设备处')GOSELECT worker.职工号,worker.姓名,depart.部门名FROM worker FULL JOIN depart ON(worker.部门号=depart.部门号)ORDER BY worker.职工号GODELETE FROM worker WHERE 职工号='20'GODELETE FROM depart WHERE 部门号='5'GO5.USE factorySELECT worker.职工号,worker.姓名,depart.部门名,salary.日期,salary.工资FROM worker,salary,depart WHERE worker.职工号=salary.职工号 AND worker.部门号=depart.部门号AND salary.工资=(SELECT MAX(工资) FROM salary)6.USE factorySELECT 部门名FROM depart WHERE 部门号=(SELECT 部门号 FROM worker WHERE 职工号=(SELECT 职工号 FROM salary WHERE 工资=(SELECT MAX(工资) FROM salary )7.USE factorySELECT 职工号,姓名FROM worker WHERE 职工号 IN(SELECT 职工号 FROM salary GROUP BY 职工号 HAVING AVG(工资)<(SELECT AVG(工资) FROM salary )8.USE factoryGO SET NOCOUNT ON -声明变量DECLARE dname char(10) -声明游标DECLARE d_cursor CURSORFOR SELECT 部门名FROM depart WHERE 部门号=(SELECT 部门号 FROM worker WHERE 职工号=(SELECT 职工号 FROM salary WHERE 工资=(SELECT MAX(工资) FROM salary ) -打开游标OPEN d_cursor -提取第一行数据FETCH NEXT FROM d_cursor INTO dname -打印表标题PRINT '部门名'WHILE FETCH_STATUS =0BEGIN -打印一行数据PRINT dname -提取下一行数据FETCH NEXT FROM d_cursor INTO dnameEND -关闭游标CLOSE d_cursor -释放游标DEALLOCATE d_cursorGO 9.USE factoryGO SET NOCOUNT ON -声明变量DECLARE no int,name char(10) -声明游标DECLARE w_cursor CURSORFOR SELECT 职工号,姓名FROM worker WHERE 职工号 IN(SELECT 职工号 FROM salary GROUP BY 职工号 HAVING AVG(工资)<(SELECT AVG(工资) FROM salary )-打开游标OPEN w_cursor -提取第一行数据FETCH NEXT FROM w_cursor INTO no,name -打印表标题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_cursorGO 10.USE factoryGO DECLARE num intSELECT num=COUNT(*)FROM workerPRINT '原职工人数：'+CAST(num AS CHAR(3)GO DECLARE 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 -回滚事务GO PRINT '回滚事务'DECLARE num int SELECT num=COUNT(*)FROM workerPRINT '职工人数：'+CAST(num AS CHAR(3)GO4
