數(shù)據(jù)庫技術(shù)與應(yīng)用B-SQL命令題題庫

1、SQL 查詢命令題題庫一、寫出 SQL 查詢命令 設(shè)有一個職工表，表中有若干條記錄，表結(jié)構(gòu)定義如下： 編號 姓名 部門 年齡 進廠日期 職稱 性別 電話 文本 文本 文本 整型 日期 / 時間 文本 文本 長整型 記錄舉例： 2006 汪洋 技術(shù)部 44 1980/05/06 技術(shù)員 男 73979400 編寫 SQL 命令完成下面的查詢 ： 1、列出姓名中包含“秋”字的全部男職工的信息： 2、列出所有除技術(shù)部以外的年齡在 40 歲以上的職工的姓名和電話： 3、統(tǒng)計進廠迄今已滿 25 年或以上的職工人數(shù)： 4、按部門顯示職工的平均年齡，顯示形式見下表，注意按部門順序排列： 性別 平均年齡 財務(wù)

2、部 52 技術(shù)部 36.2 人事部 52.3 銷售部 44.8 5、顯示男職工中年齡最小者的姓名1、SELECT *FROM 職工表WHERE 姓名LIKE”*秋*”AND 性別=”男”2、SELECT 姓名,電話FROM 職工表WHERE 部門LIKE”!技術(shù)部” AND年齡403、SELECT COUNT(編號) FROM 職工表 WHERE YEAR(NOW()-YEAR(進廠日期)=254、SELECT 性別,AVG(年齡)AS平均年齡 FROM 職工表 GROUP BY 部門 ORDER BY 部門5、SELECT 姓名 FROM 職工表 WHERE 性別=”男” AND 年齡=（S

3、ELECT MIN(年齡)FROM 職工表）二、根據(jù)主教材第四章所給的商品庫和教學(xué)庫，按照下列所給的每條SQL查詢語句寫出相應(yīng)的功能。在名稱為商品庫的數(shù)據(jù)庫中包含有商品表1和商品表2，它們的定義分別為：商品表1（商品代號char（8），分類名 char（8），單價 float ，數(shù)量 int ）商品表2（商品代號char（8），產(chǎn)地 char（8），品牌 char（6）在名稱為教學(xué)庫的數(shù)據(jù)庫中包含有學(xué)生、課程和選課三個表，它們的定義分別為：學(xué)生（學(xué)生號char（7），姓名char（6），性別char（2），出生日期 datetime，專業(yè)char（10），年級int）課程（課程號char（4）

4、，課程名char（10），課程學(xué)分 int）選課（學(xué)生號char（7），課程號char（4），成績 int）1. selectx.商品代號，分類名，數(shù)量，品牌（09年1月考）from商品表1x ，商品表2ywherex.商品代號 = y.商品代號從商品庫中查詢出每一種商品的商品代號、分類名、數(shù)量和品牌等信息2.select distinct 產(chǎn)地from 商品表2從商品庫中查詢出所有商品的不同產(chǎn)地3. select distinct 品牌（07年7月考）from 商品表2從商品庫中查詢出所有商品的不同品牌4.select count(distinct 產(chǎn)地)as 產(chǎn)地數(shù)（08年7月考）from

5、 商品表2從商品庫中查詢出所有商品的不同產(chǎn)地的總數(shù)5. selectx.商品代號，分類名，數(shù)量，品牌from商品表1x ，商品表2ywherex.商品代號 = y.商品代號 and （ 品牌 =熊貓or品牌 =春蘭）從商品庫中查詢出品牌為熊貓或春蘭的所有商品的商品代號、分類名、產(chǎn)地和品牌6.select 學(xué)生.學(xué)生號,count(*)as 選課門數(shù)（08年1月考、09.7考）from 學(xué)生,選課where 學(xué)生.學(xué)生號=選課.學(xué)生號group by 學(xué)生.學(xué)生號從教學(xué)庫中查詢出每個學(xué)生選課的門數(shù)7.select distinct x.*from 學(xué)生 x,選課 y,選課 zwhere y.學(xué)生

6、號=z.學(xué)生號 and y.課程號z.課程號 and x.學(xué)生號=y.學(xué)生號從教學(xué)庫中查詢出至少選修了兩門課程的全部學(xué)生8.select *（09年1月考）from 學(xué)生where學(xué)生號in （select 學(xué)生號from 選課group by 學(xué)生號 having count（*）=1 ）從教學(xué)庫中查詢出只選修了一門課程的全部學(xué)生9.select x.學(xué)生號,y.學(xué)生號,y.課程號（07年7月考）（09年1月考）from選課 x,選課 ywherex.學(xué)生號=s1 and y.學(xué)生號=s2and x.課程號=y.課程號從教學(xué)庫中查詢出學(xué)生號為 s1的學(xué)生和學(xué)生號為 s2的學(xué)生選修的共同課程的

7、課程號10.select x.*from課程 x，選課 ywherex .課程號= y .課程號 and y .學(xué)生號= s1and y .課程號 notin （select 課程號 from 選課 where 選課.學(xué)生號= s2）從教學(xué)庫中查詢出學(xué)生號為 s1的學(xué)生所選修、而學(xué)生號為 s2的學(xué)生沒有選修的課程11.select *from課程wherenotexists (select*from 選課where 課程.課程號=選課.課程號 )從教學(xué)庫中查詢出所有未被學(xué)生選修的課程12. select *（08年7月考）from 課程whereexists (select * from 選課

8、 where 課程.課程號=選課.課程號 )從教學(xué)庫中查詢出所有已被學(xué)生選修的課程13.select *（08年1月考）from學(xué)生whereexists(select *from 選課where學(xué)生.學(xué)生號=選課.學(xué)生號group by 選課.學(xué)生號 having count(*)=3)從教學(xué)庫中查詢出同時選修了3門課程的全部學(xué)生14.select *from學(xué)生where學(xué)生號 in(select 學(xué)生號from 選課group by 學(xué)生號 having count(*)103. 從商品庫中查詢出數(shù)量在10和20之間的所有商品。select*from商品表1where數(shù)量=10and數(shù)量

9、 all (select單價from商品表1where分類名=”電視機”)8. 從商品庫中查詢出比所有商品單價的平均值要高的全部商品。（09年1月考）select*from商品表1where單價 all (selectavg (單價)from商品表1)9. 從商品庫中查詢出數(shù)量最多的一種商品。（09年7月考）select*from商品表1where數(shù)量= some(selectmax (數(shù)量)from商品表1)10. 從商品庫中查詢出同一類商品多于一種的所有分類名。（07年7月考）selectdistinct 分類名from商品表1groupby 分類名 having count(*)111.

10、從商品庫中查詢出同一產(chǎn)地的商品只有一種的所有商品。select商品表1.* ,產(chǎn)地from商品表1, 商品表2where商品表1.商品代號=商品表2.商品代號 and 產(chǎn)地in (select 產(chǎn)地from商品表1 X ,商品表2 YwhereX.商品代號=y.商品代號groupby產(chǎn)地 having count(*)=1 )12. 從商品庫中查詢出每種商品的總價值，并按降序排列出來。select*, 單價*數(shù)量 as 總價值from商品表1orderby 總價值 desc13.從教學(xué)庫中查詢出至少選修了姓名為 m1學(xué)生所選課程中一門課的全部學(xué)生號。selectdistinct 學(xué)生.*fro

11、m學(xué)生, 選課where 學(xué)生.學(xué)號=選課.學(xué)號and 課程號= any (select 課程號from 學(xué)生,選課where 學(xué)生.學(xué)生號=選課.學(xué)生號 and 姓名=m1 )14.從教學(xué)庫中查詢出每門課程被選修的學(xué)生人數(shù)，并按所選人數(shù)的升序排列出課程號、課程名和選課人數(shù)。select課程.課程號, 課程名,count(課程.課程號)as 人數(shù)from課程, 選課where課程.課程號=選課.課程號group by 課程.課程號,課程名orderby 人數(shù)15. 從商品庫中查詢出不同產(chǎn)地，并按產(chǎn)地所具有的品牌數(shù)多少升序排列。（08年7月考）select 產(chǎn)地 ,count (品牌)as 品牌

12、數(shù)from 商品表2group by產(chǎn)地order by品牌數(shù)16.從教學(xué)庫中查詢出至少有5名學(xué)生所選修的全部課程（提示：使用子查詢）（08年1月考）select*from課程whereexists(select *from 選課where課程.課程號=選課.課程號group by 選課.課程號 having count(*)=5)四、根據(jù)下面所給的AAA數(shù)據(jù)庫，寫出下列每條查詢語句的執(zhí)行結(jié)果，或者寫出下列每條語句或程序段的功能。假設(shè)存在名為AAA的數(shù)據(jù)庫，包括Students（學(xué)號char(8)，姓名varchar(8)，年齡int，專業(yè)varchar(20)，入學(xué)日期 DateTime）和

13、Score（學(xué)號char(8)，課程名 varchar(10)，成績numeric(5,2)）兩張表。1.SELECT*FROMStudentsWHEREDATEPART（year，入學(xué)日期）=datepart（year，GETDATE（）從Students表中查詢出所有當年入學(xué)的學(xué)生記錄2.DECLAREMyNOchar（8）SETMyNO=20030001IF (SELECT 專業(yè) fromstudents WHERE學(xué)號=MyNO)=計算機軟件BeginSelectAVG(成績)AS 平均成績From scroeWhere 學(xué)號= MyNOEndElsePrint學(xué)號為+MyNO +的學(xué)

14、生號不存在或不屬于軟件專業(yè)Go首先定義一個名為MyNo的局部變量，并給它賦初值，如果MyNo屬于計算機軟件專業(yè)，則顯示出平均成績，否則顯示“學(xué)生為MyNo的學(xué)生不存在或不屬于軟件專業(yè)”。3.declare a numeric(5,2)（09.7考）set a=(select avg(成績) fromscore)select *from scorewhere 成績=a從Score表中查詢出大于等于平均成績的所有記錄。4. declare a numeric(5,2)，b numeric(5,2)（08年1月考）set a=(select max(成績) fromscore)setb=(selec

15、t min(成績) fromscore)print a- b求出Score表中最高成績與最低成績的分數(shù)之差5.declare a char(6)set a=劉亮if(exists(select * from students where 姓名=a)print 姓名為+a+的同學(xué)存在！elseprint 姓名為+a+的同學(xué)不存在！從Students表中查詢出姓名為a值的學(xué)生是否存在，根據(jù)不同情況顯示出相應(yīng)信息。6. declare a char(8)（08年7月考）set a=計算機select計算機專業(yè)人數(shù)=count（*）from studentsWhere left（專業(yè)，3）= a從St

16、udents表中統(tǒng)計出專業(yè)名開頭為a的值的所有學(xué)生人數(shù)7.select year(入學(xué)日期) as 入學(xué)年份,count(*) as 人數(shù)from studentsgroup by year(入學(xué)日期)從Students表中分組統(tǒng)計出每個年份入學(xué)的學(xué)生人數(shù)8. selectmonth (入學(xué)日期) as 入學(xué)月份,count(*) as 人數(shù)（09年1月考）from studentsgroup by month (入學(xué)日期)從Students表中分組統(tǒng)計出每個月份入學(xué)的學(xué)生人數(shù)9.select day(入學(xué)日期) as 入學(xué)日號,count(*) as 人數(shù)from studentsgroup

17、 by day(入學(xué)日期)從Students表中分組統(tǒng)計出每個日號入學(xué)的學(xué)生人數(shù)10. create procedurexxk1（09.7考）asbeginselect * from studentsx，score yWherex.學(xué)號 = y.學(xué)號End顯示出AAA數(shù)據(jù)庫中所有學(xué)生的記錄信息及選課成績11.create procedurexxk2（08年1月考）asbeginselect x.學(xué)號,x.姓名,x.專業(yè),count(*) as 門數(shù)from students x,score ywhere x.學(xué)號=y.學(xué)號group by x.學(xué)號,x.姓名,x.專業(yè)end顯示出AAA數(shù)據(jù)庫中每個學(xué)生的學(xué)號、姓名、專業(yè)及選課門數(shù)。12.create procedurexxk3（08年7月考）asbeginselect學(xué)號，avg（成績） as 平均成績fromscoregroupby 學(xué)號end顯示出AAA數(shù)據(jù)庫中每個學(xué)生的平均成績13.create procedurexxk4(a char(8),b varchar(10),c numeric(5,2)asbeginupdate scoreset 成績=cwhere 學(xué)號=a and 課程名=bend修改Score表中學(xué)號為a的值，課程名為b的值的學(xué)生的成績?yōu)閏的值。14.