A6: Prerequisite for A7

CREATE TABLE itu_courses (
  course_id VARCHAR(10) PRIMARY KEY,
  course_name VARCHAR(100) NOT NULL
);

INSERT INTO itu_courses (course_id, course_name) 
VALUES ('DSDS', 'Introduction to Scripting, Databases, and System Architecture');

INSERT INTO itu_courses (course_id, course_name) 
VALUES ('GSD', 'Global Software Development');

INSERT INTO itu_courses (course_id, course_name) 
VALUES ('IMBW', 'Introduction to Malaysian Basket Weaving');

INSERT INTO itu_courses (course_id, course_name) 
VALUES ('IWJX', 'Interactive Web Services using Java and XML');

INSERT INTO itu_courses (course_id, course_name) 
VALUES ('UDCS', 'Understanding Digital Culture and Society');

INSERT INTO itu_courses (course_id, course_name) 
VALUES ('IUSP', 'Introduction to American Politics');

SELECT * FROM itu_courses;

+-----------+---------------------------------------------------------------+
| course_id | course_name                                                   |
+-----------+---------------------------------------------------------------+
| DSDS      | Introduction to Scripting, Databases, and System Architecture | 
| GSD       | Global Software Development                                   | 
| IMBW      | Introduction to Malaysian Basket Weaving                      | 
| IWJX      | Interactive Web Services using Java and XML                   | 
| UDCS      | Understanding Digital Culture and Society                     | 
| IUSP      | Introduction to American Politics                             | 
+-----------+---------------------------------------------------------------+



CREATE TABLE itu_persons (
  person_id INT PRIMARY KEY,
  email VARCHAR(100) NOT NULL,
  name VARCHAR(100) NOT NULL,
  role VARCHAR(20) NOT NULL
);

INSERT INTO itu_persons (person_id, email, name, role) 
VALUES (1, 'brabrand@itu.dk', 'Claus', 'teacher');

INSERT INTO itu_persons (person_id, email, name, role) 
VALUES (2, 'obama@gmail.com', 'Barack', 'teacher');

INSERT INTO itu_persons (person_id, email, name, role) 
VALUES (3, 'john_doe@notmail.com', 'John', 'teacher');

INSERT INTO itu_persons (person_id, email, name, role) 
VALUES (4, 'jane_doe@notmail.com', 'Jane', 'student');

INSERT INTO itu_persons (person_id, email, name, role) 
VALUES (5, 'anna@hotmail.com', 'Anna', 'student');

INSERT INTO itu_persons (person_id, email, name, role) 
VALUES (6, 'brian@hotmail.com', 'Brian', 'student');

INSERT INTO itu_persons (person_id, email, name, role) 
VALUES (7, 'claire@hotmail.com', 'Claire', 'student');

SELECT * FROM itu_persons;
+-----------+----------------------+--------+---------+
| person_id | email                | name   | role    |
+-----------+----------------------+--------+---------+
|         1 | brabrand@itu.dk      | Claus  | teacher | 
|         2 | obama@gmail.com      | Barack | teacher | 
|         3 | john_doe@notmail.com | John   | teacher | 
|         4 | jane_doe@notmail.com | Jane   | student | 
|         5 | anna@hotmail.com     | Anna   | student | 
|         6 | brian@hotmail.com    | Brian  | student | 
|         7 | claire@hotmail.com   | Claire | student | 
+-----------+----------------------+--------+---------+



CREATE TABLE itu_course_responsible (
  course_id VARCHAR(10) NOT NULL,
  person_id INT NOT NULL,
  term VARCHAR(5) NOT NULL
);

INSERT INTO itu_course_responsible (course_id, person_id, term) 
VALUES ('DSDS', '1', 'E2012');

INSERT INTO itu_course_responsible (course_id, person_id, term) 
VALUES ('GSD', '1', 'F2011');

INSERT INTO itu_course_responsible (course_id, person_id, term) 
VALUES ('IWJX', '1', 'F2011');

INSERT INTO itu_course_responsible (course_id, person_id, term)  
VALUES ('IUSP', '2', 'E2012');

INSERT INTO itu_course_responsible (course_id, person_id, term) 
VALUES ('IMBW', '3', 'E2012');

SELECT * FROM itu_course_responsible;
+-----------+-----------+-------+
| course_id | person_id | term  |
+-----------+-----------+-------+
| DSDS      |         1 | E2012 | 
| GSD       |         1 | F2011 | 
| IWJX      |         1 | F2011 | 
| IUSP      |         2 | E2012 | 
| IMBW      |         3 | E2012 | 
+-----------+-----------+-------+



SELECT itu_courses.course_id, itu_courses.course_name, itu_persons.name AS itu_course_responsible
FROM itu_courses, itu_persons, itu_course_responsible
WHERE (itu_courses.course_id = itu_course_responsible.course_id
            AND itu_persons.person_id = itu_course_responsible.person_id
            AND term = 'E2012');
+-----------+---------------------------------------------------------------+------------------------+
| course_id | course_name                                                   | itu_course_responsible |
+-----------+---------------------------------------------------------------+------------------------+
| DSDS      | Introduction to Scripting, Databases, and System Architecture | Claus                  | 
| IUSP      | Introduction to American Politics                             | Barack                 | 
| IMBW      | Introduction to Malaysian Basket Weaving                      | John                   | 
+-----------+---------------------------------------------------------------+------------------------+



CREATE TABLE itu_course_enrollment (
  course_id VARCHAR(10) NOT NULL,
  person_id INT NOT NULL,
  term VARCHAR(5) NOT NULL
);

INSERT INTO itu_course_enrollment (course_id, person_id, term) 
VALUES ('DSDS', '4', 'E2012');

INSERT INTO itu_course_enrollment (course_id, person_id, term) 
VALUES ('IUSP', '4', 'E2012');

INSERT INTO itu_course_enrollment (course_id, person_id, term) 
VALUES ('GSD', '4', 'F2011');

INSERT INTO itu_course_enrollment (course_id, person_id, term)  
VALUES ('DSDS', '5', 'E2012');
 
INSERT INTO itu_course_enrollment (course_id, person_id, term)  
VALUES ('IMBW', '5', 'E2012');

INSERT INTO itu_course_enrollment (course_id, person_id, term)  
VALUES ('GSD', '5', 'F2011');

INSERT INTO itu_course_enrollment (course_id, person_id, term) 
VALUES ('DSDS', '6', 'E2012');

INSERT INTO itu_course_enrollment (course_id, person_id, term)  
VALUES ('GSD', '6', 'F2011');
 
INSERT INTO itu_course_enrollment (course_id, person_id, term)  
VALUES ('IUSP', '6', 'E2012');

INSERT INTO itu_course_enrollment (course_id, person_id, term) 
VALUES ('IWJX', '6', 'F2011');

INSERT INTO itu_course_enrollment (course_id, person_id, term) 
VALUES ('DSDS', '7', 'E2012');