Assignment 2: The Relational Model Below Using Your ADU ✓ Solved
Assignment 2given The Relational Model Below Using Your Adu Id And Sq
Given the relational model below, using your ADU ID and SQL statements:
- List all your courses of the last term [0.5 pt.]
- List the instructors’ names of these courses in descending order [0.5 pt.]
- List all courses of the last term that have no prerequisite [0.5 pt.]
- List all courses of the last term with grade above ‘C’ [0.5 pt.]
- Determine the CSIT department of the last term courses [1 pt.]
- Define the courses of the last term that have more than one section [1 pt.]
- Display the sections of the courses above that are offered in your location (‘Abu Dhabi’ or ‘Al-Ain’) [1 pt.]
- Please attach your last semester schedule details for crosschecking. Answer
Sample Paper For Above instruction
```sql
-- Assuming the relational database schema includes tables such as Students, Courses, Enrollments, Instructors, Departments, Sections, Locations, and Grades. Replace table and column names as per the actual database schema.
-- Replace 'yourADUId' with your actual ADU ID.
-- Replace 'LastTerm' with the specific term identifier (semester year/term).
-- The following queries are structured to fulfill the assignment requirements.
```
1. List all your courses of the last term
```sql
SELECT c.CourseID, c.CourseName, e.Term
FROM Enrollments e
JOIN Courses c ON e.CourseID = c.CourseID
WHERE e.StudentID = 'yourADUId' AND e.Term = 'LastTerm';
```
2. List the instructors' names of these courses in descending order
```sql
SELECT DISTINCT i.InstructorName
FROM Enrollments e
JOIN Courses c ON e.CourseID = c.CourseID
JOIN Instructors i ON c.InstructorID = i.InstructorID
WHERE e.StudentID = 'yourADUId' AND e.Term = 'LastTerm'
ORDER BY i.InstructorName DESC;
```
3. List all courses of the last term that have no prerequisite
```sql
SELECT c.CourseID, c.CourseName
FROM Courses c
LEFT JOIN Prerequisites p ON c.CourseID = p.PrerequisiteCourseID
WHERE p.PrerequisiteCourseID IS NULL
AND c.Term = 'LastTerm';
```
4. List all courses of the last term with grade above ‘C’
```sql
SELECT c.CourseID, c.CourseName, g.Grade
FROM Enrollments e
JOIN Courses c ON e.CourseID = c.CourseID
JOIN Grades g ON e.EnrollmentID = g.EnrollmentID
WHERE e.StudentID = 'yourADUId' AND e.Term = 'LastTerm' AND g.Grade > 'C';
```
5. Determine the CSIT department of the last term courses
```sql
SELECT DISTINCT d.DepartmentName
FROM Enrollments e
JOIN Courses c ON e.CourseID = c.CourseID
JOIN Departments d ON c.DepartmentID = d.DepartmentID
WHERE e.StudentID = 'yourADUId' AND e.Term = 'LastTerm' AND d.DepartmentName LIKE '%CSIT%';
```
6. Define the courses of the last term that have more than one section
```sql
SELECT c.CourseID, c.CourseName, COUNT(s.SectionID) AS SectionCount
FROM Sections s
JOIN Courses c ON s.CourseID = c.CourseID
WHERE s.Term = 'LastTerm'
GROUP BY c.CourseID, c.CourseName
HAVING COUNT(s.SectionID) > 1;
```
7. Display the sections of the courses above that are offered in your location (‘Abu Dhabi’ or ‘Al-Ain’)
```sql
SELECT s.SectionID, s.CourseID, s.Location, s.Term
FROM Sections s
WHERE s.CourseID IN (
SELECT c.CourseID
FROM Sections sec
JOIN Courses c ON sec.CourseID = c.CourseID
WHERE sec.Term = 'LastTerm'
GROUP BY c.CourseID
HAVING COUNT(sec.SectionID) > 1
)
AND s.Location IN ('Abu Dhabi', 'Al-Ain')
AND s.Term = 'LastTerm';
```
8. Attach your last semester schedule details for crosschecking
References
- Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.