# CSIE4105 Database Systems Homework #3
##### 108598079 江宛芸
1.
```sql=
-- (a)
SET SQL_SAFE_UPDATES=0;
UPDATE school.course SET CreditHour=3 WHERE CourseName='Database Systems' AND Department = 'EECS';
-- (b)
DELETE FROM grade_report WHERE StudentNumber='1';
DELETE FROM grade_report WHERE Name='Edward';
DELETE FROM student WHERE Name='Edward' OR StudentNumber='1';
-- (c)
SELECT course.CourseName FROM course
INNER JOIN section
ON course.CourseNumber = section.CourseNumber
WHERE section.Instructor = 'Liu' AND '2020'<= section.Year <='2021';
-- (d)
SELECT course.CourseNumber, section.Semester, section.Year, COUNT(student.StudentNumber)
FROM (student, course
INNER JOIN section
ON course.CourseNumber = section.CourseNumber)
INNER JOIN grade_report
ON student.StudentNumber = grade_report.StudentNumber
WHERE section.Instructor = 'Liu';
--(e)有點問題
SELECT PREREQUISITE.PrerequisiteCourseNumber, course.CourseNumber, course.CourseName
FROM PREREQUISITE
INNER JOIN course
ON PREREQUISITE.CourseNumber = course.CourseNumber
WHERE CourseName='Web Programming'
--(f)
SELECT student.Name, course.CourseNumber, course.CourseName, section.Semester, course.CreditHour, section.Year, grade_report.Grade FROM (STUDENT, COURSE
INNER JOIN SECTION
ON COURSE.CourseNumber = SECTION.CourseNumber)
INNER JOIN GRADE_REPORT
ON STUDENT.StudentNumber = GRADE_REPORT.StudentNumber
WHERE Class='3' AND Major='EECS';
--(g)
SELECT student.name FROM (student, course
INNER JOIN section
ON COURSE.CourseNumber = section.CourseNumber)
INNER JOIN grade_report
ON student.StudentNumber = grade_report.StudentNumber
WHERE grade>=80;
--(h)
SELECT student.name, student.major FROM (student, course
INNER JOIN section
ON course.CourseNumber = section.CourseNumber)
INNER JOIN grade_report
ON student.StudentNumber = grade_report.StudentNumber
WHERE grade >= '60';
--(i)
SELECT student.name, student.major FROM (student, course
INNER JOIN section
ON course.CourseNumber = section.CourseNumber)
INNER JOIN grade_report
ON student.StudentNumber = grade_report.StudentNumber
GROUP BY student.StudentNumber HAVING AVG(GRADE_REPORT.Grade) > 80;
--(j)
SELECT student.name, AVG(grade_report.Grade) FROM (student, course
INNER JOIN section
ON course.CourseNumber = section.CourseNumber)
INNER JOIN grade_report
ON student.StudentNumber = grade_report.StudentNumber
WHERE section.Year = '2021'
GROUP BY student.StudentNumber HAVING AVG(grade_report.Grade) > 80;
--(k)
SELECT student.major, COUNT(student.StudentNumber) FROM (student, course
INNER JOIN section
ON course.CourseNumber = section.CourseNumber)
INNER JOIN grade_report
ON student.StudentNumber = grade_report.StudentNumber
GROUP BY student.StudentNumber HAVING AVG(grade_report.Grade) < 60;
-- (l)
CREATE VIEW view_1 AS
SELECT student.Name, student.StudentNumber,
course.CourseName, section.Semester,
section.Year, grade_report.Grade FROM
(student, course INNER JOIN section
ON course.CourseNumber = section.CourseNumber)
INNER JOIN grade_report
ON student.StudentNumber = grade_report.StudentNumber
```
```sql=
-- (a)
CREATE TABLE STUDENT (
StudentNumber INT NOT NULL PRIMARY KEY,
Name VARCHAR(45) NULL,
Class INT NULL,
Major VARCHAR(45) NULL
);
CREATE TABLE COURSE (
CourseNumber INT NOT NULL PRIMARY KEY,
CourseName VARCHAR(45) NULL,
CreditHour INT NULL,
Department VARCHAR(45) NULL
);
CREATE TABLE SECTION (
SectionNumber INT NOT NULL PRIMARY KEY,
CourseNumber INT NULL,
Semester INT NULL,
Year INT NULL,
Instructor VARCHAR(45) NULL,
FOREIGN KEY (CourseNumber) REFERENCES COURSE(CourseNumber)
);
CREATE TABLE GRADE_REPORT (
StudentNumber INT NOT NULL PRIMARY KEY,
SectionNumber INT NOT NULL,
Grade INT NULL,
FOREIGN KEY (StudentNumber) REFERENCES STUDENT(StudentNumber),
FOREIGN KEY (SectionNumber) REFERENCES SECTION(SectionNumber)
);
CREATE TABLE PREREQUISITE (
CourseNumber INT NOT NULL PRIMARY KEY,
PrerequisiteCourseNumber INT NOT NULL,
FOREIGN KEY (CourseNumber) REFERENCES COURSE(CourseNumber),
FOREIGN KEY (PrerequisiteCourseNumber) REFERENCES COURSE(CourseNumber)
);
```
```sql=
INSERT INTO STUDENT
VALUES
(008,'Tiana','1','EECS'),
(009,'Tom','2','CSIE'),
(010,'Lia','1','CSIE'),
(011,'Nixon','3','EECS')
INSERT INTO COURSE
VALUES
(990015,'Programming A','3','CSIE'),
(990016,'Programming B','3','CSIE'),
(990017,'Programming D','3','CSIE'),
(990018,'Programming E','3','EECS')
INSERT INTO SECTION
VALUES
(000005,'990015','2','2020','JYKUO'),
(000006,'990016','2','2021','Leo'),
(000007,'990017','2','2020','Liu')
INSERT INTO GRADE_REPORT
VALUES
(008,'000005','99'),
(009,'000006','77'),
(010,'000007','66'),
(008,'000006','88'),
(010,'000006','66')
INSERT INTO PREREQUISITE
VALUES
('990015','5901202'),
('990017','5901202'),
('990018','5901202'),
('5902108','5901202'),
('5902206','5902108')
```
```sql=
DELIMITER $$
CREATE PROCEDURE `getAverageGrade`(studentNumber int)
BEGIN
SELECT student.StudentNumber, student.name, AVG(grade_report.Grade) FROM student
LEFT JOIN grade_report
ON student.StudentNumber = grade_report.StudentNumber
WHERE student.StudentNumber = studentNumber;
END$$
DELIMITER ;
call school.getAverageGrade(8);
```
```sql=
('5902108','5901202'),
('5902206','5902108')
```
{"metaMigratedAt":"2023-06-16T16:18:14.978Z","metaMigratedFrom":"Content","title":"CSIE4105 Database Systems Homework #3","breaks":true,"contributors":"[{\"id\":\"bdef9846-a16b-4416-8270-6b0be2970238\",\"add\":5619,\"del\":416}]"}