MySQL Practice-primary foreign key multi-table query

Practice:

1. Establish a table relationship:

Please create the following table and create the related constraints

1  Usedb1;2 CREATE TABLEClass (3CidINTAuto_incrementPRIMARY KEY,4CaptionVARCHAR(Ten)5 );6 CREATE TABLETeacher (7TidINTAuto_incrementPRIMARY KEY,8TnameVARCHAR(Ten)9 );Ten CREATE TABLECourse ( OneCidINTAuto_incrementPRIMARY KEY, ACNameVARCHAR(Ten), -teach_idINT, -     CONSTRAINTFk_course_teacherFOREIGN KEY(teach_id)REFERENCESTeacher (TID) the ); - CREATE TABLEStudent ( -SidINTAuto_incrementPRIMARY KEY, -SnameVARCHAR(Ten), +GenderVARCHAR(Ten), -class_idINT, +     CONSTRAINTFk_student_classFOREIGN KEY(class_id)REFERENCESClass (CID) A ); at CREATE TABLEScore ( -SidINTAuto_incrementPRIMARY KEY, -student_idINT, -course_idINT, -      Number INT, -     CONSTRAINTFk_score_studentFOREIGN KEY(student_id)REFERENCESstudent (SID), in     CONSTRAINTFk_score_courseFOREIGN KEY(course_id)REFERENCESCourse (CID) -);

Create tables and establish table relationships

1 INSERT  intoClass (Caption)VALUES("Three-year Class II");2 INSERT  intoClass (Caption)VALUES("Three shifts a year");3 INSERT  intoClass (Caption)VALUES("One class for three years");4 5 INSERT  intoTeacher (Tname)VALUES("Bo-much");6 INSERT  intoTeacher (Tname)VALUES("Cang-empty");7 INSERT  intoTeacher (Tname)VALUES("Rice Island");8 9 INSERT  intoStudent (SNAME,GENDER,CLASS_ID)VALUES("Steel Egg", "female",1);Ten INSERT  intoStudent (SNAME,GENDER,CLASS_ID)VALUES("Hammer", "female",1); One INSERT  intoStudent (SNAME,GENDER,CLASS_ID)VALUES("Shanbao", "male",2); A  - INSERT  intoCourse (cname,teach_id)VALUES("Creature",1); - INSERT  intoCourse (cname,teach_id)VALUES("Sports",1); the INSERT  intoCourse (cname,teach_id)VALUES("Physical",2); -  - INSERT  intoScore (student_id,course_id, Number)VALUES(1,1, -); - INSERT  intoScore (student_id,course_id, Number)VALUES(1,2, -); + INSERT  intoScore (student_id,course_id, Number)VALUES(2,2, -);

table Data

2. Operation Exercises:

1. Create your own test data

1 INSERT  into VALUES ("Iron Man", "Male",4); 2 -- cannot add or update a child row:a FOREIGN KEY constraint fails (' DB1 '. ' Student ', constraint ' Fk_student_class ' FORE IGN KEY (' class_id ') REFERENCES ' class ' (' CID '))

Test Failed Data

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

1 SELECTs1.student_id from(SELECTA.student_id,a. ' Number` fromscore A,course B2 WHEREA. ' course_id '=B. ' CID ' andB. ' CNAME '="Creature") S1, (SELECTA.student_id,a. ' Number` fromscore A,course B3 WHEREA. ' course_id '=B. ' CID ' andB. ' CNAME '="Sports") S24 WHERES1. ' student_id '=s2.student_id andS1. Number >S2. Number

Multi-table nested queries

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

1 SELECT student_id,AVG(number)2from3  GROUP by student_id4hasAVG( number >

Having usage

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

5, inquire the surname "Li" the number of teachers;
6, the inquiry did not learn "cotyledons" teacher class students of the school number, name;
7, the inquiry learned "001" and also learned the number "002" course of the students ' study number, name;
8, the inquiry has learned "Cotyledons" the teacher teaches all classes The student's school number, the name;
9, inquires the course number "002" The result is lower than the course number "001" The student's school number, the name;
10, the inquiry has the course result is less than 60 points student's study number, the name;
11, the inquiry did not learn all the class student's school number, the name;
12, the inquiry has at least one course and the school number for "001" students learn the same student number and name;
13, inquiry at least study number for the "001" Students selected courses in any one of the other students study number and name;
14, inquires and "002" number of students to study the course of the same class of other students and the name of the school;
15, delete learning "cotyledons" the SC table record of the teacher class;
16, insert some records into the SC table, these records require the following conditions: ① did not have the number "002" course of the student number; ② Insert the "002" number of the average score of the course;
17, according to the average grade from low to high display all students "language", "Mathematics", "English" the course results, as shown in the following form: Student ID, language, Mathematics, English, effective course number, effective average score;
18. Check the highest and lowest score of each section: show the following form: Course ID, highest score, lowest score;
19, according to the average performance of the sections from low to high and the percentage of passing rate from high to low order;
20, the average level of the course from high to low display (real classroom teacher);
21. Check the records of the top three grades of each section: (regardless of the performance of the situation)
22. Query the number of students who have been enrolled in each course;
23. Inquire about the number and name of all the students who have only been enrolled in a course;
24. Query the number of boys and girls;
25. Check the list of students whose surname is "Zhang";
26. Check the names of students with the same name and count the number of names;
27, the average score of each course is queried, the results are arranged in ascending order of average grade and the average grade is the same, descending by the course number.
28. The number, name and average scores of all students with average scores greater than 85 are queried;
29, the name of the course is "mathematics", and the score is less than 60 of the student's name and score;
30. The number and name of the student whose course number is 003 and the course score is more than 80;
31. Number of students selected for the course
32. The students with the highest performance of the students who have been enrolled in the courses offered by the "Linda" teacher are named and their achievements;
33. Inquire about each course and the corresponding elective number;
34. Check the students ' academic number, course number and students ' achievement of different courses but the same results;
35. Check the top two of each course score.
36. Search for the student number of at least two elective courses;
37. Check the course number and course name of all the students who are enrolled in the course;
38, inquiry did not learn the "cotyledons" teacher taught any course of the student's name;
39. Inquire about the students ' academic number and average achievement of two or more failed courses;
40, the search "004" course score is less than 60, in descending order by the number of students;
41, delete the "002" Students of the "001" The results of the course;

MySQL Practice-primary foreign key multi-table query