有S,C,SC三张表，结构如下：

insert into s values('1','张三')insert into s values('2','李四')insert into s values('3','王五')insert into s values('4','赵六')insert into c values('1','语文','李明')insert into c values('2','数学','刘老师')insert into c values('3','英语','王老师')insert into sc values('1','1','78')insert into sc values('1','2','90')insert into sc values('1','3','86')---insert into sc values('2','1','55')insert into sc values('2','2','59')insert into sc values('2','3','75')---insert into sc values('3','1','59')insert into sc values('3','2','44')insert into sc values('3','3','55')---insert into sc values('4','2','88')insert into sc values('4','3','56')请列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

create table S(s_no varchar,s_name varchar(10))
create table C(c_no varchar,c_name varchar(10),t varchar(10))
create table SC(s_no varchar,c_no varchar,score varchar(3))insert into s values('1','张三')
insert into s values('2','李四')
insert into s values('3','王五')
insert into s values('4','赵六')
insert into c values('1','语文','李明')
insert into c values('2','数学','刘老师')
insert into c values('3','英语','王老师')
insert into sc values('1','1','78')
insert into sc values('1','2','90')
insert into sc values('1','3','86')---
insert into sc values('2','1','55')
insert into sc values('2','2','59')
insert into sc values('2','3','75')---
insert into sc values('3','1','59')
insert into sc values('3','2','44')
insert into sc values('3','3','55')---
insert into sc values('4','2','88')
insert into sc values('4','3','56')select s.s_name,b.avg_score from s,
(select s_no,avg(cast(score as integer)) as avg_score from sc where s_no IN
(select s_no from
(select s_no,sum(c) as cc from
(select s_no,score,1 as c from sc where score <'60' ) as a
group by s_no) as a
where cc >= 2)
group by s_no) as b
where s.s_no=b.s_no

1.insert into student(sno,sname,sex) select s.sno,s.sname,s.sex from sc sc left join s s on s.sno=sc.sno

left join c c on sc.cno=c.cno where sc.grade>=80 and sc.cno='第一门课程'

2.delete from sc where grade=0 or grade is null

3.delete from sc where sno='0001'

4.update sc set grade=0 where sno

in (select sc.sno from sc sc left join c c on sc.cno=c.cno

where c.cname='数据库应用' and sc.grade<60)

5.update sc set grade=grade*1.05
where sno in(
select sc.sno
from sc sc
left join s s
on sc.sno=s.sno
where  grade<(select avg(grade) from sc)
and s.ssex='f'
)
6.update sc set grade=grade*1.04
where grade>75 and cno='c001'
update sc set grade=grade*1.05
where grade<=75 and cno='c001'

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息