#课程表DROP TABLE IF EXISTS `course`;CREATE TABLE `course` ( `CNO` char(5) NOT NULL, `CNAME` varchar(10) NOT NULL, `TNO` int(10) NOT NULL, PRIMARY KEY (`CNO`)) ENGINE=MyISAM AUTO_INCREMENT=348 DEFAULT CHARSET=utf8;-- ------------------------------ Table structure for score-- ----------------------------#成绩表DROP TABLE IF EXISTS `score`;CREATE TABLE `score` ( `SNO` varchar(3) NOT NULL, `CNO` varchar(5) NOT NULL, `DEGREE` decimal(10,1) NOT NULL) ENGINE=MyISAM AUTO_INCREMENT=348 DEFAULT CHARSET=utf8;#学生表-- ------------------------------ Table structure for student-- ----------------------------DROP TABLE IF EXISTS `student`;CREATE TABLE `student` ( `SNO` int(10) NOT NULL, `SNAME` varchar(4) NOT NULL, `SSEX` tinyint(2) NOT NULL, `SBIRTHDAY` datetime DEFAULT NULL, `CLASS` varchar(5) DEFAULT NULL, PRIMARY KEY (`SNO`)) ENGINE=MyISAM AUTO_INCREMENT=348 DEFAULT CHARSET=utf8;#教师表-- ------------------------------ Table structure for teacher-- ----------------------------DROP TABLE IF EXISTS `teacher`;CREATE TABLE `teacher` ( `TNO` varchar(3) NOT NULL, `TNAME` varchar(4) NOT NULL, `TSEX` tinyint(2) NOT NULL, `TBIRTHDAY` datetime NOT NULL, `PROF` varchar(6) DEFAULT NULL, `DEPART` varchar(10) NOT NULL, PRIMARY KEY (`TNO`)) ENGINE=MyISAM AUTO_INCREMENT=348 DEFAULT CHARSET=utf8; 以课程号升序、成绩降序查询成绩表的所有记录。 SELECT * from score ORDER BY cno asc, degree DESC; 查询“95031”班的学生人数。SELECT COUNT(CLASS) as 9501班 FROM student WHERE class="95033";
查询‘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;
转载于:https://www.cnblogs.com/JohnsonZilch/p/6549916.html
相关资源：Sql高级查询练习题(有答案）
