因为答案都是小编自己写的，解法可能有多种，如果您觉得我的解法有误，希望您有时间给我留言。
题目：
1、 查询student表中的所有记录的sname、ssex和class列。
SELECT sname,ssex,class FROM student;
2、 查询教师所有的单位即不重复的depart列。
SELECT distinct depart FROM t_teacher;
3、 查询student表的所有记录。
SELECT * FROM student;
4、 查询t_score表中成绩在60到80之间的所有记录。
SELECT * FROM t_score where degree>60 and degree<80;
5、 查询t_score表中成绩为85，86或88的记录。
SELECT * FROM t_score where degree in (85,86,88)
6、 查询student表中“95031”班或性别为“女”的同学记录。
SELECT * FROM student where class="95031" and ssex='女'
7、 以class降序查询student表的所有记录。
SELECT * FROM student order by class
8、 以cno升序、degree降序查询score表的所有记录。
SELECT * FROM (SELECT * FROM t_score order by cno ) order by degree desc
9、 查询“95031”班的学生人数。
SELECT * FROM student where class="95031"
10、查询score表中的最高分的学生学号和课程号。
SELECT sno,cno FROM t_score where degree=(SELECT max(degree) FROM t_score )
--记录第一条： rownum= 1
11、查询‘3-105’号课程的平均分。
SELECT avg(degree) FROM t_score where cno='3-105'
12、查询score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT * FROM (SELECT cno FROM t_score group by cno having count(*)>5 ) where cno like'%3%'
13、查询最低分大于70，最高分小于90的sno列。
SELECT sno FROM t_score where degree>70 and degree<90
14、查询所有学生的sname、cno和degree列。
SELECT t1.sname,t2.cno,t2.degree FROM student t1,t_score t2 where t1.sno=t2.sno
--学习表
SELECT * FROM student;
--课表表
SELECT * FROM t_course;
--分数表
SELECT * FROM t_score ;
15、查询所有学生的sno、cname和degree列。
SELECT t1.sname,t2.sno,t2.degree FROM student t1,t_score t2 where t1.sno=t2.sno
16、查询所有学生的sname、cname和degree列。
--SELECT * FROM t_course;
--SELECT * FROM t_score;
--SELECT * FROM student;
--SELECT sname FROM student;
--SELECT cname FROM t_course;
--SELECT degree FROM t_score;
SELECT t3.sname,t1.cname,t2.degree FROM t_course t1
inner join t_score t2 on t1.cno=t2.cno
inner join student t3 on t3.sno=t2.sno
17、查询“95033”班所选课程的平均分。
SELECT avg(degree) FROM (SELECT tt.class,yy.degree FROM student tt ,t_score yy where tt.sno=yy.sno) group byclass having class="95033" ;
18、查询所有同学的sno、cno和rank列。
--着题是多字段关链
SELECT t1.sno,t1.cno,t2.rank FROM t_score t1 left join t_grade t2 on t1.degree<=t2.upp and t1.degree>=t2.low
SELECT * FROM t_score
--评级
SELECT * FROM t_grade;
19、查询选修“3-105”课程的成绩高于“109”号同学课程最高成绩的所有同学的记录。
SELECT degree FROM t_score where cno= '3-105' and degree>(
SELECT max(degree) FROM t_score where sno=109 )
20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
SELECT * FROM t_score where cno in (
SELECT cno FROM t_score group by cno having count(*)>1) ORDER BY degree desc
21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
SELECT * FROM (
SELECT * FROM t_score where cno='3-105')where degree>(SELECT degree FROM t_score where sno=109 and rownum=1 )
22、查询和学号为108的同学同年出生的所有学生的sno、sname和sbirthday列。
SELECT * FROM student where sno=108;
23、查询“张旭“教师任课的学生成绩。
SELECT pp.sno,ii.sname,pp.degree,pp.cno
FROM t_course oo,t_score pp ,student ii
where tno=(
SELECT tno FROM t_teacher where tname='张旭') and oo.cno=pp.cno and ii.sno=pp.sno
24、查询选修某课程的同学人数多于5人的教师姓名。
SELECT hh.tname FROM t_course gg left join t_teacher hh on gg.tno=hh.tno where cno=(
SELECT cno FROM t_score group by cno having count(sno)>5
)
25、查询95033班和95031班全体学生的记录。
SELECT vv.*,cc.cno,cc.degree FROM student vv left join t_score cc on vv.sno=cc.sno where class="95033" or class="95031"
26、查询存在有85分以上成绩的课程cno.
SELECT dd.*,ss.cname FROM t_score dd left join t_course ss on dd.cno=ss.cno where degree >85
27、查询出“计算机系“教师所教课程的成绩表。
--课程
SELECT * FROM t_score where cno in (
SELECT cno FROM t_course where tno in (
SELECT tno FROM t_teacher where depart='计算机系'
)
)
28、查询“计算机系”与“电子工程系“不同职称的教师的tname和prof。
SELECT * FROM t_teacher where prof in (SELECT prof FROM t_teacher ui group by prof having count(*)<2) and depart in ('电子工程系','计算机系')
29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的cno、sno和degree,并按degree从高到低次序排序。
SELECT * FROM t_score where cno='3-245' and degree>(SELECT degree FROM t_score where cno='3-105') order by degree
--等一下再做
30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的cno、sno和degree.
select * from t_score a where cno = '3-105' and a.degree>(
select b.degree from t_score b where cno = '3-245'and a.sno=b.sno)
31、查询所有教师和同学的name、sex和birthday.
SELECT t1.tname,t1.tsex,t1.tbirthday,t4.sname,t4.ssex,t4.sbirthday FROM t_teacher t1
inner join t_course t2 on t1.tno=t2.tno
inner join t_score t3 on t2.cno=t3.cno
inner join student t4 on t3.sno=t4.sno
32、查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT t1.tname,t1.tsex,t1.tbirthday,t4.sname,t4.ssex,t4.sbirthday FROM t_teacher t1
inner join t_course t2 on t1.tno=t2.tno
inner join t_score t3 on t2.cno=t3.cno
inner join student t4 on t3.sno=t4.sno where t4.ssex='女' and t1.tsex='女'
33、查询成绩比该课程平均成绩低的同学的成绩表。
SELECT * FROM t_score q1 inner join (
SELECT cno,avg(degree) degree FROM t_score group by cno) q2 on q1.cno=q2.cno where q1.degree>q2.degree
34、查询所有任课教师的tname和depart.
SELECT distinct y1.tname,y1.depart FROM t_teacher y1 left join t_course y2 on y1.tno=y2.tno
35、查询所有未讲课的教师的tname和depart.
SELECT distinct y1.tname,y1.depart FROM t_teacher y1 left join t_course y2 on y1.tno!=y2.tno
36、查询至少有2名男生的班号。
SELECT * FROM student where class in (SELECT class FROM student group by class having count(*)>2) and ssex='男'
37、查询student表中不姓“王”的同学记录。
SELECT * FROM student where sname not like '%王%'
38、查询student表中每个学生的姓名和年龄。
SELECT sname,to_char(sysdate,'yyyy')-to_char(sbirthday,'yyyy') FROM student
39、查询student表中最大和最小的sbirthday日期值。
SELECT max(sbirthday),min(sbirthday) FROM student ;
40、以班号和年龄从大到小的顺序查询student表中的全部记录。
SELECT sno,sname,ssex, class,to_char(sysdate,'yyyy')-to_char(sbirthday,'yyyy')nl FROM student ORDER BY class,nl desc
41、查询“男”教师及其所上的课程。
SELECT ii.cname FROM t_course ii inner join (
SELECT tno FROM t_teacher where tsex='男'
) oo on ii.tno=oo.tno
42、查询最高分同学的sno、cno和degree列。
SELECT tt.* FROM (
SELECT sno,cno,degree FROM t_score ORDER BY degree desc)tt where rownum=1
43、查询和“李军”同性别的所有同学的sname.
SELECT sname FROM student where ssex=(
SELECT ssex FROM student where sname='李军')
44、查询和“李军”同性别并同班的同学sname.
SELECT tq.*,qw.class FROM student tq inner join (
SELECT ssex,class FROM student where sname='李军') qw on tq.ssex=qw.ssex and tq.class=qw.class
45、查询所有选修“计算机导论”课程的“男”同学的成绩表
SELECT fg.*,gh.degree,gh.sno,hj.ssex FROM t_course fg
inner join t_score gh on fg.cno=gh.cno
inner join student hj on gh.sno=hj.sno
where cname='计算机导论' and hj.ssex='男'
46、查询出选修课程号为3-245和6-166的课程的学生学号与姓名
SELECT vc.sname,vc.sno FROM student vc inner join t_score xc on vc.sno=xc.sno where cno in ('3-245','6-166')
47、查询出没有选修课程号为3-245和6-166的课程的学生学号与姓名
SELECT distinct hd.sno,dd.sname FROM t_score hd left join student dd on hd.sno=dd.sno where cno!='3-245' or cno!='6-166'
48、查询学号为'101'的学生的所有课程的成绩，如果没有选修该课程，则成绩为0
select nvl(sno,'101'),cname,nvl(degree,0) from
t_score t2 right join t_course t1
on t1.cno=t2.cno and t2.sno='101'
附加题：
查询 班级为3-105所有学生的成绩
SELECT q1.sno,q1.cno,q3.degrees FROM t_score q1 inner join (
SELECT sno ,sum(degree)degrees FROM t_score group by sno
) q3 on q1.sno=q3.sno where cno='3-105'
欢迎关注小编的公众号，更多学习视频、模板、工具（wind激活工具）！！！
