--学生表
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)
);