# ARCGIS Pro Random Forest Model Deep Machine Learning Predict Seal Herbal Habitat [Tutorial] Giser Before starting

2023-01-14   ES

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
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;``````

source