sql練習(xí)題+答案

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

2、(sex in('男','女'),-檢查約束性別的值只能是男或女dept varchar(20) constraint c check(dept in('信息系','計算機(jī)科學(xué)系','數(shù)學(xué)系','管理系','中文系','外語系','法學(xué)系'),-檢查約束系包括這幾個：信息系，計算機(jī)科學(xué)系，數(shù)學(xué)系，管理系，中文系，外語系，法學(xué)系birth datetime,age smallint constraint d check(age between 0 and

3、 100)-檢查約束年齡為數(shù)值型，且在100之間)cs(成績表)：snocnocj其中約束如下：（1）sno和cno分別參照student和course表中的sno,cno的字段（2）cj(成績)只能在0100之間，可以不輸入值create table cs(sno smallint not null references student(sno),-定義成外鍵cno smallint not null references course(cno),-定義成外鍵cj smallint constraint e check(cj between 0 and 100),-檢查約束cj(成績)只能在

4、100之間，可以不輸入值constraint f primary key(sno,cno)-定義學(xué)生學(xué)號和課程號為sc表的主鍵)course（課程表）cnocname其約束如下：（1）課程號（cno）不能有重復(fù)的（2）課程名（cname）非空create table course(cno smallint not null constraint g primary key,-設(shè)置課程號為course的主鍵cname varchar(20) not null)（三）針對學(xué)生課程數(shù)據(jù)庫查詢(1) 查詢?nèi)w學(xué)生的學(xué)號與姓名。Select sno,sname from student(2) 查詢?nèi)w學(xué)

5、生的姓名、學(xué)號、所在系，并用別名顯示出結(jié)果。Select sname as '姓名',sno as '學(xué)號',dept as '所在地' from student(3) 查詢?nèi)w學(xué)生的詳細(xì)記錄。select * from student(4) 查全體學(xué)生的姓名及其出生年份。select sname,birth from student(5) 查詢學(xué)校中有哪些系。select distinct dept from student(6) 查詢選修了課程的學(xué)生學(xué)號。select sno from cs where cno is not null(7)

6、查詢所有年齡在20歲以下的學(xué)生姓名及其年齡。select sname,age from student where age < 20(8) 查詢年齡在2023歲（包括20歲和23歲）之間的學(xué)生的姓名、系別和年齡。select sname,dept,age from student where age between 20 and 23(9) 查詢年齡不在2023歲之間的學(xué)生姓名、系別和年齡。select sname,dept,age from student where age<20 or age>23(10) 查詢信息系、數(shù)學(xué)系和計算機(jī)科學(xué)系生的姓名和性別。select s

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

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

9、no from student where sname not like('劉%')(17) 查詢sql課程的課程號和學(xué)分。select cno from course where cname='sql'(18) 查詢以"DB_"開頭，且倒數(shù)第3個字符為 i的課程的詳細(xì)情況。select * from course where cname like('DB_%i_')(19) 查詢?nèi)鄙俪煽兊膶W(xué)生的學(xué)號和相應(yīng)的課程號。select sno,cno from cs where cj is null(20) 查所有有成績的學(xué)生學(xué)號和

10、課程號。select sno,cno from cs where cj is not null(21) 查詢計算機(jī)系年齡在20歲以下的學(xué)生姓名。select sname from student where age < 20 and dept='計算機(jī)科學(xué)系'(22) 查詢信息系、數(shù)學(xué)系和計算機(jī)科學(xué)系學(xué)生的姓名和性別。（使用多個條件表達(dá)式）select sname,sex from student where dept='信息系' or dept='數(shù)學(xué)系' or dept='計算機(jī)科學(xué)系'(23) 查詢年齡在2023歲（包

11、括20歲和23歲）之間的學(xué)生的姓名、系別和年齡。（使用多個條件表達(dá)式）select sname,dept,age from student where age between 20 and 23 (24) 查詢選修了3號課程的學(xué)生的學(xué)號及其成績，查詢結(jié)果按分?jǐn)?shù)降序排列。select sno,cj from cs where cno=3 order by cj desc(25) 查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系號升序排列，同一系中的學(xué)生按年齡降序排列。select * from student order by dept asc,age desc(26) 查詢學(xué)生總?cè)藬?shù)。select co

12、unt(*) from student(27) 查詢選修了課程的學(xué)生人數(shù)。select count(sno) from cs where cno is not null(28) 計算1號課程的學(xué)生平均成績。select avg(cj) from cs where cno=1(29) 查詢選修1號課程的學(xué)生最高分?jǐn)?shù)。select max(cj) from cs where cno=1(30) 求各個課程號及相應(yīng)的選課人數(shù)。select o,count(cs.sno) from course left join cson o=o group by o(31) 查詢選修了3門以上課程的學(xué)生學(xué)號。se

13、lect sno, count(cno) from cs group by sno having count(cno)>3(32) 查詢有3門以上課程是90分以上的學(xué)生的學(xué)號及（90分以上的）課程數(shù)。select sno, count(cno) as '課程數(shù)' from cs where cj>90 group by sno having count(cno)>=3(33) 查詢學(xué)生2006011選修課程的總學(xué)分。select sum(course) from course,cs where o=cs.sno and cs.sno=2006011(34) 查

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

15、ere cno=2 and cj>90)(37) 查詢每個學(xué)生的學(xué)號、姓名、選修的課程名及成績。select student.sno,sname,course.course,cs.cj from student,course,cs where student.sno=cs.sno and o=o(38) 查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生（分別用嵌套查詢和連接查詢）-嵌套查詢select * from student where dept in(select dept from student where sname='劉晨')-連接查詢select stu1.* fro

16、m student as stu1,student as stu2 where stu1.dept=stu2.dept and stu2.sname='劉晨'-exists查詢select * from student s1 where exists(select * from student s2 where s1.dept=s2.dept ands2.sname='劉晨' )(39) 查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號和姓名select sno,sname from student where sno in(select sno from cs whe

17、re cno in(select cno from course where cname='信息系統(tǒng)')(40) 查詢其他系中比信息系任意一個(其中某一個)學(xué)生年齡小的學(xué)生姓名和年齡select sname,age from student where age <any(select age from student where dept='信息系')(41) 查詢其他系中比信息系所有學(xué)生年齡都小的學(xué)生姓名及年齡。分別用ALL謂詞和集函數(shù)-用ALLselect sname,age from student where age <all(select

18、age from student where dept='信息系')-聚合函數(shù)select sname,age from student where age <(select min(age) from student where dept='信息系')(42) 查詢所有選修了1號課程的學(xué)生姓名。（分別用嵌套查詢和連查詢）-嵌套查詢select sname from student where sno in(select sno from cs where cno=1)-連接查詢select sname from student,cs where stud

19、ent.sno=cs.sno and o=1(43) 查詢沒有選修1號課程的學(xué)生姓名。select sname from student where sno in(select sno from cs where cno!=1)(44) 查詢選修了全部課程的學(xué)生姓名。select sname from student where not exists(select * from course where not exists (select * from cs where cs.sno=student.sno ando=o)(45) 查詢至少選修了學(xué)生95002選修的全部課程的學(xué)生號碼。sel

20、ect distinct sno from sc scx where not exists(select * from cs scy where scy.sno='95002' and not exists(select * from sc scz where scz.sno=scx.sno and o=o)(46) 查詢計算機(jī)科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生的信息。select * from student where dept='計算機(jī)科學(xué)系' or age<19(47) 查詢選修了課程1或者選修了課程2的學(xué)生的信息。select student.

21、* from student,cs where student.sno = cs.sno and (o=1 or o=2)(48) 查詢計算機(jī)科學(xué)系中年齡不大于19歲的學(xué)生的信息。select * from student where age<=19 and dept='計算機(jī)科學(xué)系'(49) 查詢既選修了課程1又選修了課程2的學(xué)生的信息。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 student where exists (select * from cs where student.sno=cs.sno and cno='003' and sno in(select sno from cs where cno='004')(50) 查詢計算機(jī)科學(xué)系的學(xué)生與年齡