MySQL 查询练习

查询‘3-105’号课程的平均分
SELECT AVG(degree) FROM score WHERE CNO="3-105";
SELECT avg(degree) 平均分  from score GROUP BY cno HAVING cno = ‘3-105‘;

查询成绩表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT CNO,avg(degree) from score  GROUP BY CNO HAVING  COUNT(*)>5 and CNO like "3%";
查询最低分大于70，最高分小于90的学生编号 列
SELECT SNO  from score  GROUP BY SNO HAVING  min(DEGREE)>70 and MAX(DEGREE)<90;

查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT  stu.* from  student stu LEFT JOIN score sc ON sc.SNO=stu.SNO and cno="3-105" and sc.DEGREE>
(SELECT degree FROM score WHERE CNO="3-105" AND SNO=109 )

查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
SELECT score.* from score INNER JOIN 
(SELECT sno ,MAX(degree) as maxdegree from score GROUP BY sno HAVING COUNT(*) >1) temp 
on score.sno = temp.sno and score.degree < temp.maxdegree;
查询和学号为107的同学同年出生的所有学生的Sno、Sname和Sbirthday列
SELECT sno,sname,sbirthday FROM student WHERE YEAR(SBIRTHDAY)=
(SELECT YEAR(SBIRTHDAY) from student WHERE SNO=107);

查询“张旭“教师任课的学生成绩
SELECT * from score WHERE cno in (SELECT cno from course WHERE tno = (SELECT tno from teacher WHERE tname =‘张旭‘));

查询出“计算机系“教师所教课程的成绩表。
SELECT * from score WHERE cno in (SELECT CNO from course WHERE TNO in (SELECT tno from teacher WHERE DEPART = ‘计算机系‘));

查询“计算机系”与“电子工程系“不同职称的教师的名字和职称。
SELECT tname ,prof from teacher WHERE DEPART = ‘计算机系‘ and  prof not in (SELECT prof from teacher WHERE DEPART = ‘电子工程系‘)
UNION
SELECT tname ,prof from teacher WHERE DEPART = ‘电子工程系‘ and  prof not in (SELECT prof from teacher WHERE DEPART = ‘计算机系‘);

查询所有教师和同学的名字、性别和生日.
SELECT sname as name , ssex as sex , sbirthday as birthday from student
UNION ALL #万一老师和学生姓名 性别 出生日相同呢 所以加了ALL
SELECT tname , tsex  , tbirthday as birthday from teacher;

MySQL 查询练习

标签：null   asc   having   charset   var   color   rem   structure   记录