-
Notifications
You must be signed in to change notification settings - Fork 0
/
sql.py
198 lines (181 loc) · 3.89 KB
/
sql.py
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
### SQL CREATE STATEMENTS ###
CREATE_STUDENT = """
CREATE TABLE IF NOT EXISTS Student (
ID INTEGER PRIMARY KEY,
Name TEXT NOT NULL,
Age INTEGER NOT NULL,
YearEnrolled INTEGER NOT NULL,
GraduatingYear INTEGER NOT NULL,
StudentClass INTEGER,
FOREIGN KEY(StudentClass) REFERENCES Class(ID)
);
"""
CREATE_CLASS = """
CREATE TABLE IF NOT EXISTS Class (
ID INTEGER PRIMARY KEY,
Name TEXT NOT NULL,
ClassLevel TEXT NOT NULL CHECK (
ClassLevel IN ('JC1', 'JC2')
)
);
"""
CREATE_SUBJECT = """
CREATE TABLE IF NOT EXISTS Subject (
ID TEXT PRIMARY KEY,
Name TEXT NOT NULL CHECK (
Name IN (
'GP', 'MATH', 'FM', 'COMP', 'PHY', 'CHEM', 'ECONS', 'BIO',
'GEO', 'HIST', 'ELIT', 'ART', 'CLTRANS', 'CL', 'ML', 'TL',
'CLL', 'CLB', 'PW', 'PUNJABI', 'HINDI', 'BENGALESE', 'JAPANESE'
)
),
SubjectLevel TEXT NOT NULL CHECK (
SubjectLevel IN ('H1','H2','H3')
)
);
"""
CREATE_CLUB = """
CREATE TABLE IF NOT EXISTS Club (
ID INTEGER PRIMARY KEY,
Name TEXT NOT NULL
);
"""
# start date has a certain format
# end date is optional
CREATE_ACTIVITY = """
CREATE TABLE IF NOT EXISTS Activity (
ID INTEGER PRIMARY KEY,
Name TEXT NOT NULL,
StartDate TEXT NOT NULL,
EndDate TEXT,
Description TEXT
);
"""
### RELATIONAL TABLES ###
CREATE_STUDENTCLUB = """
CREATE TABLE IF NOT EXISTS StudentClub (
StudentID INTEGER,
ClubID INTEGER,
Role TEXT DEFAULT 'MEMBER',
PRIMARY KEY (StudentID, ClubID)
FOREIGN KEY (StudentID) REFERENCES Student(ID),
FOREIGN KEY (ClubID) REFERENCES Club(ID)
);
"""
CREATE_STUDENTSUBJECT = """
CREATE TABLE IF NOT EXISTS StudentSubject (
StudentID INTEGER,
SubjectID TEXT,
PRIMARY KEY (StudentID, SubjectID)
FOREIGN KEY (StudentID) REFERENCES Student(ID),
FOREIGN KEY (SubjectID) REFERENCES Subject(ID)
);
"""
CREATE_STUDENTACTIVITY = """
CREATE TABLE IF NOT EXISTS StudentActivity (
StudentID INTEGER,
ActivityID INTEGER,
PRIMARY KEY (StudentID, ActivityID)
FOREIGN KEY (StudentID) REFERENCES Student(ID),
FOREIGN KEY (ActivityID) REFERENCES Activity(ID)
);
"""
### SQL INSERT STATEMENTS ###
# Insert for Student, Class, Club, Subject, Activity
INSERT_STUDENT = """
INSERT INTO Student (
Name,
Age,
YearEnrolled,
GraduatingYear,
StudentClass
) VALUES (
:Name,
:Age,
:YearEnrolled,
:GraduatingYear,
:StudentClass
);
"""
INSERT_CLASS = """
INSERT INTO Class (
Name,
ClassLevel
) VALUES (
:Name,
:ClassLevel
);
"""
INSERT_CLUB = """
INSERT INTO Club (
Name
) VALUES (
:Name
)
"""
INSERT_ACTIVITY = """
INSERT INTO Activity (
Name,
StartDate,
EndDate,
Description
) VALUES (
:Name,
:StartDate,
:EndDate,
:Description
);
"""
INSERT_SUBJECT = """
INSERT INTO Subject (
ID,
Name,
SubjectLevel
) VALUES (
:ID,
:Name,
:SubjectLevel
);
"""
INSERT_MEMBER = """
INSERT INTO StudentClub (
StudentID,
ClubID
) VALUES (
:StudentID,
:ClubID
)
"""
#others
STUDENT_NOT_IN_CLUB = """
SELECT Student.ID, Student.Name, Club.Name
FROM Student
LEFT JOIN StudentClub
ON Student.ID = StudentClub.StudentID
LEFT JOIN Club
ON Club.ID = StudentClub.ClubID
WHERE Student.ID IN (
SELECT ID
FROM Student
WHERE ID NOT IN (
SELECT StudentID
FROM StudentClub
WHERE ClubID = ?
)
);
"""
STUDENTROLE_IN_CLUB = """
SELECT Student.Name, StudentClub.Role
FROM Student
JOIN StudentClub
ON Student.ID = StudentClub.StudentID
JOIN Club
ON Club.ID = StudentClub.ClubID
WHERE ClubID = ?;
"""
#joins
INNERJOIN_STUDENT_CLUB = """
SELECT Student.Name, Class.Name
FROM Student INNER JOIN Class
ON StudentClass = Class.ID
"""