SQL Classic Question Collection

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