这是一个公司的面试题,因第一个没做出来..后面的就都没做出来了.. 题目如下:
学生表 S (SNO, SNAME) SNO为学生号,SNAME为学生名
课程表 C (CNO,CNAME,CTEACHER) CNO为课程号,CNAME为课程名,CTEACHER课程老师
()
学生课程关系表 SC (SNO ,CNO,SCERAPER) SCERAPER为学生成绩..
1.查询出没有选修李明老师课的学生名字.
2.二科课程以上(包括两科)不及格学生姓名及平均成绩.
3.即学过课程A又学过课程B的学生名.
4.课程A成绩比课程B好的学生名
5.课程A成绩比课程B好的学生名,及这些学生课程A的成绩,及课程B的成绩
我基本都没操作出来,感觉简单,但select后就发现需要合并表..但是我当时都做错了..哎..惭愧呀..求解..
学生表 S (SNO, SNAME) SNO为学生号,SNAME为学生名
课程表 C (CNO,CNAME,CTEACHER) CNO为课程号,CNAME为课程名,CTEACHER课程老师
()
学生课程关系表 SC (SNO ,CNO,SCERAPER) SCERAPER为学生成绩..
1.查询出没有选修李明老师课的学生名字.
2.二科课程以上(包括两科)不及格学生姓名及平均成绩.
3.即学过课程A又学过课程B的学生名.
4.课程A成绩比课程B好的学生名
5.课程A成绩比课程B好的学生名,及这些学生课程A的成绩,及课程B的成绩
我基本都没操作出来,感觉简单,但select后就发现需要合并表..但是我当时都做错了..哎..惭愧呀..求解..
select s.sname from s left join (
select sc.sno from c inner join sc on c.cno=sc.cno where c.CTEACHER='李明') as t
on s.snp=t.sno
where t.sno is null
or:
select s.sname from s
where not exists (
select 1 from c,sc where c.CTEACHER='李明' and c.cno=sc.cno
and sc.sno=s.sno
) 2.二科课程以上(包括两科)不及格学生姓名及平均成绩.
select s.sname,avg(sc.SCERAPER) as AVGSCERAPER
from s,sc,(
select SNO
from sc
where SCERAPER<60
group by SNO
having count(*)>=0
) as t
where s.sno=sc.sno
and s.sno=t.sno
group by s.sname
3.即学过课程A又学过课程B的学生名.
select s.sname
from s,sc a,sc b,c ac,c bc
where s.sno=a.sno
and s.sno=b.sno
and a.cno=ac.cno
and b.cno=bc.cno
and ac.cname='A'
and bc.cname='b' 4.课程A成绩比课程B好的学生名
select s.sname
from s,sc a,sc b,c ac,c bc
where s.sno=a.sno
and s.sno=b.sno
and a.cno=ac.cno
and b.cno=bc.cno
and ac.cname='A'
and bc.cname='b'
and a.SCERAPER>b.SCERAPER
5.课程A成绩比课程B好的学生名,及这些学生课程A的成绩,及课程B的成绩
select s.sname,a.SCERAPER as ASCERAPER,b.SCERAPER as BSCERAPER
from s,sc a,sc b,c ac,c bc
where s.sno=a.sno
and s.sno=b.sno
and a.cno=ac.cno
and b.cno=bc.cno
and ac.cname='A'
and bc.cname='b'
and a.SCERAPER>b.SCERAPER
select S.Sname from S where S.Sno not in (select SC.Sno from C,SC where C.Cteacher='李明' and SC.Cno=C.Cno)
------------------------
select s.sname from s
where not exists (
select 1 from c,sc where c.CTEACHER='李明' and c.cno=sc.cno
and sc.sno=s.sno
)
有误
-------------
看错了,不好意思,是对的~
select sno,sname from s where sno not in (
select sno from sc
where cno in(select cno from c where cteacher='李明'))--二科课程以上(包括两科)不及格学生姓名及平均成绩.
select s.sname,avg(sc.sceraper)as avrage from s ,sc
where (select count(sceraper) from sc where sceraper<60 and s.sno=sc.sno)>2
and s.sno=sc.sno
group by s.sname
--即学过课程A又学过课程B的学生名
select s.sname from s,sc a,sc b,sc
where s.sno=sc.sno
and a.cno='a'
and a.sno=b.sno
and a.sno=sc.sno
and b.cno='b'
group by s.sname
--课程A成绩比课程B好的学生名
select s.sname from s,sc a,sc b,sc
where s.sno=sc.sno
and sc.sno=a.sno
and a.sno=b.sno
and a.cno='a'
and b.cno='b'
and a.sceraper>b.sceraper
group by s.sname--课程A成绩比课程B好的学生名,及这些学生课程A的成绩,及课程B的成绩select s.sname,sc.sno,sc.cno,sc.sceraper from s,sc a,sc b,sc
where s.sno=sc.sno
and sc.sno=a.sno
and a.sno=b.sno
and a.cno='a'
and b.cno='b'
and sc.cno in(a.cno,b.cno)
and a.sceraper>b.sceraper
group by s.sname,sc.cno,sc.sceraper,sc.sno
5. Select * from
(Select
SNO,
sum(case when CNAME=’A’ then SCERAPER ‘A’),
sum(case when CNAME=’B’ then SCERAPER ‘B’)
from
(Select SNO, CNAME, SCERAPER from SC inner join C on C.CNO=C.CNO where Cname=’A’
Union
Select SNO, CNAME, SCERAPER from SC inner join C on SC.CNO=C.CNO where Cname=’B’)
Group by SNO) ) tb1
Where tb1.A>tb1.B