上海大學(xué)數(shù)據(jù)庫(kù)上機(jī)作業(yè)上機(jī)練習(xí)

1、【精品文檔】如有侵權(quán)，請(qǐng)聯(lián)系網(wǎng)站刪除，僅供學(xué)習(xí)與交流上海大學(xué)數(shù)據(jù)庫(kù)上機(jī)作業(yè)上機(jī)練習(xí).精品文檔.上海大數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)系統(tǒng)與應(yīng)用上機(jī)習(xí)題* 第六部分、SQL高級(jí)應(yīng)用要求掌握：熟練掌握T-SQL語(yǔ)言，了解事務(wù)處理的相關(guān)語(yǔ)句，學(xué)會(huì)用游標(biāo)方式對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作。1、 寫(xiě)出書(shū)上練習(xí)題10中第14、15、16、17題的結(jié)果，并上機(jī)驗(yàn)證。完成第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.

2、 給出下列程序的執(zhí)行結(jié)果USE schoolGOSELECT teacher.tname AS '教師',student.sclass AS '班號(hào)',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 schoolGOBE

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

4、MytranCOMMIT TRANSACTIONGOSELECT * FROM teacher -查詢(xún)教師表的記錄GO DELETE teacher WHERE tno='999' -刪除插入的記錄GO17. 編寫(xiě)一個(gè)程序，查詢(xún)最高分的課程名USE schoolSELECT cnameFROM course,scoreWHERE o=o AND degree=(SELECT MAX(degree) FROM score)2、 完成書(shū)上上機(jī)實(shí)驗(yàn)題51.1）進(jìn)入企業(yè)管理器，展開(kāi)數(shù)據(jù)庫(kù)，單擊“factory”，單擊下方的“關(guān)系圖”。 2）在數(shù)據(jù)庫(kù)關(guān)系圖中，選擇要表示要從關(guān)系圖中刪除的

5、關(guān)系的聯(lián)接線(xiàn)。 3）右擊關(guān)系線(xiàn)，并從快捷菜單中選擇“從數(shù)據(jù)庫(kù)中刪除關(guān)系”。 4）出現(xiàn)一個(gè)消息框，提示確認(rèn)刪除。單擊“是”按鈕。2.USE factorySELECT worker.職工號(hào),worker.姓名,salary.工資FROM worker,salaryWHERE worker.職工號(hào)=salary.職工號(hào)ORDER BY worker.職工號(hào),worker.姓名COMPUTE SUM(salary.工資) BY worker.職工號(hào)3.USE factorySELECT worker.性別,depart.部門(mén)名,AVG(salary.工資) AS '平均工資'FROM

6、 worker,salary,depart WHERE worker.職工號(hào)=salary.職工號(hào) AND worker.部門(mén)號(hào)=depart.部門(mén)號(hào)GROUP BY worker.性別,depart.部門(mén)名 WITH CUBEORDER BY worker.性別,depart.部門(mén)名4.USE factoryGOINSERT INTO worker VALUES(20,'陳立','女','55/03/08',1,'75/10/10',4)GO INSERT INTO depart VALUES(5,'設(shè)備處')G

7、OSELECT worker.職工號(hào),worker.姓名,depart.部門(mén)名FROM worker FULL JOIN depart ON(worker.部門(mén)號(hào)=depart.部門(mén)號(hào))ORDER BY worker.職工號(hào)GODELETE FROM worker WHERE 職工號(hào)='20'GODELETE FROM depart WHERE 部門(mén)號(hào)='5'GO5.USE factorySELECT worker.職工號(hào),worker.姓名,depart.部門(mén)名,salary.日期,salary.工資FROM worker,salary,depart WHER

8、E worker.職工號(hào)=salary.職工號(hào) AND worker.部門(mén)號(hào)=depart.部門(mén)號(hào)AND salary.工資=(SELECT MAX(工資) FROM salary)6.USE factorySELECT 部門(mén)名FROM depart WHERE 部門(mén)號(hào)=(SELECT 部門(mén)號(hào) FROM worker WHERE 職工號(hào)=(SELECT 職工號(hào) FROM salary WHERE 工資=(SELECT MAX(工資) FROM salary )7.USE factorySELECT 職工號(hào),姓名FROM worker WHERE 職工號(hào) IN(SELECT 職工號(hào) FROM s

9、alary GROUP BY 職工號(hào) HAVING AVG(工資)<(SELECT AVG(工資) FROM salary )8.USE factoryGO SET NOCOUNT ON -聲明變量DECLARE dname char(10) -聲明游標(biāo)DECLARE d_cursor CURSORFOR SELECT 部門(mén)名FROM depart WHERE 部門(mén)號(hào)=(SELECT 部門(mén)號(hào) FROM worker WHERE 職工號(hào)=(SELECT 職工號(hào) FROM salary WHERE 工資=(SELECT MAX(工資) FROM salary )-打開(kāi)游標(biāo)OPEN d_cur

10、sor -提取第一行數(shù)據(jù)FETCH NEXT FROM d_cursor INTO dname -打印表標(biāo)題PRINT '部門(mén)名'WHILE FETCH_STATUS =0BEGIN -打印一行數(shù)據(jù)PRINT dname -提取下一行數(shù)據(jù)FETCH NEXT FROM d_cursor INTO dnameEND -關(guān)閉游標(biāo)CLOSE d_cursor -釋放游標(biāo)DEALLOCATE d_cursorGO 9.USE factoryGO SET NOCOUNT ON -聲明變量DECLARE no int,name char(10) -聲明游標(biāo)DECLARE w_cursor

11、CURSORFOR SELECT 職工號(hào),姓名FROM worker WHERE 職工號(hào) IN(SELECT 職工號(hào) FROM salary GROUP BY 職工號(hào) HAVING AVG(工資)<(SELECT AVG(工資) FROM salary )-打開(kāi)游標(biāo)OPEN w_cursor -提取第一行數(shù)據(jù)FETCH NEXT FROM w_cursor INTO no,name -打印表標(biāo)題PRINT '職工號(hào) 姓名'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 -關(guān)閉游標(biāo)CLOSE w_cursor -釋放游標(biāo)DEALLOCATE w_cursorGO 10.USE factoryGO DECLARE num intSELECT num=COUNT(*)FROM workerPRINT '原職工人數(shù)：'+CAST(num AS CHAR(3)GO DECLARE num intBEGIN TRANSACTION -啟動(dòng)事務(wù)-插入一個(gè)職工記錄INSERT INTO worker VALUES(20,'陳立','女','55/03/08',1,'75/10/10',4)PRINT '插入一個(gè)職工記錄'SELECT num=COUNT