I'm new to mysql, I've been trying all day to do the following:
How would the code show the number of students enrolled in each of the tutorials?
Table Enrolled
mysql> select * from Inscritos;
+----+--------------+------------+
| id | estudianteId | tutorialId |
+----+--------------+------------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 3 | 1 |
| 4 | 5 | 2 |
| 5 | 5 | 3 |
| 6 | 7 | 1 |
| 7 | 7 | 2 |
| 8 | 7 | 3 |
+----+--------------+------------+
Table students:
mysql> select * from Student;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
| 1 | John | Doe |
| 2 | Mary | Moe |
| 3 | Julie | Dooley |
| 4 | Johanna | Robatti |
| 5 | Ham | Ludwing |
| 6 | Bruce | Heckel |
| 7 | Robert | Sanekis |
| 8 | Irving | Utianov |
| 9 | Mark | Spencer |
| 10 | Daysi | Oconnor |
+----+-----------+----------+
Table tutorials:
mysql> select * from tutorials;
+----+---------------+-----------+------------+
| id | title | author | date |
+----+---------------+-----------+------------+
| 1 | Learn Python | John Paul | 2018-06-06 |
| 2 | Learn MySQL | Abdul S | 2018-06-06 |
| 3 | JAVA Tutorial | Sanjay | 2017-11-05 |
+----+---------------+-----------+------------+
Creation of the Enrolled table:
mysql> create table Inscritos(
-> id int(6) not null auto_increment,
-> estudianteId int(6) unsigned not null,
-> tutorialId int(6) not null,
-> primary key(id),
-> index (estudianteId),
-> index (tutorialId),
-> foreign key(estudianteId) references Student(id),
-> foreign key(tutorialId) references Tutorials(id)
-> );
As you can see, the Student and Tutorials tables are FK of the Enrolled table.
I would greatly appreciate your help, thank you.