Permalink
Cannot retrieve contributors at this time
51 lines (49 sloc)
1.96 KB
CREATE TABLE Modules( | |
MODULE_ID VARCHAR PRIMARY KEY NOT NULL, | |
MODULE_NAME TEXT NOT NULL, | |
Course VARCHAR , | |
FOREIGN KEY (Course) REFERENCES Courses(Course_ID) | |
); | |
CREATE TABLE Classes( | |
ID INTEGER NOT NULL , | |
NAME TEXT NOT NULL , | |
ROOM VARCHAR NOT NULL , | |
Time TEXT NOT NULL, | |
Location TEXT NOT NULL, | |
Course VARCHAR , | |
Lecturer VARCHAR NOT NULL, | |
Student_ID INTEGER NOT NULL, | |
MODULE VARCHAR , | |
FOREIGN KEY (MODULE) REFERENCES Modules(MODULE_ID) | |
); | |
CREATE TABLE Students( | |
STUDENT_ID INTEGER NOT NULL , | |
STUDENT_NAME VARCHAR NOT NULL , | |
Course VARCHAR, | |
FOREIGN KEY (Course) REFERENCES Courses(Course_ID) | |
); | |
CREATE TABLE Courses( | |
Course_ID VARCHAR PRIMARY KEY NOT NULL, | |
Course_Name VARCHAR NOT NULL | |
); | |
INSERT INTO Courses (Course_ID, Course_Name) VALUES ('1765','Computer Science'); | |
INSERT INTO Modules (MODULE_ID, MODULE_NAME, Course) VALUES ('4001CEM','Software Design', '1765'); | |
INSERT INTO Classes (ID, NAME, ROOM, Time, Location, MODULE, Course, Lecturer, Student_ID) VALUES ('15','Mathematics for Computer Science','15','1 O-Clock','West Wing','4001CEM','1765','Ham', '978335'); | |
INSERT INTO Students (STUDENT_ID, STUDENT_NAME, Course) VALUES ('978335','Youssef','Computer Science'); | |
SELECT NAME as Class_Name, | |
Time as Class_Time, | |
Location as Class_Location, | |
MODULE_NAME as M_Name, | |
Lecturer as Class_Lecturer, | |
Course_Name as Course, | |
Classes.Student_ID as Student_ID, | |
S.STUDENT_NAME as STUDENT_NAME | |
FROM Classes | |
INNER JOIN Modules M on M.MODULE_ID = Classes.MODULE | |
INNER JOIN Courses C on C.Course_ID = M.Course | |
INNER JOIN Students S on S.STUDENT_ID = Classes.Student_ID | |
WHERE Classes.Student_ID = '978335' | |
DROP TABLE Modules | |
DROP TABLE Classes | |
DROP TABLE Students | |
DROP TABLE Courses |