1. Background of the problem
(1) Student form (school number, name, age, gender)
Student (S#,sname,sage,ssex)
(2) Course Schedule (course number, course name, teacher number)
Course (c#,cname,t#)
(3) Score table (School number, course number, fraction)
Student_score (S#,c#,score)
(4) Teacher's form (teacher number, teacher's name)
Teacher (T#,tname)
2. Create a table
(1) Student table
Create tablecreate TABLE ' student ' ( ' s# ' bigint () NOT NULL COMMENT ' study number ', ' Sname ' varchar () DEFAULT NULL COMME NT ' name ', ' Sage ' int (3) default null COMMENT ' age ', ' Ssex ' varchar (4) default null COMMENT ' gender ', PRIMARY KEY (' S # ')) Engine=innodb DEFAULT Charset=utf8
(2) Schedule
Create tablecreate TABLE ' cource ' ( ' C # ' bigint (a) NOT NULL COMMENT ' course number ', ' Cname ' varchar ') DEFAULT NULL COMM ENT ' Course name ', ' t# ' bigint (a) default NULL COMMENT ' teacher number ', PRIMARY KEY (' C # ')) Engine=innodb default Charset=utf8
(3) score table
Create tablecreate TABLE ' student_score ' ( ' s# ' bigint () NOT NULL COMMENT ' study number ', ' C # ' bigint (+) NOT NULL COMMENT ' Course number ', ' score ' double default NULL COMMENT ' fraction ', PRIMARY KEY (' s# ', ' C # ')) Engine=innodb default Charset=utf8
(4) Teachers ' table
Create tablecreate TABLE ' teacher ' ( ' t# ' bigint () not null COMMENT ' teacher number ', ' tname ' varchar () DEFAULT NULL COM ment ' teacher name ', PRIMARY KEY (' t# ')) Engine=innodb DEFAULT Charset=utf8
3. Questions
(1) Find out the number of all students with "1" courses that are higher than the "2" course;
Select A. ' s# ' from (select sc. ' s# ', SC. ' Score ' from student_score SC WHERE SC. ' C # ' = 1) A, (SELECT sc. ' s# ', SC. ' Score ' from student_score sc ' student_score ' WHERE sc. ' C # ' = 2) b WHERE a.score > B.score and A. ' s# ' = B. ' s# ';
(2) The number of students with average scores greater than 60 points and average scores
SELECT SC. ' s# ', avg (SC. ' score ') from Student_score SC GROUP by SC. ' s# ' has AVG (SC. ' score ') > 60;
SQL Classic Question Collection