資料結構問題請求解決

1. (60%) Consider the following database schema.
   STUDENT(StudentNumber, Name, Class, Major) COURSE(CourseNumber, CourseName, CreditHour, Department) SECTION(SectionNumber, CourseNumber, Semester, Year, Instructor) GRADE_REPORT(StudentNumber, SectionNumber, Grade) PREREQUISITE(CourseNumber, PrerequisiteCourseNumber)
   Specify the following queries in SQL.

(a) (5%) Change the credit hours of the course 'Database Systems' for the ' EECS'
department to 3.
(b) (5%) Delete the record for the student whose name is 'Edward' and whose
student number is '001'.
(c) (5%) Retrieve the names of all courses taught by Professor 'Liu' in years 2020
and 2021.
(d) (5%) For each section taught by Professor 'Liu', retrieve the course number,
semester, year, and the number of students who took the section.
(e) (5%) Retrieve the prerequisite course number and name for the course 'Web
Programming' offered by the 'EECS' department.
(f) (5%) Retrieve the name and transcript of each junior student (Class=3)
majoring in 'EECS'. A transcript includes course number, course name, credit
hours, semester, year, and grade for each course completed by the student.
(g) (5%) Retrieve the names of students who have a grade greater than or equal to
80 in all of their courses.
(h) (5%) Retrieve the names and major departments of all students who do not
have a grade below 60 in any of their courses.
(i) (5%) Retrieve the names and majors of all students who have a grade below
60 in any of their courses, order alphabetically by the student number.
(j) (5%) Retrieve the names and their average grades for the students who have
an average grade greater than 80.0 in year 2021.
(k) (5%) For each department (i.e., student major), list the number of students
whose average grade is below 60.0.
(l) (5%) Create a view that has student ID, student name, the names of the courses
took by the student, the semester and the year that the courses offered, and the grade received by the student for the courses.
2. (40%) Choose a DBMS (e.g., SQL Server, Oracle or MySQL) to create and populate the tables in problem 1.
(a) (10%) Show your SQL statements and the execution results (screen snapshots)
for creating the tables. The SQL statements need to specify the key and
referential constraints (i.e., primary key and foreign key) for each table.
(b) (10%) Populate the tables so that each table contains at least 3 tuples. Show your SQL insertion statements and the execution results (screen snapshots)
for populating the tables.
(c) (10%) Perform the SQL queries in problem 1 in your DBMS. Show your

SQL statements and the execution results (screen snapshots). Note that your
SQL query results MUST NOT contain no tuples.
(d) (10%) Based on the DB in problem 1, write a stored procedure (can be functions or procedures) that reads student’s number and prints the average grade of the student. Show the source code and the execution results (screen snapshots) of your stored procedure.

太费时，勉强做几道

(a) (5%) Change the credit hours of the course 'Database Systems' for the ' EECS'
department to 3.
update COURSE set CreditHour = 3 where CourseName ='Database Systems' and Department = 'EECS';

(b) (5%) Delete the record for the student whose name is 'Edward' and whose
student number is '001'.
delete from STUDENT where Name = 'Edward' and StudentNumber = '001';

(c) (5%) Retrieve the names of all courses taught by Professor 'Liu' in years 2020
and 2021.
select CourseName from COURSE where CourseNumber in (
select CourseNumber from SECTION where Instructor = 'Liu'  and Year in (2020,2021)) ;


(d) (5%) For each section taught by Professor 'Liu', retrieve the course number,
semester, year, and the number of students who took the section.

select CourseNumber,Semester, Year,stunum  from (
select CourseNumber,Semester, Year,SectionNumber from SECTION where Instructor = 'Liu' ) SEC , 
(select SectionNumber , count(1) as stunum from GRADE_REPORT ) GRD 
where SEC.SectionNumber = GRD.SectionNumber;

(e) (5%) Retrieve the prerequisite course number and name for the course 'Web
Programming' offered by the 'EECS' department.
select PrerequisiteCourseNumber,CourseName from PREREQUISITE (
select CourseNumber,CourseName from  COURSE   where CourseName ='Web Programming' and Department = 'EECS') CUS
where PREREQUISITE.CourseNumber = CUS.CourseNumber;

(f) (5%) Retrieve the name and transcript of each junior student (Class=3)
majoring in 'EECS'. A transcript includes course number, course name, credit
hours, semester, year, and grade for each course completed by the student.

select name,CourseNumber, Semester, Year, Grade from (
select name , SectionNumber, Grade from GRADE_REPORT, (
select Name, StudentNumber from STUDENT where Class = 3 and Major = 'EECS') STU WHERE GRADE_REPORT.StudentNumber = STU.StudentNumber
) STD_GRD , SECTION WHERE STD_GRD.SectionNumber = SECTION.SectionNumber;


(g) (5%) Retrieve the names of students who have a grade greater than or equal to
80 in all of their courses.

select Name from  STUDENT where StudentNumber in ( 
select DISTINCT StudentNumber from GRADE_REPORT where StudentNumber not in 
(select StudentNumber from GRADE_REPORT  where Grade<80) )
;


(h) (5%) Retrieve the names and major departments of all students who do not
have a grade below 60 in any of their courses.
select Name,Major from  STUDENT where StudentNumber in ( 
select DISTINCT StudentNumber from GRADE_REPORT where StudentNumber not in 
(select StudentNumber from GRADE_REPORT  where Grade<60) );

(i) (5%) Retrieve the names and majors of all students who have a grade below
60 in any of their courses, order alphabetically by the student number.

select Name,Major from  STUDENT where StudentNumber in ( 
select DISTINCT StudentNumber from GRADE_REPORT where StudentNumber  in 
(select StudentNumber from GRADE_REPORT  where Grade<60) ) order by StudentNumber;

(j) (5%) Retrieve the names and their average grades for the students who have
an average grade greater than 80.0 in year 2021.

select Name,avg_grade from  STUDENT , (
select StudentNumber , avg(Grade) as avg_grade from (
SELECT StudentNumber, Grade  from GRADE_REPORT where SectionNumber in 
( select SectionNumber from SECTION where Instructor = 'Liu'  and Year = 2021 ) 
) group by StudentNumber HAVING avg(Grade) >80 ) GRD_AVG where STUDENT.StudentNumber =GRD_AVG.StudentNumber ;


(k) (5%) For each department (i.e., student major), list the number of students
whose average grade is below 60.0.

SELECT Major,COUNT(1) AS Below60Number FROM (
select Name,avg_grade,Major from  STUDENT , (
select StudentNumber , avg(Grade) as avg_grade from (
SELECT StudentNumber, Grade  from GRADE_REPORT where SectionNumber in 
( select SectionNumber from SECTION where Instructor = 'Liu'  and Year = 2021 ) 
) group by StudentNumber HAVING avg(Grade) <60 ) GRD_AVG where STUDENT.StudentNumber =GRD_AVG.StudentNumber)
GROUP BY  Major;

(l) (5%) Create a view that has student ID, student name, the names of the courses
took by the student, the semester and the year that the courses offered, and the grade received by the student for the courses.

create view stu_cours
as 
select stu_sec_cour.*, GRADE_REPORT.Grade from (
select StudentNumber, Name , COURSE.CourseName, SectionNumber,  Semester, Year 
from (
select STUDENT.StudentNumber, STUDENT.Name 
SECTION.SectionNumber,SECTION.CourseNumber,SECTION.Semester,SECTION.Year
  from STUDENT,SECTION  where STUDENT.StudentNumber=SECTION.StudentNumber
  ) stu_sec , COURSE where stu_sec.CourseNumber = COURSE.CourseNumber
) stu_sec_cour , GRADE_REPORT where 
stu_sec_cour.SectionNumber  = GRADE_REPORT.SectionNumber and stu_sec_cour.SectionNumber  = GRADE_REPORT.SectionNumber 
;

which db? mqsql?

2. (40%) Choose a DBMS (e.g., SQL Server, Oracle or MySQL) to create and populate the tables in problem 1.
   (a) (10%) Show your SQL statements and the execution results (screen snapshots)
   for creating the tables. The SQL statements need to specify the key and
   referential constraints (i.e., primary key and foreign key) for each table.

(b) (10%) Populate the tables so that each table contains at least 3 tuples. Show your SQL insertion statements and the execution results (screen snapshots)
for populating the tables.

(c) (10%) Perform the SQL queries in problem 1 in your DBMS. Show your

SQL statements and the execution results (screen snapshots). Note that your
SQL query results MUST NOT contain no tuples.

D 解答 , 可以结贴了
(d) (10%) Based on the DB in problem 1, write a stored procedure (can be functions or procedures) that reads student’s number and prints the average grade of the student. Show the source code and the execution results (screen snapshots) of your stored procedure.