sql練習題+答案

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

2、b check ( sex in(男,女),-檢查約束一H性別的值只能是男或女 dept varchar ( 20) constraint c check ( dept in( 信息系,計算機科學系,數(shù)學系,管理系,中文系,外語系,法學系),-檢查約束一療包括這幾 個：信息系，計算機科學系，數(shù)學系，管理系，中文系，外 語系，法學系birth datetime ,age smallint constraint d check (age between 0and 100 )- 檢查約束一辛齡為數(shù)值型，且在 100之間)cs(成績表):snocnocj其中約束如下:(1) sno和cno分別參照st

3、udent和course表中的sno,cno的字段(2) cj(成績)只能在0100之間，可以不輸入值createtablecs (sno smallintnot null referencesstudent ( sno ),-定義成外鍵cno smallintnot null referencescourse ( cno ),-定義成外鍵cj smallint constraint e check (cj between0 and 100 ),- 檢查約束 一j(成績)只能在100之間，可以不輸入值constraint f primary key ( sno , cno )- 定義學生學號和

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

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

6、023歲(包括20歲和23歲)之間的學生的姓名、系別和年齡。select sname , dept , age from student where agebetween 20 and 23(9) 查詢年齡不在2023歲之間的學生姓名、系別和年齡。select sname , dept , age from student whereage 23(10) 查詢信息系、數(shù)學系和計算機科學系生的姓名和性別。select sname , sex from student where dept = 信息系or dept =數(shù)學系or dept =計算機科學系(11) 查詢既不是信息系、數(shù)學系，也不是計

7、算機科學系的學生的姓名和性別。 select sname , sex from student where dept != 信息系and dept匸數(shù)學系and dept !=計算機科 學系(12) 查詢所有姓劉學生的姓名、學號和性別。select sname , sno , sex from student where sname like(劉 %)(13) 查詢學號為2009011的學生的詳細情況。(具體的學號值根據(jù)表中數(shù)據(jù)確定)select * from student where sno =5(14) 查詢姓“歐陽”且全名為三個漢字的學生姓名select sname from stud

8、ent where sname like(歐陽_)(15) 查詢名字中第2個字為“晨”字的學生的姓名和學號select sname , sno from student where snamelike( _ 晨)(16) 查詢所有不姓劉的學生姓名。select sname , sno from student where sname not like(劉 %)(17)查詢sql課程的課程號和學分select cno from course where cname =sql(18)查詢以DB_開頭，且倒數(shù)第3個字符為i的課程的詳細情況。select* fromcoursewhere cnamel

9、ike(DB_%i_)(19)查詢?nèi)鄙俪煽兊膶W生的學號和相應(yīng)的課程號。select sno , eno from cs where cj is null(20) 查所有有成績的學生學號和課程號。select sno , cno from cs where cj is not null(21) 查詢計算機系年齡在20歲以下的學生姓名。select sname from student where age 3(32)查詢有3門以上課程是90分以上的學生的學號及(90分以上的)課程數(shù)。selectsno ,count (cno ) as 課程數(shù)from cswherecj 90groupby sno

10、having count ( cno )= 3(33)查詢學生2006011選修課程的總學分select sum( course ) from course , cs wherecourse . cno =cs . sno and cs . sno =2006011(34)查詢每個學生選修課程的總學分。selectsno , sum( cj ) from cs , coursewherecs . cno =course . cnogroupby snounionselect sno , 0 from studentwhere sno not in (select sno from cs )(3

11、5) 查詢每個學生及其選修課程的情況。select cs . sno , course .* from cs , course where cs . cno =course . cno(36) 查詢選修2號課程且成績在90分以上的所有學生的學號、姓名select sno , sname from student wheresno =( select sno from cs where cno =2 and cj 90)(37) 查詢每個學生的學號、姓名、選修的課程名及成績selectstudent.sno , sname , course . course , cs . cjfrom stud

12、ent,course,cswherestudent.sno=cs . snoandcs . cno =course . cno(38) 查詢與“劉晨”在同一個系學習的學生(分別用嵌套查詢和連接查詢)-嵌套查詢select * from student where dept in(select dept from student where sname =劉晨)-連接查詢select stul .* from student as stul , student as stu2where stul . dept =stu2 . dept and stu2 . sname =劉晨-exists 查詢

13、select * from student si where exists(select * from student s2 where si . dept =s2 . deptands2 . sname =劉晨)(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) 查詢其他系中比信息系任意一個(其中某一個)學生年齡小的學生姓名 和年齡selec

14、t sname , age from student where age any(select age from student where dept =信息系(41) 查詢其他系中比信息系所有學生年齡都小的學生姓名及年齡。分別用ALL胃詞和集函數(shù)-用 ALLselectsname , age fromstudentwhereage vail(select age)-聚合函數(shù)fromstudentwhere dept=信息系selectsname,agefromstudentwhereage (selectmin ( age )fromstudentwheredept =信息系)(42)查詢所

15、有選修了 1號課程的學生姓名。(分別用嵌套查詢和連查詢)-嵌套查詢select snamefromstudentwhere snoin(select snofromcs wherecno=1)-連接查詢select snamefromstudent,cswhere student.sno=cs . snoandcs . cno :=1(43)查詢沒有選修1號課程的學生姓名。select sname from student where sno in(select sno from cs where eno != 1)(44)查詢選修了全部課程的學生姓名。select sname from st

16、udent where not exists (select* from course where not exists(select* from cs wherecs . sno =student . sno andcs . cno =course . cno )(45)查詢至少選修了學生95002選修的全部課程的學生號碼selectdistinctsnofrom sc scxwhere notexists(select* fromcs scywherescy.sno =95002and notexists(select* fromsc sczwherescz.sno =scx . snoa

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

18、號為1學生的總分和平均分。select sum( cj ) as 總分,avg ( cj )平均分from cs where sno =1(52) 求出每個系的學生數(shù)量select dept , count (sno ) as 學生個數(shù)from student group by dept(53) 查詢平均成績大于85的學生學號及平均成績。select sno , avg ( cj ) from cs group by sno having avg ( cj ) 85(54) 要求查尋學生的所有信息，并且查詢的信息按照年齡由高到低排序，如 果年齡相等，則按照學號從低到高排序select * from student order by age desc , sno asc1.在 SELECT 語句中