【正文】
師信息。InSELECT * FROM teacherWHERE teacherno IN (SELECT teacherno FROM teach_class WHERE courseno=39。c0512739。)視圖與索引使用SQL語言1. 創(chuàng)建一個(gè)視圖v_teacher,查詢所有“計(jì)算機(jī)學(xué)院”的教師信息, 添加WITH CHECK OPTION選項(xiàng)。CREATE VIEW v_teacherASSELECT * FROM teacherWHERE department=39。計(jì)算機(jī)學(xué)院39。WITH CHECK OPTIONGOSELECT * FROM v_teacher2. 創(chuàng)建一個(gè)視圖v_avgstu,查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名及平均分,并且按照平均分降序排列。CREATE VIEW v_avgstuASSELECT TOP 100 ,sname,avg(final) AS 39。平均分39。FROM student JOIN scoreON =GROUP BY ,snameORDER BY avg(final)GOSELECT * FROM v_avgstu3. 通過視圖v_teacher向基表teacher中分別插入數(shù)據(jù)(‘05039’, ‘張馨月’,’計(jì)算機(jī)應(yīng)用’,’講師’,’計(jì)算機(jī)學(xué)院’)和(‘06018’, ‘李誠(chéng)’,’機(jī)械制造’,’副教授’,’機(jī)械學(xué)院’),并查看插入數(shù)據(jù)情況。INSERT INTO v_teacher VALUES (39。0503939。, 39。張馨月39。,39。計(jì)算機(jī)應(yīng)用39。,39。講師39。,39。計(jì)算機(jī)學(xué)院39。)GOSELECT * FROM teacher試圖進(jìn)行的插入或更新已失敗,原因是目標(biāo)視圖或者目標(biāo)視圖所跨越的某一視圖指定了WITH CHECK OPTION,而該操作的一個(gè)或多個(gè)結(jié)果行又不符合CHECK OPTION 約束。語句已終止。4. 通過視圖v_teacher將基表teacher中教師編號(hào)為05039的教師職稱修改為”副教授”。UPDATE v_teacherSET prof=39。副教授39。WHERE teacherno=39。0503939。GOSELECT * FROM teach