sql练习题+答案.doc

(一) 新建以下几个表
student(学生表)：
sno
sname
sex
dept
birth
age

其中约束如下：
（1） 学号不能存在相同的
（2） 名字为非空
（3） 性别的值只能是’男’或’女’
（4） 系包括这几个：信息系，计算机科学系，数学系，管理系，中文系，外语系，法学系
（5） 出生日期为日期格式
（6） 年龄为数值型，且在0~100之间
create table student(
sno smallint constraint a primary key,----设置学生学号为student的主键
sname varchar(10) not null,
sex varchar(2) constraint b check(sex in('男','女')),----检查约束——性别的值只能是’男’或’女’
dept varchar(20) constraint c check(dept in('信息系','计算机科学系','数学系','管理系','中文系','外语系','法学系')),----检查约束——系包括这几个：信息系，计算机科学系，数学系，管理系，中文系，外语系，法学系
birth datetime,
age smallint constraint d check(age between 0 and 100)----检查约束——年龄为数值型，且在~100之间
)

cs(成绩表)：
sno
cno
cj

其中约束如下：
（1）sno和cno分别参照student和course表中的sno,cno的字段
（2）cj(成绩)只能在0~100之间，可以不输入值
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之间，可以不输入值
constraint f primary key(sno,cno)----定义学生学号和课程号为sc表的主键
)

course（课程表）
cno
cname

其约束如下：
（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 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岁以下的学生姓名及其年龄。
select sname,age from student where age < 20

(8) 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。
select sname,dept,age from student where age between 20 and 23

(9) 查询年龄不在20~23岁之间的学生姓名、系别和年龄。
select sname,dept,age from student where age<20 or age>23

(10) 查询信息系、数学系和计算机科学系生的姓名和性别。
select sname,sex from student 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 sname like('刘%')

(13) 查询学号为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('刘%')

(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 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) 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。（使用多个条件表达式）
select sname,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(sno) 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 cs
on o=o group by o

(31) 查询选修了3门以上课程的学生学号。
select sno, count(cno) from cs group by sno having count(cno)>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(cj)from cs,course
where o=o
group by sno
union
select sno, 0 from student
where 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,sname,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='刘晨'
----exists查询
select * from student s1 where exists(
select * from student s2 where s1.dept=s2.dept and
s2.sname='刘晨'
)

(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 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 where cno!=1)

(44) 查询选修了全部课程的学生姓名。
select sname from student where not exists
(select * from course where not exists
(select * from cs where
cs.sno=student.sno and
o=o))

(45) 查询至少选修了学生95002选修的全部课程的学生号码。
select 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) 查询计算机科学系的学生及年龄不大于19岁的学生的信息。
select * from student where dept='计算机科学系' or age<19

(47) 查询选修了课程1或者选修了课程2的学生的信息。
select student.* from student,cs where student.sno = cs.sno and (o1 or o=2)

(48) 查询计算机科学系中年龄不大于19岁的学生的信息。
select * from student 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) 查询计算机科学系的学生与年龄不大于19岁的学生的差集。
select *from student where dept='计算机科学系' and age>19

(51) 通过查询求学号为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 asc

1．在SELECT语句中DISTINCT、ORDER BY、GROUP BY和HAVING子句的功能各是什么？

答 各子句的功能如下。
DISTINCT：查询唯一结果。
ORDER BY：使查询结果有序显示。
GROUP BY：对查询结果进行分组。
HAVING：筛选分组结果。

2．在一个SELECT语句中，当WHERE子句、GROUP BY子句和HAVING子句同时出现在一个查询中时，SQL的执行顺序如何？
答 其执行顺序如下：
（1）执行WHERE子句，从表中选取行。
（2）由GROUP BY对选取的行进行分组。
（3）执行聚合函数。
（4）执行HAVING子句选取满足条件的分组。