SQL語句練習(xí)及答案

1、sql 語句練習(xí)題1數(shù)據(jù)庫有如下四個表格：student(sno,sname,sage,ssex ， sdpt) 學(xué)生表系表(dptno,dname)course(cno,cname, gradet, tno) 課程表sc(sno,cno,score) 成績表teacher(tno,tname) 教師表要求：完成以下操作1. 查詢姓 " 歐陽" 且全名為三個漢字的學(xué)生的姓名。select sname from student where sname like “歐陽_? ；2. 查詢名字中第2 個字為 " 陽 " 字的學(xué)生的姓名和學(xué)號。select sn

2、ame， sno from studentwhere sname like'_ 陽 %'；3. 查詢所有不姓劉的學(xué)生姓名。selectsname， sno， ssexfrom studentwhere sname not like “劉%”；4. 查詢 db_design 課程的課程號和學(xué)分。selectcno， ccredit from coursewhere cname like 'db_design'5. 查詢以 "db_" 開頭，且倒數(shù)第3 個字符為i 的課程的詳細(xì)情況。select* from course where cname

3、like'db%i_ _' ；6. 某些學(xué)生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。查詢?nèi)鄙俪煽兊膶W(xué)生的學(xué)號和相應(yīng)的課程號。select sno,cno from sc where grade is null ；7. 查所有有成績的學(xué)生學(xué)號和課程號。select sno， cno from sc where grade is not null ；8. 查詢計算機系年齡在20 歲以下的學(xué)生姓名。select sname from student where sdept= 'cs' and sage<2 0；9. 查詢選修了3 號課程的學(xué)生的學(xué)

4、號及其成績，查詢結(jié)果按分?jǐn)?shù)降序排列。selectsno，grade from sc where cno= '3 ' order by grade desc；10. 查詢學(xué)生總?cè)藬?shù)。selectcount(*) from student ；11. 查詢選修了課程的學(xué)生人數(shù)。selectcount(distinctsno) from sc；12. 計算1 號課程的學(xué)生平均成績。selectavg(grade)fromsc wherecno='1'；13. 查詢選修1 號課程的學(xué)生最高分?jǐn)?shù)。selectmax(grade)fromsc wherecno='1&

5、#39;；14. 查詢學(xué)生2 選修課程的總學(xué)分?jǐn)?shù)。selectsum(grade)fromsc,coursewhere sno= '2 ' and =；1015.查詢選修了3 門以上課程的學(xué)生學(xué)號。select sno from sc group by sno having count(*) >3;16. 查詢每個學(xué)生及其選修課程的情況。select student.* ， sc.*, course.* from student ， sc , coursewhere = and = ；17. 查詢每個學(xué)生及其選修課程的情況包括沒有選修課程的學(xué)生18. 查詢選修2 號課程且

6、成績在90 分以上的所有學(xué)生的學(xué)號、姓名select ,from student,scwhere = and = ” 2? and >90；19. 查詢每個學(xué)生的學(xué)號、姓名、選修的課程名及成績。select ， sname， ssex， sage， sdept ， cno， gradefrom student left outjoin sco on= ；20. 查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生。selectsno ， sname， sdeptfrom studentwhere sdept in(select sdept from student where sname= ”劉晨 ? )

7、 ；21. 查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號和姓名select sno ， sname from student where sno in(select sno from sc where cno in(select cno from course where cname= ”信息系統(tǒng) ? );22. 找出每個學(xué)生超過他選修課程平均成績的課程號。select sno ， cno from sc x where grade>=(select avg(grade) from sc y where =；23. 將一個新學(xué)生記錄(學(xué)號：8；姓名：陳冬；性別：男；所在系：is ；年齡：18

8、 歲)插入到student 表中。insert into student values ('8'， ' 陳冬 ' ， ' 男 ' ， 'is' ， 18) ；24. 將學(xué)生 1 的年齡改為22 歲。update student setsage=22 where sno='1'；25. 將所有學(xué)生的年齡增加1 歲。update student setsage=sage+1 ；26. 將計算機科學(xué)系全體學(xué)生的成績置零。update sc set grade=0 where exits(selete * from stu

9、dent where = and sdept= ” 計算機科學(xué)系”) ；27. 刪除學(xué)號為的學(xué)生記錄delete from student wheresno=” 8' ；28. 刪除所有的學(xué)生選課記錄。delete from sc ；29.刪除 2 號課程的所有選課記錄。delete from sc where cno='2'；30. 刪除計算機科學(xué)系所有學(xué)生的選課記錄。delete from sc where sno in（selete sno from student where sdept= ” 計算機科學(xué)系”） ；31. 建立信息系學(xué)生的視圖。create vi

10、ew is_student asselect sno ， sname， sage from student where sdept='is' ；sql 語句練習(xí)題2設(shè)教學(xué)數(shù)據(jù)庫education, 有三個關(guān)系：學(xué)生關(guān)系s（sno，sname，age， sex， sdept）；學(xué)習(xí)關(guān)系sc（ sno， cno， grade） ；課程關(guān)系c（cno，cname，cdept ， tname）查詢問題：1：查所有年齡在20 歲以下的學(xué)生姓名及年齡。select sname ， sagefrom swhere sage<20;（not age>=20）;2：查考試成績有不及格

11、的學(xué)生的學(xué)號select distinct snofrom scwhere grade<60 ；3：查所年齡在20 至 23 歲之間的學(xué)生姓名、系別及年齡。select sname ， sdept， sagefrom swhere sage between 20 and 23 ；4：查計算機系、數(shù)學(xué)系、信息系的學(xué)生姓名、性別。select sname， ssex from s where sdept in （ cs , is , math）；5：查既不是計算機系、數(shù)學(xué)系、又不是信息系的學(xué)生姓名、性別select sname ， ssex from s where sdept not in

12、cs , is , math）；6：查所有姓“劉”的學(xué)生的姓名、學(xué)號和性別。select sname ， sno， ssex from s where sname like 劉%；7：查姓“上官”且全名為3 個漢字的學(xué)生姓名。select sname from s where sname like 上官_；8：查所有不姓“張”的學(xué)生的姓名。select sname ， sno， ssex from s where sname not like 張%；9：查db_design 課程的課程號。select cno from c where cname like db_design ;10：查缺考的

13、學(xué)生的學(xué)號和課程號。select sno ， cno from sc where grade is null；11：查年齡為空值的學(xué)生的學(xué)號和姓名。select sno ， sname from s where sage is null；12：查計算機系20 歲以下的學(xué)生的學(xué)號和姓名。select sno ， snamefrom swhere sdept= cs and sage<20;13：查計算機系、數(shù)學(xué)系、信息系的學(xué)生姓名、性別。select sname ， ssexfrom swhere sdept= cs or sdept= is or sdept= math；14：查詢選修了

14、c3 課程的學(xué)生的學(xué)號和成績，其結(jié)果按分?jǐn)?shù)的降序排列。select sno ， gradefrom scwhere cno= c3order by grade desc;15： 查詢?nèi)w學(xué)生的情況，查詢結(jié)果按所在系升序排列，對同一系中的學(xué)生按年齡降序排列。select *from sorder by sdep ， sage desc ；16：查詢學(xué)生總?cè)藬?shù)。select count(*) from s;17：查詢選修了課程的學(xué)生人數(shù)。select count(distinct sno) from sc18：計算選修了c1 課程的學(xué)生平均成績。select avg(grade)from scwh

15、ere cno= c1 ;19：查詢學(xué)習(xí)c3 課程的學(xué)生最高分?jǐn)?shù)。select max(grade)from scwhere cno= c3 ;20：查詢各個課程號與相應(yīng)的選課人數(shù)。select cno, count(sno)from scgroup by cno ；21：查詢計算機系選修了3 門以上課程的學(xué)生的學(xué)號。select snofrom scwhere sdept= cs group by snohaving count(*)>3;22：求基本表s 中男同學(xué)的每一年齡組(超過50 人)有多少人要求查詢結(jié)果按人數(shù)升序排列，人數(shù)相同按年齡降序排列。select sage ， cou

16、nt ( sno)from swhere ssex='m'group by sagehaving count ( *) >50order by 2 ， sage desc ；1223：查詢每個學(xué)生及其選修課程的情況。select , sname, sage, ssex, sdept, cno, gradefrom s, scwhere = ；24：查詢選修了c2 課程且成績在90 分以上的所有學(xué)生。select ， snamefrom s ， scwhere =and = c2and >90;25：查詢每個學(xué)生選修的課程名及其成績。select ， sname， c

17、name，from s ， sc， cwhere = and =26：統(tǒng)計每一年齡選修課程的學(xué)生人數(shù)。select sage ， count( distinct )from s ， scwhere =group by sage ；27：查詢選修了c2 課程的學(xué)生姓名。select sname from s where sno in(select sno from sc where cno= c2 );28：查詢與“張三”在同一個系學(xué)習(xí)的學(xué)生學(xué)號、姓名和系別。select sno ， sname， sdept from where sdept=(select sdept from s where

18、 sname= 張三 );29：查詢選修課程名為“數(shù)據(jù)庫”的學(xué)生學(xué)號和姓名。select sno ， sname from s where sno in (select sno from sc where cno in (select cno from c where cname= db );30：查詢與“張三”在同一個系學(xué)習(xí)的學(xué)生學(xué)號、姓名和系別。select sno ， sname， sdept from s where sdept= (select sdept from s where sname= 張三)；31：查詢選修課程名為“數(shù)據(jù)庫”的學(xué)生學(xué)號和姓名。select sno ， sn

19、ame from s where sno in( select sno from sc where cno=( select cno from c where cname= db)；32：查詢選修了c2 課程的學(xué)生姓名。1. select sname from s where sno in( select sno from sc where cno= c2 );2. select sname from s where exists( select * from sc where = and cno= c2 );1533：查詢選修了全部課程的學(xué)生姓名。select sname from s wh

20、ere not exists( select * from c where not exists( select * from sc where = and =)；36：查詢所學(xué)課程包含學(xué)生s3 所學(xué)課程的學(xué)生學(xué)號select distinct sno from sc as x where not exists( select * from sc as y where = s3 and not exists( select * from sc as z where = and =)；#sql 語句練習(xí)題3一、簡單查詢1、列出全部學(xué)生的信息。select * from 學(xué)生2、列出軟件專業(yè)全部學(xué)

21、生的學(xué)號及姓名。select學(xué)號，姓名from學(xué)生where專業(yè)="軟件"3、列出所有必修課的課號。select distinct 課號 from 必修課4、求 1 號課成績大于80分的學(xué)生的學(xué)號及成績，并按成績由高到低列出。select 學(xué)號，成績 from 選課 where 課號="1"and 成績 >80 order by 成績 desc5、列出非軟件專業(yè)學(xué)生的名單。方法一：select姓名from 學(xué)生where 專業(yè)<>"軟件"方法二：select 姓名 from 學(xué)生 where not 專業(yè) =&quo

22、t;軟件 "方法三：select姓名from 學(xué)生where 專業(yè)!=" 軟件"6、查詢成績在7080分之間的學(xué)生選課得分情況方法一：select*from 選課 where 成績 >=70and 成績 <=80方法二：select*from 選課 where 成績 between70and80不在此范圍內(nèi)的查詢：(注意寫出和以下語句等價的語句)select * from 選課 where 成績 not between70and807、列出選修1 號課或 3 號課的全體學(xué)生的學(xué)號和成績。方法一：select學(xué)號，成績from選課where課號=&quo

23、t;1"or課號="3"方法二：select學(xué)號，成績from選課where課號in("1","3")相反條件查詢：select學(xué)號，成績from選課where課號notin("1","3")8、列出所有98級學(xué)生的學(xué)生成績情況。select*from 選課 where 學(xué)號 like"98%"select*from 選課 where 學(xué)號 like"98"相反條件查詢：select*from 選課where學(xué)號notlike"98%&

24、quot;9、列出成績?yōu)榭罩担ɑ虿粸榭罩担┑膶W(xué)生的學(xué)號和課號。答案一：select 學(xué)號，課號 from 選課 where成績isnull答案二：select 學(xué)號，課號 from 選課 where成績isnotnull10、求出所有學(xué)生的總成績。select sum（ 成績）as 總成績 from 選課11、列出每個學(xué)生的平均成績。select學(xué)號,avg（成績）as 平均成績from選課group by 學(xué)號12、列出各科的平均成績、最高成績、最低成績和選課人數(shù)。select課號,avg（成績）as平均成績,max（成績）as最高分,;min（成績）as 最低分,count（ 學(xué)號）as

25、選課人數(shù) from 選課 group by 課號二、連接查詢（一）簡單連接1、列出選修1號課的學(xué)生姓名及成績。select 姓名，成績from 學(xué)生，選課where學(xué)生.學(xué)號=選課.學(xué)號and課號 ="1"2、列出選修1號課的學(xué)生的學(xué)號、姓名及成績。select學(xué)生.學(xué)號，姓名，成績from學(xué)生s,選課xwheres.學(xué)號=x.學(xué)號and 課號""1"3、求出總分大于150的學(xué)生的學(xué)號、姓名及總成績。select學(xué)生.學(xué)號,姓名,sum（成績）as總成績from學(xué)生,選課；where 學(xué)生 . 學(xué)號=選課. 學(xué)號 groupby 選課 . 學(xué)號

26、 havingsum( 成績 )>150(二)自連接查詢1、列出那些專業(yè)相同的學(xué)生相應(yīng)的姓名及專業(yè)信息。select a. 姓名 ,b. 姓名 , 專業(yè) from 學(xué)生 a, 學(xué)生 bwherea. 學(xué)號 <>b. 學(xué)號 anda. 專業(yè)=b. 專業(yè)2、求至少選修1 號課和 2 號課的學(xué)生的學(xué)號。selectx. 學(xué)號 from 選課 x, 選課ywherex. 學(xué)號 =y. 學(xué)號 andx. 課號 ="1"andy.課號="2"3、有以下表幣種 1 代碼 c(2) 、幣種 2 代碼 c(2) 、買入價n(8,4) 、賣出價n(8,4)

27、外匯匯率.dbf幣種 1c(4) 、幣種 2c(4) 、買入價n(8,4) 、賣出價n(8,4)外匯代碼.dbf外匯名稱c(10)、外匯代碼c(10)要求：將所有“外匯匯率”表中的數(shù)據(jù)插入 rate 表中并且順序不變，由于“外匯匯率”中的幣種 1 和幣種 2 存放的是外幣名稱，而rate 表中的幣種1 代碼和幣種2 代碼應(yīng)該存放外幣代碼，所以插入時要做相應(yīng)的改動，外幣名稱與外向代碼的對應(yīng)關(guān)系存儲在“外匯代碼”表中。selecta. 外幣代碼as 幣種 1 代碼 ,b. 外幣代碼as 幣種 2 代碼 ,;買入價 , 賣出價 from 外匯代碼a, 外匯匯率 , 外匯代碼b;wherea.外幣名稱

28、=外匯匯率.幣種landb.外幣名稱=外匯匯率.幣種 2intotablerate4、 假定有“雇員”表(雇員號c( 2)，雇員姓名c( 6)，經(jīng)理號c( 2)，根據(jù)雇員關(guān)系列出上一級經(jīng)理及其所領(lǐng)導(dǎo)的職員清單。(教案中的例題)select" 領(lǐng)導(dǎo) ",s. 雇員姓名," 雇員 ",e. 雇員姓名from 雇員 s, 雇員 ewheres.雇員號 =e. 經(jīng)理(三)超連接1、列出選修1 號課的學(xué)生姓名及成績。方法一：（使用簡單連接查詢格式）select姓名,成績from學(xué)生,選課where學(xué)生.學(xué)號=選課.學(xué)號and課號="1"方法二：

29、（使用內(nèi)部連接格式）select 姓名 , 成績 from 學(xué)生 innerjoin 選課 on 學(xué)生 . 學(xué)號=選課. 學(xué)號 where課號 ="1"方法三：內(nèi)部連接的inner 短語可以省略。（與方法二等價）select 姓名 , 成績 from 學(xué)生 join 選課 on 學(xué)生 . 學(xué)號=選課. 學(xué)號 where 課號="1"2、查詢訂貨管理數(shù)據(jù)庫中數(shù)據(jù)的倉庫號、城市、供應(yīng)商名和地址信息。方法一：使用簡單連接格式。select 倉庫 . 倉庫號 , 城市 , 供應(yīng)商名, 地址 from 供應(yīng)商 , 訂購單 , 職工 , 倉庫 ;where供應(yīng)商.供

30、應(yīng)商號=訂購單.供應(yīng)商號and訂購單.職工號二職工.職工 號;and職工.倉庫號二倉庫.倉庫號方法二：使用超連接的內(nèi)部連接格式。（注意連接條件的順序）select 倉庫 . 倉庫號 , 城市 , 供應(yīng)商名, 地址 from 供應(yīng)商 join 訂購單 join 職工 join 倉庫 ;on職工.倉庫號=倉庫.倉庫號on訂購單.職工號二職工.職工號on供應(yīng)商.供應(yīng)商號=訂購單. 供應(yīng)商號3、查詢沒有選修任何課程的學(xué)生姓名。方法一：使用嵌套查詢select 姓名 from 學(xué)生 where 學(xué)號 notin（select 學(xué)號 from 選課 ）方法二：使用超連接的右連接。select姓名from選

31、課rightjoin 學(xué)生on選課.學(xué)號二學(xué)生.學(xué)號where選課.學(xué)號<>學(xué)生. 學(xué)號方法三：使用超連接的左連接。（注意表名順序和方法二的不同）select姓名from學(xué)生leftjoin 選課on選課.學(xué)號二學(xué)生.學(xué)號where選課. 學(xué)號<>學(xué)生. 學(xué)號三、嵌套查詢（一）普通嵌套與謂詞exists1、列出選修匯編語言課的學(xué)生的學(xué)號。方法一：select 學(xué)號 from 選課 where 課號 =（select 課號 from 課程 where 課名="匯編語言 "）方法二：使用謂詞exists 。注意和方法一格式上的不同。select 學(xué)號 f

32、rom 選課 whereexist（select*from 課程 ;where 課名 ="匯編語言"and 選課 . 課號=課程. 課號 ）2、求軟件專業(yè)所有必修課的課程信息。方法一：select*from 課程 where 課號 in;（select 課號 from 必修課 where 必修專業(yè)="軟件 "）方法二：select*from 課程 whereexist（select*from 必修課 where 必修專業(yè)=" 軟件; ;and課程.課號=必修課.課號）（二）量詞any、 some、 all1、求選修2 號課的學(xué)生中，成績比選修1

33、 號課的最低成績要高的學(xué)生的學(xué)號和成績。方法一：select 學(xué)號, 成績from 選課where 課號="2"and成績 >（selectmin（成績）from 選課where 課號="1"）方法二：any等價于 some 所以可將 any 換成 somaselect 學(xué)號 , 成績 from 選課 where 課號 ="2"and 成績 >any;（select 成績 from 選課 where 課號 ="1"）2、求選修2 號課的學(xué)生中，成績比選修1 號課的任何學(xué)生的成績都要高的那些學(xué)生的學(xué)號和成

34、績。方法一：select 學(xué)號 , 成績 from 選課where 課號="2"and 成績 >（selectmax（ 成績）from 選課where 課號="1"）方法二：select 學(xué)號 , 成績 from 選課 where 課號 ="2"and 成績 >all;（select 成績 from 選課where 課號="1"）（三）內(nèi)外層互相關(guān)嵌套（外層依賴于內(nèi)層的查詢結(jié)果，內(nèi)層依賴于外層來進一步查詢）1、列出每門課程中成績最高的選課信息。select*from 選課 awhere 成績 =（sel

35、ectmax（ 成績 ）from 選課 bwherea. 課號 =b. 課號 ）2、列出每個學(xué)生中成績低于本人平均成績的選課信息。select*from 選課 awhere 成績 <（selectavg（ 成績 ）from 選課 bwherea. 學(xué)號 =b. 學(xué)號 ）3、列出表“訂購單”（ 其內(nèi)容就是在訂購單表的基礎(chǔ)上增加一個總金額字段）中每個職工經(jīng)手的具有最高總金額的訂購單信息。（教案中例題）selectout. 職工號 ,out. 供應(yīng)商號,out. 訂貨單號,out. 訂貨日期,out. 總金額;from 訂購單 outwhere 總金額 =（selectmax（ 總金額 ）from 訂購單 inner1;whereout. 職工號 =inner1. 職工號 ）四、操作功能1、在課程表中插入新的元組(5,大學(xué)英語)insertinto 課程(課號,課名)values("5","大學(xué)英語")2、給學(xué)生表中男生的記錄加上刪除標(biāo)記。deletefrom 學(xué)生 where 性別="男"3、將選課表中所有課程的成績分別提高5分。將課號為“ 4”的課程的成績置為空值。update選課set成績=成績+5update 選課 set 成績=nullwhere 課號="4"五、定義表結(jié)構(gòu)1