dbtask1

1)
Single:
CREATE VIEW high_credit_students AS
SELECT
    ID,
    name,
    dept_name,
    tot_cred
FROM
    student
WHERE
    tot_cred > 50;



Multiple:
CREATE VIEW student_course_instructor AS
SELECT
    s.name AS student_name,
    c.title AS course_title,
    i.name AS instructor_name
FROM student s
JOIN takes tk ON s.ID = tk.ID
JOIN course c ON tk.course_id = c.course_id
JOIN teaches te ON tk.course_id = te.course_id
JOIN instructor i ON te.ID = i.ID;

2)
With Condition:
SELECT *
FROM student_course_instructor
WHERE instructor_name = 'Katz';

student_name	course_title	instructor_name
Zhang	Intro. to Computer Science	Katz
Shankar	Intro. to Computer Science	Katz
Levy	Intro. to Computer Science	Katz
Levy	Intro. to Computer Science	Katz
Levy	Image Processing	Katz
Williams	Intro. to Computer Science	Katz
Brown	Intro. to Computer Science	Katz
Brown	Image Processing	Katz
Bourikas	Intro. to Computer Science	Katz


With aggregrate function:
SELECT student_name, COUNT(course_title) AS num_courses
FROM student_course_instructor
GROUP BY student_name;

student_name	num_courses
Aoi	1
Bourikas	3
Brandt	1
Brown	4
Chavez	1
Levy	6
Peltier	1
Sanchez	1
Shankar	6
Tanaka	2
Williams	4
Zhang	3


3)
INSERT INTO student_course_instructor (student_name, course_title, instructor_name)
VALUES ('Michael Scott', 'Management 101', 'David Wallace');
Uncaught Error: cannot modify student_course_instructor because it is a view

UPDATE student_course_instructor
SET instructor_name = 'Jan Levinson'
WHERE course_title = 'Management 101' AND instructor_name = 'David Wallace';
Uncaught Error: cannot modify student_course_instructor because it is a view

DELETE FROM student_course_instructor
WHERE student_name = 'Michael Scott';
Uncaught Error: cannot modify student_course_instructor because it is a view

4)
INSERT INTO student (ID, name) VALUES ('1001', 'Michael Scott');
INSERT INTO course (course_id, title, dept_name, credits) VALUES ('MGT101', 'Management 101', 'Business', 3);
INSERT INTO instructor (ID, name, dept_name, salary) VALUES ('2001', 'David Wallace', 'Business', 50000);
INSERT INTO takes (ID, course_id, sec_id, semester, year) VALUES ('1001', 'MGT101', '01', 'Fall', 2023);
INSERT INTO teaches (ID, course_id, sec_id, semester, year) VALUES ('2001', 'MGT101', '01', 'Fall', 2023);
SELECT * FROM student_course_instructor WHERE student_name = 'Michael Scott';

student_name	course_title	instructor_name
Michael Scott	Management 101	David Wallace

UPDATE instructor SET name = 'Enamul Hassan' WHERE ID = '2001';
SELECT * FROM student_course_instructor WHERE course_title = 'Management 101';

student_name	course_title	instructor_name
Michael Scott	Management 101	Enamul Hassan

DELETE FROM student WHERE name = 'Zhang';
SELECT * FROM student_course_instructor WHERE student_name = 'Zhang';

No rows returned.

So, it can be observed that the views are updated accordingly.