请教“面试试题”中的一道sql的查询语句的写法

Question

1
select t1.sno from student t1,course t2,sc t3
where t1.son = t3.son
and   t2.cno = t3.con
and   t3.con = '001'
and   t3.con = '002'
and   t3.con != '003';

Answer 1

根本只用T3表就可以了,木头的答案肯定是错的,肯定是什么也选不到.
可以用IN和NOT IN过滤来筛选.后面一题就用MAX算出来的平均就可以了,平均先SUM(grade)再除以COUNT.当然要GROUP BY SNO

Answer 2

都是用exists的查询
1.select sno from sc t1 where 
exists (select * from sc where sno=t1.sno and cno='001') and
exists (select * from sc where sno=t1.sno and cno='002') and 
not exists (select * from sc where sno=t1.sno and cno='003')2.select * from sc t1 where not exists(
select * from sc where cno=t1.cno and grade>t1.grade)当然也可以用ORACLE的分析函数，这样可以考虑并列名次的情况，并且取前N名都很容易
select * from (
select t1.*,rank() over (partition by cno order by grade desc) as r from sc t1) where r=1

Answer 3

1.
select distinct sno from sc where (cno='001' or cno='002') and (cno!='003');
2.
select a.* from(select sno,avg(grade) avgs from sc group by sno) a,(select
 max(avg(grade)) max from sc group by sno) b where a.avgs=b.max;

Answer 4

1.
select * from sc where cno in('001','002') and cno<>'003';

Answer 5

用这样又简单又明了：
(select sno from sc where con = '001'
intersect                             --交
select sno from sc where con = '002')
minus                                 --差
select sno from sc where con = '003'

Answer 6

1.Select Distinct sc.sno From sc Where sc.sno Not In (
              Select sc.sno From sc Where sc.con  In ('003') )
                   And sc.con In ('002','001')-----子查询，应该可以吧。
2.Select sno From (
             Select Sum(grade) ,sno,Count(sno),Sum(grade)/Count(sno) avga From sc Group By sno)
Where avga = (Select Max(avga) From (
             Select Sum(grade) ,sno,Count(sno),Sum(grade)/Count(sno) avga From sc Group By sno))

Answer 7

同意nebulaly的第一题解
第二题如下：
select sc.sno 
from sc 
group by sc.sno
having avg(grade) =
(select max(t1.average) 
from (select avg(grade) as average from sc group by sno) t1 )

Answer 8

第二个也可这样
    select sno 
    from sc
    where grade=(select max(avg(grade))
                 from sc
                 group by xh);

Answer 9

create table student(sno number,sname varchar2(20));
        create table course(cno number,cname varchar2(20));
        create table sc(sno number,cno number,grade number);
        
        insert into student values(1,'jxc1');
        insert into student values(2,'jxc2');
        insert into student values(3,'jxc3');
        insert into student values(4,'jxc4');
        
        insert into course values(1,'course1');
        insert into course values(2,'course2');
        insert into course values(3,'course3');
        
        insert into sc values(1,1,10);
        insert into sc values(1,2,20);
        insert into sc values(1,3,30);
        
        insert into sc values(2,1,20);
        insert into sc values(2,2,20);
        insert into sc values(2,3,20);
        
        insert into sc values(3,1,10);
        insert into sc values(3,2,50);
        
        insert into sc values(4,1,70);
        insert into sc values(4,3,10);
        commit;
        
        1 ============================================================        
        select sno from sc where cno = 1
        union
        select sno from sc where cno = 2
        minus
        select sno from sc where cno = 3
        
        或者
        select sno from sc a where cno = 1 
         and exists(select * from sc b where a.sno=b.sno and b.cno = 2)
         and not exists(select * from sc b where a.sno=b.sno and b.cno = 3);
        
        
        2. 计算平均成绩最高的学生学号；
        ============================================================
        SELECT Sno
          FROM Sc
         GROUP BY Sno
        HAVING AVG(Grade) =  (SELECT MAX(AVG(Grade)) FROM Sc GROUP BY Sno);

Answer 10

select * from
(
select sno,avg(grade) ag from sc
group by sno
order by ag desc
) where rownum = 1

Answer 11

select sno from sc
where cno in ('001','002')
group by sno having count(*)>1

Answer 12

select distinct t.sno
  from sc t,
       sc t1,
       sc t2
 where t.sno= t2.sno and
       t.sno= t1.sno and t.cno= '001' and
       t1.cno= '002' and t2.cno<> '003'

Answer 13

计算平均成绩就比较简单了
jxc(GameHeart)
select sno from sc where cno = 1
        union
        select sno from sc where cno = 2
        minus
        select sno from sc where cno = 3
这个语句写的话 cno为1或者con2都会被取出来,只要cno<>3

Answer 14

select distinct t.sno
  from sc t,
       sc t1
 where t.sno= t1.sno and t.cno= '001' and
       t1.cno= '002' and and t.sno not in
( select distinct t2.sno from sc t2 where t2.cno= '003')