2tfZHV8z

1SELECT
2  student.StudentID,
3  student.`Name`,
4  COUNT(attendance.AttendanceID) AS Total
5FROM
6  student
7LEFT JOIN attendance ON student.StudentID = attendance.StudentID
8	
9SELECT
10  student.StudentID,
11  student.`Name`,
12  COUNT(attendance.AttendanceID) AS Total
13FROM
14  student
15LEFT JOIN attendance ON student.StudentID = attendance.StudentID
16GROUP BY student.StudentID,student.`Name`;
17	
18DROP DATABASE IF EXISTS alishaikh; CREATE DATABASE alishaikh;
19USE alishaikh
20CREATE TABLE student
21(
22  StudentID CHAR(8) NOT NULL,
23  Name VARCHAR(40),
24  PRIMARY KEY (StudentID)
25);
26INSERT INTO student (StudentID,Name) VALUES
27('k1052280','Ali Shaikh'),('k1052287','McKenzie Roth'),
28('k1052288','Dacey Sullivan'),('k1052294','Zelda Cantu'),
29('k1052295','Kimberly Melton'),('k1052296','Tatianna Cantrell'),
30('k1052297','Morgan Thornton'),('k1052298','Allistair Barlow'),
31('k1052299','Troy Fulton');
32CREATE TABLE attendance
33(
34  AttendanceID INT NOT NULL AUTO_INCREMENT,
35  StudentID CHAR(8) NOT NULL,
36  PRIMARY KEY (AttendanceID),
37  KEY (StudentID)
38);
39INSERT INTO attendance (StudentID) VALUES
40('k1052280'),('k1052280'),('k1052287'),('k1052287'),
41('k1052288'),('k1052295'),('k1052295'),('k1052295');
42	
43mysql> DROP DATABASE IF EXISTS alishaikh; CREATE DATABASE alishaikh;
44Query OK, 2 rows affected (0.01 sec)
45Query OK, 1 row affected (0.00 sec)
46mysql> USE alishaikh
47Database changed
48mysql> CREATE TABLE student
49    -> (
50    ->   StudentID CHAR(8) NOT NULL,
51    ->   Name VARCHAR(40),
52    ->   PRIMARY KEY (StudentID)
53    -> );
54Query OK, 0 rows affected (0.03 sec)
55
56mysql> INSERT INTO student (StudentID,Name) VALUES
57    -> ('k1052280','Ali Shaikh'),('k1052287','McKenzie Roth'),
58    -> ('k1052288','Dacey Sullivan'),('k1052294','Zelda Cantu'),
59    -> ('k1052295','Kimberly Melton'),('k1052296','Tatianna Cantrell'),
60    -> ('k1052297','Morgan Thornton'),('k1052298','Allistair Barlow'),
61    -> ('k1052299','Troy Fulton');
62Query OK, 9 rows affected (0.00 sec)
63Records: 9  Duplicates: 0  Warnings: 0
64
65mysql> CREATE TABLE attendance
66    -> (
67    ->   AttendanceID INT NOT NULL AUTO_INCREMENT,
68    ->   StudentID CHAR(8) NOT NULL,
69    ->   PRIMARY KEY (AttendanceID),
70    ->   KEY (StudentID)
71    -> );
72Query OK, 0 rows affected (0.02 sec)
73
74mysql> INSERT INTO attendance (StudentID) VALUES
75    -> ('k1052280'),('k1052280'),('k1052287'),('k1052287'),
76    -> ('k1052288'),('k1052295'),('k1052295'),('k1052295');
77Query OK, 8 rows affected (0.00 sec)
78Records: 8  Duplicates: 0  Warnings: 0
79	
80mysql> SELECT
81    ->   student.StudentID,
82    ->   student.`Name`,
83    ->   COUNT(attendance.AttendanceID) AS Total
84    -> FROM
85    ->   student
86    -> LEFT JOIN attendance ON student.StudentID = attendance.StudentID
87    -> GROUP BY student.StudentID,student.`Name`;
88+-----------+-------------------+-------+
89| StudentID | Name              | Total |
90+-----------+-------------------+-------+
91| k1052280  | Ali Shaikh        |     2 |
92| k1052287  | McKenzie Roth     |     2 |
93| k1052288  | Dacey Sullivan    |     1 |
94| k1052294  | Zelda Cantu       |     0 |
95| k1052295  | Kimberly Melton   |     3 |
96| k1052296  | Tatianna Cantrell |     0 |
97| k1052297  | Morgan Thornton   |     0 |
98| k1052298  | Allistair Barlow  |     0 |
99| k1052299  | Troy Fulton       |     0 |
100+-----------+-------------------+-------+
1019 rows in set (0.00 sec)
102
103mysql>