MySQL Exercises reference answers

To export existing database data:

• Mysqldump-u User name-p password database name > export file path # structure + data
• Mysqldump-u User name-p password-d database name > export file path # structure

Import existing database data:

• Mysqldump-uroot-p Password Database name < file path

/* Navicat Premium Data Transfer source server:localhost Source Server type:mysql Source server Version:5  0624 source Host:localhost source Database:sqlexam target server type:mysql target server Version : 50624 File encoding:utf-8 date:10/21/2016 06:46:46 am*/set NAMES UTF8; 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 ')) ENGI Ne=innodb auto_increment=5 DEFAULT Charset=utf8,--------------------------------Records of ' class '----------------- -------------Begin;insert into ' class ' VALUES (' 1 ', ' three years '), (' 2 ', ' Three ', ' three Shifts '), (' 3 ', ' Two classes a year '), (' 4 ', ' nine classes in two years '); COMMIT;--------------------------------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_tea Cher ' (' teacher_id '), CONSTRAINT ' Fk_course_teacher ' FOREIGN KEY (' teacher_id ') REFERENCES ' teacher ' (' tid ')) Engine=inn ODB auto_increment=5 DEFAULT Charset=utf8,--------------------------------Records of ' course '---------------------- --------Begin;insert into ' Course ' VALUES (' 1 ', ' creature ', ' 1 '), (' 2 ', ' Physical ', ' 2 '), (' 3 ', ' Sport ', ' 3 '), (' 4 ', ' Art ', ' 2 '); COMMIT;--------------------------------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 ' NU LL, ' 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 '------------------------------begin;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 '); COMMIT;--------------------------------table structure for ' student '------------------------------DROP table IF EXISTS ' student ';   CREATE TABLE ' student ' (' Sid ' Int (one) not NULL auto_increment, ' Gender ' char (1) is not NULL, ' class_id ' int (one) ' is not NULL, ' sname ' varchar (+) not NULL, PRIMARY key (' Sid '), key ' Fk_class ' (' class_id '), CONSTRAINT ' Fk_class ' FOREIGN KEY (' C lass_id ') REFERENCES ' class ' (' CID ')) Engine=innodb auto_increment=17 DEFAULT Charset=utf8;--------------------------  ------Records of ' student '------------------------------begin;insert into ' student ' VALUES (' 1 ', ' Male ', ' 1 ', ' Understanding '), (' 2 ', ' Female ', ' 1 ', ' Steel Egg '), (' 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 One '), (' 15 ', ' Female ', ' 3 ', ' Ryuji '), (' 16 ', ' Male ', ' 3 ', ' Liusi '); COMMIT;--------------------------------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 ')) ENGI Ne=innodb auto_increment=6 DEFAULT Charset=utf8,--------------------------------Records of ' teacher '--------------- ---------------Begin;insert into ' teacher ' VALUES (' 1 ', ' Zhang Lei '), (' 2 ', ' Miss Li '), (' 3 ', ' Liu Haiyan Teacher '), (' 4 ', ' Zhu Yunhai teacher '), (' 5 ', ' Li Jie old Division '); COMMIT; SET foreign_key_checks = 1; table structure and data

table structure and data

2, the inquiry "Biology" course is higher than "physics" course all student's school number;

Select a.student_id from (select Student_id,num from score left joins course on Score.course_id=course.cid WHERE COURSE.CNA Me= "Creature") as Ainner join (select Student_id,num from score left JOIN course on Score.course_id=course.cid WHERE course.cname = "Physical") as BON a.student_id=b.student_id WHERE a.num > B.num

3, the query average score is more than 60 points of the student's school number, name and average score;

Select Student.sid,student.sname,b.numname from (select Student_id,avg (num) as Numname from score GROUP by student_id HAVI NG AVG (num) >60) as Bleft JOIN student on B.student_id=student.sid

4, inquiry all students of the school number, name, number of courses selected, total;

SELECT Student_id,student.sname,count (course_id), SUM (num) from score left JOIN student on Score.student_id=student.sid GROUP by student_id

5, inquire the surname "Li" the number of teachers;

SELECT * FROM teacher WHERE tname like "Li%"

6, inquiry did not learn the "Li Ping" teacher class students of the school number, name;

Select Student.sid,student.sname from student where Student.sid not in (SELECT student_id from score where score.course_id  In (select Course.cid to course left joins teacher on Course.teacher_id=teacher.tid WHERE teacher.tname= "teacher Li") GROUP by STUDENT_ID)

MySQL Exercises reference answers