SQL statement Exercises

The table structure diagram is as follows:

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/06/89/wKiom1m56N-jqdsBAAB-q8SwhwE496.png "title=" Untitled. png "alt=" Wkiom1m56n-jqdsbaab-q8swhwe496.png "/>

To create a table and insert data:

set foreign_key_checks=0;-- ------------------------------ Table structure for  class-- ----------------------------drop table if exists  ' class '; create table  ' class '   (   ' CID '  int (one)  not null auto_increment,    ' caption '  varchar (+)  NOT NULL,  PRIMARY KEY  (' CID '))   engine=innodb auto_increment=5 default charset=utf8;-- ------------------------------  Records of class-- ----------------------------insert into  ' class '  values   (' 1 ',  ' three Years II ');insert into  ' class '  VALUES  (' 2 ',  ' three years three shifts ') Insert into   ' class '  VALUES  (' 3 ',  ' two classes a year ');insert into  ' class '  VALUES  (' 4 ',  ') Two years and nine classes ');-- ------------------------------ Table structure for course-- ---------- ------------------Drop table if exists  ' Course '; create table  ' Course '   (   ' CID '  int (one)  not null auto_increment,    ' CNAME '  varchar (+)  NOT NULL,   ' teacher_id '  int (one)  NOT  null,  primary key  (' cid '),  key  ' fk_course_teacher '   (' teacher_id '),   CONSTRAINT  ' Fk_course_teacher '  FOREIGN KEY  (' teacher_id ')  references   ' teacher '   (' tid '))  ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;--  ------------------------------ Records of course-- ----------------------------INSERT  INTO  ' Course '  VALUES  (' 1 ',  ' creature ',  ' 1 ');insert into  ' course '   values  (' 2 ',  ' physical ',  ' 2 ');insert into  ' Course '  VALUES  (' 3 ',  ' Sport ',  ') 3 ');insert into  ' Course '  VALUES  (' 4 ',  ' Art ',  ' 2 ');-- ----------------------- -------&nbsP table structure for score-- ----------------------------drop table if  exists  ' score '; create table  ' score '   (   ' Sid '  int (one)  not null auto_increment,    ' student_id '  int (one)  NOT NULL,   ' course_id '  int (one)  NOT  null,   ' num '  int (one)  NOT NULL,  PRIMARY KEY  (' Sid '),   key  ' fk_score_student '   (' student_id '),  key  ' fk_score_course '   (' course_id '),   CONSTRAINT  ' Fk_score_course '  FOREIGN KEY  (' course_id ')  REFERENCES  ' Course '   (' cid '),  constraint  ' fk_score_student '  FOREIGN KEY  (' student_id ')  REFERENCES  ' student '   (' Sid ')  ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT  charset=utf8;-- ------------------------------ Records of score-- ------------------- ---------insert into  ' score '  VALUES  (' 1 ',  ' 1 ',  ' 1 ',  ');insert into  ' Score '  VALUES  (' 2 ',  ' 1 ',  ' 2 ',  ' 9 ');insert into  ' score '  VALUES  (' 5 ',  ' 1 ',  ' 4 ',  ' (') ');insert into  ' score '  VALUES  (' 6 ',  ' 2 ',  ' 1 ',   ' 8 ');insert into  ' score '  VALUES  (' 8 ',  ' 2 ',  ' 3 ',  ' ');insert  into  ' score '  VALUES  (' 9 ',  ' 2 ',  ' 4 ',  ' in ') ';insert into  ' score '   values  (',  ' 3 ',  ' 1 ',  ') ';insert into  ' score '  VALUES  (' one ',  ') 3 ',  ' 2 ',  ');insert into  ' score '  VALUES  (',  ' 3 ',  ' 3 ',  ' 87 ') ;insert into  ' score '  VALUES  (',  ' 3 ',  ' 4 ',  ' ");insert into  ' Score '  VALUES  (',  ' 4 ',  ' 1 ',  ') ';insert into  ' score '  VALUES  ( ',  ' 4 ',  ' 2 ',  ' one ');insert into  ' score '  VALUES  (',  ' 4 ',  ' 3 ',  ' ");insert  into  ' score '  VALUES  (' + ',  ' 4 ',  ' 4 ',  ') ';insert into  ' score '   values  (',  ' 5 ',  ' 1 ',  ') ';insert into  ' score '  VALUES  (',  ') 5 ',  ' 2 ',  ' one ');insert into  ' score '  VALUES  (',  ' 5 ',  ' 3 ',  ' 67 ') ;insert into  ' score '  VALUES  (',  ' 5 ',  ' 4 ',  ') ';insert into  ' Score '  VALUES  (',  ' 6 ',  ' 1 ',  ' 9 ');insert into  ' score '  VALUES  (' ",  ' 6 ',  ' 2 ',  ');insert into  ' score '  VALUES  (',  ', ' 6 ',  ' 3 ',   ';insert into  ' score '  VALUES  (',  ' 6 ',  ' 4 ',  ') ';insert  into  ' score '  VALUES  (',  ' 7 ',  ' 1 ',  ' 9 ');insert into  ' score '   values  (' 27',  ' 7 ',  ' 2 ',  ' + ');insert into  ' score '  VALUES  (' ',  ' 7 ',  ' 3 ',   ';insert into  ' score '  VALUES  (',  ' 7 ',  ' 4 ',  ') ';insert  into  ' score '  VALUES  (',  ' 8 ',  ' 1 ',  ' 9 ');insert into  ' score '   values  (',  ' 8 ',  ' 2 ',  ') ';insert into  ' score '  VALUES  (' + ',  ' 8 ',  ' 3 ',  ';insert into  ' score '  VALUES  (' 8 ',  ' 4 ',  ' 88 ') ;insert into  ' score '  VALUES  (',  ' 9 ',  ' 1 ',  ') ';insert into  ' Score '  VALUES  (',  ' 9 ',  ' 2 ',  ') ';insert into  ' score '  VALUES  ( ',  ' 9 ',  ' 3 ',  ' ";insert into  ' score '  VALUES  (' PNs ',  ' 9 ',  ' 4 ',   ' score '  VALUES  (';insert into  ',  ',  ' 1 ',  ');insert  into  ' score ' VALUES  (',  ',  ' 2 ',  ');insert into  ' score '  VALUES  (' 40 ',   ',  ' 3 ',  ' + ');insert into  ' score '  VALUES  (' All in ',  ',,  ' 4 ',   ';insert into  ' score '  VALUES  (',  ',  ' 1 ',  ');insert  into  ' score '  VALUES  (',  ',  ' 2 ',  ') ';insert into  ' score '   values  (',  ',  ' 3 ',  ') ';insert into  ' score '  VALUES  (',  ') ' One ',  ' 4 ',  ' ",  ');insert into  ' score '  VALUES  (' the ' 90 ',  ' the ' 1 ',  ' of ' + '--')--'--'-" ';insert into  ' score '  VALUES  (',  ',  ' 2 ',  ' "); Insert into   ' score '  VALUES  (',  ',  ' 3 ',  ') ';insert into  ' score '   values  (',  ',  ' 4 ',  ');insert into  ' score '  VALUES  (',  ') ',  ' 3 ',  ' 87');-- ------------------------------ Table structure for student-- ------------- ---------------drop table if exists  ' student '; create table  ' student '   (   ' Sid '  int (one)  not null auto_increment,    ' Gender '  char (1)  NOT NULL,   ' class_id '  int (one)  not null,    ' sname '  varchar (+)  NOT NULL,  PRIMARY KEY  (' Sid '),   key  ' Fk_class '   (' class_id '),  constraint  ' Fk_class '  FOREIGN KEY  (' class_id ')  REFERENCES  ' class '   (' CID ')  engine=innodb auto_increment=17 default  CHARSET=utf8;-- ------------------------------ Records of student-- ----------- -----------------insert into  ' student '  VALUES  (' 1 ',  ' man ',  ' 1 ',  ' understanding '); INSERT  INTO  ' student '  VALUES  (' 2 ',  ' female ', ' 1 ',  ' steel eggs ');insert into  ' student '  VALUES  (' 3 ',  ' Male ',  ' 1 ',  ' Zhang San '); INSERT  INTO  ' student '  VALUES  (' 4 ',  ' Man ',  ' 1 ',  ' Zhang One ');insert into  ' student '  VALUES  (' 5 ',  ' female ',  ' 1 ',  ' Zhang Yi ');insert into  ' student '  VALUES  (' 6 ',   ' Male ',  ' 1 ',  ' Zhang Si ';insert into  ' student '  VALUES  (' 7 ',  ' female ',  ' 2 ',   ' hammer ');insert into  ' student '  VALUES  (' 8 ',  ' Male ',  ' 2 ',  ' lie Triple ');insert  into  ' student '  VALUES  (' 9 ',  ' Male ',  ' 2 ',  ' Lee ');insert into  ' student '  VALUES  (',  ' female ',  ' 2 ',  ' Li II ');insert into  ' student '  VALUES  (' 11 ' ,  ' Male ',  ' 2 ',  ' John Doe ');insert into  ' student '  VALUES  (' the ',  ' ' Female ',  ' 3 ',   ' as Flower ');insert into  ' student '  VALUES  (',  ',  ' 3 ',  ' Liu San '); INSERT  INTO  ' Student ' &NBSp values  (',  ' m ',  ' 3 ',  ' Liu Yi ');insert into  ' student '  VALUES  (' 15 ',   ' Female ',  ' 3 ',  ' Ryuji ');insert into  ' student '  VALUES  (',  ', ' Male ',  ' 3 ',   ' Liusi ');-- ------------------------------ Table structure for teacher-- ---- ------------------------drop table if exists  ' teacher '; create table  ' teacher '   (   ' tid '  int (one)  not null auto_increment,    ' Tname '  varchar (+)  NOT NULL,  PRIMARY KEY  (' Tid ')  engine =innodb auto_increment=6 default charset=utf8;-- ------------------------------  records of teacher-- ----------------------------insert into  ' teacher '  values   (' 1 ',  ' Mr. Zhang Lei ');insert into  ' teacher '  VALUES  (' 2 ',  ' Li Ping '); Insert into   ' teacher '  VALUES  (' 3 ',  ' Liu Haiyan Teacher '); insert into  ' teacher '  VALUES  (' 4 ',  ' Zhu Yunhai teacher ');insert into  ' teacher '  values   (' 5 ',  ' li Jie Teacher '); set foreign_key_checks=1;

SQL Exercise:

1. Check the name of all courses and the corresponding teacher's name

Select Course.cname Course, Teacher.tname Lecturer from course inner join teacher on Course.teacher_id=teacher.tid;

2. Find out how many men and women are in the student list.

Select Gender Gender, COUNT (*) Number of people from student group by gender;

3. The name of the student who queried the physical result equal to 100

Select Sname name from student where Sid in (select student_id from score inner join course on Score.course_id=course.cid whe Re course.cname= ' physical ' and score.num=100);

4. The names and average scores of students who have average scores greater than 80 points

Select Student.sname Name, AVG (score.num) Avg. score left JOIN student on Score.student_id=student.sid GROUP by student_i D

5. Check all students ' student number, name, number of courses selected, total

Select student.sid number, student.sname name, Count (score.course_id) course number, sum (score.num) total from student left join score on Stude NT.SID=SCORE.STUDENT_ID GROUP BY Student.sid;

6. Query the number of teachers surnamed Li

Select Tname Li's teacher from teacher where tname like "Li%";

7. The name of the student who did not report the teacher's class

Select SID Number, sname name from student where Sid isn't in (select student_id from score where course_id in (select Course.cid fr Om teacher left joins course on teacher.tid=course.teacher_id where teacher.tname= "teacher Li"));

8, inquiry Physics course higher than biology course student's school number

Select phy.student_id number from (select Score.student_id,score.num from score left joins course on Score.course_id=course.cid where cname= "physical") as PHY inner JOIN (select Score.student_id,score.num from score left joins course on Score.course_id=cou Rse.cid where cname= "creature") as bio on phy.student_id=bio.student_id where phy.num>bio.num

9. Check the names of students who do not take physical and physical courses at the same time

Select Sname name from student where Sid isn't in (select student_id from score left join course on SCORE.COURSE_ID=COURSE.CIDW Here Cname= "Sport" or cname= "physical" group by student_idhaving Count (course_id) >1));

10. Check the name and class of the students who have more than two doors (including two doors) in Hang Ke

Select Sname,caption from student inner join class onstudent.class_id=class.cid where Student.sid in (select student_id fro M score where Score.num<60group by student_idhaving count (student_id) >=2);

11. Check the names of students who have enrolled in all courses

Select Sname name from the student where Sid in (select student_id from Scoregroup by student_idhaving count (student_id) = (Selec T count (CID) from course));

12. Check all records of the courses taught by teacher Li Ping

Select Course.cname Course, Score.num score from score inner join Courseon course_id=cidwhere course_id in (select CID from Course Left join teacher on Teacher_id=tidwhere tname like "Li ping%");

13. Check the course number and course name that all students have enrolled in

Select Cid,cname from Course where CID on (select course_id from Scoregroup by course_idhaving count (course_id) = (select C Ount (SID) from student));

14. Check the number of electives per course

Select Course.cname Course, COUNT (course_id) number of electives from score inner join course on score.course_id=course.cidgroup by course_id;

15. The name and number of students who have only one course of inquiry

Select SID Number, sname name from Studentwhere SID in (select student_id from Score Group by student_idhaving Count (student_id) =1 );

16. Check all students ' scores and sort them from high to low (grades go Heavy)

Select Sname name, SUM (NUM) score from score inner join Studenton score.student_id=student.sidgroup by Student_idorder by sum (nu m) desc;

17. The average student's name and average score is more than 85.

Select Student.sname name, AVG (NUM) average score from student inner join Scoreon Student.sid=student_idgroup by student_idhaving avg ( NUM) >85;

18. The name of the student who failed to check the biological grade and the corresponding biological score

Select Sname name, num score from score inner join student on Student_id=student.sidwhere course_id in (select CID from Course W Here cname= "creature") having num <60;

19. For all students who have enrolled in Li Ping's course, these courses (Li Ping's course, not all courses) have the highest average student name

Select Student.sname name, AVG (NUM) average score from score inner join Studenton score.student_id=student.sidwhere course_id in (sele CT CID from course inner joins Teacheron teacher_id=tidwhere teacher.tname like "Li Ping") group by Student_idorder by AVG (num ) Desclimit 1

This article is from the "Lyndon" blog, make sure to keep this source http://lyndon.blog.51cto.com/11474010/1965155

SQL statement Exercises