oracle查询练习

--1. 查询每个学生，各考了几门课
select stu.sid ,count(*)
from stu,sc
where stu.sid=sc.sid
group by stu.sid;

select sid,count(*)
from sc
group by sid;

--2. 查询每门课，各被几个学生考了。
select course.cid,count(*)
from course,sc
where course.cid=sc.cid
group by course.cid;

select cid,count(*)
from sc
group by cid;

--3. 查询学生，男女各多少人.
select gender,count(1) from stu
group by gender;

--4. 查询在考试及格的成绩中，每个学生各考多少门课.
select sc.sid,count(*)
from sc
where sc.score>=60
group by sid;

--5. 查询黄药师带什么课
select *
from course
where tid = (select tid from teacher where teacher.tname = '黄药师');

--6. 查询哪些学生考过洪七公带的课
select * from stu
where sid in
(select sid from sc
where cid =
(select cid from course
where tid =
(select tid from teacher where teacher.tname = '洪七公')));

select * from sc,course c
where c.cid=(select tid from teacher where teacher.tname = '洪七公')
and c.cid=sc.cid;

-- 7. 查询，没有考过洪七公课的学生.
select * from sc
where cid not in(select cid from course
where tid=( select tid from teacher where teacher.tname = '洪七公'));

 

-- 8. 查询考过的课，与2号学生考过的课任意一门一样的学生。
select * from stu
where sid in (
select sid from sc
where cid in(
select cid from sc
where sid=2));

--9. 查询考过的课，完全与2号学生一样的学生.
-- 比如 2号学生考过1,2 那么其它学生必须也仅仅只是考过1,2！！ 不能是 123 13 23
--查询出，考的课程的数量与2号学生一致的
--再查询2没考过的课
-- 再在考试数量与2一致的学生中，查询谁考过2没考过的课。
select sid from sc
group by sid
having count(*) = (
select count(*) from sc
where sid = 2
)
minus
select sid from sc
where cid in (
select cid from course
where cid not in (
select cid from sc
where sid = 2
)
)

-- 10. 查询学生的名字，和学生考的课的名字，以及分数。
select s.sname,c.cname,sc.score
from stu s, course c,sc
where s.sid=sc.sid
and c.cid=sc.cid;

-- 11. 查询所有课程都及格的学生
-- 方法1：
select * from stu
where sid not in(
select sid from sc
where score<60);
-- 方法2：
select sid,min(score) from sc
group by sid
having min(score) >= 60;

-- 12. 查询1号课程比2号课程成绩高的学生 (难题)
--方法1
select sid
from sc o
where cid=1
and score>(select score from sc where cid=2 and o.sid=sid);

--方法2
select * from sc a join sc b
on a.sid = b.sid
and a.cid = 1
and b.cid = 2
and a.score > b.score;

--13. 查询，所有学生的名字、考的课程名、成绩、级别 (用case)
/*
成绩在85-100 就是A
成绩在70-84 就是B
成绩在60-69 就是C
其余就是D
*/
select s.sname,c.cname,sc.score,
case
when sc.score>=85 then 'A'
when sc.score>=70 then 'B'
when sc.score>=60 then 'C'
else 'D'
end "级别"
from stu s,course c,sc
where s.sid=sc.sid
and c.cid=sc.cid;

-- 对比单独使用的case
begin
case 4
when 1 then dbms_output.put_line('aa');
when 2 then dbms_output.put_line('bb');
when 3 then dbms_output.put_line('cc');
else dbms_output.put_line('qita');
end case;
end;

--14. 有如下规则：
/*成绩在85-100 就是A
成绩在70-84 就是B
成绩在60-69 就是C
其余就是D
查询出每个级别个多少学生？
*/
select lev,count(*) from (
select
sname,cname,score,
case
when score between 85 and 100 then 'A'
when score between 70 and 84 then 'B'
when score between 60 and 69 then 'C'
else 'D'
end lev
from sc join stu
on sc.sid = stu.sid
join course
on sc.cid = course.cid
)
group by lev

--15. 查询每个课程，各被多少学生考
--要求，显示出课程的名字，和参考人数。
select c.cname,count(*)
from course c,sc
where c.cid=sc.cid
group by c.cname;

-- 16. 查询年龄比张辽小的学生中，每个学生各考多少们课程。
-- 要求显式：学生名 参考课程数
select s.sname,count(1)
from sc,stu s,course c
where sc.sid=s.sid
and sc.cid=c.cid
and sc.sid in(select sid from stu
where birthday>(
select birthday from stu
where sname='张辽'))
group by s.sname;

 

-- 17.查询1号课程比2号课程成绩高的学生 (难题)
-- 使用相关子查询来做.

-- 这是可以的
select * from stu
-- 这是可以的
select stu.*,5 from stu
-- 这是可以的
select stu.*,2+3 from stu
-- 这是可以的
select stu.*,(select 5 from dual) from stu
-- 这是可以的
select stu.*,(select score from sc where sid = 1 and cid = 1) from stu
-- 这是可以的
-- 相关子查询，就是在子查询中，使用到了外表的值!
-- 随着外表行的变化，使用的外表的值也变化!
select stu.*,
(select score from sc where sid = stu.sid and cid = 1),
(select score from sc where sid = stu.sid and cid = 2),
sid
from stu
-- 这是可以的
-- 相关子查询，就是在子查询中，使用到了外表的值!
-- 随着外表行的变化，使用的外表的值也变化!
select stu.*,
nvl((select score from sc where sid = stu.sid and cid = 1),0),
nvl((select score from sc where sid = stu.sid and cid = 2),0),
sid
from stu

-- 最终解决问题：
select stu.*, (select score from sc where sid = stu.sid and cid = 1), (select score from sc where sid = stu.sid and cid = 2)from stu
where
(select score from sc where sid = stu.sid and cid = 1)
>
(select score from sc where sid = stu.sid and cid = 2)

-- 举一反三：
-- a
select sid,cid,score, (select 5 from dual) from sc
-- b
select sid,cid,score, (select sname from stu where sid = sc.sid), sid from sc
-- c
select sid,cid,score,
(select sname from stu where sid = sc.sid),
(select cname from course where cid = sc.cid)
from sc
-- d
select
(select sname from stu where sid = sc.sid) as sname,
(select cname from course where cid = sc.cid) as cname,
score
from sc;

 

 

 

select * from stu;
select * from sc;
select * from course;
select * from teacher;

alter session set nls_date_format = 'yyyy-mm-dd';
insert into stu values(null,'张无忌', '2011-11-11',1);
insert into sc values(6,1,100);
insert into sc values(6,2,95);

update stu set sid=8 where sname='周芷若'
alter session set nls_date_format = 'yyyy-mm-dd';
insert into stu values(null, '张无忌', '2011-11-11', 1);
insert into sc values(6,1,100);
insert into sc values(6,2,95);

insert into stu values(null, '赵敏','2009-12-12',0);
insert into sc values(7,1,50);
insert into sc values(7,3,35);

insert into stu values(8, '周芷若','2008-8-8',0);
insert into sc values(8,2,65);
insert into sc values(8,3,85);