Simple SQL code here Using Access and the School database, code the SQL for the
ID: 3679027 • Letter: S
Question
Simple SQL code here
Using Access and the School database, code the SQL for the following queries. Each problem must be answered in a single query. Do not change any of the data in the School database. Name the queries Query 11 through Query 20. Submit the Access file the queries included to the Assignment.
Query 11: For each class, show the number of students enrolled. This should be a two column table with well-labeled headings.
Query 12: Find all classes in which fewer than three students are enrolled. This should be a one column table with well-labeled headings.
Query 13: Find ID's and names of all students taking ART 103A. The result should be a two column table.
Query 14: Find STUID and GRADE of all students taking any class taught by the faculty member whose FACID is F110. Arrange in order by STUID. The result should be a two column table. Only stuids which have grades should appear in the result.
Query 15: In one column, list all faculty names in the CSC department and student names majoring in CSC.
Query 16: Find the class IDs for all of the classes taught by Byrne of the Math Department (assume you don’t know Byrne’s facid). The result should be a single column table.
Query 17: Find the names and ID's of all faculty members who teach a class in room H221. The result should be a two column table.
Query 18: Get an alphabetical list of names and ID's of all students in any class taught by F110. The result should be a two column table.
Query 19: Find the names of all students enrolled in CSC201A. The result should be a single column table.
Query 20: Find the names of all students who are enrolled in some course but are not enrolled in CSC201A. The result should be a single column table with no names repeated.
ENROLL CLASSID STUID GRADE ART103A S1001 A ART103A S1002 D ART103A S1010 CSC201A S1002 F CSC201A S1020 B HST205A S1001 C MTH101B S1020 A MTH103C S1002 B MTH103C S1010Explanation / Answer
Query 11: For each class, show the number of students enrolled. This should be a two column table with well-labeled headings.
SELECT CLASSID, COUNT(*) AS NUMOFSTUDENTS FROM CLASS GROUP BY CLASSID;
Query 12: Find all classes in which fewer than three students are enrolled. This should be a one column table with well-labeled headings.
SELECT CLASSID FROM CLASS GROUP BY CLASSID HAVING COUNT(*) < 3;
Query 13: Find ID's and names of all students taking ART 103A. The result should be a two column table.
SELECT S.STUID, S.STUNAME FROM ENROLL E, STUDENT S WHERE E.STUID = S.STUID AND E.CLASSID = 'ART103A';
Query 14: Find STUID and GRADE of all students taking any class taught by the faculty member whose FACID is F110. Arrange in order by STUID. The result should be a two column table. Only stuids which have grades should appear in the result.
SELECT E.STUID, E.GRADE FROM ENROLL E, CLASS C WHERE C.CLASSID = E.CLASSID AND FACID = 'F110' AND GRADE IS NOT NULL ORDER BY STUID;
If you need any refinements, just get back to me.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.