Oracle sql语句练习
在网上找的
Oracle sql语句练习
终于磕磕绊
绊的做完了！！！！
--1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
select x.sno
from
(select * from sc where cno=’c001’
)x,
(select * from sc where cno=’c002’
)y
where x.score>y.score
and x.sno =y.sno;
--2、查询平均成绩大于60 分的同学的学号和平均成绩；
select sno,
avg(score)
from sc
group by sno
having avg(score)>60
order by avg(score);
--3、查询所有同学的学号、姓名、选课数、总成绩；
select s1.sno,
s1.sname,
s2.c,
s2.s
from student s1,
(select sno, count(cno) c,sum(score) s from sc group by sno
)s2
where s1.sno=s2.sno;
--4、查询姓“刘”的老师的个数；
select tname,
count(*)
from teacher
where tname like(‘刘%’)
group by tname;
--5、查询没学过“谌燕”老师课的同学的学号、姓名；
select s1.sno
from sc s1,
(select cno
from course t1,
(select distinct tno from teacher where tname=’谌燕’
)t2
where t1.tno!=t2.tno
)s2
where s1.cno=s2.cno;
select a.sno,
a.sname
from student a
where a.sno not in
(select distinct s.sno
from sc s,
(select c.*
from course c ,
(select tno from teacher t where tname=’谌燕’
)t
where c.tno=t.tno
) b
where s.cno = b.cno
)
--6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；
select *
from student
where sno in
(select DISTINCT sno from sc where cno in (‘c001’,’c002’)
);
--7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；
select *
from student
where sno in
(select DISTINCT s3.sno
from sc s3,
(select distinct s1.cno
from course s1,
(select tno from teacher where tname=’谌燕’
)s2
where s1.tno=s2.tno
)s4
where s4.cno=s3.cno
);
select distinct st.*
from student st
join sc s
on st.sno=s.sno
join course c
on s.cno=c.cno
join teacher t
on c.tno =t.tno
where t.tname=’谌燕’
--8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；
select *
from student
where sno in
( select distinct a.sno
from
(select * from sc where cno=’c002’
)a,
(select * from sc where cno=’c001’
)b
where a.score
THEN 1
else 0
end)/count(*)
from sc
group by cno
--19、查询不同老师所教不同课程平均分从高到低显示
select t.tno,
t.tname,
c.cno,
avg(s.score)
from sc s,
teacher t,
course c
where c.tno= t.tno
and c.cno = s.cno
group by c.cno;
--20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60\]
select sc.cno,
c.cname,
sum(
case
when score between 85 and 100
THEN 1
else 0
end ) "\[100-85\]",
sum(
case
when score between 70 and 85
THEN 1
else 0
end )"\[85-70\]",
sum(
case
when score between 70 and 60
THEN 1
else 0
end ) "\[70-60\]",
sum(
case
when score<60
THEN 1
else 0
end )"\[<60\]"
FROM sc,
course c
where sc.cno = c.cno
group by sc.cno,
c.cname;
\--21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
select \*
from
(select sno,
cno,
score,
row\_number()over (partition BY cno order by score DESC)rn
from sc
)
where rn<4;
\--22、查询每门课程被选修的学生数
select cno, count(sno)from sc group by cno;
\--23、查询出只选修了一门课程的全部学生的学号和姓名
select s1.sno,
s1.sname
from student s1
join
(select sno,count(cno) from sc group by sno HAVING count(cno)=1
)s2
on s1.sno= s2.sno;
\--24、查询男生、女生人数
select ssex, count(ssex) from student group by ssex;
\--25、查询姓“张”的学生名单
select \* from student where sname like '张%';
\--26、查询同名同姓学生名单，并统计同名人数
select sname,
count(\*)
from student
group by sname
having count(\*)>1;
--27、1990 年出生的学生名单(注：Student 表中Sage 列的类型是number)
select *
from student
where to_char(sysdate,’yyyy’)- sage=1990;
--28、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select cno,
avg(score)
from sc
group by cno
order BY avg(score),
cno desc;
--29、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩
select st.sno,
st.sname,
ss.*
from student st,
(select sno,avg(score)ags from sc group by sno having avg(score)>70
)ss
where st.sno=ss.sno
--30、查询课程名称为“SSH”，且分数低于60 的学生姓名和分数
select distinct st.sname,
sc.score
from student st
join course c
on c.cname=’SSH’
join sc
ON sc.score<80
and sc.sno = st.sno
and c.cno = sc.cno;
\--31、查询所有学生的选课情况；
select st.sno,
st.sname,
c.cname
from student st,
sc,
course c
where sc.sno=st.sno
and sc.cno =c.cno;
\--32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；
select st.sname,
c.cname,
sc.score
from student st,
course c,
sc
where st.sno=sc.sno
and sc.cno =c.cno
and sc.score>70;
--33、查询不及格的课程，并按课程号从大到小排列
select c.cno,
c.cname,
sc.score
from course c,
sc
where sc.score<60
and sc.cno =c.cno;
\--34、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名；
select st.sno,
st.sname
from student st,
sc
where sc.score>80
and st.sno =sc.sno
and sc.cno = ‘c001’;
--35、求选了课程的学生人数
select count(DISTINCT sno)from sc;
--36、查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩
select st.sname,
sc.score
from course c,
sc,
student st,
teacher t
where t.tname=’谌燕’
and t.tno = c.tno
and c.cno = sc.cno
and st.sno = sc.sno
and sc.score =
( select max(score) from sc where sc.cno= c.cno
)
--37、查询各个课程及相应的选修人数
select cno ,count(sno) from sc group by cno;
--38、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select s1.sno,
s1.cno,
s1.score
from sc s1,
sc s2
where s1.score=s2.score
and s1.cno! = s2.cno;
--39、查询每门功课成绩最好的前两名
--40、统计每门课程的学生选修人数（超过10 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select cno,
count(sno)
from sc
group by cno
having count(sno)>10
order by count(sno)desc,
cno ASC;
--41、检索至少选修两门课程的学生学号
select sno ,
count(cno)
from sc
group by sno
having count(cno)>=2;
--42、查询全部学生都选修的课程的课程号和课程名
select DISTINCT c.cname,
c.cno
from course c,
sc
where c.cno=sc.cno;
--43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名
select st.sname
from student st
where sno not in
( select distinct sno
from sc,
course c,
teacher t
where t.tname= ‘谌燕’
and c.tno =t.tno
and sc.cno = c.cno
)
--44、查询两门以上不及格课程的同学的学号及其平均成绩
select sno,
avg(score)
FROM sc s
where sno in
( select sno from sc where sc.score<60 group by sno having count(sno)>1
)
group by sno;
--45、检索“c004”课程分数小于60，按分数降序排列的同学学号
select sno,
score
from sc
where score>60
and cno =’c002’
order by score;
--46、删除“s002”同学的“c001”课程的成绩
delete sc where cno=’c001’ and sno=’s002’;
select * from sc order by sno;
亦凉
叁歲伎倆
红太狼
忘是亡心i
小灰灰
╰+哭是因爲堅強的太久メ
还没有评论，来说两句吧...