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

1、【精品文檔】如有侵權，請聯(lián)系網站刪除，僅供學習與交流上海大學數(shù)據(jù)庫上機作業(yè)上機練習.精品文檔.上海大數(shù)據(jù)庫數(shù)據(jù)庫系統(tǒng)與應用上機習題* 第六部分、SQL高級應用要求掌握：熟練掌握T-SQL語言，了解事務處理的相關語句，學會用游標方式對數(shù)據(jù)庫進行操作。1、 寫出書上練習題10中第14、15、16、17題的結果，并上機驗證。完成第20、21、22、23、24題14. 給出下列程序的執(zhí)行結果USE schoolSELECT sno,cno,degreeFROM scoreWHERE sno IN (103,105)ORDER BY sno COMPUTE AVG(degree) BY snoGO15.

2、 給出下列程序的執(zhí)行結果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í)行結果USE schoolGOBE

3、GIN TRANSACTION Mytran -啟動事務 INSERT INTO teacher VALUES(999,'張瑛','男','1960/03/05','教授','計算機系') -插入一個教師記錄SAVE TRANSACTION Mytran -保存點 INSERT INTO teacher VALUES(888,'胡麗','男','1982/8/04','副教授','電子工程系')ROLLBACK TRANSACTION

4、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”，單擊下方的“關系圖”。 2）在數(shù)據(jù)庫關系圖中，選擇要表示要從關系圖中刪除的

5、關系的聯(lián)接線。 3）右擊關系線，并從快捷菜單中選擇“從數(shù)據(jù)庫中刪除關系”。 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

6、 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,'設備處')G

7、OSELECT 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 WHER

8、E 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 s

9、alary 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_cur

10、sor -提取第一行數(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 -關閉游標CLOSE d_cursor -釋放游標DEALLOCATE d_cursorGO 9.USE factoryGO SET NOCOUNT ON -聲明變量DECLARE no int,name char(10) -聲明游標DECLARE w_cursor

11、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ù)FETC

12、H NEXT FROM w_cursor INTO no,nameEND -關閉游標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 -啟動事務-插入一個職工記錄INSERT INTO worker VALUES(20,'陳立','女','55/03/08',1,'75/10/10',4)PRINT '插入一個職工記錄'SELECT num=COUNT