oracle创建表，插入，删除

--1.学生表
create table student
(
            s_id  number(4),
            s_name  varchar(30),
            s_age number(4),
            s_sex   varchar(10)


);
--2课程表
create table course
(
            c_id  number(4),
            c_name  varchar(30),
            t_id    number(4)


);
--3.教师表
create table teacher
(
            t_id  number(4),
            t_name  varchar(30)


);
--4.成绩表
create table sc
(
            s_id  number(4),
            c_id  number(4),
            score  number(4)


);
--插入学生表数据
insert into student
values (1,'aaa',22,'女');
insert into student
values (2,'bbb',23,'男');
insert into student
values (3,'ccc',24,'女');
insert into student
values (4,'ddd',25,'男');
insert into student
values (5,'eee',26,'男');
--插入课程表数据
insert into course
values (01,'java',1);
insert into course
values (02,'web',2);
insert into course
values (03,'oracle',3);
--插入教师表数据


insert into teacher
values (01,'张三');
insert into teacher
values (02,'李四');
insert into teacher
values (03,'李五');
insert into teacher
values (04,'李六');
insert into teacher
values (05,'王五');
--插入成绩表数据


insert into sc
values (01,01，89);
insert into sc
values (01,02，59);


insert into sc
values (02,02，89);
insert into sc
values (02,01，59);
insert into sc
values (03,03，99);
insert into sc
values (04,01，79);
insert into sc
values (04,03，89);
insert into sc
values (05,02，100);




--练习题 
--1查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select a.*,b.score as "01课程分数",c.score as "02课程分数"
from student a
right join sc b on a.s_id=b.s_id and b.c_id='01'
right join sc c on c.s_id=a.s_id and c.c_id='02' 
where b.score>c.score;
--2查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.*,b.score as "01课程分数",c.score as "02课程分数"
from student a
left join sc b on a.s_id=b.s_id and b.c_id='01'
left join sc c on c.s_id=b.s_id and c.c_id='02' 
where b.score<c.score;
--3.查询平均成绩大于等于60分的同学的学生编号、学生姓名和平均成绩
select s.s_id 学生编号,s.s_name 学生姓名,avg(sco.score) 平均成绩
from student s,sc sco
where s.s_id=sco.s_id 
group by s.s_id,s.s_name having avg(sco.score)>60
order by s.s_id ;


--4查询平均成绩小于60分的同学的学生编号、学生姓名和平均成绩
select s.s_id 学生编号,s.s_name 学生姓名,avg(sco.score) 平均成绩
from student s,sc sco
where s.s_id=sco.s_id 
group by s.s_id,s.s_name having avg(sco.score)<60
order by s.s_id ;




--60 没有
--5查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s.s_id 学生编号,s.s_name 学生姓名, count(sco.score) 选课总数,sum(sco.score)总成绩
from student s,sc sco
where s.s_id=sco.s_id
group by s.s_id,s.s_name
order by s.s_id ;
--6查询"李"姓老师的数量
select count(t.t_id) 姓李老师数量
from teacher t
where t.t_name like '李%'
--7查询学过"张三"老师所授课程的同学的信息
--查询张三老师授课的课程号
select t.t_id
from teacher t
where t.t_name='张三';
--查询学过"张三"老师所授课程的同学的信息
select s.*
from student s
left join sc sco on s.s_id=sco.s_id
left join course c on sco.c_id=c.c_id
where c.t_id=(select t.t_id
from teacher t
where t.t_name='张三')
order by s.s_id;
--8.查询没学过"张三"老师所授课程的同学的信息


select *
from student s
where s.s_id not in(select s.s_id
from student s
left join sc sco on s.s_id=sco.s_id
left join course c on sco.c_id=c.c_id
where c.t_id=(select t.t_id
from teacher t
where t.t_name='张三')
)order by s.s_id;


--9查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select s.*
from student s
left join sc sco1 on s.s_id=sco1.s_id
left join sc sco2 on sco1.s_id=sco2.s_id
where sco1.c_id=1 and sco2.c_id=2;
--10查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select student.* 
from student, sc 
where student.s_id = sc.s_id and sc.c_id = '01' 
and student.s_id  
not in (
select sc.s_id 
from sc 
where sc.s_id = sc.s_id and sc.c_id = '02');