SQL Queries
Include Name
Tables:
STUDENT FACULTY CLASS ENROLL
PK StuID PK FacID PK ClassID FK ClassID
StuName FacName FK FacID FK StuID
Major Dept Sched Grade
Credits Rank Room
Given the table information above, if you were asked to create an Access query that showed the Student Name and Grade for all students taking a class in Room H201, what tables would you need and how would you link them together?
The tables needed include: Student, Class, Enroll
These tables would be linked together by foreign keys; Enroll Table would be linked to Student table by FKStuID; Class would be linked to Enroll Table by FK ClassID
For the requested query above, what, if any, criteria would you use and on what field(s)?
Student Name and grade for all students taking a class in Room H201
SELECT S.StuName, Grade
FROM Student S, Enroll E, Class C
WHERE C.Room = ‘H201’;
Suppose you wanted to change the query above to also only show students with a grade of A or B? What would you change?
To change the query above and have it only show students with a grade of A or B I would add criteria to the grade field where e.grade = ‘A’ OR E.Grade = ‘B’;
Given the table information above, create the following queries using SQL:
Query 1: Find the names, ID's, and number of credits of all students who are math majors. The result should be a three column table.
SELECT Name, StudentID, Credits
FROM Students
WHERE Major = ‘Math’;
Query 2: Find the ClassID of all classes in which students are enrolled. The result should be a one column table, and no ClassID should appear more than once in that column.
SELECT DISTINCT ClassID
FROM Class;
Query 3: Find the names and ID's of all faculty members, sorted in reverse alphabetical order by name. The result should be a two column table (SELECT should only be two in order for the table to be 2 columns)
SELECT FacName, FacID
FROM Faculty
ORDER BY FacName DEC;
Query 4: Find the
Tables:
STUDENT FACULTY CLASS ENROLL
PK StuID PK FacID PK ClassID FK ClassID
StuName FacName FK FacID FK StuID
Major Dept Sched Grade
Credits Rank Room
Given the table information above, if you were asked to create an Access query that showed the Student Name and Grade for all students taking a class in Room H201, what tables would you need and how would you link them together?
The tables needed include: Student, Class, Enroll
These tables would be linked together by foreign keys; Enroll Table would be linked to Student table by FKStuID; Class would be linked to Enroll Table by FK ClassID
For the requested query above, what, if any, criteria would you use and on what field(s)?
Student Name and grade for all students taking a class in Room H201
SELECT S.StuName, Grade
FROM Student S, Enroll E, Class C
WHERE C.Room = ‘H201’;
Suppose you wanted to change the query above to also only show students with a grade of A or B? What would you change?
To change the query above and have it only show students with a grade of A or B I would add criteria to the grade field where e.grade = ‘A’ OR E.Grade = ‘B’;
Given the table information above, create the following queries using SQL:
Query 1: Find the names, ID's, and number of credits of all students who are math majors. The result should be a three column table.
SELECT Name, StudentID, Credits
FROM Students
WHERE Major = ‘Math’;
Query 2: Find the ClassID of all classes in which students are enrolled. The result should be a one column table, and no ClassID should appear more than once in that column.
SELECT DISTINCT ClassID
FROM Class;
Query 3: Find the names and ID's of all faculty members, sorted in reverse alphabetical order by name. The result should be a two column table (SELECT should only be two in order for the table to be 2 columns)
SELECT FacName, FacID
FROM Faculty
ORDER BY FacName DEC;
Query 4: Find the