数据库建标练习完结篇（32~45）
时间：2021-07-01 10:21:17
帮助过：2人阅读
32、查询所有“女”教师和“女”同学的name、sex和birthday.
select sname, ssex, sbirthday
from student
where ssex
= ‘女‘
union select tname, tsex,tbirthday
from teacher
where tsex
= ‘女‘;
--33、查询成绩比该课程平均成绩低的同学的成绩表。
select c.cno,c.degree
from score c
where c.degree
< (
select avg(s.degree)
from score s
where s.cno
= c.cno
group by s.cno);
--34、 查询所有任课教师的Tname和Depart.
select t.tname, t.depart
from teacher t, course c
where t.tno
= c.tno;
--35，查询所有未讲课的教师的Tname和Depart.
select distinct t.tname, t.depart
from teacher t, course c
where t.tno
not in (
select tno
from course);
--36、查询至少有2名男生的班号。
select * from (
select s.sclass,
count(s.sclass) c
from student s
where s.ssex
= ‘男‘ group by s.sclass)
where c
>=2 ;
--37、查询Student表中不姓“王”的同学记录。
select * from student s
where s.sname
not like ‘王%‘;
--38、查询Student表中每个学生的姓名和年龄。
select s.sname,to_char(sysdate,
‘yyyy‘)
-to_char(s.sbirthday,
‘yyyy‘)
from student s;
--39、查询Student表中最大和最小的Sbirthday日期值。
select min(s.sbirthday) 最大生日,
max(s.sbirthday) 最小生日
from student s;
--40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select * from student s
order by s.sclass
desc ,s.sbirthday;
--41、查询“男”教师及其所上的课程。
select t.tname,c.cname
from teacher t,course c
where t.tno
= c.tno
and t.tsex
= ‘男‘;
--42、查询最高分同学的Sno、Cno和Degree列。
select s.sname,c.cname,r.degree
from student s ,course c,score r
where r.degree
= (
select max(degree)
from score)
and s.sno
= r.sno
and c.cno
= r.cno;
--43、查询和“李军”同性别的所有同学的Sname.
select t.sname
from student t
where t.ssex
= (
select s.ssex
from student s
where s.sname
= ‘李军‘);
--44、查询和“李军”同性别并同班的同学Sname.
select t.sname
from student t
where t.ssex
= (
select s.ssex
from student s
where s.sname
= ‘李军‘)
and
t.sclass = (
select s.sclass
from student s
where s.sname
= ‘李军‘);
--45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
select t.sname,t.ssex,s.degree
from score s,course c,student t
where s.cno
= c.cno
and s.sno
= t.sno
and t.ssex
= ‘男‘ and c.cname
= ‘计算机导论‘;
数据库建标练习完结篇（32~45）
标签：
