oracle作業(yè)(答案)

《oracle作業(yè)(答案)》由會員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在教育資源-天天文庫。

1、方法一1、從表1獲取數(shù)據(jù)，格式化輸出為表2。dropuserbencascade;createuserbenidentifiedby12345;grantdbatoben;connben/12345;showuser;createtabletableone(studnamevarchar2(16),coursenamevarchar2(16),cscorenumber(2)check(cscore>=0andcscore<=100),uscorenumbercheck(uscorein(1,3,5)))

2、;insertintotableonevalues('李白','摔跤',80,'1');insertintotableonevalues('李白','軍事理論',60,'5');insertintotableonevalues('李白','數(shù)學(xué)',70,'3');insertintotableonevalues('杜甫','摔跤',95,'1');insertintotableonevalues('杜甫','軍事理論',85,'5');insertintotableonevalues('杜甫','數(shù)學(xué)'

3、,75,'3');insertintotableonevalues('李清照','摔跤',45,'1');insertintotableonevalues('李清照','軍事理論',55,'5');insertintotableonevalues('李清照','數(shù)學(xué)',50,'3');insertintotableonevalues('蔡琰','摔跤',25,'1');insertintotableonevalues('蔡琰','軍事理論',15,'5');insertintotableonevalue

4、s('蔡琰','數(shù)學(xué)',10,'3');createorreplaceviewtabletwoasselectstudnameas姓名,max(decode(coursename,'摔跤',cscore))as摔跤,max(decode(coursename,'軍事理論',cscore))as軍事理論,max(decode(coursename,'數(shù)學(xué)',cscore))as數(shù)學(xué),sum(uscore)as總學(xué)分fromtableonegroupbystudname;setpagesize15;colu

5、mnstudnameformata18;columncoursenameformata18;select*fromtableone;select*fromtabletwo;2、從表2讀取數(shù)據(jù)，格式化輸出為表1。droptabletableone;dropviewtabletwo;createtabletabletwo(姓名varchar2(16),摔跤number(2),軍事理論number(2),數(shù)學(xué)number(2),總學(xué)分number);insertintotabletwovalues('李白',

6、80,60,70,'9');insertintotabletwovalues('杜甫',95,85,75,'9');insertintotabletwovalues('李清照',45,55,50,'9');insertintotabletwovalues('蔡琰',25,15,10,'9');createorreplaceviewtableoneasselectdistinct(姓名)asstudname,decode(user_tab_cols.column_name,'摔跤',user_tab_co

7、ls.column_name,'軍事理論',user_tab_cols.column_name,'數(shù)學(xué)',user_tab_cols.column_name)ascoursename,decode(user_tab_cols.column_name,'摔跤',摔跤,'軍事理論',軍事理論,'數(shù)學(xué)',數(shù)學(xué))ascscore,decode(user_tab_cols.column_name,'摔跤',1,'軍事理論',5,'數(shù)學(xué)',3)asuscorefromtabletwo,user_tab_colsor

8、derbystudname;setpagesize20;columnstudnameformata26;columncoursenameformata26;select*fromtabletwo;select*fromtableone;方法二1、從表1獲取數(shù)據(jù)，格式化輸出為表2。dropuserbencascade;createuserbenidentifiedby12345;grantdbatoben;connben/12345;show