上海大学数据库上机作业上机练习.doc

上海大数据库数据库系统与应用上机习题* 第六部分、SQL高级应用要求掌握：熟练掌握T-SQL语言，了解事务处理的相关语句，学会用游标方式对数据库进行操作。1、 写出书上练习题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)2、 完成书上上机实验题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 职工号=20GODELETE FROM depart WHERE 部门号=5GO5.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 插入一个职工记录SELEC