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 check(sex

2、 in('男','女',-检查约束性别的值只能是男或女dept varchar(20 constraint c check(dept in('信息系','计算机科学系','数学系','管理系','中文系','外语系','法学系',-检查约束系包括这几个：信息系，计算机科学系，数学系，管理系，中文系，外语系，法学系birth datetime,age smallint constraint d check(age between 0 and 100-检查

3、约束年龄为数值型，且在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(成绩只能在100之间，可以不输入值con

4、straint f primary 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 查询全体学生的姓名、学号、所在系，并用别名显示出结果

5、。Select 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岁以下的学生姓名及其年龄。sel

6、ect sname,age from student where age < 20(8 查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。select sname,dept,age from student where age between 20 and 23(9 查询年龄不在2023岁之间的学生姓名、系别和年龄。select sname,dept,age from student where age<20 or age>23(10 查询信息系、数学系和计算机科学系生的姓名和性别。select sname,sex from student where

7、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 sname like('刘%'(13 查询学号

8、为2009011的学生的详细情况。（具体的学号值根据表中数据确定）select * from student where sno=5(14 查询姓“欧阳”且全名为三个汉字的学生姓名select sname from student where sname like('欧阳_'(15 查询名字中第2个字为“晨”字的学生的姓名和学号select sname,sno from student where sname like('_晨'(16 查询所有不姓刘的学生姓名。select sname,sno from student where sname not like(

9、'刘%'(17 查询sql课程的课程号和学分。select cno from course where cname='sql'(18 查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况。select * from course where cname like('DB_%i_'(19 查询缺少成绩的学生的学号和相应的课程号。select sno,cno from cs where cj is null(20 查所有有成绩的学生学号和课程号。select sno,cno from cs where cj is not

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

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

12、no from cs where cno is not null(28 计算1号课程的学生平均成绩。select avg(cj from cs where cno=1(29 查询选修1号课程的学生最高分数。select max(cj from cs where cno=1(30 求各个课程号及相应的选课人数。select o,count(cs.sno from course left join cson o=o group by o(31 查询选修了3门以上课程的学生学号。select sno, count(cno from cs group by sno having count(cno&g

13、t;3(32 查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数。select sno, count(cno as '课程数' from cs where cj>90 group by sno having count(cno>=3(33 查询学生2006011选修课程的总学分。select sum(course from course,cs where o=cs.sno and cs.sno=2006011(34 查询每个学生选修课程的总学分。select sno,sum(cjfrom cs,coursewhere o=ogroup by snou

14、nionselect sno, 0 from studentwhere sno not in (select sno from cs (35 查询每个学生及其选修课程的情况。select cs.sno,course.* from cs,course where o=o (36 查询选修2号课程且成绩在90分以上的所有学生的学号、姓名select sno,sname from student where sno=(select sno from cs where cno=2 and cj>90(37 查询每个学生的学号、姓名、选修的课程名及成绩。select student.sno,sna

15、me,course.course,cs.cj from student,course,cs where student.sno=cs.sno and o=o(38 查询与“刘晨”在同一个系学习的学生（分别用嵌套查询和连接查询）-嵌套查询select * from student where dept in(select dept from student where sname='刘晨'-连接查询select stu1.* from student as stu1,student as stu2 where stu1.dept=stu2.dept and stu2.sname=

16、'刘晨'-exists查询select * from student s1 where exists(select * from student s2 where s1.dept=s2.dept ands2.sname='刘晨' (39 查询选修了课程名为“信息系统”的学生学号和姓名select sno,sname from student where sno in(select sno from cs where cno in(select cno from course where cname='信息系统'(40 查询其他系中比信息系任意一个

17、(其中某一个学生年龄小的学生姓名和年龄select sname,age from student where age (select age from student where dept='信息系'(41 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。分别用ALL谓词和集函数-用ALLselect sname,age from student where age (select age from student where dept='信息系'-聚合函数select sname,age from student where age <(selec

18、t min(age from student where dept='信息系'(42 查询所有选修了1号课程的学生姓名。（分别用嵌套查询和连查询）-嵌套查询select sname from student where sno in(select sno from cs where cno=1-连接查询select sname from student,cs where student.sno=cs.sno and o=1(43 查询没有选修1号课程的学生姓名。select sname from student where sno in(select sno from cs w

19、here cno!=1(44 查询选修了全部课程的学生姓名。select sname from student where not exists(select * from course where not exists (select * from cs where cs.sno=student.sno ando=o(45 查询至少选修了学生95002选修的全部课程的学生号码。select distinct sno from sc scx where not exists(select * from cs scy where scy.sno='95002' and not e

20、xists(select * from sc scz where scz.sno=scx.sno and o=o(46 查询计算机科学系的学生及年龄不大于19岁的学生的信息。select * from student where dept='计算机科学系' or age<19(47 查询选修了课程1或者选修了课程2的学生的信息。select student.* from student,cs where student.sno = cs.sno and (o=1 or o=2(48 查询计算机科学系中年龄不大于19岁的学生的信息。select * from student

21、 where age<=19 and 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 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 查询计算机科学系的学生与年龄不大于