select练习题（二）答案

--1
select Sname,Ssex,Class
from s;
--2
select depart
from t
group by depart;
--3
select *
from s;
--4
select *
from sc
where degree between 60 and 80;
--5
select *
from sc
where degree in(85,86,88);
--6
select *
from s
where ssex='女' and class='95031';
--7
select *
from s
order by class desc;
--8
select *
from sc
order by cno asc,degree desc;
--9
select class,count(1) as 人数
from s
where class=95031 group by class;
--10
select sno,cno
from sc
where degree = (select max(degree) from sc);
--11
select avg(degree)
from sc
where cno='3-105';
--12
select cno,avg(degree)
from sc
where cno like '3%' group by cno having count(cno)>4;
--13
select sno
from sc
having max(degree)<90 and min(degree)>70
group by sno;

--14
select s.sname,sc.cno,sc.degree
from s,sc
where s.sno = sc.sno;
--15
select sc.sno,c.cname,sc.degree
from sc,c
where sc.cno = c.cno;
--16
select s.sname,c.cname,sc.degree
from s,sc,c
where sc.sno = s.sno
  and sc.cno = c.cno;
--17
select avg(degree)
from s,sc
where s.sno = sc.sno
  and sc.sno in(select sno from s where class = 95033);

--18
select s.*
from s,sc
where s.sno = sc.sno
  and sc.cno = '3-105'
  and sc.degree >(
                  select degree
                  from sc
                  where sno = 109
                    and cno = '3-105'
                  );
--19
select s.*,sc.cno,sc.degree
from s,sc
where s.sno = sc.sno
  and sc.sno in (
              select sno
              from sc
              where sno not in(select sno
                               from sc
                               where degree in (
                                               select max(degree)
                                               from sc
                                               group by cno))
              group by sno
              having count(cno)>1
              );
--20
select *
from sc
where degree > (select degree
                from sc
                where sno = 109
                  and cno = '3-105'
               )
--21
select sno,sname,sbirthday
from s
where trunc(to_date(sbirthday,'yyyy-mm-dd'),'yyyy') = (
                                                     select trunc(to_date(sbirthday,'yyyy-mm-dd'),'yyyy')
                                                     from s
                                                     where sno=101
                                                    )
      and sno != 101;
--22
select sno,degree
from sc
where cno in(
             select c.cno
             from c
             where tno in(
                          select tno
                          from t
                          where tname = '张旭'
                         )
            );
--23
select tname
from t
where tno in(
             select tno
             from c
             where cno in(
                          select cno
                          from sc
                          group by cno
                          having count(1) >5
                         )
            );
--24
select *
from s
where class = 95031
union
select *                               
from s
where class = 95033;
--25
SELECT cno
from sc
where degree >85
group by cno;
--26
select sc.sno,s.sname,sc.cno,c.cname,sc.degree,t.tname
from sc,c,s,t
where sc.cno in (
              select cno from c where tno in(
                                             select tno from t where depart = '计算机系'
                                            )
             )
  and s.sno = sc.sno
  and c.cno = sc.cno
  and t.tno = c.tno
group by t.tname,sc.sno,s.sname,sc.cno,c.cname,sc.degree
--27
select * from t;
select tname,prof
from t
where depart = '计算机系'
  and prof not in (
                   select prof
                   from t
                   where depart = '电子工程系'
                  )；

--28
select s.sname name,s.ssex sex,s.sbirthday birthday
from s
union all
select t.tname,t.tsex,t.tbirthday
from t;
--29 
select *
from (    
select s.sname name,s.ssex sex,s.sbirthday birthday
from s
union all
select t.tname,t.tsex,t.tbirthday
from t
)
where sex = '女' ;          
---30
select sno,cno,degree
from sc
group by cno,sno,degree
having degree<avg(degree);

select * from sc a where degree<(select avg(degree)
from sc b where a.cno=b.cno);

select sno,cno,degree from sc t1 group by cno,sno,degree;

--31
select tname,depart
from t
where tno in(
             select tno from c
            );
--32
select tname,depart
from t
where not exists(select 1 from c where c.tno = t.tno);
--33
select class,count(sno)
from s
where ssex = '男'
group by class
having count(sno)>1;
--34
select *
from s
where sname not like '王%';
--35
select sname,substr(to_char(sysdate,'yyyy-mm-dd'),0,4)-to_number(substr(sbirthday,0,4))+1
from s
--36
select sname,sbirthday 最大最小
from s
where sbirthday = (select max(sbirthday) from s)
union all
select sname,sbirthday as 最大&最小
from s
where sbirthday = (select min(sbirthday) from s)
--37
select *
from s
order by class desc,sbirthday
--38
select t.tno,t.tname,c.cno,c.cname
from t,c
where t.tno = c.tno
  and t.tsex = '男';
--39
select *
from sc
where degree = (select max(degree)
                from sc
               )
--40
select sname
from s
where ssex = (
              select ssex from s where sname = '李军'
             )
  and not exists (select * from s t1 where s.sno = t1.sno and t1.sname = '李军');
--41
select sname
from s
where ssex = (
              select ssex from s where sname = '李军'
             )
  and not exists (select * from s t1 where s.sno = t1.sno and t1.sname = '李军')
  and s.class = (
                 select class from s where sname = '李军'
                );
--42
select sno,cno,degree
from sc
where cno = (select cno from c where cname = '计算机导论')
  and exists (select 1 from s where s.sno = sc.sno and s.ssex = '男');
  select sno,cno,degree from sc where cno in (select cno from c where cname = '计算机导论')
  and sno in (select sno from s where ssex = '男');