· 9 years ago · Jan 04, 2017, 07:38 PM
1SELECT school.id AS `School ID`,
2 COUNT(DISTINCT students.id) AS `Enrollment Count`
3FROM courses
4 LEFT JOIN course_enrollment ce
5 ON ce.course_id = courses.id
6 LEFT JOIN school school
7 ON school.id = courses.school_id
8 LEFT JOIN students students
9 ON students.id = ce.student_id
10GROUP BY school.id
11
12School ID Enrollment Count
131 7
142 4
15
16SELECT school.id AS `School ID`,
17 courses.course_name AS `Course Name`,
18 COUNT(students.id) AS `Enrollment Count`
19FROM courses
20 LEFT JOIN course_enrollment ce
21 ON ce.course_id = courses.id
22 LEFT JOIN school school
23 ON school.id = courses.school_id
24 LEFT JOIN students students
25 ON students.id = ce.student_id
26GROUP BY school.id,
27 courses.course_name
28
29School ID Course Name Enrollment Count
301 CHEM1301 7
311 ENGL1301 3
322 ACCT1301 4
33
34SELECT school.id AS `School ID`,
35 school.name AS `School Name`,
36 courses.course_name AS `Course Name`,
37 courses.course_professor AS `Professor`,
38 students.first_name `Student First Name`,
39 students.last_name AS `Student Last Name`
40FROM school school
41 LEFT JOIN courses courses
42 ON courses.school_id = school.id
43 LEFT JOIN course_enrollment ce
44 ON ce.course_id = courses.id
45 LEFT JOIN students students
46 ON students.id = ce.student_id
47GROUP BY school.id,
48 courses.id,
49 students.id
50
51School ID School Name Course Name Professor Student First Name Student Last Name
521 School A CHEM1301 John Doe Aden Baxter
531 School A CHEM1301 John Doe Anthony Leslie
541 School A CHEM1301 John Doe Laird Jewel
551 School A CHEM1301 John Doe Osmond Mikey
561 School A CHEM1301 John Doe Isidore Josiah
571 School A CHEM1301 John Doe Rafferty Tate
581 School A CHEM1301 John Doe Dave Lenox
591 School A ENGL1301 Jacob Prask Aden Baxter
601 School A ENGL1301 Jacob Prask Rafferty Tate
611 School A ENGL1301 Jacob Prask Dave Lenox
622 School B ACCT1301 Glenn Ogg Sachie Baldwin
632 School B ACCT1301 Glenn Ogg Cletis Nicky
642 School B ACCT1301 Glenn Ogg Garey Simon
652 School B ACCT1301 Glenn Ogg Bennie Howard
66
67# ************************************************************
68# Sequel Pro SQL dump
69# Version 4541
70#
71# http://www.sequelpro.com/
72# https://github.com/sequelpro/sequelpro
73#
74# Host: REDACTED (MySQL 5.7.14-google-log)
75# Database: school
76# Generation Time: 2017-01-04 19:30:50 +0000
77# ************************************************************
78
79
80/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
81/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
82/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
83/*!40101 SET NAMES utf8 */;
84/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
85/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
86/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
87
88
89# Dump of table course_enrollment
90# ------------------------------------------------------------
91
92DROP TABLE IF EXISTS `course_enrollment`;
93
94CREATE TABLE `course_enrollment` (
95 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
96 `student_id` int(11) unsigned DEFAULT NULL,
97 `course_id` int(11) unsigned NOT NULL,
98 PRIMARY KEY (`id`),
99 KEY `course_id` (`course_id`),
100 KEY `student_id` (`student_id`),
101 CONSTRAINT `course_enrollment_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course_enrollment` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
102 CONSTRAINT `course_enrollment_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
103) ENGINE=InnoDB DEFAULT CHARSET=utf8;
104
105LOCK TABLES `course_enrollment` WRITE;
106/*!40000 ALTER TABLE `course_enrollment` DISABLE KEYS */;
107
108INSERT INTO `course_enrollment` (`id`, `student_id`, `course_id`)
109VALUES
110 (1,1,1),
111 (2,1,2),
112 (3,2,1),
113 (4,3,1),
114 (5,4,1),
115 (6,5,1),
116 (7,6,1),
117 (8,6,2),
118 (9,7,3),
119 (10,8,3),
120 (11,9,3),
121 (12,10,3),
122 (13,11,1),
123 (14,11,2);
124
125/*!40000 ALTER TABLE `course_enrollment` ENABLE KEYS */;
126UNLOCK TABLES;
127
128
129# Dump of table courses
130# ------------------------------------------------------------
131
132DROP TABLE IF EXISTS `courses`;
133
134CREATE TABLE `courses` (
135 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
136 `course_name` text,
137 `course_professor` text,
138 `school_id` int(11) unsigned DEFAULT NULL,
139 PRIMARY KEY (`id`),
140 KEY `school_id` (`school_id`),
141 CONSTRAINT `courses_ibfk_1` FOREIGN KEY (`school_id`) REFERENCES `school` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
142) ENGINE=InnoDB DEFAULT CHARSET=utf8;
143
144LOCK TABLES `courses` WRITE;
145/*!40000 ALTER TABLE `courses` DISABLE KEYS */;
146
147INSERT INTO `courses` (`id`, `course_name`, `course_professor`, `school_id`)
148VALUES
149 (1,'CHEM1301','John Doe',1),
150 (2,'ENGL1301','Jacob Prask',1),
151 (3,'ACCT1301','Glenn Ogg',2);
152
153/*!40000 ALTER TABLE `courses` ENABLE KEYS */;
154UNLOCK TABLES;
155
156
157# Dump of table school
158# ------------------------------------------------------------
159
160DROP TABLE IF EXISTS `school`;
161
162CREATE TABLE `school` (
163 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
164 `name` text,
165 PRIMARY KEY (`id`)
166) ENGINE=InnoDB DEFAULT CHARSET=utf8;
167
168LOCK TABLES `school` WRITE;
169/*!40000 ALTER TABLE `school` DISABLE KEYS */;
170
171INSERT INTO `school` (`id`, `name`)
172VALUES
173 (1,'School A'),
174 (2,'School Bn');
175
176/*!40000 ALTER TABLE `school` ENABLE KEYS */;
177UNLOCK TABLES;
178
179
180# Dump of table students
181# ------------------------------------------------------------
182
183DROP TABLE IF EXISTS `students`;
184
185CREATE TABLE `students` (
186 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
187 `first_name` text,
188 `last_name` text,
189 PRIMARY KEY (`id`)
190) ENGINE=InnoDB DEFAULT CHARSET=utf8;
191
192LOCK TABLES `students` WRITE;
193/*!40000 ALTER TABLE `students` DISABLE KEYS */;
194
195INSERT INTO `students` (`id`, `first_name`, `last_name`)
196VALUES
197 (1,'Aden','Baxter'),
198 (2,'Anthony','Leslie'),
199 (3,'Laird','Jewel'),
200 (4,'Osmond','Mikey'),
201 (5,'Isidore','Josiah'),
202 (6,'Rafferty','Tate'),
203 (7,'Sachie','Baldwin'),
204 (8,'Cletis','Nicky'),
205 (9,'Garey','Simon'),
206 (10,'Bennie','Howard'),
207 (11,'Dave','Lenox');
208
209/*!40000 ALTER TABLE `students` ENABLE KEYS */;
210UNLOCK TABLES;
211
212
213
214/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
215/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
216/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
217/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
218/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
219/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;