The university database has the following schema: STUDENT(NAME, STUDENT_NUM, CLA

Question

The university database has the following schema:

STUDENT(NAME, STUDENT_NUM, CLASS, MAJOR)

COURSE (COURSE_NAME, COURSE_NUM, CREDITS, DEPARTMENT)

SECTION (SECTION_ID, COURSE_NUM, SEMESTER, YEAR, INSTRUCTOR)

GRADEREPORT(STUDENT_NUM, SECTION_ID, GRADE)

Write each query in SQL.

A. Retrieve the names of all courses offered by CIS department in 2014.

b) Retrieve a summary list that shows students' names along with accumulated credits for all CIS majors.

c) Retrieve the semester, year and names of students who take CIS 3400 and CIS 3100 at the same time.

Explanation / Answer

--a) Retrieve the names of all courses offered by CIS department in 2014.
SELECT DISTINCT c.course_name
FROM course c, section s
WHERE c.couse_num = s.course_num
AND UPPER(c.department) = 'CIS'
AND s.year = 2014;

--b) Retrieve a summary list that shows students' names along with accumulated credits for all CIS majors.
SELECT s.name, SUM(c.credits) total_credits
FROM student s, gradereprt r, section se, course c
WHERE UPPER(s.major) = 'CIS'
AND s.student_num = r.student_num
AND r.section_id = se.section_id
AND se.course_num = c.course_num
GROUP BY s.name;

--c) Retrieve the semester, year and names of students who take CIS 3400 and CIS 3100 at the same time.
SELECT a.*
FROM (SELECT s.name, se.semester, se.year
   FROM student s, gradereprt r, section se
   WHERE s.student_num = r.student_num
   AND r.section_id = se.section_id
   AND UPPER(se.section_id) = 'CIS 3400') a,
(SELECT s.name, se.semester, se.year
   FROM student s, gradereprt r, section se
   WHERE s.student_num = r.student_num
   AND r.section_id = se.section_id
   AND UPPER(se.section_id) = 'CIS 3100') b
WHERE a.name = b.name
AND a.semester = b.semester
AND a.year = b.year;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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