select练习题（二）答案

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

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

--18select 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'                  );--19select s.*,sc.cno,sc.degree from s,scwhere 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              );--20select * from scwhere degree > (select degree                 from sc                 where sno = 109                   and cno = '3-105'               )--21select sno,sname,sbirthdayfrom swhere 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;--22select sno,degreefrom scwhere cno in(             select c.cno              from c             where tno in(                          select tno                           from t                          where tname = '张旭'                         )            );--23select tnamefrom twhere tno in(             select tno              from c             where cno in(                          select cno                          from sc                           group by cno                          having count(1) >5                         )            ); --24select * from swhere class = 95031unionselect *                                from swhere class = 95033;--25SELECT cnofrom scwhere degree >85group by cno;--26select sc.sno,s.sname,sc.cno,c.cname,sc.degree,t.tnamefrom sc,c,s,twhere 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.tnogroup by t.tname,sc.sno,s.sname,sc.cno,c.cname,sc.degree--27select * from t;select tname,prof from twhere depart = '计算机系'  and prof not in (                   select prof                    from t                    where depart = '电子工程系'                  )；

--28select s.sname name,s.ssex sex,s.sbirthday birthdayfrom sunion allselect t.tname,t.tsex,t.tbirthdayfrom t;--29  select * from (     select s.sname name,s.ssex sex,s.sbirthday birthdayfrom sunion allselect t.tname,t.tsex,t.tbirthdayfrom t)where sex = '女' ;           ---30select sno,cno,degreefrom scgroup by cno,sno,degreehaving 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,departfrom twhere tno in(             select tno from c            );--32select tname,departfrom twhere not exists(select 1 from c where c.tno = t.tno);--33select class,count(sno)from swhere ssex = '男'group by classhaving count(sno)>1;--34select * from swhere sname not like '王%';--35 select sname,substr(to_char(sysdate,'yyyy-mm-dd'),0,4)-to_number(substr(sbirthday,0,4))+1from s--36select sname,sbirthday 最大最小from swhere sbirthday = (select max(sbirthday) from s)union allselect sname,sbirthday as 最大&最小from swhere sbirthday = (select min(sbirthday) from s)--37select * from sorder by class desc,sbirthday --38select t.tno,t.tname,c.cno,c.cnamefrom t,cwhere t.tno = c.tno  and t.tsex = '男';--39select * from scwhere degree = (select max(degree)                 from sc               )--40select sname from swhere ssex = (              select ssex from s where sname = '李军'             )  and not exists (select * from s t1 where s.sno = t1.sno and t1.sname = '李军');--41select sname from swhere 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,degreefrom scwhere 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 = '男');

原文章浏览量：1664

原文章分类：数据库

原文链接：https://www.iteye.com/blog/lizhiyu-667777