# Homework 4 (60 points)
## 姓名 陳彥均

### 1. Create the student table including all attributes and constraints.
我這邊把所有的Table先建好.

CREATE TABLE student (
    student_id varchar(20) PRIMARY KEY,
    student_name varchar(20),
    department varchar(20),    
    `year` int
);

CREATE TABLE instructor (
    employee_id int PRIMARY KEY,
    employee_name varchar(20),
    `rank` varchar(20),
    department varchar(20),
    office varchar(10)
);

CREATE TABLE course (
    course_id varchar(10) PRIMARY KEY,
    course_name varchar(20),
    credit_hour int
);

CREATE TABLE section (
    course_id varchar(10),
    section_id varchar(2),
    semester_year varchar(10),
    employee_id INT,
    PRIMARY KEY (course_id, section_id, semester_year),
    FOREIGN KEY (course_id) REFERENCES course(course_id),
    FOREIGN KEY (employee_id) REFERENCES instructor(employee_id)
);

CREATE TABLE enroll(
	student_id varchar(20),
    course_id varchar(10),
    section_id varchar(2),
    semester_year varchar(10),
    score INT,
    primary key(student_id, course_id, section_id, semester_year),
    FOREIGN KEY(student_id) references student(student_id),
    FOREIGN KEY(course_id, section_id, semester_year) references section(course_id, section_id, semester_year)
);

### 2. Insert a student whose student ID is "b10701234", whose name is "Ariana Grande", whose major is "music", and whose year is 2.

INSERT INTO student VALUES('b10701234', 'Ariana Grande', 'music', 2);

### 3. Change the office of instructor "Daenerys Targaryen" from "E320" to "E220".

INSERT INTO instructor VALUES('100000', 'Daenerys Targaryen', 'Professor', 'Computer Science', 'E320');

UPDATE instructor
SET office = 'E220'
WHERE employee_id = 100000;

### 4. List names of all instructors in the department of "Computer Science".

select * FROM instructor WHERE department = 'Computer Science';

### 5.Count total number of students majoring in "Computer Science".

INSERT INTO student VALUES
('b10703131', 'bebe', 'Computer Science', 2),
('b10702222', 'nene', 'Computer Science', 1);

select count(*) FROM student WHERE department = 'Computer Science';

### 6. List the number of instructors in each department.

select department, COUNT(*) FROM instructor group by department;

### 7. List employee IDs and names of all teachers who are offering "Database System"

INSERT INTO instructor VALUES
('100004', 'Leo', 'Professor', 'Database System', 'E110'),
('100003', 'Mao', 'Professor', 'Database System', 'E120');

select employee_id, employee_name FROM instructor WHERE department = 'Database System';

### 8. Display the class roster, including student ID and student name, for all students enrolled in course ID "B02204" and section ID "A" in Spring 2020.

SELECT s.student_id , s.student_name
FROM student s
JOIN enroll e ON s.student_id = e.student_id
JOIN section sec ON e.course_id = sec.course_id AND e.section_id = sec.section_id
WHERE e.course_id = 'B02204'
    AND e.section_id = 'A'
    AND sec.semester_year = 'Spring 2020';

### 9. Remove all courses offered by the instructor "Justin Bieber".

delete from course WHERE course_id IN (
    SELECT s.course_id 
    FROM section s
    JOIN instructor i ON s.employee_id = i.employee_id
    WHERE i.employee_name = 'Justin Bieber'
);

### 10. Calculate the total credit hours taken by "Taylor Swift".

SELECT SUM(c.credit_hour)
FROM enroll e
JOIN course c ON e.course_id = c.course_id
JOIN student s ON e.student_id = s.student_id
WHERE s.student_name = 'Taylor Swift';

### 11. Find student IDs and names of all students who were not enrolled in any course during semester "Spring 2020".

SELECT student_id, student_name
FROM student
WHERE student_id NOT IN (
    SELECT student_id
    FROM enroll
    WHERE semester_year = 'Spring 2020'
);

### 12. Create a view which contains the instructor names and courses that they offer.

CREATE VIEW instructor_coview AS
SELECT i.employee_name, c.course_name
FROM instructor i
JOIN section s ON i.employee_id = s.employee_id
JOIN course c ON s.course_id = c.course_id;

SELECT * FROM instructor_coview;

### 13.Add an attribute email into the student table.

ALTER TABLE student
ADD COLUMN email varchar(50);

### 14.Remove the attribute score from the enroll table.

ALTER TABLE enroll
DROP COLUMN score;

### 15. Specify (course_id, section_id, semeser_year) as the primary key of the section table.

ALTER TABLE section
DROP PRIMARY KEY,
ADD PRIMARY KEY (course_id, section_id, semester_year);

### 16. Specify student_id of the enroll table as a foreign key references to student_id of the student table.

ALTER TABLE enroll
ADD FOREIGN KEY (student_id) REFERENCES student(student_id);

### 17.Remove the enroll table.

DROP TABLE enroll;

### 18. Specify a constraint 0 < score < 100 in the enroll table.

ALTER TABLE enroll
ADD CHECK (score > 0 AND score < 100);

### 19. Specify course_id as an index in the course table.

ALTER TABLE course
ADD INDEX idx_course_id (course_id);

### 20. Delete all sections taught by "Ariana Grande".

DELETE FROM section
WHERE employee_id IN (
    SELECT employee_id
    FROM instructor
    WHERE employee_name = 'Ariana Grande'
);