表名
student(S#,Sname,Sage,Ssex)学生表 S#:学号;Sname:学生姓名;Sage;学生年龄;Ssex:学生性别
Course(C#,Cname,T#) 课程表 C#:课程编号;Cname:课程名字;T#:教师编号
SC(S#,C#,score)成绩表 S#:学号;C#:课程编号;score:成绩
Teacher(T#,Tname)教师表 T#:教师编号;Tname:教师名字
问题1:查询“001”课程比“002”课程成绩高的所有学生的学号;问题2:查询没学过“叶平”老师课的同学的学号,姓名问题3:删除学习“叶平”老师课的SC表记录问题4:把“sc”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩
student(S#,Sname,Sage,Ssex)学生表 S#:学号;Sname:学生姓名;Sage;学生年龄;Ssex:学生性别
Course(C#,Cname,T#) 课程表 C#:课程编号;Cname:课程名字;T#:教师编号
SC(S#,C#,score)成绩表 S#:学号;C#:课程编号;score:成绩
Teacher(T#,Tname)教师表 T#:教师编号;Tname:教师名字
问题1:查询“001”课程比“002”课程成绩高的所有学生的学号;问题2:查询没学过“叶平”老师课的同学的学号,姓名问题3:删除学习“叶平”老师课的SC表记录问题4:把“sc”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩
from sc a,
sc b,
student s
where a.s# = b.s#
and a.s# = s.s#
and a.c# = '001'
and b.c# = '002'
and a.score > b.score;
select distinct s.s#, s.sname
from sc,
student s
where sc.s# = s.S#
and not exists ( select 1
from course c,
teacher t
where c.t# = t.t#
and c.c# = sc.c#
and t.tname = '叶平') ;
delete from sc
where exists ( select 1
from course c,
teacher t
where c.t# = t.t#
and c.c# = sc.c# );
update sc
set sc.score = ( select avg(score)
from sc c
where sc.c# = c.c# )
where exists ( select 1
from course c,
teacher t
where c.t# = t.t#
and c.c# = sc.c# );
delete from sc
where exists ( select 1
from course c,
teacher t
where c.t# = t.t#
and c.c# = sc.c#
and t.tname = '叶平');
update sc
set sc.score = ( select avg(score)
from sc c
where sc.c# = c.c# )
where exists ( select 1
from course c,
teacher t
where c.t# = t.t#
and c.c# = sc.c#
and t.tname = '叶平' );
用上面的变量赋值表示上月第一天,上月今天,今月第一天和今天(写出具体sql语句)
Oracle数据库表存在伪列rowid,请利用rowid编写一个sql语句,从以下记录中找到唯一记录,并将其flag列更新为Y
No name sex flag
1 张三 1 N
1 张三 1 N
1 张三 1 N