-
Notifications
You must be signed in to change notification settings - Fork 0
/
views.sql
142 lines (114 loc) · 3.72 KB
/
views.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
--12/04/16--
--Frank Mitarotonda--
--Team:108--
--Views--
-- Give common participant information such as name, cell phone, age, race, DOB, and status
CREATE VIEW participantinfo AS
SELECT Ref_F_Name, Ref_L_Name, AGE, Ref_Cell_Phone, DOB, Sex, Race, Status
FROM referrals, participants
WHERE referrals.P_num = participants.P_num;
-- Gets the names of all currently scheduled curriculums and the person who is teaching them
CREATE VIEW curriculuminfo AS
SELECT Distinct Curriculum_Name, E_F_NAME, E_L_NAME, Location_Name, TO_CHAR(Date_Time_Schedules, 'DD-MM-YYYY HH:MI:SS AM') "Date 12Hr"
FROM curriculum, classes_scheduled, employees, locations, curriculum_Subjects
WHERE curriculum.cid = curriculum_subjects.cid
and curriculum_subjects.cid = classes_scheduled.cid
and locations.location_id = classes_scheduled.location_id
and employees.eid = classes_scheduled.eid;
--Marengo
--All info from intake packet. This one was really hard
--SELECT *
--FROM participant_intake
--Name and curriculum stuff
--SELECT Ref_F_Name, Ref_L_Name, Curriculum_Name
--FROM curriculum, classes_scheduled, curriculum_Subjects
--WHERE curriculum.cid = curriculum_subjects.cid
--IS THE VM BEING EXCEPTIONALLY SLOW FOR ANYONE ELSE?
--*
--* Dan Martino
--* Returns all of the class names of the classes in the "Full" curriculum
--*/
CREATE VIEW full_curriculum_classes AS
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
*/
CREATE VIEW rehab_curriculum_classes AS
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 ids, class names, and comments of the classes a specific
* participant has attended.
* PID 1 is used as an example.
*/
CREATE VIEW participant_attendence AS
SELECT DISTINCT
Class_Subjects.C_Subject,
Class_Subjects.Class_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 of the class ids and class names of the classes a specific
* participant has not attended
* PID 1 is used as an example
*/
CREATE VIEW participant_remaining_classes AS
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);