跪求 sql 查询高手,该如何处理

跪求 sql 查询高手

create table student
(
stuNo int,
stuName char(6)
)
insert into student values(1,'李明')
insert into student values(2,'小红')
insert into student values(3,'小利')
insert into student values(4,'小甜甜')
insert into student values(5,'咪咪')
insert into student values(6,'好莱坞')
create table course
(
cNo int,--为课程号
cName char(6),--课程名
cteacher char(6)--代课老师
)
insert into course values(1,'语文','李老师')
insert into course values(2,'数学','马老师')
insert into course values(3,'英语','杜老师')
select * from course
create table elective--成绩表
(
sNo int,
cNo int,
elecGrade int
) 
select * from elective where cNo=1 and cNo=2
insert into elective values(1,1,80)
insert into elective values(1,2,70)
insert into elective values(2,2,60)
insert into elective values(2,3,60)
insert into elective values(1,2,60)
insert into elective values(1,3,40)
insert into elective values(1,3,50)

select * from student
select * from course
select * from elective

-- 3． 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名???????
select student.stuName from course join student 
on student.stuNo = course.cNo
where cNo = 1 or cNo= 2
-- 4． 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号????
5． 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
??????????


[解决办法]

SQL code

--3． 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名select stuname from student s where exists(    select 1     from elective e     where exists(        select 1         from elective         where sNo=e.sNo and cNo=1 and e.cNo=2)        and e.sNo=s.stuNo)/*stuname李明  */
[解决办法]
4 5没看懂,帮顶.
[解决办法]
-- 3． 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名???????
SQL code
select stuNamefrom student swhere exists(select 1 from course where sNo=s.stuNo and cNo = 1)and exists(select 1 from course where sNo=s.stuNo and cNo = 2)
[解决办法]
SQL code
-- 3． 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名select m.stuname from student m where stuNo in(   select sno from  (  select distinct sno from elective  where cno = 1  union all  select distinct sno from elective  where cno = 1  ) t  group by sno having count(1) = 2)/*stuname ------- 李明  （所影响的行数为 1 行）*/-- 4．列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号--你的sno为1,cno为2怎么多次？select m.stunofrom student m, elective n1 , elective n2where m.stuno = n1.sno and m.stuno = n2.sno and n1.cno = 1 and n2.cno = 2 and n1.elecgrade > n2.elecgrade/*stuno       ----------- 11（所影响的行数为 2 行）*/select distinct m.stunofrom student m, elective n1 , elective n2where m.stuno = n1.sno and m.stuno = n2.sno and n1.cno = 1 and n2.cno = 2 and n1.elecgrade > n2.elecgrade/*stuno       ----------- 1（所影响的行数为 1 行）*/--5.列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩--你的sno为1,cno为2怎么多次？所以出现两个结果select m.stuno , n1.elecgrade , n2.elecgradefrom student m, elective n1 , elective n2where m.stuno = n1.sno and m.stuno = n2.sno and n1.cno = 1 and n2.cno = 2 and n1.elecgrade > n2.elecgrade/*stuno       elecgrade   elecgrade   ----------- ----------- ----------- 1           80          701           80          60（所影响的行数为 2 行）*/