DATABASE - Answer 8-11 Answer 8-11 only Given the following samples of larger ta
ID: 3862968 • Letter: D
Question
DATABASE - Answer 8-11
Answer 8-11 only
Given the following samples of larger tables, give the queries for the problems below STUDENT Name StudentID Class Major CS Brown Class is 1 for Freshman, 2 for Sophomore, 3 for Junior and 4 for Seniors COURSE Credit hours CourselD Name Department CS101 intro. to Computer Science CS191 Discrete Struct I 3 CS282 Assembly Language 4 OBOE 101 OBOA Freshman Oboe SECTION SectionID Course ID Semester Year Faculty ID 2014 OBOE 101 BBishop 781 CS101 541 2014 KBingham 2014 552 CS101 BHare CS101 2014 BHare 448 662 CS191 FS 2014 XLiuExplanation / Answer
/*8. Retrieve the count of students in each department.*/
SELECT Major, count(StudentID) FROM Student GROUP BY Major;
/*9. Retrieve the course name and course ID where CS101 is the prerequisite.*/
SELECT C.Name, C.CourseID FROM Course C, Prerequisite P
WHERE P.CourseID = C.CourseID AND
P.Prerequisite_CourseID == 'CS101';
/*10. Return the name, StudentID, and class of all students that have not taken CS101. */
SELECT S.Name, S.StudentID, S.Major FROM Student S
WHERE S.StudentID NOT IN
(SELECT G.StudentID FROM Section S, Grade G WHERE S.SectionID == G.SectionID
AND S.CourseID == 'CS101');
/*11. Find the average grade value grouped by major, using the major of the student. */
SELECT S.Major, avg(G.Grade_Value) FROM Student S, Grade G
WHERE G.StudentID == S.StudentID GROUP BY S.Major;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.