Writing questions:

Student table student, curriculum table Course, student selection table StudentCourse

1. Please design the above three tables and draw a physical relationship diagram.
2. Please write a formal statement and create the above three tables.
3. Please write the insert statement and insert a course selected by a student.
4. The name of a student was written wrong, please write the update statement.
5. Please write the query statement and query the students who have not chosen a course.
6. Please write the query statement and query the students who have been elected by 5 courses.

First analyze that a student can choose multiple courses, a course can be selected by multiple students, determining that the relationship is more relationships.

Multi -relationship building principles:

need to create a third table, at least two fields in the middle table. These two fields are used as the main key to the other key.

1. The physical relationship between the tables is as follows:

2, build statements:

Student watch

Create table `Student` ( 
   `sid` int (11) not null auto_increment, 
   `name` varchar (20) not null, 
   `class` varchar (20) not null, 
   Primary Key (`sid`) 
 ); 
 Insert Into Student (`Name`, Class) Values 
 ('Zhang San', 'first grade'), ('Li Four', 'Second Grade'), ('Wang Five', 'Third Grade'), ('Zhao Liu ",' Fourth Grade '); 
 Select * from Student;

course table

Create table `Course` ( 
   `cid` int (11) not null auto_increment, 
   `name` varchar (20) not null, 
   Primary Key (`cid`) 
 ); 
 Insert INTO COURSE (`Name`) Values 
 ('Chinese'), ('Mathematics'), ('English'), ('Physics'), ('Geography'), ('History'); 
 Select * From Course;

Student curriculum selection table

CREATE TABLE `studentcourse` (
  `sid` INT(11) NOT NULL,
  `cid` INT(11) NOT NULL,
  PRIMARY KEY (`sid`,`cid`),
  FOREIGN KEY(sid) REFERENCES student(sid),
  FOREIGN KEY(cid) REFERENCES course(cid)
); 
INSERT INTO studentcourse VALUES 
(1,1),(1,2),(1,3),(2,1),(2,3),(3,1),(3,2),(3,3),(3,4),(3,5);
SELECT * FROM studentcourse;

3. Update the student’s name wrong.

Update Student Set `name` = 'Eight Eight' where sign = 4; 

 Select * from Student;

4, query students who have not chosen a course

SELECT s.`name` FROM student s WHERE s.`sid` NOT IN 
  (SELECT sc.`sid` FROM studentcourse sc WHERE sc.`sid`);

5. Query students who have been selected for 5 courses

SELECT s.`name` FROM student s JOIN studentcourse sc ON 
 s.`sid`=sc.`sid` GROUP BY s.`sid` HAVING COUNT(sc.`sid`)=5;