Consider the following database schema. STUDENT(StudentNumber, Name, Class, Majo

Question

Consider the following database schema.

STUDENT(StudentNumber, Name, Class, Major)
COURSE(CourseNumber, CourseName, CreditHours, Department)
SECTION(SectionIdentifier, CourseNumber, Semester, Year, Instructor)
GRADE_REPORT(StudentNumber, SectionIdentifier, Grade)
PREREQUISITE(CourseNumber, PrerequisiteNumber)

Specify the following queries in SQL.

(a) Insert a new student, <'002', 'Eric', 3, 'CS' > in the database. Assume that the value sets of the attributes StudentNumber and Class are {'001', '002', …, '999'} and {1, 2, …, 10}, respectively.

(b) Change the credit hours of the course 'Database Systems' for the 'CS' department to 3.

(c) Delete the record for the student whose name is 'Eric' and whose student number is '002'.

(d) Retrieve the course numbers, names, and credit hours for the courses offered by the 'CS' department.

(e) Retrieve the names of all courses taught by Professor 'John' in years 2014 and 2015.

(f) For each section taught by Professor 'John', retrieve the course number, semester, year, and the number of students who took the section.

Explanation / Answer

(a) insert into student values('002', 'Eric', 3, 'CS');

(b) update course set creditHours = 3 where coursename = 'Database System' and department = 'CS';

(c) delete from student whre name='Eric' and student number = '002';

(d) select coursenumber, coursename, credithours from course where department = 'CS';

(e) select a.coursename from course a, section b where a.coursenumber = b.coursenumber and b.instructor='John' and b.year = 2014 or b.year = 2015;

(f) select b.coursenumber, b.semester, b.year, count(a.studentnumber) from grade_report a, section b where b.instructor='John' and a.sectionidentifier = b.sectionidentifier;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.