sql练习题答案

1、(一)新建以下几个表student(学生表)：snosnamesexdeptbirthage其中约束如下：(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 b chec

2、k ( sex in('男 ','女'),一 检查约束一性别的值只能是男或女 dept varchar ( 20) constraint c check ( dept in(' 信息系，计算机科学系，数学系，管理系，中文系 ',外语系，法学系),-检查约束一源包括这几个：信息系，计算机科学系，数学系，管理系，中文系， 外语系，法学系 birth datetime ,age smallint constraint d check (age between 0and 100 ）- 检查约束一年龄为数值型，且在 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)(三)针对学生课程数据库查询(1) 查询全体学生的学号与姓名。Select sno , sname from student(2) 查询全体学生的姓名、学号、所在系，并用别名显示出结果。S

5、elect sname as '姓名',sno as '学号',dept as 所在地'from student(3) 查询全体学生的详细记录。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)查询信息系、数学系和计算机科学系生的姓名和性别。select sname , sex from s

7、tudent where dept =' 信息系or dept ='数学系'or dept ='计算机科学系(11)查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。select sname , sex from student where dept !=' 信息系and dept !=数学系'and dept !=计算机科 学系(12)查询所有姓刘学生的姓名、学号和性别。select sname , sno , sex from student where snamelike('刘')(13)查询学号为的学生的详细

8、情况。(具体的学号值根据表中数据确定)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_"开头，且倒数第3个字符为i的课程的详细情况。select * from course where cnamelike('DBi_')(19)查询缺少成绩的学生的学号和相应的课程号。selectsno , cno from cswhere cjisnull(20)查所有有成绩的学生学号和课程号。selectsno , cno from cswhere cjisnotnull(21)查询计算机系年龄在20

10、岁以下的学生姓名。selectsname from studentwhereage< 20anddept ='计算机科学系(22)查询信息系、数学系和计算机科学系学生的姓名和性别。(使用多个条件表达式)select sname , sex from student where dept = 信息系or dept ='数学系'or dept ='计算机科学系(23)查询年龄在2023岁(包括20岁和23岁)之间的学生的姓名、系别和年 龄。(使用多个条件表达式)select sname , dept , age from student where agebe

11、tween 20 and 23(24)查询选修了 3号课程的学生的学号及其成绩，查询结果按分数降序排列。select sno , cj from cs where cno =3 order by cjdesc(25)查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。select * from student order by dept asc , agedesc(26)查询学生总人数。select count (*) from student(27)查询选修了课程的学生人数。select count (sno ) from cs where cno is not

12、null(28)计算1号课程的学生平均成绩。select avg ( cj ) from cs where cno = 1(29)查询选修1号课程的学生最高分数。select max( cj ) from cs where cno =1(30)求各个课程号及相应的选课人数。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分以上的)课程数。select sno , count (cno ) as '课程数'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)查询选修了课程名为“信息系统”的学生学号和姓名select sno , sname from student where sno in(select sno from cs where cno in( select cno from course where cname ='信息系统')(40)查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名 和年龄select sname , age from student where age <any(select age from student where dept ='信息系(41)查询

18、其他系中比信息系所有学生年龄都小的学生姓名及年龄。分别用 ALL胃词和集函数用 ALLselect sname , age from student where age <all(select age from student where dept ='信息系 ') 聚合函数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岁的学生