SQL练习题，带答案

最近工作比较清闲，好久没有写SQL了，从网上搜了题目，自己写了答案，供大家参考。如有错误，欢迎指正，谢谢。

数据就不提供了，大家自己写就好了。为了不影响练习，在个别题目对表进行了复制，用复制的表进行的练习。

建表语句：

create table student(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(2),
ssex varchar2(5)
);
create table teacher(
tno varchar2(10) primary key,
tname varchar2(20)
);
create table course(
cno varchar2(10),
cname varchar2(20),
tno varchar2(20)
);
create table sc(
sno varchar2(10),
cno varchar2(10),
score number(4,2)
);

题目与答案：

--1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
select a.sno from sc a
where a.cno = 'c001'
and exists (select b.sno from sc b where b.cno = 'c002' and a.sno = b.sno and a.score > b.score);
--------------------------
select a.sno from
(select a.* from sc a where a.cno = 'c001') a,
(select b.* from sc b where b.cno = 'c002') b
where a.sno = b.sno
and a.score > b.score;

--2、查询平均成绩大于60 分的同学的学号和平均成绩；
select a.sno,round(avg(a.score),2) score from sc a group by a.sno having avg(a.score) > 60;

--3、查询所有同学的学号、姓名、选课数、总成绩；
select s.sno,s.sname,a.con,a.total from student s,
(select a.sno,count(*) con ,sum(a.score) total from sc a group by a.sno) a
where s.sno = a.sno;

--4、查询姓“卡”的老师的个数；
select count(*) from teacher t where t.tname like '卡%'

--5、查询没学过“迈特凯”老师课的同学的学号、姓名；
select s.sno,s.sname from student s
where s.sno not in (
select distinct a.sno from sc a,(
select c.* from course c,(
select t.tno from teacher t where t.tname = '迈特凯') t
where t.tno = c.tno) b 
where a.cno = b.cno);

--6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；
select s.sno,s.sname from student s
where s.sno in (
select a.sno from sc a,sc b where a.cno = 'c001' and b.cno = 'c002' and a.sno = b.sno
);
-------
select s.sno,s.sname from student s
join (select a.sno from sc a where a.cno = 'c001') a
on s.sno = a.sno
join (select b.sno from sc b where b.cno = 'c002') b
on a.sno = b.sno;
-------
select s.sno,s.sname from student s
join sc a on s.sno = a.sno
join sc b on a.sno = b.sno
where a.cno = 'c001' and b.cno = 'c002';

--7、查询学过“卡卡西”老师所教的所有课的同学的学号、姓名；
select s.sno,s.sname from student s
join sc a on a.sno = s.sno
join course c on c.cno = a.cno
join teacher t on t.tno = c.tno
where t.tname = '卡卡西';

--8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；
select s.sno,s.sname from student s
join sc a on a.sno = s.sno
join sc b on b.sno = a.sno
where a.cno='c002' and b.cno='c001' and a.score < b.score;
-------
select s.sno,s.sname from student s
join (
select a.sno from sc a
where a.cno = 'c001'
and exists (select b.sno from sc b where b.cno = 'c002' and a.score > b.score and a.sno = b.sno)
) b on b.sno = s.sno;

--9、查询所有课程成绩小于60 分的同学的学号、姓名；
select s.sno,s.sname from student s
where s.sno not in (
select a.sno from sc a where a.sno = s.sno and a.score > 60);

--10、查询没有学全所有课的同学的学号、姓名；
select s.sno,s.sname from  student s
join sc a on a.sno = s.sno
group by s.sno,s.sname
having count(a.cno) < (select count(distinct c.cno) from course c);

--11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名；
select s.sno,s.sname from student s
join (select * from sc a where a.sno <> 's001') a
on s.sno = a.sno
join (select from sc b from b.sno = 's001') b
on a.cno = b.cno;

--12、查询学过学号为“s001”同学所有课的其他同学学号和姓名；
select s.sno,s.sname from student s
join sc a on s.sno = a.sno
where a.cno in
(select b.cno from sc b where b.sno = 's001')
group by s.sno,s.sname
having count(a.cno) = (select count(*) from sc b where b.sno = 's001') and s.sno <> 's001';

--13、把“SC”表中“自来也”老师教的课的成绩都更改为此课程的平均成绩；
create table sc2 as select * from sc;
select * from sc2;
--1.查询'自来也'老师所教的课程号
select c.cno from teacher t, course c
where t.tname = '自来也' and t.tno = c.tno;
--2.查询'自来也'老师所教课程的平均成绩
select round(avg(a.score),2) scodre from sc2 a,
(select c.cno cno from teacher t, course c
where t.tname = '自来也' and t.tno = c.tno) b
where a.cno = b.cno;
--3.结果:
--方式一：
update sc2 a set a.score = (select round(avg(a.score),2) scodre from sc2 a,
(select c.cno cno from teacher t, course c
where t.tname = '自来也' and t.tno = c.tno) b
where a.cno = b.cno)
where a.cno in (select c.cno from teacher t, course c
where t.tname = '自来也' and t.tno = c.tno);
commit;
select * from sc2;
--方式二：
create table sc3 as select * from sc;
update sc3 a set a.score = case when a.cno in (select c.cno from teacher t, course c
where t.tname = '自来也' and t.tno = c.tno) then (select round(avg(a.score),2) scodre from sc2 a,
(select c.cno cno from teacher t, course c
where t.tname = '自来也' and t.tno = c.tno) b
where a.cno = b.cno) else a.score end;
commit;
select * from sc3;

--14、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名；
select s.sno,s.sname from student s where exists(
select a.sno from sc a where a.cno in(
select distinct b.cno from sc b where b.sno = 's001')
and s.sno <> 's001' and s.sno = a.sno
group by a.sno
having count(a.cno) = (select count(*) from sc where sc.sno = 's001')
) and exists(
select c.sno from sc c
where c.sno = s.sno
group by c.sno having count(c.cno) = (select count(*) from sc where sc.sno = 's001' group by sno)
);
--1.找到所有和s001任意课程相同的同学
select a.sno from sc a join sc b on a.cno = b.cno where a.sno <> 's001' and b.sno = 's001'; 
--2.将步骤1 得到结果进行分组，按sno
select a.sno ,count(*) from
(select a.sno from sc a join sc b on a.cno = b.cno where a.sno <> 's001' and b.sno = 's001') a
group by a.sno;
--3.对结果进行筛选，选出相同课程数和s002一样的同学
select a.sno,count(*) from
(select a.sno from sc a join sc b on a.cno = b.cno where a.sno <> 's001' and b.sno = 's001') a
group by a.sno
having count(*) = (select count(*) from sc a where a.sno = 's001');
--4.到这里是不是结束了呢，不是，有一种情况，A学了s002所有的课程，还学了其他的课程所以需要再次排除
select sno,count(*) from sc group by sno
intersect (select a.sno,count(*) from
(select a.sno from sc a join sc b on a.cno = b.cno where a.sno <> 's001' and b.sno = 's001') a
group by a.sno
having count(*) = (select count(*) from sc where sno = 's001'));
--最后结果
select s.sno,s.sname from student s
join (select sno,count(*) from sc group by sno
intersect (select a.sno,count(*) from
(select a.sno from sc a join sc b on a.cno = b.cno where a.sno <> 's001' and b.sno = 's001') a
group by a.sno
having count(*) = (select count(*) from sc where sno = 's001'))) b
on s.sno = b.sno;

--15、删除学习“自来也”老师课的SC表记录；
create table sc4 as select * from sc;
--1.'自来也'老师所有课的课程id
select c.cno from course c join teacher t
on c.tno = t.tno where t.tname = '自来也';
--结果
delete from sc4 a where a.cno in
(select distinct a.cno from sc4 a,course c,teacher t
where a.cno = c.cno and c.tno = t.tno and t.tname = '自来也');
select * from sc4;

--16、向SC 表中插入一些记录，这些记录要求符合以下条件：
      --没有上过编号“c004”课程的同学学号、“c004”号课的平均成绩；
create table sc5 as select * from sc;
insert into sc5(sno,cno,score)
select s.sno,'c004',
(select avg(sc.score) from sc where sc.cno = 'c004')
from student s where s.sno not in(
select a.sno from sc a where a.cno = 'c004'
);
commit;
select * from sc5;
--17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select c.cno,nvl(max(a.score),0),nvl(min(a.score),0) from sc a right join course c
on a.cno = c.cno
group by c.cno
order by c.cno;

--18、按各科平均成绩从低到高和及格率的百分数从高到低顺序
select a.cno,round(avg(a.score),2) score,
round(sum(case when score >= 60 then 1 else 0 end) / count(*),3) as 及格率
from sc a
group by a.cno
order by avg(a.score),及格率 desc;

--19、查询不同老师所教不同课程平均分从高到低显示
select t.tno,t.tname,c.cno,c.cname,a.score from teacher t,
(select avg(score) as score,cno as cno from sc group by cno) a,
course c
where a.cno = c.cno and t.tno = c.tno
order by a.score desc;
------------------
select max(t.tno) tno,
max(t.tname) tname,
max(c.cno) cno,
max(c.cname) cname,
avg(score) score from sc, course c,teacher t
where sc.cno=c.cno and c.tno=t.tno
group by c.cno
order by score desc

--20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
select max(c.cno) cno,
max(c.cname) cname,
sum(case when a.score between 85 and 100 then 1 else 0 end) as "[100-85]",
sum(case when a.score between 70 and 85 then 1 else 0 end) as "[85-70]",
sum(case when a.score between 60 and 70 then 1 else 0 end) as "[70-60]",
sum(case when a.score < 60 then 1 else 0 end) as "[ <60]"
from sc a,course c
where a.cno = c.cno
group by c.cno
order by cno;

--21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
select s.sno,s.sname,b.* from student s,
(select a.sno sno,a.cno cno,a.score score,
row_number()over(partition by a.cno order by a.score desc) rn from sc a) b
where s.sno = b.sno
and b.rn < 4
order by b.cno ,rn;

--22、查询每门课程被选修的学生数
select c.cno,max(c.cname) cname,nvl(count(a.cno),0) from sc a right join course c
on a.cno = c.cno
group by c.cno
order by c.cno;

--23、查询出只选修了一门课程的全部学生的学号和姓名
select s.sno,s.sname from student s,
(select a.sno sno,count(a.cno) cnt from sc a group by a.sno having count(a.cno) = 1) b
where s.sno = b.sno;

--24、查询男生、女生人数
select s.ssex,count(*) cnt from student s group by s.ssex;

--25、查询姓“张”的学生名单
select s.* from student s where s.sname like '张%';

--26、查询同名同性学生名单，并统计同名人数
select s.sname,s.ssex,count(*) from student s group by s.sname,s.ssex having count(*) > 1;

--27、2004 年出生的学生名单(注：Student 表中Sage 列的类型是number)
select s.* from student s where to_char(sysdate,'yyyy') - s.sage = '2004';

--28、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select a.cno,round(avg(a.score),2) score from sc a group by a.cno order by score,a.cno desc; 

--29、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩
select s.sno,s.sname,b.score from student s,
(select a.sno,round(avg(a.score),2) score from sc a 
group by a.sno having round(avg(a.score),2) >85) b
where s.sno = b.sno;

--30、查询课程名称为“Oracle”，且分数低于60 的学生姓名和分数
select s.sname,a.score from student s,
(select a.sno,a.score from sc a join
course c on a.cno = c.cno where c.cname = 'Oracle' and a.score < 60) a
where s.sno = a.sno;

--31、查询所有学生的选课情况；
select s.sno,s.sname,wm_concat(c.cname) from student s,sc a,course c
where s.sno = a.sno and a.cno = c.cno
group by s.sno,s.sname;

--32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；
select s.sname,wm_concat(c.cname || '(' || a.score || '分' || ')' ) as 结果
from student s,sc a,course c
where s.sno = a.sno and a.cno = c.cno
and a.score > 70
group by s.sname;

--33、查询不及格的课程，并按课程号从大到小排列
select c.cno,c.cname,a.score from sc a,course c
where a.cno = c.cno
and a.score < 60
order by c.cname desc;

--34、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名；
select s.sno,s.sname from student s,
(select a.sno from sc a where a.cno = 'c001' and a.score > 80) a
where s.sno = a.sno;

--35、求选了课程的学生人数
select count(distinct a.sno) from sc a;

--36、查询选修“卡卡西”老师所授课程的学生中，成绩最高的学生姓名及其成绩
select s.sname,a.score from student s,sc a,course c,teacher t
where t.tname = '卡卡西' and t.tno = c.tno and c.cno = a.cno and s.sno = a.sno
and a.score = (select max(a.score) from sc a where a.cno = c.cno);

--37、查询各个课程及相应的选修人数
select a.cno,max(c.cname),count(distinct a.sno) as 人数
from sc a,course c
where a.cno = c.cno
group by a.cno;

--38、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select s.sno,a.cno,a.score from student s,
(select a.sno,a.cno,a.score from sc a,sc b where a.score = b.score and a.cno <> b.cno) a
where a.sno = s.sno;

--39、查询每门功课成绩最好的前两名
select s.sno,s.sname,a.cno,a.score from student s,
(select a.sno,a.cno,a.score,
row_number()over(partition by a.cno order by a.score desc) rn from sc a) a
where s.sno = a.sno
and a.rn <=2;

--40、统计每门课程的学生选修人数（超过5 人的课程才统计）。
     --要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select a.cno,count(*) cnt from sc a
group by a.cno
having count(*) > 5
order by cnt desc,a.cno asc;

--41、检索至少选修两门课程的学生学号
select a.sno from sc a
group by a.sno
having count(a.cno) >=2;

--42、查询全部学生都选修的课程的课程号和课程名
select c.cno,c.cname from course c,
(select a.cno from sc a group by a.cno
having count(a.sno) = (select count(*) from student)) a
where a.cno = c.cno;

--43、查询没学过“迈特凯”老师讲授的任一门课程的学生姓名
select s.sno,max(s.sname) sname from student s
right join sc a on s.sno = a.sno
where a.cno not in
(select c.cno from course c join teacher t on t.tno = c.cno where t.tname = '迈特凯')
group by s.sno;

--44、查询两门及以上不及格课程的同学的学号及其平均成绩
select a.sno,avg(a.score) score from sc a
where a.score < 60
group by a.sno
having count(a.cno) >= 2;

--45、检索“c004”课程分数小于80，按分数降序排列的同学学号
select a.sno,a.score from sc a where a.cno = 'c004' and a.score < 80
order by a.score desc;
--46、删除“s002”同学的“c001”课程的成绩
create table sc1 as select * from sc;
delete from sc1 a where a.sno = 's002' and a.cno = 'c001';
commit;
select * from sc1 a where a.sno = 's002';

最后

以上就是可耐哈密瓜为你收集整理的SQL练习题，带答案的全部内容，希望文章能够帮你解决SQL练习题，带答案所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。