1. Specify the following queries in SQL on the database schema of figure1. Retri

Question

1. Specify the following queries in SQL on the database schema of figure1.

Retrieve the name of all senior students majoring in ‘COSC’ (Computer Science).

Retrieve the names of all courses taught by professor King in 1985 and 1986.

For each section taught by Professor King, retrieve the course number, semester, year, and number of students who took the section.      

Retrieve the name and transcript of each senior student (Class=5) majoring in ‘COSC’. A transcript includes course name, course number, credit hours, semester, year, and grade for each course completed by the student.

Retrieve the names and major departments of all A students (students who have a grade of A in all their courses).

Retrieve the names and major departments of all students who do not have a grade of A in any of their courses.

Table: STUDENT

Name       |        StudentNumber          |        Class          |     Major

Table: COURSE

CourseName | CourseNumber   | CreditHours      |     Department | Professor

Table: PREREQUISITE

CourseNumber     |     PrerequisiteNumber

Table: SECTION

SectionIdentifier | CourseNumber   | Semester | Year | Instructor

Table: GRADE_REPORT

StudentNumber     |   SectionNumber     |   Grade

Explanation / Answer

1.

Retrieve the name of all senior students majoring in ‘COSC’ (Computer Science):-

SELECT NAME FROM STUDENT WHERE Major='COSC'

Retrieve the names of all courses taught by professor King in 1985 and 1986:-

SELECT CourseName FROM COURSE
WHERE CourseNumber IN (SELECT CourseNumber FROM SECTION
WHERE Instructor='King' AND (Year='1985' OR Year='1986')

For each section taught by Professor King, retrieve the course number, semester, year, and number of

students who took the section:-

SELECT CourseNumber, Semester, Year, COUNT(*)
FROM SECTION, GRADE_REPORT
WHERE Instructor='King' AND SECTION.SectionIdentifier = GRADE_REPORT.SectionIdentifier
GROUP BY CourseNumber, Semester, Year

Retrieve the name and transcript of each senior student (Class=5) majoring in ‘COSC’. A transcript

includes course name, course number, credit hours, semester, year, and grade for each course

completed by the student:-

SELECT Name, CourseName, C.CourseNumber, CreditHours, Semester, Year, Grade
FROM STUDENT ST, COURSE C, SECTION S, GRADE_REPORT G
WHERE Class=5 AND Major='COSC' AND ST.StudentNumber=G.StudentNumber AND
G.SectionIdentifier=S.SectionIdentifier AND S.CourseNumber=C.CourseNumber

Retrieve the names and major departments of all A students (students who have a grade of A in all

their courses):-

SELECT Name, Major
FROM STUDENT
WHERE NOT EXISTS ( SELECT *
FROM GRADE_REPORT
WHERE StudentNumber= STUDENT.StudentNumber AND NOT(Grade='A'))

Retrieve the names and major departments of all students who do not have a grade of A in any of

their courses:-

SELECT Name, Major
FROM STUDENT
WHERE NOT EXISTS ( SELECT *
FROM GRADE_REPORT
WHERE StudentNumber= STUDENT.StudentNumber AND Grade='A' )

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

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