select sid、name、cname,grade from Studen s,Course c ,Sc t where s.sid = t.sid and t.cid = c.cid and t.grade = (select max(grade) from sc where cid = t.cid)select sid、name、cname,grade from Studen s,Course c ,Sc t where s.sid = t.sid and t.cid = c.cid and not exists(select 1 from sc where cid = t.cid and grade > t.grade)
create proc exercise as begin select a.studentname,b.coursename,c.score from student a inner join sc c on a.id=c.studentid inner join course b on c.courseid=b.id where not exists(select 1 from sc where courseid=c.courseid and score>c.score) end
create table student( sno char(7) primary key, sname char(10) not null, ssex char(2), sage tinyint, sdept char(20) )create table course( cno char(10) not null, cname char(20) not null, ccredit tinyint, semester tinyint, primary key(cno) ) create table sc( sno char(7) not null, cno char(10) not null, grade smallint, xklb char(4), primary key(sno,cno), foreign key(sno) references student(sno), foreign key(cno) references course(cno) ) create table employe( eno char(7) not null, ename char(8), ewid char(8), esalary int, ecode char(8) not null )create table worktable( wid char(8) not null, wminsal int, wmaxsal int ) 请回复问题答案的楼主注意下。。给个正确的回复 谢谢!!!!
create proc exercise as begin select a.sname,b.cname,c.grade from student a inner join sc c on a.sno=c.sno inner join course b on c.cno=b.cno where not exists(select 1 from sc where cno=c.cno and grade>c.grade) end go exec exercise
select a.sname,b.cname,c.grade from student a ,course b,sc c where a.sno=c.sno and c.cno=b.cno and grade=(select max(grade) from sc where cno=c.cno)
Studen s,Course c ,Sc t
where s.sid = t.sid and t.cid = c.cid
and t.grade = (select max(grade) from sc where cid = t.cid)select sid、name、cname,grade from
Studen s,Course c ,Sc t
where s.sid = t.sid and t.cid = c.cid
and not exists(select 1 from sc where cid = t.cid and grade > t.grade)
as
begin
select a.studentname,b.coursename,c.score
from student a inner join sc c on a.id=c.studentid
inner join course b on c.courseid=b.id
where not exists(select 1 from sc where courseid=c.courseid and score>c.score)
end
sno char(7) primary key,
sname char(10) not null,
ssex char(2),
sage tinyint,
sdept char(20)
)create table course(
cno char(10) not null,
cname char(20) not null,
ccredit tinyint,
semester tinyint,
primary key(cno)
)
create table sc(
sno char(7) not null,
cno char(10) not null,
grade smallint,
xklb char(4),
primary key(sno,cno),
foreign key(sno) references student(sno),
foreign key(cno) references course(cno)
)
create table employe(
eno char(7) not null,
ename char(8),
ewid char(8),
esalary int,
ecode char(8) not null
)create table worktable(
wid char(8) not null,
wminsal int,
wmaxsal int
)
请回复问题答案的楼主注意下。。给个正确的回复 谢谢!!!!
create proc exercise
as
begin
select a.sname,b.cname,c.grade
from student a inner join sc c on a.sno=c.sno
inner join course b on c.cno=b.cno
where not exists(select 1 from sc where cno=c.cno and grade>c.grade)
end
go
exec exercise
a.sname,b.cname,c.grade
from
student a ,course b,sc c
where
a.sno=c.sno
and
c.cno=b.cno
and
grade=(select max(grade) from sc where cno=c.cno)