oracle——SQL复习01

--学生表
create table t_student(
sid int,
sname varchar2(20),
sage int,
ssex varchar2(20),
sdept int
); 

insert into t_student values(2005001, 'zs', 22, 'M', 1001); 
insert into t_student values(2005002, 'ls', 24, 'M', 1001); 
insert into t_student values(2005003, 'ww', 21, 'f', 1001); 
insert into t_student values(2005004, 'xgz', 26, 'M', 1002); 
insert into t_student values(2005005, 'xpz', 20, 'f', 1002);
insert into t_student values(2005006, 'kdj', 22, 'M', 1002); 
insert into t_student values(2005007, 'bsk', 24, 'M', 1003); 
insert into t_student values(2005008, 'hbl', 21, 'f', 1003); 
insert into t_student values(2005009, 'hqb', 26, 'M', 1004); 
insert into t_student values(2005010, 'dm', 20, 'f', 1004);
commit;

--
select  * from t_student;



--课程表
create table t_course(
cid int,
cname varchar2(20),
tid int
);


insert into t_course values(001, 'yuwen', 2005001);
insert into t_course values(001, 'yuwen', 2005002);
insert into t_course values(002, 'shuxue', 2005001);
insert into t_course values(002, 'shuxue', 2005003);
insert into t_course values(003, 'yingyu', 2005004);
insert into t_course values(004, 'huaxue', 2005005);
insert into t_course values(004, 'huaxue', 2005001);
commit;
--
select * from t_course;


--成绩表
drop table t_score;
create table t_score(
scid int,
sid int,
cid varchar2(20),
grade number
);

insert into t_score values(1001001, 2005001, '001',100);
insert into t_score values(1001001, 2005001,  '002',90);
insert into t_score values(1001002, 2005002,  '001',82);
insert into t_score values(1001002, 2005002,  '003',72);
insert into t_score values(1001003, 2005004,  '001',90);
insert into t_score values(1001003, 2005004,  '002',77);
insert into t_score values(1001003, 2005005,  '002',67);
insert into t_score values(1001004, 2005005,  '003',83);  
insert into t_score values(1001003, 2005002,  '004',72);
insert into t_score values(1001004, 2005001,  '004',31);
insert into t_score values(1001004, 2005003,  '004',66);
commit;

select * from t_score;
--t1表
select * from t_score  where cid = '001';
--t2表
select * from t_score  where cid = '002';
/** 查询“001”课程比“002”课程成绩高的所有学生的学号 */
--正确的思路

select * from t_score;

select t1.sid,t1.grade
  from ( select * from t_score where cid = '001'  ) t1
 inner join (select * from t_score where cid = '002') t2
    on t1.sid = t2.sid
   and t1.grade > t2.grade;
--错误的思路   ,对我来说，在工作中有现成的数据比较容易写出SQL
select t1.sid,t1.grade from t_score t1 inner join t_score t2 on 
t1.sid = t2.sid and t1.grade > t2.grade ;


 
/**查询所有同学的学号、姓名、选课数、总成绩*/
select t1.sid ,t1.sname, nvl(t2.NUM,0),nvl(t2.score,0) from t_student t1 
left join (
     select sid, count(1) NUM ,sum(grade) score from t_score t
     group by sid) t2
on t1.sid = t2.sid; 
-- 我的不靠谱,不完整思路,没有数据,没有考虑到score是否都有成绩,所以选择了inner
-- 也没有将选课数和总成绩字段查出来
select st.sid,st.sname from t_student st inner join 
(
select sid, count(1), sum(grade) from t_score group by sid
) t1
on st.sid = t1.sid 


/** 查询每门功成绩最好的前两名 */
select T1.cid, rownum RN from(
select t.cid  from t_score t  group by t.cid) T1
where ROWNUM <=2;
select * from t_score t; 
--直接用ROW_ NUMBER() 函数
select t.* from (
select t.*, row_number() over(partition by cid order by t.grade desc ) rn from t_score  t 
) t
where  rn < = 2 ;

select * from t_score;


-- 面试时的错误思路,获得的是最高的前两名,
-- 而不是每门功课的前两名,个人觉得思路的堵塞没有具体的数字造成的
--搜索答案是在百度后看到row_number()才记得来
select t.sid,t.cid,t.grade,rownum num from  
(
    select t.sid,t.cid,t.grade from t_score t order by t.cid
) t 
where rownum <=2 ;



 

/** 查询两门以上不及格课程的同学的学号及其平均成绩 */
select * from t_score order by sid ;

--  自己反复测试数据出来的结果
select sid, round(avg(t.grade),2) avg_score
  from t_score t
 where t.sid in (select t.sid
                   from (select t.sid, count(1) rn
                           from t_score t
                          where t.grade <= 59
                          group by t.sid) t
                  where rn >= 2)
 group by sid;
-- 面试时候写的结果 
-- 第一步
select t.sid, count(1) rn  from t_score t
  group by t.sid where t.grade <= 59 --这里出错了,group by 应该在where语句后面
-- 第二步 
select sid,  avg(t.grade)  
  from t_score t
 where t.sid = ----这里应该使用in ,报错了,返回多行结果
 (select t.sid from (select t.sid, count(1) rn from t_score t
                          where t.grade <= 59 group by t.sid) t
               where rn >= 2) --结尾也没写group by 
 
--百度  答案
select sid, avg(nvl(grade,0))
  from t_score
 where sid in (select sid
                 from t_score
                where grade < 60
                group by sid
               having count(*) > = 2)
 group by sid;
 



--教师表
create table t_teacher(
tid int,
tname varchar2(20)
);