MySQL Query Exercise

#课程表
DROP TABLE IF EXISTS ' course ';
CREATE TABLE ' Course ' (
' CNO ' char (5) Not NULL,
' CNAME ' varchar (ten) is not NULL,
' TNO ' int (ten) is 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 (ten) is 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 () not NULL,
PRIMARY KEY (' TNO ')
) Engine=myisam auto_increment=348 DEFAULT Charset=utf8;

 以课程号升序、成绩降序查询成绩表的所有记录。 
SELECT * from score ORDER BY cno asc, degree DESC;

Check the number of students in the "95031" class.
Select COUNT (Class) as 9501 shifts 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;

The results of the "3-105" course are more than the records of all the students in the "109" grade. 
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 )

 query score a record of a non-highest score for a student who has chosen to learn more than one course.  
 select score.* from score INNER joins 
 (SELECT Sno, MAX (degree) as Maxdegree from score GROUP by Sno have COUNT (*) >1) temp 
 on score.sno = Temp.sno and Score.degree < Temp.maxdegree;  
  Query and study number 107 students of all students born in the same year Sno, sname and Sbirthday columns  
 Select Sno,sname,sbirthday from student WHERE year (sbirthday) = 
 (SELECT year ( Sbirthday) from student WHERE sno=107); 
  
 Query "Zhang Xu" Student results for Instructor Class 
    select * from score where cno in (select CNO from course where TNO = (SELECT TNO from teacher where tname = ' Zhang Xu '); 
            

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

Query "computer department" and "Electronic Engineering department" different titles of the teacher's name and title.SELECT Tname, Proffrom teacher where depart =  ' computer system ' Span class= "Hljs-keyword" >and Prof not in (select Prof from teacher where DEPART = unionselect Tname, Prof from teacher where depart =  ' Electronic Engineering Department ' and Prof not in ( select Prof from teacher WHERE Depart =  ' computer system ');            

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

< Span class= "Hljs-keyword" >< Span class= "Hljs-keyword" > 
 
             

MySQL Query Exercise