oracle题目练习

create table course(c_cid number(2),c_name char(6),t_id number(2));
create table score(s_id number(2),c_id number(2),s_t_score number(2));
create table student(s_id number(2),s_name char(6),s_birth date,s_sex char(4));
create table teacher(t_id number(2),t_name char(6));


drop  table course;
drop table score;
drop table student;
drop table teacher;


insert into course values('01', '语文', '02');
insert into course values('02', '数学', '01');
insert into course values('03', '英语', '03');
insert into score values('01', '01', 80);
insert into score values('01', '02', 90);
insert into score values('01', '03', 99);
insert into score values('02', '01', 70);
insert into score values('02', '02', 60);
insert into score values('02', '03', 80);
insert into score values('03', '01', 80);
insert into score values('03', '02', 80);
insert into score values('03', '03', 80);
insert into score values('04', '01', 50);
insert into score values('04', '03', 20);
insert into score values('05', '01', 76);
insert into score values('05', '02', 87);
insert into score values('06', '03', 34);
insert into score values('07', '02', 89);
insert into score values('07', '03', 98);
insert into score values('06', '01', 31);
insert into student values('01', '赵雷', '1-1月-1990', '男');
insert into student values('02', '钱电', '21-12月-1990', '男');
insert into student values('03', '孙风', '20-5月1990', '男');
insert into student values('04', '李云', '6-8月1990', '男');
insert into student values('05', '周梅', '1-12月1991', '女');
insert into student values('06', '吴兰', '1-3月-1992', '女');
insert into student values('07', '郑竹', '1-7月1989', '女');
insert into student values('08', '王菊', '20-1月1990', '女');
insert into teacher(t_id,t_name) values('01', '张三');
insert into teacher(t_id,t_name) values('02' ,'李四');
insert into teacher(t_id,t_name) values('03', '王五');


select * from student;
select * from teacher;
select * from score;
select * from course;


-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数  
select s.*,s1.s_t_score"01课程成绩" ,s2.s_t_score "02课程成绩" 
 from 
(select s_id,s_t_score from score where c_id=01 )s1,
(select s_id,s_t_score from score where c_id=02 )s2,
student s
where s1.s_id=s2.s_id and s1.s_t_score> s2.s_t_score and s1.s_id=s.s_id;
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数  
select s.*,s1.s_t_score"01课程成绩" ,s2.s_t_score "02课程成绩" 
 from 
(select s_id,s_t_score from score where c_id=01 )s1,
(select s_id,s_t_score from score where c_id=02 )s2,
student s
where s1.s_id=s2.s_id and s1.s_t_score< s2.s_t_score and s1.s_id=s.s_id;






-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩 
select stu.s_id,s_name,trunc("平均成绩",2)
from student stu,
(select s_id,avg(s_t_score)"平均成绩" from score group by s_id having avg(s_t_score)>=60) score
where stu.s_id=score.s_id;


-- 4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 
select stu.s_id,s_name,"选课总数","总成绩"
from student stu,(
select s_id,count(*) "选课总数",sum(s_t_score)"总成绩" from score group by s_id) s
where stu.s_id=s.s_id;
-- 5、查询"李"姓老师的数量  
select count(*)"李姓老师的数量" from teacher where t_name like '李%';


-- 6、查询学过"张三"老师授课的同学的信息 


怎么知道哪个学生学了什么课
select * from score
怎么知道这个课时哪个老师带的
select * from course;
怎么知道老师的名字
select * from teacher;


select *
from student
where s_id in (
select s_id from score where c_id =(
select c_cid from course where t_id =(
select t_id from teacher where t_name='张三')));




select stu.*
from student stu ,score sc , course c ,teacher t
where stu.s_id=sc.s_id and sc.c_id=c.c_cid and c.t_id=t.t_id  and t_name='张三';






-- 7、查询没学过"张三"老师授课的同学的信息 
select *
from student
where s_id not in(
select stu.s_id
from student stu ,score sc , course c ,teacher t
where stu.s_id=sc.s_id and sc.c_id=c.c_cid and c.t_id=t.t_id  and t_name ='张三');


-- 8、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select *
from student
where s_id in(
              select s1.s_id 
              from (
                     select * from score where c_id=01) s1,
                     (select * from score where c_id=02) s2
              where s1.s_id=s2.s_id);