數(shù)據(jù)庫考試重點.doc

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