數(shù)據(jù)庫(kù)考試重點(diǎn).doc

use 教學(xué)庫(kù)GO-1select * from 學(xué)生 where 年齡=(select max(年齡) from 學(xué)生 where 性別=女)GO-3-第一種做法select s.姓名,s.專(zhuān)業(yè) from 學(xué)生 s inner join 選課 sc on s.學(xué)號(hào)=sc.學(xué)號(hào)inner join 課程 c on sc.課程號(hào)=c.課程號(hào) and c.課程名=c+程序設(shè)計(jì)-第二種做法select s.姓名,s.專(zhuān)業(yè) from 學(xué)生 s,課程 c,選課 sc where s.學(xué)號(hào)=sc.學(xué)號(hào) and sc.課程號(hào)=c.課程號(hào) and c.課程名=c+程序設(shè)計(jì)GO-4select 課程號(hào), count(學(xué)號(hào)) as 選課人數(shù) from 選課 group by 課程號(hào)GO-5select 姓名,性別,年齡,專(zhuān)業(yè) from 學(xué)生 where 專(zhuān)業(yè) in (計(jì)算機(jī)網(wǎng)絡(luò),通信,電子)order by 性別 DESC,專(zhuān)業(yè) ASC九章課后題use 教學(xué)庫(kù)go-9_1_1select count(distinct 課程號(hào)) as 有選課門(mén)數(shù) from 選課go-9_1_2select avg(年齡) as 選101課平均年齡 from 學(xué)生 where 學(xué)號(hào) in(select 學(xué)號(hào) from 選課 where 課程號(hào)=0300101)go-9_1_3select 課程號(hào),avg(成績(jī)) as 平均成績(jī) from 選課group by 課程號(hào) having 課程號(hào) in(select 課程號(hào) from 課程 where 學(xué)分=3)go-9_1_4select 課程號(hào),count(*) as 選修人數(shù) from 選課group by 課程號(hào) having count(*)3order by 選修人數(shù) desc,課程號(hào)go-9_1_5select 姓名 from 學(xué)生 where 學(xué)號(hào)(select 學(xué)號(hào) from 學(xué)生 where 姓名=王三朋) and 年齡(select avg(年齡) from 學(xué)生 where 性別=女) go-9_1_9select 姓名,年齡 from 學(xué)生 where 性別=男 and 年齡(select max(年齡) from 學(xué)生 where 性別=女) go-9_1_10select 姓名,年齡,性別 from 學(xué)生 where 年齡(select 年齡 from 學(xué)生 where 姓名=王三朋)go-9_1_11select top 1 學(xué)號(hào) from 選課 where 課程號(hào)=0300105order by 課程號(hào),成績(jī) descgo-9_1_12select a.姓名,b.課程號(hào),b.成績(jī) from 學(xué)生 a inner join 選課 b on a.學(xué)號(hào)=b.學(xué)號(hào)go-9_1_13select sum(成績(jī)) as 總成績(jī) from 選課 where 成績(jī)=60 group by 課程號(hào) having count(學(xué)號(hào))=4order by 總成績(jī) descGO-9-2use 教學(xué)庫(kù)goif exists(select * from 選課 where 學(xué)號(hào)=20090311002)select 課程號(hào),成績(jī) from 選課 where 學(xué)號(hào)=20090311002elseprint 此學(xué)生無(wú)成績(jī)！GO-9-3create function cunstu(zy char(50),cn char(11) returns intas begindeclare cun intselect cun=(select count(*) from 選課,學(xué)生where 選課.學(xué)號(hào)=學(xué)生.學(xué)號(hào) and 課程號(hào)=cn and 專(zhuān)業(yè)=zy) return cun end goselect dbo.cunstu(0300101,計(jì)算機(jī)網(wǎng)絡(luò)) as 0300101課計(jì)算機(jī)網(wǎng)絡(luò)專(zhuān)業(yè)的選課人數(shù)goGO-9-4create function kc_cj(zy char(50) returns tableas return(select 課程號(hào),avg(成績(jī)) as 平均成績(jī) from 選課 inner join 學(xué)生 on 選課.學(xué)號(hào)=學(xué)生.學(xué)號(hào) and 專(zhuān)業(yè)=zy group by 課程號(hào))goselect * from dbo.kc_cj(計(jì)算機(jī)網(wǎng)絡(luò)) GO-9-5use 倉(cāng)庫(kù)庫(kù)存gos