數(shù)據(jù)庫基礎(chǔ)與應(yīng)用復(fù)習(xí)一

1、數(shù)據(jù)庫基礎(chǔ)與應(yīng)用復(fù)習(xí)（一）必須要熟悉書上的兩個數(shù)據(jù)庫實例1. 教學(xué)庫：包括學(xué)生、課程和選課三個基本表它們的定義分別為：學(xué)生(學(xué)生號 char(7),姓名 char(6),性別 char(2), 專業(yè) char(10)課程(課程號 char(4),課程名 char(10),課程學(xué)分 int)選課(學(xué)生號 char(7),課程號 char(4),成績 int)學(xué)生表：學(xué)生號姓名性別專業(yè)0101001王明男計算機0102005劉芹女電子0202003張魯男電子0303001趙紅女電氣0304006劉川男通信課程表：課程號課程名課程學(xué)分C001C+語言4C004操作系統(tǒng)3E002電子技術(shù)5X003信號

2、原理4選課表：學(xué)生號課程號成績0101001C001780101001C004620102005E002730202003C001940202003C004650202003X003800303001C001760304006E002722. 商品庫：包括商品表1和商品表2兩個基本表它們的定義分別為：商品表1(商品代號 char(8),分類名 char(8),單價 float,數(shù)量 int) 商品表2(商品代號 char(8),產(chǎn)地 char(6),品牌 char(6)商品表1：商品代號分類名單價數(shù)量DBX-134電冰箱14568DSJ-120電視機186515DSJ-180電視機207310

3、DSJ-340電視機37265KTQ-12空調(diào)器280012WBL-6微波爐64010XYJ-13洗衣機46820XYJ-20洗衣機87312商品表2：商品代號產(chǎn)地品牌DBX-134北京雪花DSJ-120南京熊貓DSJ-180南京熊貓DSJ-340北京牡丹KTQ-12無錫春蘭WBL-6青島海信XYJ-13無錫小天鵝XYJ-20山西海棠根據(jù)主教材第四章所給的商品庫和教學(xué)庫，按照下列所給的每條SQL查詢語句寫出相應(yīng)的功能1select x.商品代號,分類名,數(shù)量,品牌from 商品表1 x,商品表2 ywhere x.商品代號=y.商品代號從商品庫中查詢出每一種商品的商品代號、分類名、數(shù)量和品牌等

4、信息2select distinct 產(chǎn)地from 商品表2從商品庫中查詢出所有商品的不同產(chǎn)地3select distinct 品牌from 商品表2從商品庫中查詢出所有商品的不同品牌4select count(distinct 產(chǎn)地) as 產(chǎn)地數(shù)from 商品表2從商品庫中查詢出所有商品的不同產(chǎn)地的總數(shù)5select x.商品代號,分類名,產(chǎn)地,品牌from 商品表1 x,商品表2 ywhere x.商品代號=y.商品代號 and (品牌='熊貓' or 品牌='春蘭')從商品庫中查詢出品牌為熊貓或春蘭的所有商品的商品代號、分類名、產(chǎn)地和品牌等信息6sele

5、ct 學(xué)生.學(xué)生號,count(*) as 選課門數(shù)from 學(xué)生,選課where 學(xué)生.學(xué)生號=選課.學(xué)生號group by 學(xué)生.學(xué)生號從教學(xué)庫中查詢出每個學(xué)生的選課門數(shù)7select 課程.課程號,課程.課程名,count(*) as 選課人數(shù)from 課程,選課where 課程.課程號=選課.課程號group by 課程.課程號,課程.課程名從教學(xué)庫中查詢出每門課程的選課學(xué)生數(shù)8select distinct x.* from 學(xué)生 x,選課 y,選課 z where y.學(xué)生號=z.學(xué)生號 and y.課程號<>z.課程號 and x.學(xué)生號=y.學(xué)生號從教學(xué)庫中查詢出至

6、少選修了兩門課程的全部學(xué)生9select *from 學(xué)生where 學(xué)生號 in (select 學(xué)生號 from 選課 group by 學(xué)生號 having count(*)=1)從教學(xué)庫中查詢出只選修了一門課程的全部學(xué)生10select x.學(xué)生號,y.學(xué)生號,y.課程號from 選課 x,選課 ywhere x.學(xué)生號=s1 and y.學(xué)生號=s2 and x.課程號=y.課程號注：一個由字母開頭的標(biāo)識符再加上字符前綴，則表示為一個變量，假定已保存著一個相應(yīng)值。從教學(xué)庫中查詢出學(xué)生號為s1的學(xué)生和學(xué)生號為s2的學(xué)生所選修的共同課程的課程號11select x.*from 課程 x,

7、選課 ywhere x.課程號=y.課程號 and y.學(xué)生號=s1and y.課程號 not in (select 課程號 from 選課 where 選課.學(xué)生號=s2)從教學(xué)庫中查詢出學(xué)生號為s1的學(xué)生所選修、而學(xué)生號為s2的學(xué)生沒有選修的全部課程12select *from 課程where not exists (select * from 選課 where 課程.課程號=選課.課程號)從教學(xué)庫中查詢出所有未被學(xué)生選修的課程13select *from 課程where exists (select * from 選課 where 課程.課程號=選課.課程號)從教學(xué)庫中查詢出所有已被學(xué)生

8、選修的課程14select *from 課程where exists (select 課程號 from 選課 where 課程.課程號=選課.課程號 group by 課程號 having count(*) between 2 and 4)從教學(xué)庫中查詢出被2至4名學(xué)生選修的所有課程15select *from 學(xué)生where exists (select * from 選課 where 學(xué)生.學(xué)生號=選課.學(xué)生號 group by 選課.學(xué)生號 having count(*)=3)從教學(xué)庫中查詢出同時選修了3門課程的全部學(xué)生16select *from 學(xué)生where 學(xué)生號 in (sel

9、ect 學(xué)生號 from 選課 group by 學(xué)生號 having count(*)<=2) or not exists (select * from 選課 where 學(xué)生.學(xué)生號=選課.學(xué)生號)從教學(xué)庫中查詢出最多選修了2門課程（含未選任何課程）的全部學(xué)生17select 專業(yè),count(*) as 專業(yè)人數(shù)from 學(xué)生group by 專業(yè)order by 專業(yè)人數(shù) desc從教學(xué)庫中查詢出每個專業(yè)的學(xué)生人數(shù)，并按人數(shù)多少降序排列18select 專業(yè),性別,count(*) as 人數(shù)from 學(xué)生group by 專業(yè),性別order by 專業(yè)從教學(xué)庫中查詢出每個專業(yè)

10、每種性別的學(xué)生人數(shù)，并按專業(yè)升序排列19select x.*,課程名,課程學(xué)分,成績from 學(xué)生 x,課程 y,選課 zwhere x.學(xué)生號=z.學(xué)生號 and y.課程號=z.課程號order by x.學(xué)生號,z.成績從教學(xué)庫中查詢出每個學(xué)生選課的全部情況，并依次按學(xué)生號和成績升序排列20select *from 學(xué)生where not exists(select y.課程號 from 學(xué)生 x inner join 選課 y on x.學(xué)生號=y.學(xué)生號 where x.姓名=a and not exists (select * from 選課 where 學(xué)生.學(xué)生號=選課.學(xué)生號

11、 and y.課程號=選課.課程號) )從教學(xué)庫中查詢出選修了姓名為a的學(xué)生的全部選課的所有學(xué)生根據(jù)教材第四章所給的商品庫和教學(xué)庫，按照下列所給的每種功能寫出相應(yīng)的查詢語句。在名稱為商品庫的數(shù)據(jù)庫中包含有商品表1和商品表2，它們的定義分別為：商品表1(商品代號 char(8),分類名 char(8),單價 float,數(shù)量 int) 商品表2(商品代號 char(8),產(chǎn)地 char(6),品牌 char(6),)在名稱為教學(xué)庫的數(shù)據(jù)庫中包含有學(xué)生、課程和選課三個表，它們的定義分別為：學(xué)生(學(xué)生號 char(7),姓名 char(6),性別 char(2),出生日期 datetime, 專業(yè)

12、char(10),年級 int)課程(課程號 char(4),課程名 char(10),課程學(xué)分 int)選課(學(xué)生號 char(7),課程號 char(4),成績 int)1從商品庫中查詢出每種商品的商品代號、單價、數(shù)量和產(chǎn)地。select 商品表1.商品代號,單價,數(shù)量,產(chǎn)地from 商品表1,商品表2where 商品表1.商品代號=商品表2.商品代號2從商品庫中查詢出數(shù)量大于10的商品種數(shù)。select count(*) from 商品表1where 數(shù)量>103從商品庫中查詢出數(shù)量在10和20之間的所有商品。select * from 商品表1where 數(shù)量 between 1

13、0 and 20 （或where 數(shù)量>=10 and 數(shù)量<=20）4從商品庫中查詢出每類（即分類名相同）商品的最高單價。select 分類名,max(單價) as 最高單價from 商品表1group by 分類名5從商品庫中查詢出每類（即分類名相同）商品的平均數(shù)量。select 分類名,avg(數(shù)量) as 平均數(shù)量from 商品表1group by 分類名6從商品庫中查詢出每類（即分類名相同）商品的總數(shù)量。select 分類名,sum(數(shù)量) as 總數(shù)量from 商品表1group by 分類名7從商品庫中查詢出單價大于等于2000元、或者數(shù)量大于等于15的所有商品sel

14、ect * from 商品表1where 單價>=2000 or 數(shù)量>=158從商品庫中查詢出比所有電視機的單價都高的每種商品。select *from 商品表1where 單價>all(select 單價 from 商品表1 where 分類名='電視機')或select * from 商品表1where 單價>all(select max(單價) from 商品表1 where 分類名='電視機')9從商品庫中查詢出比所有商品單價的平均值要高的全部商品。select * from 商品表1where 單價>all(select

15、 avg(單價) from 商品表1)10從商品庫中查詢出數(shù)量最多的一種商品。select * from 商品表1where 數(shù)量=some(select max(數(shù)量) from 商品表1)11從商品庫中查詢出同一類商品多于一種的所有分類名。select distinct 分類名from 商品表1group by 分類名 having count(*)>112從商品庫中查詢出同一產(chǎn)地的商品只有一種的所有商品。select 商品表1.*,產(chǎn)地from 商品表1,商品表2where 商品表1.商品代號=商品表2.商品代號 and 產(chǎn)地 in (select 產(chǎn)地 from 商品表1 x,商

16、品表2 y where x.商品代號=y.商品代號 group by 產(chǎn)地 having count(*)=1)13從商品庫中查詢出每種商品的總價值，并按降序排列出來。select *,單價*數(shù)量 as 總價值from 商品表1order by 總價值 desc 14從教學(xué)庫中查詢出至少有兩名學(xué)生所選修的全部課程。select distinct x.* from 課程 x,選課 y,選課 zwhere y.課程號=z.課程號 and y.學(xué)生號<>z.學(xué)生號 and x.課程號=y.課程號或：select * from 課程where 課程號 in (select 課程號 from

17、 選課 group by 課程號 having count(*)>=2)15從教學(xué)庫中查詢出至少選修了姓名為m1學(xué)生所選課程中一門課的全部學(xué)生。select distinct 學(xué)生.* from 學(xué)生,選課where 學(xué)生.學(xué)生號=選課.學(xué)生號 and 課程號=any(select 課程號 from 學(xué)生,選課 where 學(xué)生.學(xué)生號=選課.學(xué)生號 and 姓名=m1 )16從教學(xué)庫中查詢出每門課程被選修的學(xué)生人數(shù)，并按所選人數(shù)的升序排列出課程號、課程名和選課人數(shù)。select 課程.課程號,課程名,count(課程.課程號) as 人數(shù)from 課程,選課where 課程.課程號=選

18、課.課程號group by 課程.課程號,課程名order by 人數(shù)寫出下列每條語句或程序段的功能假設(shè)存在名為AAA的數(shù)據(jù)庫，包括Students（學(xué)號 char(8)，姓名 varchar(8)，年齡 int，專業(yè) varchar(20)，入學(xué)日期 DateTime）和Score（學(xué)號 char(8)，課程名 varchar(10),成績 numeric(5,2)）兩張表。1SELECT * FROM StudentsWHERE DATEPART(year,入學(xué)日期) =DATEPART(year,GETDATE()從Students表中查詢出所有當(dāng)年（系統(tǒng)時間）入學(xué)的學(xué)生記錄。2DECL

19、ARE MyNO CHAR(8)SET MyNO='20030001'IF (SELECT 專業(yè) FROM Students WHERE 學(xué)號=MyNO)='計算機軟件' BEGIN SELECT AVG(成績) AS 平均成績 FROM Score WHERE 學(xué)號=MyNO ENDELSE PRINT '學(xué)號為' +MyNO+'的學(xué)生不存在或不屬于軟件專業(yè)'GO首先定義一個名為MyNo的局部變量，并給它賦初值，如果MyNo屬于計算機軟件專業(yè)，則顯示出平均成績，否則顯示“學(xué)號為MyNo的學(xué)生不存在或不屬于軟件專業(yè)”。3decla

20、re a numeric(5,2)set a=(select avg(成績) from score)select *from scorewhere 成績>=a從score表中查詢出成績大于等于平均成績的所有記錄。4declare a numeric(5,2),b numeric(5,2)set a=(select max(成績) from score)set b=(select min(成績) from score)print a-b求出score表中最高成績與最低成績的分數(shù)之差。5declare a char(6)set a='劉亮'if(exists(select *

21、 from students where 姓名=a) print '姓名為'+a+'的同學(xué)存在！'else print '姓名為'+a+'的同學(xué)不存在！'從students表中查詢姓名為a的值（即“劉亮”）的同學(xué)是否存在，根據(jù)不同情況顯示出相應(yīng)信息。6declare a char(8)set a='計算機'select 計算機專業(yè)人數(shù)=count(*)from studentswhere left(專業(yè),3)=a從students表中統(tǒng)計出專業(yè)名開頭為a的值（即“計算機”）的所有學(xué)生人數(shù)。7select year(

22、入學(xué)日期) as 入學(xué)年份,count(*) as 人數(shù)from studentsgroup by year(入學(xué)日期)從students表中分組統(tǒng)計出每個年份入學(xué)的學(xué)生人數(shù)。8select month(入學(xué)日期) as 入學(xué)月份,count(*) as 人數(shù)from studentsgroup by month(入學(xué)日期)從students表中分組統(tǒng)計出每個月份入學(xué)的學(xué)生人數(shù)。9select day(入學(xué)日期) as 入學(xué)日號,count(*) as 人數(shù)from studentsgroup by day(入學(xué)日期)從students表中分組統(tǒng)計出每個日子入學(xué)的學(xué)生人數(shù)。10create p

23、rocedure xxk1asbegin select * from students x,score y where x.學(xué)號=y.學(xué)號end顯示出AAA庫中所有學(xué)生的記錄信息及選課成績11.create procedure xxk2asbegin select x.學(xué)號,x.姓名,x.專業(yè),count(*) as 門數(shù) from students x,score y where x.學(xué)號=y.學(xué)號 group by x.學(xué)號,x.姓名,x.專業(yè)end顯示出AAA庫中每個學(xué)生的學(xué)號、姓名、專業(yè)等信息及選課門數(shù)12.create procedure xxk3asbegin select 學(xué)號,

24、avg(成績) as 平均成績 from score group by 學(xué)號end顯示出AAA庫中每個學(xué)生的平均成績13.create procedure xxk4( a char(8),b varchar(10),c numeric(5,2)asbegin update score set 成績=c where 學(xué)號=a and 課程名=bend修改score表中學(xué)號為a的值、課程名為b的值的學(xué)生的成績?yōu)閏的值。14.create procedure xxk5(a char(8),b varchar(10),c numeric(5,2)asbegin insert into score va

25、lues(a,b,c)end向score表中插入學(xué)號為a的值、課程名為b的值、成績?yōu)閏的值的學(xué)生成績記錄。15.create procedure xxk6(a char(8),b varchar(10)asbegin delete from score where 學(xué)號=a and 課程名=bend從score表中刪除學(xué)號為a的值、課程名為b的值的學(xué)生成績記錄。16.declare a char(8),b varchar(10),c numeric(5,2)declare d intset d=0declare xxx cursorfor select 學(xué)號,課程名,成績 from score

26、open xxxfetch xxx into a,b,cwhile fetch_status=0begin set d=d+1 fetch from xxx into a,b,cendclose xxxdeallocate xxxprint d從score表中統(tǒng)計并顯示出記錄總數(shù)17.declare a char(8),b varchar(10),c numeric(5,2)declare xxx cursorfor select 學(xué)號,課程名,成績 from scoreopen xxxfetch xxx into a,b,cwhile fetch_status=0begin print a+

27、replicate(' ',3)+b+str(c)+replicate(' ',3) +(case when c>=90 then '優(yōu)秀' when c>=70 then '良好' when c>=60 then '及格' else '不及格' end) fetch from xxx into a,b,cendclose xxxdeallocate xxx顯示出score表中每個成績記錄，并根據(jù)具體成績在每條記錄最后給出優(yōu)秀、良好、及格、不及格等相應(yīng)等級。18.declare c

28、 numeric(5,2)declare c1 int, c2 int, c3 int, c4 intset c1=0; set c2=0; set c3=0; set c4=0declare xxx cursorfor select 成績 from scoreopen xxxfetch xxx into cwhile fetch_status=0begin if(c>=90) set c1=c1+1; else if(c>=70) set c2=c2+1; else if(c>=60) set c3=c3+1; else set c4=c4+1 fetch from xxx

29、 into cendclose xxxdeallocate xxxprint '優(yōu)秀生人數(shù):'+str(c1,5);print '良好生人數(shù):'+str(c2,5);print '及格生人數(shù):'+str(c3,5);print '及格生人數(shù):'+str(c4,5)從score表中按成績統(tǒng)計并顯示出優(yōu)秀、良好、及格、不及格各多少人。19.declare a char(8),b varchar(10)declare c numeric(5,2)declare d intset d=80declare xxx cursorfor sel

30、ect 學(xué)號,課程名,成績 from scoreopen xxxfetch xxx into a,b,cwhile fetch_status=0begin if(c>=d) print a+replicate(' ',3)+b+str(c,5) fetch from xxx into a,b,cendclose xxxdeallocate xxx顯示出score表中成績大于等于d值的所有記錄。20.declare a char(8),b varchar(10),c numeric(5,2)declare s char(8),r varchar(10)set s='

31、20030001'set r='數(shù)學(xué)'set c=84declare xxx cursorfor select 學(xué)號,課程名 from scoreopen xxxfetch xxx into a,bwhile fetch_status=0begin if(a=s and b=r) update score set 成績=c where current of xxx fetch from xxx into a,bendclose xxxdeallocate xxx修改score表中學(xué)號為a的值、課程名為b的值的學(xué)生的成績?yōu)閏的值。21.declare a char(8),

32、b varchar(10)declare s char(8),r varchar(10)set s='20030001'set r='數(shù)學(xué)'declare xxx cursorfor select 學(xué)號,課程名 from scoreopen xxxfetch xxx into a,bwhile fetch_status=0begin if(a=s and b=r) begin delete from score where current of xxx break end fetch from xxx into a,bendclose xxxdeallocate

33、 xxx從score表中刪除學(xué)號為a的值、課程名為b的值的學(xué)生記錄。寫出下列每條語句或程序段的功能假設(shè)存在名為AAA的數(shù)據(jù)庫，包括Students（學(xué)號 char(8)，姓名 varchar(8)，年齡 int，專業(yè) varchar(20)，入學(xué)日期 DateTime）和Score（學(xué)號 char(8)，課程名 varchar(10),成績 numeric(5,2)）兩張表。1SELECT CEILING(888.33)，CEILING(-888.33) 889 -8882SELECT FLOOR(888.33)，F(xiàn)LOOR(-888.33) 888 -8893SELECT ROUND(888

34、.33,0)，ROUND(888.55,0) 888 8894. SELECT LEN(I am a student)145. SELECT SUBSTRING(I am a student,8,7)student6. select '學(xué)生號','課程號',isnull(null,0) 學(xué)生號 課程號 07select '學(xué)生號','課程號',isnull(cast(null as char(6),'無成績') 學(xué)生號 課程號 無成績8. select '學(xué)生號','課程號',isnull(cast(0 as char(6),'無成績')學(xué)生號 課程號 0根據(jù)下面所給的AAA數(shù)據(jù)庫，按照下列每種功能寫出相應(yīng)的Transact-SQL語句或程序段假設(shè)使用名稱為AAA的數(shù)據(jù)庫，它包括Students（學(xué)號 char(8)，姓名 varchar(8)，年齡 int，專業(yè) varchar(20)，入學(xué)日期 DateTime）和Score（學(xué)號 char(8)，課程名 varchar(10),成績 numeric(5,2)）兩張表。1. 以“xxxx年xx月xx日”的格式顯示某日期時間型數(shù)據(jù)，假設(shè)字段名為Mydate，存于Mytable表中。SELECT