-
Notifications
You must be signed in to change notification settings - Fork 0
/
queries.sql
208 lines (163 loc) · 4.6 KB
/
queries.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
--Query for Colin
SELECT DISTINCT
Classes_Scheduled.Class_ID,
Referrals.Ref_F_Name,
Referrals.Ref_L_Name,
Curriculum.Curriculum_Name,
Class_Subjects.Class_Subject
FROM
Referrals,
Curriculum,
Participants,
Classes_Scheduled,
Curriculum_Subjects,
Class_Subjects
WHERE
-- PID 1 is in Curriculum 1 (the full curriculum)
Referrals.P_Num = 1
AND Referrals.P_Num = Participants.P_Num
AND Participants.CID = Curriculum.CID
AND Curriculum.CID = Curriculum_Subjects.CID
AND Curriculum_Subjects.C_Subject = Class_Subjects.C_Subject
--- Give the class_id for a scheduled class that a participant has not attened
SELECT DISTINCT
Classes_Scheduled.Class_ID
FROM
Classes_Scheduled
WHERE
Classes_Scheduled.Class_ID NOT IN (
-- Gets the id and names of all the classes a specific participant has already taken
SELECT DISTINCT
Classes_Scheduled.Class_ID
FROM
Referrals,
Participants,
Class_Attendence,
Classes_Scheduled,
Curriculum_Subjects,
Class_Subjects
WHERE
-- TESTING WITH P_Num 1
Referrals.P_Num = 1
And Referrals.P_Num = Participants.P_Num
AND Participants.P_Num = Class_Attendence.P_Num
AND Class_Attendence.Class_ID = Classes_Scheduled.Class_ID
AND Classes_Scheduled.C_Subject = Curriculum_Subjects.C_Subject
AND Curriculum_Subjects.C_Subject = Class_Subjects.C_Subject)
ORDER BY Classes_Scheduled.Class_ID
/*
* Dan Martino
* Returns all of the class names of the classes in the "Full" curriculum
*/
SELECT
Class_Subjects.Class_Subject
FROM
Class_Subjects,
Curriculum_Subjects
WHERE
Class_Subjects.C_Subject = Curriculum_Subjects.C_Subject
AND Curriculum_Subjects.CID = 2
/*
* Dan Martino
* Returns all of the class names of the classes in the "Rehab" curriculum
*/
SELECT
Class_Subjects.Class_Subject
FROM
Class_Subjects,
Curriculum_Subjects
WHERE
Class_Subjects.C_Subject = Curriculum_Subjects.C_Subject
AND Curriculum_Subjects.CID = 3
/*
* Dan Martino
* Returns all of the class names of classes that are in
* more than one curriculum
*/
SELECT
Class_Subjects.Class_Subject
FROM
Class_Subjects,
Curriculum_Subjects
WHERE Class_Subjects.C_Subject = Curriculum_Subjects.C_Subject
GROUP BY Class_Subjects.Class_Subject
HAVING Count(*) > 1
/*
* Dan Martino
* Returns all of the class ids, class names, and comments of the classes a specific
* participant has attended.
* PID 1 is used as an example.
*/
SELECT DISTINCT
Class_Subjects.C_Subject,
Class_Subjects.Class_Subject,
Class_Attendence.Participant_Comment
FROM
Referrals,
Participants,
Class_Attendence,
Classes_Scheduled,
Curriculum_Subjects,
Class_Subjects
WHERE
Referrals.P_Num = 1
AND Referrals.P_Num = Participants.P_Num
AND Participants.P_Num = Class_Attendence.P_Num
AND Class_Attendence.Class_ID = Classes_Scheduled.Class_ID
AND Classes_Scheduled.C_Subject = Curriculum_Subjects.C_Subject
AND Curriculum_Subjects.C_Subject = Class_Subjects.C_Subject
ORDER BY Class_Subjects.C_Subject
/*
* Dan Martino
* Returns all of the class ids and class names of the classes a specific
* participant has not attended
* PID 1 is used as an example
*/
SELECT DISTINCT
Class_Subjects.C_Subject,
Class_Subjects.Class_Subject
FROM
Referrals,
Participants,
Curriculum,
Curriculum_Subjects,
Class_Subjects
WHERE
Referrals.P_Num = 1
AND Referrals.P_Num = Participants.P_Num
AND Participants.CID = Curriculum.CID
AND Curriculum.CID = Curriculum_Subjects.CID
AND Curriculum_Subjects.C_Subject = Class_Subjects.C_Subject
AND Curriculum_Subjects.C_Subject NOT IN (
SELECT DISTINCT
Curriculum_Subjects.C_Subject
FROM
Referrals,
Participants,
Class_Attendence,
Classes_Scheduled,
Curriculum_Subjects,
Class_Subjects
WHERE
Referrals.P_Num = 1
AND Referrals.P_Num = Participants.P_Num
AND Participants.P_Num = Class_Attendence.P_Num
AND Class_Attendence.Class_ID = Classes_Scheduled.Class_ID
AND Classes_Scheduled.C_Subject = Curriculum_Subjects.C_Subject
AND Curriculum_Subjects.C_Subject = Class_Subjects.C_Subject)
ORDER BY Class_Subjects.C_Subject
/*
* Dan Martino
* Returns all information about all the classes a specific employee is teaching
* EID 1 is used as an example.
*/
SELECT DISTINCT
Class_Subjects.Class_Subject
FROM
Class_Subjects,
Curriculum_Subjects,
Classes_Scheduled
WHERE
Class_Subjects.C_Subject = Curriculum_Subjects.C_Subject
AND Curriculum_Subjects.C_Subject = Classes_Scheduled.C_Subject
AND Classes_Scheduled.EID = 1