sql练习题+答案

1、(一)新建以下几个表student(学生表)：snosn amesexdeptbirthage其中约束如下：(1) 学号不能存在相同的(2) 名字为非空(3) 性别的值只能是男或女(4) 系包括这几个：信息系，计算机科学系，数学系，管理系，中文系，外语系，法学系(5) 出生日期为日期格式(6) 年龄为数值型，且在0100之间create table student (sno smallint constraint a primary key ,- 设置学生学号为student 的主键sname varchar ( 10) not null,sex varchar (2) constraint

2、b check ( sex in(男,女),-检查约束一H性别的值只能是男或女 dept varchar ( 20) constraint c check ( dept in( 信息系,计算机科学系,数学系,管理系,中文系,外语系,法学系),-检查约束一療包括这几 个：信息系，计算机科学系，数学系，管理系，中文系，外 语系，法学系birth datetime ,age smallint constraint d check (age between 0and 100 )- 检查约束一辛龄为数值型，且在 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)不能有重复的(2) 课程名(cname非空create table course (cno smallint not null constraint g primary key ,-设置课程号为course的主键cname varchar (20) not null)(三)针对学生课程数据库查询(1) 查询全体学生的学号与姓名。Select sno , sname from student(2) 查询全体学生的姓名、学号、所在系，并用别名显示出结果。Select sname as 姓名,sno

5、as 学号,dept as 所在地from student(3) 查询全体学生的详细记录。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) 查询信息系、数学系和计算机科学系生的姓名和性别。select sname , sex from student where dept = 信息系or dept =数学系or dept =计算机科学系(11) 查询既不是信息系、数学系，也不是计

7、算机科学系的学生的姓名和性别。 select sname , sex from student where dept != 信息系and dept匸数学系and dept !=计算机科 学系(12) 查询所有姓刘学生的姓名、学号和性别。select sname , sno , sex from student where sname like(刘 %)(13) 查询学号为2009011的学生的详细情况。(具体的学号值根据表中数据确定)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_开头，且倒数第3个字符为i的课程的详细情况。select* fromcoursewhere cnamel

9、ike(DB_%i_)(19)查询缺少成绩的学生的学号和相应的课程号。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分以上的)课程数。selectsno ,count (cno ) as 课程数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) 查询选修了课程名为“信息系统”的学生学号和姓名select sno , sname from student where sno in (select sno from cs where cno in( select cno from course where cname =信息系统)(40) 查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名 和年龄selec

14、t sname , age from student where age any(select age from student where dept =信息系(41) 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。分别用ALL胃词和集函数-用 ALLselectsname , age fromstudentwhereage vail(select age)-聚合函数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) 求出每个系的学生数量select dept , count (sno ) as 学生个数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 语句中