有3张表
student(sno,sname,sage,ssex)
course(cno,cname,teacher)
sc(sno,cno,grade)
现找出"程序设计"课成绩在90分以上的学生姓名
我设计了两种查询语句
<1
select sname,grade from student,course,sc where grade>90 and student.sno=sc.sno and course.cno=sc.cno and course.cname='程序设计'
<2
select sname from student where sno in (select sno from sc where grade>90 and cno in(select cno from course where cname='程序设计'))
请高手帮忙分析一下哪一种好啊~并说明一下为什么!谢谢~
student(sno,sname,sage,ssex)
course(cno,cname,teacher)
sc(sno,cno,grade)
现找出"程序设计"课成绩在90分以上的学生姓名
我设计了两种查询语句
<1
select sname,grade from student,course,sc where grade>90 and student.sno=sc.sno and course.cno=sc.cno and course.cname='程序设计'
<2
select sname from student where sno in (select sno from sc where grade>90 and cno in(select cno from course where cname='程序设计'))
请高手帮忙分析一下哪一种好啊~并说明一下为什么!谢谢~
因为处理SQL语句更方便~我一般是用第二种~
(
select sc.sno
from sc , course
where sc.cno = course.cno and sc.grade > 90 and course.cname = '程序设计'
)
select sname,grade
from student s
join sc on s.sno =sc.sno
join course c on sc.cno = c.cno
where grade>90 and c.cname ='程序设计'
--方法3最快(用判断真假)我曾经测试过,用法看联机帮助
select sname from student where grade>90
exists(select 1 from sc where sc.sno=student.sno and
exists(select 1 from course where cname='程序设计' and course.cno=sc.cno))