Python's database

# Job content ' 1, query the name of all the courses and the corresponding teacher name 2, query the student table of male and female students each have how many people 3, query physical results equal to 100 of the student's name 4, query average score of more than 80 students ' names and average results 5, query All students of the school number, name, select the number of courses Total 6, inquires the number of teachers surnamed Li 7, query did not report Li Ping Teacher Class name 8, inquiry Physics course than Biology course student's number 9, query does not simultaneously elective physics course and Physical Education class student name 10, query hang section more than two door (including two door) student name and Class "# Create library and library table , and insert Data "Class Table: Class Timetable: Course score table: Score student table: Student teacher table: Teacher" "mysql> create Database Sqlexam; Query OK, 1 row affected (0.01 sec) mysql> use sqlexamdatabase changedmysql>mysql> SET NAMES UTF8; Query OK, 0 rows affected (0.01 sec) mysql> SET foreign_key_checks = 0; Query OK, 0 rows Affected (0.00 sec) mysql> DROP TABLE IF EXISTS ' class '; Query OK, 0 rows affected, 1 Warning (0.00 sec) mysql> CREATE TABLE ' class ', ' cid ' int (one) not NULL auto_incremen T, ' caption ' varchar (+) not NULL, PRIMARY KEY (' CID '), Engine=innodb auto_increment=5 DEFAULT charset= UTF8; Query OK, 0 rows affected (0.04 sec) mysql> INSERT into ' class ' VALUES (' 1 ', ' three year Two '), (' 2 ', ' Three '), (' 3 ', ' Two shifts a year '), (' 4 ', ' nine classes in two years '); Query OK, 4 rows Affected (0.00 sec) Records: 4 duplicates:0 warnings:0mysql> DROP TABLE IF EXISTS ' course '; Query OK, 0 rows affected, 1 Warning (0.00 sec) mysql> CREATE TABLE ' Course ' (-"CID" int (one) not NULL Auto_increme NT, ' cname ' varchar (+) NOT null, ' teacher_id ' int (one) not NULL,---PRIMARY key (' CID '), key ' Fk_cour Se_teacher ' (' teacher_id '), CONSTRAINT ' Fk_course_teacher ' FOREIGN KEY (' teacher_id ') REFERENCES ' teacher ' (' tid ') ) Engine=innodb auto_increment=5 DEFAULT Charset=utf8; Query OK, 0 rows affected (0.04 sec) mysql> INSERT into ' Course ' VALUES (' 1 ', ' creature ', ' 1 '), (' 2 ', ' Physical ', ' 2 '), (' 3 ', ' Sports ', ' 3 '), (' 4 ', ' Art ', ' 2 '); Query OK, 4 rows affected (0.01 sec) records:4 duplicates:0 warnings:0mysql> DROP TABLE IF EXISTS ' score '; Query OK, 0 rows affected, 1 Warning (0.00 sec) mysql> CREATE TABLE ' score ' (-) ' Sid ' Int (one) not NULL auto_incremen T, ' student_id ' int (one) not null, ' course_id ' int (one) not null, ' num ' int (one) not NULL, PRIMARY KE Y (' Sid '), Key ' fk_score_student ' (' student_id '), key ' Fk_score_course ' (' course_id '), CONSTRAINT ' Fk_score_c Ourse ' 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; Query OK, 0 rows affected (0.02 sec) mysql> INSERT into ' score ' VALUES (' 1 ', ' 1 ', ' 1 ', ' 10 '), (' 2 ', ' 1 ', ' 2 ', ' 9 '), (' 5 ') , ' 1 ', ' 4 ', ' 66 '), (' 6 ', ' 2 ', ' 1 ', ' 8 '), (' 8 ', ' 2 ', ' 3 ', ' 68 '), (' 9 ', ' 2 ', ' 4 ', ' 99 '), (' 10 ', ' 3 ', ' 1 ', ' 77 '), (' 11 ', ' 3 '), ' 2 ', ' 66 '), (' 12 ', ' 3 ', ' 3 ', ' 87 '), (' 13 ', ' 3 ', ' 4 ', ' 99 '), (' 14 ', ' 4 ', ' 1 ', ' 79 '), (' 15 ', ' 4 ', ' 2 ', ' 11 '), (' 16 ', ' 4 ', ' 3 ', ' 67 '), (' 17 ', ' 4 ', ' 4 ', ' 100 '), (' 18 ', ' 5 ', ' 1 ', ' 79 '), (' 19 ', ' 5 ', ' 2 ', ' 11 '), (' 20 ', ' 5 ', ' 3 ', ' 67 '), (' 21 ', ' 5 ', ' 4 ', ' 100 '), (' 22 ', ' 6 ', ' 1 ', ' 9 '), (' 23 ', ' 6 ', ' 2 ', ' 100 '), (' 24 ', ' 6 ', ' 3 ', ' 67 '), (' 25 ', ' 6 ', ' 4 ', ' 100 '), (' 26 ', ' 7 ', ' 1 ', ' 9 '), (' 27 ', ' 7 ', ' 2 ', ' 100 '), (' 28 ', ' 7 ', ' 3 ', ' 67'), (' 29 ', ' 7 ', ' 4 ', ' 88 '), (' 30 ', ' 8 ', ' 1 ', ' 9 '), (' 31 ', ' 8 ', ' 2 ', ' 100 '), (' 32 ', ' 8 ', ' 3 ', ' 67 '), (' 33 ', ' 8 ', ' 4 ', ' 88 ') , (' 34 ', ' 9 ', ' 1 ', ' 91 '), (' 35 ', ' 9 ', ' 2 ', ' 88 '), (' 36 ', ' 9 ', ' 3 ', ' 67 '), (' 37 ', ' 9 ', ' 4 ', ' 22 '), (' 38 ', ' 10 ', ' 1 ', ' 90 '), (' 39 ', ' 10 ', ' 2 ', ' 77 '), (' 40 ', ' 10 ', ' 3 ', ' 43 '), (' 41 ', ' 10 ', ' 4 ', ' 87 '), (' 42 ', ' 11 ', ' 1 ', ' 90 '), (' 43 ', ' 11 ', ' 2 ', ' 77 '), (' 44 ', ' 11 ', ' 3 ', ' 43 '), (' 45 ', ' 11 ', ' 4 ', ' 87 '), (' 46 ', ' 12 ', ' 1 ', ' 90 '), (' 47 ', ' 12 ', ' 2 ', ' 77 '), (' 48 ', ' 12 ', ' 3 ', ' 43 '), (' 49 ', ' 12 ', ' 4 ', ' 87 '), (' 52 ', ' 13 ', ' 3 ', ' 87 '); Query OK, affected (0.01 sec) records:47 duplicates:0 warnings:0mysql> DROP TABLE IF EXISTS ' student '; Query OK, 0 rows affected, 1 Warning (0.00 sec) mysql> CREATE TABLE ' student ' (-) ' Sid ' Int (one) not NULL Auto_increm ENT, ' Gender ' char (1) NOT NULL, ' class_id ' int (one) not null, ' sname ' varchar (+) not NULL, and 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; Query OK, 0 rows affected (0.02 sec) mysql> INSERT into ' student ' VALUES (' 1 ', ' Male ', ' 1 ', ' Understanding '), (' 2 ', ' Female ', ' 1 ', ' Steel Eggs '), ( ' 3 ', ' Male ', ' 1 ', ' Zhang San '), (' 4 ', ' Male ', ' 1 ', ' Zhang Yi '), (' 5 ', ' Female ', ' 1 ', ' Zhang Yi '), (' 6 ', ' Male ', ' 1 ', ' Zhang Si '), (' 7 ', ' Female ', ' 2 ', ' Hammer '), (' 8 ', ' Male ', ' 2 ', ' lie Triple '), (' 9 ', ' Male ', ' 2 ', ' Lee '), (' 10 ', ' Female ', ' 2 ', ' Li II '), (' 11 ', ' Male ', ' 2 ', ' John Doe '), (' 12 ', ' Female ', ' 3 ', ' Like Flower '), (' 13 ', ' Male ', ' 3 ', ' Liu San '), (' 14 ', ' Male ', ' 3 ', ' Liu Yi '), (' 15 ', ' Female ', ' 3 ', ' Ryuji '), (' 16 ', ' Male ', ' 3 ', ' Liusi '); Query OK, affected (0.01 sec) records:16 duplicates:0 warnings:0mysql> DROP TABLE IF EXISTS ' teacher '; Query OK, 0 rows affected, 1 Warning (0.00 sec) mysql> CREATE TABLE ' teacher ' (-) ' tid ' int (one) not NULL Auto_increm ENT, ' tname ' varchar (+) not NULL, PRIMARY KEY (' tid '), Engine=innodb auto_increment=6 DEFAULT charset= UTF8; Query OK, 0 rows affected (0.02 sec) mysql> INSERT into ' teacher ' VALUES (' 1 ', ' Mr. Zhang Lei '), (' 2', ' Ms. Li Ping '), (' 3 ', ' Liu Haiyan Teacher '), (' 4 ', ' Zhu Yunhai teacher '), (' 5 ', ' Li Jie Teacher '); Query OK, 5 rows Affected (0.00 sec) records:5 duplicates:0 warnings:0mysql> SET foreign_key_checks = 1; Query OK, 0 rows Affected (0.00 sec) mysql> Show tables;+-------------------+| Tables_in_sqlexam |+-------------------+| Class | | Course | | Score | | Student | | Teacher |+-------------------+5 rows in Set (0.00 sec) # 1, check the name of all courses and the corresponding instructor name Mysql> SELECT, Course.cname, Teacher.tname, course, INNER JOIN teacher on course.teacher_id = teacher.tid;+--------+------------- ----+| CNAME | Tname |+--------+-----------------+| Biology | Mr. Zhang Lei | | Physics | Teacher Li Ping | | Art | Teacher Li Ping | | Sports | Liu Haiyan teacher |+--------+-----------------+4 rows in Set (0.01 sec) # 2, check how many men and women are in the student table mysql> SELECT, gender sex, count (1) Number of people, student, gender;+--------+--------+| sex | People |+--------+--------+| Women | 6 | | Male | |+--------+--------+2 rows in Set (0.00 sec) # 3, the name of the student who queried the physical score equals 100 mysql> SELECT-> Student.sname-----SID in (----Scor, student_id, SELECT E-INNER JOIN course on score.course_id = Course.cid, WHERE, course.cname = ' physical ' and score.num = 100 +); +--------+| Sname |+--------+| Zhang Si | | Hammer | | Lie triple |+--------+3 rows in Set (0.00 sec) # 4, the names and average scores of students who have averaged more than 80 points mysql> SELECT, student.sname, T1.avg_num-&G T INNER JOIN from student-------STUDENT_ID, AVG (num) as Avg_num, from score-&gt ; GROUP by-student_id, with AVG (num) >, as t1 on student.sid = t1.student_id;+--------+----- ----+| sname | Avg_num |+--------+---------+| Zhang San | 82.2500 | | Liu San | 87.0000 |+--------+---------+2 rows in Set (0.00 sec) # 5, check all student's number, name, number of courses selected, Total mysql> select--Student.sid, Stu Dent.sname, T1.course_num,, T1.total_num, and student,---Student_ ID, COUNT (course_ID) course_num, sum (num) total_num, score, GROUP by, student_id) as T1 on Student.s ID = t1.student_id;+-----+--------+------------+-----------+| Sid | sname | Course_num | Total_num |+-----+--------+------------+-----------+| 1 | Understanding | 3 | 85 | | 2 | Steel Egg | 3 | 175 | | 3 | Zhang San | 4 | 329 | | 4 | Zhang Yi | 4 | 257 | | 5 | Zhang Yi | 4 | 257 | | 6 | Zhang Si | 4 | 276 | | 7 | Hammer | 4 | 264 | | 8 | Lie Triple | 4 | 264 | | 9 | Lee | 4 | 268 | | 10 | Li er | 4 | 297 | | 11 | John Doe | 4 | 297 | | 12 | Like flowers | 4 | 297 | | 13 | Liu San | 1 | 87 | | 14 | Liu Yi | NULL | NULL | | 15 | Ryuji | NULL | NULL | | 16 | Liusi | NULL | NULL |+-----+--------+------------+-----------+16 rows in Set (0.00 sec) # 6, query surname Li's number mysql> SELECT, Count (TID)-& Gt Teacher, WHERE, tname like ' li% '; +------------+| Count (tid) |+------------+| 2 |+------------+1 row in Set (0.00 sec) # 7, query the name of the student who did not report to Li Ping teacher class mysql> SELECT, Student.sname, student WHERE, Sid not in (---SELECT DISTINCT, Student_id, score, WHERE, course_id in (SELECT, Course.cid, from, course-&G T INNER JOIN teacher on course.teacher_id = Teacher.tid, WHERE, teacher.tname = ' teacher Li Ping '); +--------+| Sname |+--------+| Liu San | | Liu Yi | | Ryuji | | Liusi |+--------+4 rows in Set (0.00 sec) # 8, Inquiry Physics course higher than biology course number of students mysql> SELECT, t1.student_id, from Select-student_id, Num-, from-, score, WHERE, course_id = (SELECT, CID, FR (), course, OM---------------T1, INNER JOIN as-----student_id,-&G T Num, from, score, where---course_id = (SELECT, CID, from, course, where-&gt ; CNAME = ' creature ') as t2 on t1.student_id = t2.student_id, WHERE, T1.num > t2.num;+------------+| student_id |+------------+| 6 | | 7 | | 8 |+------------+3 rows in Set (0.00 sec) # 9, query not taking both physical and physical coursesStudent name Mysql> Select, Student.sname, from, student, WHERE D-C-Score, where, course_id in (-------course, CID----- CNAME = ' physical ', OR cname = ' sports ', GROUP by, student_id, with COUNT (course_id) = 1 );+--------+| Sname |+--------+| Understanding | | Steel Egg | | Liu San |+--------+3 rows in Set (0.00 sec) # 10, search for students with more than two doors (including two doors) name and class Mysql> SELECT--Student.sname, class.caption INNER JOIN---------student T student_id (course_id) >= 2) as T1 INNER JOIN class on Stu, GROUP by Dent.sid = t1.student_id and student.class_id = class.cid;+--------+--------------+| sname | Caption |+--------+--------------+| Understanding | Three-year class two |+--------+--------------+1 row in Set (0.00 sec)

Python database