（完整版）sql语句练习题及答案
⼀在数据库 school 中建⽴student , sc, course 表。
学⽣表、课程表、选课表属于数据库School ，其各⾃的数据结构如下：学⽣Student (Sno,Sname,Ssex,Sage,Sdept)
课程表course(Cno,Cname,Cpno,Ccredit)
学⽣选课SC(Sno,Cno,Grade)
⼆设定主码
1 Student表的主码：sno
2 Course表的主码：cno
3 Sc表的主码：sno，cno
1写出使⽤ Create Table 语句创建表 student , sc, course 的SQL语句2
3 删除student表中的元组
4在数据库school中删除关系student
5在student表添加属性sbirthdate 类型datetime
Delete
1 删除所有JSJ 系的男⽣delete from Student where Sdept=’JSJ’ and Ssex=’男’;
2 删除“数据库原理”的课的选课纪录
delete from SC where Cno in (select Cno fromCourse where Cname=’数据库原理’);
Update
1 修改0001 学⽣的系科为: JSJ
2 把陈⼩明的年龄加1岁，性别改为⼥。
2 修改李⽂庆的1001课程的成绩为9
3 分
3 把“数据库原理”课的成绩减去1分
Select 查询语句
⼀单表
1查询年龄在19⾄21岁之间的⼥⽣的学号，姓名，年龄，按年龄从⼤到⼩排列。
2查询姓名中第2个字为“明”字的学⽣学号、性别。
3查询 1001课程没有成绩的学⽣学号、课程号
4查询JSJ 、SX、WL 系的年龄⼤于25岁的学⽣学号，姓名，结果按系排列
5按10分制查询学⽣的sno,cno,10分制成绩
（1-10分为1 ，11-20分为2 ，30-39分为3，。。。90-100为10）
6查询 student 表中的学⽣共分布在那⼏个系中。（distinct）
7查询0001号学⽣1001，1002课程的成绩。
⼆统计
1查询姓名中有“明”字的学⽣⼈数。
2计算‘JSJ’系的平均年龄及最⼤年龄。
3查询学⽣中姓名为张明、赵英的⼈数
4计算每⼀门课的总分、平均分，最⾼分、最低分，按平均分由⾼到低排列
5 计算 1001,1002 课程的平均分。
6 查询平均分⼤于80分的学⽣学号及平均分
7 统计选修课程超过 2 门的学⽣学号
8 统计有10位成绩⼤于85分以上的课程号。
9 统计平均分不及格的学⽣学号
10 统计有⼤于两门课不及格的学⽣学号
三连接
distinct查询
1查询 JSJ 系的学⽣选修的课程号
2查询选修1002 课程的学⽣的学⽣姓名 (不⽤嵌套及嵌套2种⽅法）
3查询数据库原理不及格的学⽣学号及成绩
4查询选修“数据库原理”课且成绩 80 以上的学⽣姓名(不⽤嵌套及嵌套2种⽅法）
5查询平均分不及格的学⽣的学号，姓名，平均分。
6查询⼥学⽣平均分⾼于75分的学⽣姓名。
7查询男学⽣学号、姓名、课程号、成绩。(⼀门课程也没有选修的男学⽣也要列出，不能四嵌套、相关及其他
1 查询平均分不及格的学⽣⼈数
2 查询没有选修1002 课程的学⽣的学⽣姓名
3 查询平均分最⾼的学⽣学号及平均分（2种⽅法 TOP , any , all）
*4 查询没有选修1001，1002课程的学⽣姓名。
5 查询1002课程第⼀名的学⽣学号（2种⽅法）
6 查询平均分前三名的学⽣学号
7 查询 JSJ 系的学⽣与年龄不⼤于19岁的学⽣的差集
8 查询1001号课程⼤于90分的学⽣学号、姓名及平均分⼤于85分的学⽣学号、姓名9查询每门课程成绩都⾼于该门课程平均分的学⽣学号
10 查询⼤于本系科平均年龄的学⽣姓名
答案
参考答案
1 create table student
(sno char(6),
sname varchar(8),
ssex char(2),
sage smallint,
sdept varchar(15),
primary key(sno));
create table sc
(sno char(6),
cno char(4),
grade decimal(12,2),
primary key(sno,cno));
insert into student
values( ’4001’,’赵茵’,’男’,20,’SX’)
delete from student
drop table student
alter table student add sbirthdate datetime
1 select sno, sname, sage
from student
where ssex=’⼥’and sage between 19 and 21
order by sage desc;
2 select sno, ssex
from student
where sname like ’_明% ’;
3 select sno, cno
from sc
where grade is null and cno=’1001’;
4 select sno, sname
from student
where sdept in (’JSJ’,’SX’,’WL’) and sage>25
group by sdept;
select sno, cno, grade/10.0+1 as level
from sc ;
select distinct sdept from student ;
select grade
from sc
where sno=’0001’and (cno=’1001’or cno=’1002’) ;
select count(*) from student where sname like ’%明% ’;
select avg(sage),max(sage) from student where sdept=’JSJ’;
select cno,sum(grade),avg(grade),max(grade),min(grade) from sc
group by cno
order by avg(grade) desc ;
select cno, avg(grade) from sc where cno in(‘1001’,’1002’)
group by cno ;
select sc.sno ,avg(grade) from sc
group by sc.sno
having avg(grade)>80 ;
select sno from sc group by sno having count(*)>2 ;
select cno from sc where grade>85 group by cno having count(*)=10 ;
select sno from sc group by sno having avg(grade)<60 ;
select sno from sc where grade<60 group by sno having count(*)>2 ;
select cno from student,sc where student.sno=sc.sno and sdept=’JSJ’;
a:select sname from student,sc where student.sno=sc.sno and cno=’1002’
b:select sname from student where sno in (select sno from sc where cno=’1002’)
select sno,grade from sc,course
where www.doczj/doc/2f54d8860708763231126edb6f1aff00bfd5707a.html
o=www.doczj/doc/2f54d8860708763231126edb6f1aff00bfd5707a.html o and cname=’数据库原理’and grade <60 a:select sname from student ,sc,course
where student.sno=sc.sno and www.doczj/doc/2f54d8860708763231126edb6f1aff00bfd5707a.html
o=www.doczj/doc/2f54d8860708763231126edb6f1aff00bfd5707a.html o and grade>80 and cname=’数据库原理’b:select sname from student where sno in (select sno from sc where grade>80 and cno in (select cno from course where cname=’数据库原理’))
select sno,sname,avg(grade) from sc,student
where student.sno=sc.sno
group by student.sno
having avg(grade)<60
a:select sname from student where ssex=’⼥’and sno in(select sno from sc group by sno having avg(grade)>75) b:select sname from sc,student where student.sno=sc.sno and ssex=’⼥’
group by student.sno having avg(grade)>75
select student.sno,sname,cno,grade from student left join sc on student.sno=sc.sno and ssex=’男’
select count(*) from student where sno in( select sno from sc group by sno having avg(grade)<60)
select sname from student where sno not in（select sno from sc where cno=’1002’）
student
0001 aa X
0002 bb
0003 cc X
Sc
0001 1001
0001 1002
0002 1001
0003 1002
Select sname from student where not exists(select* from sc where cno=’1002’and sc.sno=student.sno)
a:select top 1 sno,avg(grade) from sc group by sno order by avg(grade) desc
b:select sno, avg(grade) from sc group by sno
having avg(grade)=(select top 1 avg(grade) from sc
group by sno order by avg(grade) desc)
c:select sno, avg(grade) from sc group by sno
having avg(grade)>=all ( select avg(grade) from sc group by sno)
select sname from student where not exists(
select * from course where cno in(‘1001’,’1002’) and
not exists(select * from sc where sno =student.sno and
cno=www.doczj/doc/2f54d8860708763231126edb6f1aff00bfd5707a.html o) )
a:select top 1 sno from sc cno=’1002’order by grade desc
b:select sno from sc where cno=’1002’and grade >=all (
select grade from sc where cno=’1002’)
select top 3 sno from sc group by sno order by avg(grade)desc
a:select*from student where sdept=’JSJ’and sage>19
b:select*from student where sdept=’JSJ’except select* from student where sage<19 select student.sno,sname from student,sc where cno=’1001’and grade>90 union select sno,sname from student where sno in （
select sno from sc group by sno having avg(grade)>85)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。