create table s
(
s# int primary key,
sname varchar(10),
sex char(2),
age int
)create table c
(
c# char(4) primary key,
cname varchar(10),
teacher varchar(10)
)create table sc
(
id int primary key,
s# int foreign key (s#) references s(s#),
c# char(4) foreign key (c#) references c(c#),
grade int check (grade is null or grade between 0 and 100)
)s 学生表,c课程表,sc成绩表下面2个怎么用SQL语句实现啊?1、把课程名为MATHS的成绩提高10%2、C4的成绩低于该课程的平均成绩的,提高5%
(
s# int primary key,
sname varchar(10),
sex char(2),
age int
)create table c
(
c# char(4) primary key,
cname varchar(10),
teacher varchar(10)
)create table sc
(
id int primary key,
s# int foreign key (s#) references s(s#),
c# char(4) foreign key (c#) references c(c#),
grade int check (grade is null or grade between 0 and 100)
)s 学生表,c课程表,sc成绩表下面2个怎么用SQL语句实现啊?1、把课程名为MATHS的成绩提高10%2、C4的成绩低于该课程的平均成绩的,提高5%
(
s# int primary key,
sname varchar(10),
sex char(2),
age int
)
go
create table c
(
c# char(4) primary key,
cname varchar(10),
teacher varchar(10)
)
go
create table sc
(
id int primary key,
s# int foreign key (s#) references s(s#),
c# char(4) foreign key (c#) references c(c#),
grade int check (grade is null or grade between 0 and 100
))
go
insert into s select 1,'С􏻃􏻷','􏻖􏻐',22
go
insert into s select 2,'Сs','􏻖􏻐',22
go
insert into c select '1111','maths','􏺰􏺢􏺸􏻊'
go
insert into sc select 1,1,'1111',50
go
insert into sc select 2,2,'1111',80
go
select (case when sc.grade*1.1>100 then 100 else sc.grade*1.1 end )
from sc join c on sc.c#=c.c#
where c.cname='maths'
go
update sc
set sc.grade=(case when sc.grade*1.1>100 then 100 else sc.grade*1.1 end )
from sc,c
where sc.c#=c.c# and c.cname='maths'
go
select * from sc
update sc
set sc.grade=(case when sc.grade*1.05>100 then 100 else sc.grade*1.05 end )
where sc.c#='1111' and sc.grade <(select avg(grade) from sc where sc.c#='1111')