<sub>2025-05-15</sub> <sub>#data-visualization #data-management #sql #hmp669</sub>
<sup>[[maps-of-content|🌐 Maps of Content — All Notes]] </sup>
<sup>Series: [[hmp669|Data Management and Visualization]]</sup>
## Practice SQL questions - Small College Database
> [!question]- Practice SQL questions - Small College Database
> 1. List all faculty with their respective departments.
> 2. List all faculty with their respective departments sorted by departments and then by last name.
> 3. List the department names with their respective number of faculty.
> 4. List all faculty who belong to HMP.
> 5. List all students with their advisors.
> 6. List all of Maynard's advisees.
> 7. List all classes with their instructors.
> 8. List all fields on the enrollment table.
> 9. List all student names, courses taken, term, and instructor and grade.
### 1. List all faculty with their respective departments.
```sql
SELECT
Faculty.FFirstName AS [First Name],
Faculty.FLastName AS [Last Name],
Department.DName AS [Department]
FROM
Faculty INNER JOIN Department
ON Faculty.FDepartment = Department.DID;
```
![[prac-small-college-db-1747389853222.webp]]
```sql
SELECT
Faculty.FFirstName & " " & Faculty.FLastName AS [Faculty],
Department.DName AS [Department]
FROM
Faculty INNER JOIN Department
ON Faculty.FDepartment = Department.DID;
```
![[prac-small-college-db-1747389870582.webp]]
### 2. List all faculty with their respective departments sorted by departments and then by last name.
```sql
SELECT
Faculty.FFirstName AS [First Name],
Faculty.FLastName AS [Last Name],
Department.DName AS [Department]
FROM
Faculty INNER JOIN Department ON Faculty.FDepartment = Department.DID
ORDER BY
Department.DName, Faculty.FLastName;
```
![[prac-small-college-db-1747389887428.webp]]
### 3. List the department names with their respective number of faculty
```sql
SELECT
Department.DName AS [Department],
COUNT(Faculty.FID) AS [Number of Faculty]
FROM
Faculty INNER JOIN Department ON Faculty.FDepartment = Department.DID
GROUP BY
Department.DName;
```
![[prac-small-college-db-1747389362965.webp]]
### 4. List all faculty who belong to HMP.
```sql
SELECT
Faculty.FFirstName AS [First Name],
Faculty.FLastName AS [Last Name],
Department.DName AS [Department]
FROM
Faculty INNER JOIN Department ON Faculty.FDepartment = Department.DID
WHERE
Department.DName = "HMP";
```
![[prac-small-college-db-1747389787643.webp]]
### 5. List all students with their advisors.
```sql
SELECT
Student.SFirstName & " " & Student.SLastName AS [Student],
Faculty.FFirstName & " " & Faculty.FLastName AS [Advisor]
FROM
Student LEFT JOIN Faculty ON Student.SAdvisor = Faculty.FID;
```
![[prac-small-college-db-1747390657009.webp]]
```sql
SELECT
Student.SFirstName & " " & Student.SLastName AS [Student],
Faculty.FFirstName & " " & Faculty.FLastName AS [Advisor],
Department.DName AS [Department]
FROM (Student
LEFT JOIN Faculty ON Student.SAdvisor = Faculty.FID)
LEFT JOIN Department ON Faculty.FDepartment = Department.DID
ORDER BY
Faculty.FLastName;
```
![[prac-small-college-db-1747391753047.webp]]
### 6. List all of Maynard's advisees.
```sql
SELECT
Student.SFirstName & " " & Student.SLastName AS [Student],
Faculty.FFirstName & " " & Faculty.FLastName AS [Advisor],
Department.DName AS [Department]
FROM (Student
LEFT JOIN Faculty ON Student.SAdvisor = Faculty.FID)
LEFT JOIN Department ON Faculty.FDepartment = Department.DID
WHERE Faculty.FLastName = "Maynard"
ORDER BY
Faculty.FLastName, Student.SLastName;
```
![[prac-small-college-db-1747391961018.webp]]
### 7. List all classes with their instructors.
```sql
SELECT
Class.ClCourse AS [Class Number],
Course.CName AS [Class Name],
Class.ClFaculty AS [Instructor]
FROM Course
LEFT JOIN Class ON Course.CID = Class.ClCourse
ORDER BY
Course.CName;
```
![[prac-small-college-db-1747392531564.webp]]
### 8. List all fields on the enrollment table.
```sql
SELECT Enrollment.*
FROM Enrollment;
```
![[prac-small-college-db-1747473679291.webp]]
### 9. List all student names, courses taken, term, and instructor and grade.
```sql
SELECT
Student.SFirstName & " " & Student.SLastName AS [Student],
Course.CNumber AS [Course Number],
Course.CName AS [Course Name],
Class.ClTerm AS [Term],
Class.ClFaculty AS [Instructor],
Enrollment.EGrade AS [Grade]
FROM ((Student
LEFT JOIN Enrollment ON Student.SID = Enrollment.EID)
LEFT JOIN Class ON Enrollment.EClass = Class.ClID)
LEFT JOIN Course ON Class.ClCourse = Course.CID
ORDER BY
Student.SLastName;
```
![[prac-small-college-db-1747394365736.webp]]
```sql
SELECT
Student.SFirstName & " " & Student.SLastName AS [Student],
Course.CNumber AS [Course Number],
Course.CName AS [Course Name],
Class.ClTerm AS [Term],
Faculty.FFirstName & " " & Faculty.FLastName AS [Instructor],
Enrollment.EGrade AS [Grade]
FROM (((Student
LEFT JOIN Enrollment ON Student.SID = Enrollment.EID)
LEFT JOIN Class ON Enrollment.EClass = Class.ClID)
LEFT JOIN Course ON Class.ClCourse = Course.CID)
LEFT JOIN Faculty ON Class.ClFaculty = Faculty.FID
ORDER BY
Student.SLastName;
```
![[prac-small-college-db-1747394554298.webp]]
--
Reference: