上海大學數(shù)據(jù)庫上機作業(yè)上機練習.doc

上海大數(shù)據(jù)庫數(shù)據(jù)庫系統(tǒng)與應(yīng)用上機習題* 第六部分、SQL高級應(yīng)用要求掌握：熟練掌握T-SQL語言，了解事務(wù)處理的相關(guān)語句，學會用游標方式對數(shù)據(jù)庫進行操作。1、 寫出書上練習題10中第14、15、16、17題的結(jié)果，并上機驗證。完成第20、21、22、23、24題14. 給出下列程序的執(zhí)行結(jié)果USE schoolSELECT sno,cno,degreeFROM scoreWHERE sno IN (103,105)ORDER BY sno COMPUTE AVG(degree) BY snoGO15. 給出下列程序的執(zhí)行結(jié)果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. 給出下列程序的執(zhí)行結(jié)果USE schoolGOBEGIN TRANSACTION Mytran -啟動事務(wù) 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）進入企業(yè)管理器，展開數(shù)據(jù)庫，單擊“factory”，單擊下方的“關(guān)系圖”。 2）在數(shù)據(jù)庫關(guān)系圖中，選擇要表示要從關(guān)系圖中刪除的關(guān)系的聯(lián)接線。 3）右擊關(guān)系線，并從快捷菜單中選擇“從數(shù)據(jù)庫中刪除關(guān)系”。 4）出現(xiàn)一個消息框，提示確認刪除。單擊“是”按鈕。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,設(shè)備處)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 -提取第一行數(shù)據(jù)FETCH NEXT FROM d_cursor INTO dname -打印表標題PRINT 部門名WHILE FETCH_STATUS =0BEGIN -打印一行數(shù)據(jù)PRINT dname -提取下一行數(shù)據(jù)FETCH NEXT FROM d_cursor INTO dnameEND -關(guān)閉游標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 -提取第一行數(shù)據(jù)FETCH NEXT FROM w_cursor INTO no,name -打印表標題PRINT 職工號 姓名WHILE FETCH_STATUS =0BEGIN -打印一行數(shù)據(jù)PRINT CAST(no AS char(8)+name -提取下一行數(shù)據(jù)FETCH NEXT FROM w_cursor INTO no,nameEND -關(guān)閉游標CLOSE w_cursor -釋放游標DEALLOCATE w_cursorGO 10.USE factoryGO DECLARE num intSELECT num=COUNT(*)FROM workerPRINT 原職工人數(shù)：+CAST(num AS CHAR(3)GO DECLARE num intBEGIN TRANSACTION -啟動事務(wù)-插入一個職工記錄INSERT INTO worker VALUES(20,陳立,女,55/03/08,1,75/10/10,4)PRINT 插入一個職工記錄SELEC