-- 13、查询最低分大于70，最高分小于90的Sno列。 SELECT sno FROM scores GROUP BY sno HAVING MAX(degree) < 90 and MIN(degree) >70 -- 14、查询所有学生的Sname、Cno和Degree列。 SELECT a.sno,a.sname,b.degree from students as a INNER JOIN scores as b on a.sno = b.sno SELECT a.sname,b.cno,b.degree from students as a INNER JOIN scores as b on a.sno = b.sno ORDER BY a.sname -- 15、查询所有学生的Sno、Cname和Degree列。 SELECT a.sno,b.cname,a.degree FROM scores as a INNER JOIN courses as b  on a.cno = b.cno ORDER BY a.sno -- 16、查询所有学生的Sname、Cname和Degree列。 SELECT     a.sname,     c.cname,     b.degree FROM     students AS a INNER JOIN scores AS b ON (a.sno = b.sno) INNER JOIN courses AS c ON (b.cno = c.cno) -- GROUP BY c.cname ORDER BY     a.sname  -- 17、查询“95033”班所选课程的平均分。 SELECT     c.cname,     AVG(degree) FROM     students AS a INNER JOIN scores AS b ON (a.sno = b.sno) INNER JOIN courses AS c ON (b.cno = c.cno) WHERE     a.class = '95033' GROUP BY     c.cname ORDER BY     a.sname 

SELECT     c.cname,     AVG(degree) FROM     students AS a INNER JOIN scores AS b ON (a.sno = b.sno) INNER JOIN courses AS c ON (b.cno = c.cno) WHERE     a.class = '95033' ORDER BY     a.sname 

-- 18、假设使用如下命令建立了一个grade表：  create table grade(low   number(3,0),upp   number(3),rank   char(1));  insert into grade values(90,100,’A’); insert into grade values(80,89,’B’); insert into grade values(70,79,’C’); insert into grade values(60,69,’D’); insert into grade values(0,59,’E’); commit; -- 现查询所有同学的Sno、Cno和rank列。 -- 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。 SELECT a.sno,a.cno,a.degree from scores as a INNER JOIN scores as b on (a.cno = b.cno and a.degree > b.degree) where a.cno = '3-105' AND b.sno = '109'

-- 20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。 SELECT * from scores GROUP BY sno HAVING COUNT(cno) > 1 and degree != MAX(degree)

-- 21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。 SELECT * FROM scores as a INNER JOIN scores as b on (a.sno = b.sno and a.degree > b.degree) where b.sno = '109' and  a.cno = '3-105' -- 22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。 SELECT a.sno,a.sname,a.sbirthday FROM students as a INNER JOIN students as b on (YEAR(a.sbirthday) = (YEAR(a.sbirthday))) WHERE b.sno = '108'

-- 23、查询“张旭“教师任课的学生成绩。 SELECT a.sno,a.degree FROM scores as a INNER JOIN courses as b on (a.cno = b.cno) INNER JOIN teachers as c  ON (b.tno = c.tno) WHERE c.tname = '张旭' 

-- 24、查询选修某课程的同学人数多于5人的教师姓名。

SELECT DISTINCT tname FROM scores as a INNER JOIN courses as b on (a.cno = b.cno) INNER JOIN teachers as c ON (b.tno = c.tno) where b.cno in  (SELECT cno from scores GROUP BY cno HAVING COUNT(sno) > 5)