MySQL practiced hand

1. Create the following form according to the diagram

　　Tables that do not have foreign keys are created first, and are created in the order teacher,class,course,student

CREATE TABLE Class (CID INT NOT NULL auto_increment PRIMARY key,caption CHAR (+) NOT null) ENGINE = INNODB DEFAULT charse T = Utf8;insert into score (caption) VALUES (' Three-year class two '), (' Three shifts a year '), (' Three-year class ');

2. Check the number of all students who have higher grades in "biology" course than "physics" course

CREATE VIEW VW1 as selectscore.student_id as "study number", Student.sname as "name", Course.cname as "subject", number as "biological score" fromscoreleft Join course on course.cid = Score.course_idleft Join student on score.student_id = Student.sidWHEREcourse.cname = ' creature ';  CREATE VIEW vw2 as selectscore.student_id as "study number", Student.sname as "name", Course.cname as "subject", number as "Physical score" Fromscoreleft Join course on course.cid = Score.course_idleft Join student on score.student_id = Student.sidWHEREcourse.cname = ' physical '; Select*fromvw1inner JOIN vw2 on vw1 = vw2. Study number WHEREVW1. Biological score > VW2. Physical fraction;

We found that, according to the current table structure, students who did not meet the above criteria were not comparable, but what should be done if the amount of data was enlarged and comparable?

3, query average score is greater than 60 points of the students and the average score

selectscore.student_id as "study number", Student.sname as "name", SUM (score.number) as "total number", AVG (Score.number) as Gvafromscoreleft Join course on course.cid = Score.course_idleft Join student on score.student_id = Student.sidgroup Bystudent_idhavinggva > 60;

4, check all students of the student number, name, number of courses selected, total

selectscore.student_id as "study number", Student.sname as "name", SUM (score.number) as "Total", count (score.course_id) as ' course count ' Fromscoreleft Join course on course.cid = Score.course_idleft Join student on score.student_id = Student.sidgroup Bystuden t_id

5. The number of teachers who queried the surname "Li"

SelectCount (Tname) as "number" fromteacherwheretname like "wave%";

6, the inquiry did not learn "cotyledons" teacher class students of the school number, name

　　Thinking: I have not learned a teacher, I can find students who have studied this teacher, and in the student table to judge, ruled out these learned is not learned

(Student's course ID in (first to find the course ID taught by cotyledons teacher))

In the end, you just have to rule out not the students.

Select*fromstudentwheresid not in (SELECT distinctstudent_idfromscorewherecourse_id in (Selectcidfromcourseleft JOIN Teacher on teacher_id = Tidwheretname = "Wave multi"))

7, the inquiry learned "001" and also learned the number "002" course of the student's number, name

Idea: First find out all the students who have studied 001 or 002 course_id in (001,002)

Then group by student Id,having the number of accounts, equal to 2 is the eligible

Selectstudent_id,snamefromstudentleft JOIN score on student_id = student.sidwherecourse_id in (1, 2) GROUP BYstudent_ Idhavingcount (student_id) = 2;

8, the inquiry has learned "Cotyledons" the teacher teaches all classes The student's school number, the name

Select*fromstudentwherestudent.sid in (SELECT distinctstudent_idfromscorewherecourse_id in (SELECTcidFROMcourseLEFT JOIN teacher on teacher.tid = Course.teacher_idWHEREteacher.tname = ' rice island ');

9. The number and name of all students who have a lower grade than the course number "001" for the course number "002"

Selectid1,namefrom (selectstudent_id as id1,number as number1,student.sname as Namefromscoreleft JOIN student on Student.sid = score.student_idWHEREscore.course_id = 1) as Aleft JOIN (selectstudent_id as id2,number as Number2fromscorel EFT JOIN student on student.sid = SCORE.STUDENT_IDWHERESCORE.COURSE_ID = 2) as-B on a.id1 = b.id2wherenumber1 > Number2 ;

10, the inquiry has the course result is less than 60 points student's school number, the name

SELECT distinctstudent.sid,snamefromstudentleft JOIN score on student.sid = Score.student_idWHEREstudent.sid in ( Selectstudent_idfromscorewherenumber < 60);

11, the inquiry did not learn all the class student's school number, the name

Selectsid,snamefromstudentwheresid in (Selectstudent_idfromscoregroup bystudent_idhavingcount (student_id) = 3);

12, inquires at least one course and the student number is "001" students learn the same student number and name

Idea: First find the course of 001 classmates---a tuple

COURSE_ID in tuples---student ID tuples

SID in student ID tuple

SELECT Distinctsid,snamefromstudentwheresid in (selectstudent_idfromscorewherecourse_id in (SELECTcourse_ idfromscorewherestudent_id = 1) and Sid! = 1;

13, inquires the study number is "001" schoolmate all one course Other studentsSchool Number and name

Selectsid,snamefromstudentwheresid in (selectstudent_idfromscorewherecourse_id in (selectcourse_ idfromscorewherestudent_id = 1) GROUP bystudent_idhavingcount (student_id) = 1) and Sid! = 1;

MySQL practiced hand