--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 = '男');