sql練習題答案

1、(一)新建以下幾個表student(學生表)：snosnamesexdeptbirthage其中約束如下：(1)學號不能存在相同的(2)名字為非空(3)性別的值只能是男或女(4)系包括這幾個：信息系，計算機科學系，數(shù)學系，管理系，中文系，外語系，法學系(5)出生日期為日期格式(6)年齡為數(shù)值型，且在0100之間create table student (sno smallint constraint a primary key ,- 設 置學生學號為student 的主鍵sname varchar ( 10) not null,sex varchar (2) constraint b chec

2、k ( sex in('男 ','女'),一 檢查約束一性別的值只能是男或女 dept varchar ( 20) constraint c check ( dept in(' 信息系，計算機科學系，數(shù)學系，管理系，中文系 ',外語系，法學系),-檢查約束一源包括這幾個：信息系，計算機科學系，數(shù)學系，管理系，中文系， 外語系，法學系 birth datetime ,age smallint constraint d check (age between 0and 100 ）- 檢查約束一年齡為數(shù)值型，且在 100之間）cs（成績表）：snocno

3、cj其中約束如下:(1) sno和cno分別參照student和course表中的sno,cno的字段(2) cj(成績)只能在0100之間，可以不輸入值create table cs (sno smallintnotnull referencesstudent ( sno ),-定義成外鍵cno smallintnotnull referencescourse ( cno ),-定義成外鍵cj smallint constraint e check (cj between0 and 100 ),-檢查約束一-cj(成績)只能在100之間，可以不輸入值constraint f primary

4、key ( sno , cno ) 定義學生學號和課程號為sc表的主鍵)course（課程表）cnocname其約束如下:(1)課程號(cno)不能有重復的 (2)課程名(cname非空create table course (cno smallint not null constraint g primary key ,-設置課程號為course的主鍵cname varchar (20) not null)(三)針對學生課程數(shù)據(jù)庫查詢(1) 查詢?nèi)w學生的學號與姓名。Select sno , sname from student(2) 查詢?nèi)w學生的姓名、學號、所在系，并用別名顯示出結果。S

5、elect sname as '姓名',sno as '學號',dept as 所在地'from student(3) 查詢?nèi)w學生的詳細記錄。select * from student(4) 查全體學生的姓名及其出生年份。select sname , birth from student(5) 查詢學校中有哪些系。select distinct dept from student(6) 查詢選修了課程的學生學號。select sno from cs where cno is not null(7) 查詢所有年齡在20歲以下的學生姓名及其年齡。selec

6、t sname , age from student where age < 20(8) 查詢年齡在2023歲(包括20歲和23歲)之間的學生的姓名、系別和年 齡。select sname , dept , age from student where age between 20 and 23(9) 查詢年齡不在202觸之間的學生姓名、系別和年齡。select sname , dept , age from student whereage <20 or age >23(10)查詢信息系、數(shù)學系和計算機科學系生的姓名和性別。select sname , sex from s

7、tudent where dept =' 信息系or dept ='數(shù)學系'or dept ='計算機科學系(11)查詢既不是信息系、數(shù)學系，也不是計算機科學系的學生的姓名和性別。select sname , sex from student where dept !=' 信息系and dept !=數(shù)學系'and dept !=計算機科 學系(12)查詢所有姓劉學生的姓名、學號和性別。select sname , sno , sex from student where snamelike('劉')(13)查詢學號為的學生的詳細

8、情況。(具體的學號值根據(jù)表中數(shù)據(jù)確定)select * from student where sno =5(14)查詢姓“歐陽”且全名為三個漢字的學生姓名select sname from student where sname like( '歐陽)(15)查詢名字中第2個字為“晨”字的學生的姓名和學號select sname , sno from student where snamelike( '_ 晨')(16)查詢所有不姓劉的學生姓名。select sname , sno from student where sname notlike('劉')

9、(17)查詢sql課程的課程號和學分。select cno from course where cname ='sql'(18)查詢以"DB_"開頭，且倒數(shù)第3個字符為i的課程的詳細情況。select * from course where cnamelike('DBi_')(19)查詢?nèi)鄙俪煽兊膶W生的學號和相應的課程號。selectsno , cno from cswhere cjisnull(20)查所有有成績的學生學號和課程號。selectsno , cno from cswhere cjisnotnull(21)查詢計算機系年齡在20

10、歲以下的學生姓名。selectsname from studentwhereage< 20anddept ='計算機科學系(22)查詢信息系、數(shù)學系和計算機科學系學生的姓名和性別。(使用多個條件表達式)select sname , sex from student where dept = 信息系or dept ='數(shù)學系'or dept ='計算機科學系(23)查詢年齡在2023歲(包括20歲和23歲)之間的學生的姓名、系別和年 齡。(使用多個條件表達式)select sname , dept , age from student where agebe

11、tween 20 and 23(24)查詢選修了 3號課程的學生的學號及其成績，查詢結果按分數(shù)降序排列。select sno , cj from cs where cno =3 order by cjdesc(25)查詢?nèi)w學生情況，查詢結果按所在系的系號升序排列，同一系中的學生按年齡降序排列。select * from student order by dept asc , agedesc(26)查詢學生總人數(shù)。select count (*) from student(27)查詢選修了課程的學生人數(shù)。select count (sno ) from cs where cno is not

12、null(28)計算1號課程的學生平均成績。select avg ( cj ) from cs where cno = 1(29)查詢選修1號課程的學生最高分數(shù)。select max( cj ) from cs where cno =1(30)求各個課程號及相應的選課人數(shù)。select course . cno , count ( cs . sno ) from courseleft join cson course . cno =cs . cno group by course . cno(31)查詢選修了 3門以上課程的學生學號。select sno , count ( cno ) fro

13、m cs group by sno having count ( cno )> 3(32)查詢有3門以上課程是90分以上的學生的學號及(90分以上的)課程數(shù)。select sno , count (cno ) as '課程數(shù)'from cswhere cj >90group by sno having count ( cno )>= 3(33)查詢學生選修課程的總學分。select sum( course ) from course , cs wherecourse . cno =cs . sno and cs . sno =(34)查詢每個學生選修課程的總學

14、分。select sno , sum( cj ) from cs , coursewhere cs . cno =course . cnogroup by snounionselect sno , 0 from studentwhere sno not in (select sno from cs )(35)查詢每個學生及其選修課程的情況。select cs . sno , course .* from cs , course wherecs . cno =course . cno(36)查詢選修2號課程且成績在90分以上的所有學生的學號、姓名select sno , sname from s

15、tudent wheresno =( select sno from cs where cno =2 and cj >90)(37)查詢每個學生的學號、姓名、選修的課程名及成績。selectstudent . sno , sname , course . course , cs . cjfrom student , course , cs wherestudent . sno =cs . sno and cs . cno =course . cno(38)查詢與“劉晨”在同一個系學習的學生(分別用嵌套查詢和連接查詢)- -嵌套查詢select * from student where d

16、ept in(select dept from student where sname ='劉晨')- 一連接查詢select stul .* from student as stul , student as stu2where stul . dept =stu2 . dept and stu2 . sname =' 劉晨- -exists 查詢select * from student si where exists(select * from student s2 where si . dept =s2 . dept ands2 . sname ='劉晨&

17、#39;)(39)查詢選修了課程名為“信息系統(tǒng)”的學生學號和姓名select sno , sname from student where sno in(select sno from cs where cno in( select cno from course where cname ='信息系統(tǒng)')(40)查詢其他系中比信息系任意一個(其中某一個)學生年齡小的學生姓名 和年齡select sname , age from student where age <any(select age from student where dept ='信息系(41)查詢

18、其他系中比信息系所有學生年齡都小的學生姓名及年齡。分別用 ALL胃詞和集函數(shù)用 ALLselect sname , age from student where age <all(select age from student where dept ='信息系 ') 聚合函數(shù)select sname , age from student where age < (select min ( age ) from student where dept =' 信息系)查詢所有選修了 1號課程的學生姓名(42)- 嵌套查詢 select sname (select

19、sno-一連接查詢select sname where student(分別用嵌套查詢和連查詢)from studentfrom cs wherefrom student.sno =cs . snowhere sno incno =1),csand cs . cno = 1(43)查詢沒有選修1號課程的學生姓名where sno inselect distinctsno from sc scxwhere notselect sname from student (select sno from cs where cno != 1)(44)查詢選修了全部課程的學生姓名select sname f

20、rom student where not exists(select* from course where not exists(select* from cs wherecs . sno =student . sno andcs . cno =course . cno )(45)查詢至少選修了學生95002選修的全部課程的學生號碼where scy . sno ='95002where scz . sno =scx . snoexists(select* from cs scyand not exists(select* from sc scz and scz . cno =scy

21、 . cno )(46)查詢計算機科學系的學生及年齡不大于19歲的學生的信息。select * from student where dept ='計算機科學 系'or age <19(47)查詢選修了課程1或者選修了課程2的學生的信息。select student .* from student , cs wherestudent . sno = cs . sno and ( cs . cno =1 orcs . cno =2)(48)查詢計算機科學系中年齡不大于19歲的學生的信息。select * from student where age <= 19 and

22、 dept =計算機科學系(49)查詢既選修了課程1又選修了課程2的學生的信息。select * from student where sno in(select sno from cs where cno ='003' and sno in(select sno from cs where cno ='004')- 用 exists 查詢select* from studentwhereexists (select* from cs wherestudent. sno=cs .sno andcno ='003' and sno in(select sno from cs where cno ='004')(50)查詢計算機科學系的學生與年齡不大于19歲的學生