上海大学数据库上机作业上机练习上课讲义.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 职工号=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 插入一个职工记录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)GO收集于网络，如有侵权请联系管理员删除