Task Description: consider the following relations for a university: Student (sid: integer, sname: string(100 chars), GPA: real, dateOfBirth: date, Did: integer, FirstYear: integer) Department (did: integer, dname: string(30 chars), capacity: integer) Course (cid: integer, cname: string(50 chars), credit: integer) Enroll (studentID: integer, courseID: integer, departmentID: integer, enrollmentDate: date, finalGrade: real) studentID, courseID, and departmentID in Enroll are foreign keys referencing the primary keys of the student, course, and department relations, respectively. Did in Student is a foreign key referencing the primary key of the department relation. Using SQL, develop and execute the following queries: 1- For each student with GPA between 2.5 (included) and 3.5 (included), display the student’s name, GPA, and his/her department ID. Sort the results by the students’ IDs in descending order. 2- For each student who joined the Computer Science department between 2018 (included) and 2020 (included), display the student’s name and date of birth. Sort the results by the students’ GPA in ascending order. Note: Computer science is a name of a department, not an ID 3- Display students, by name and GPA, enrolled in the Operating Systems course sorted by the enrollment date in ascending order. Note: Operating Systems is a name of a course, not an ID 4- Display the students, by IDs, enrolled in the Marketing course and received grades between 80 and 100. The results should be sorted by students’ GPA in descending order and then students’ names in Ascending order. Note: Marketing is a name of a course, not an ID
Task Description: consider the following relations for a university:
Student (sid: integer, sname: string(100 chars), GPA: real, dateOfBirth: date, Did: integer, FirstYear: integer)
Department (did: integer, dname: string(30 chars), capacity: integer)
Course (cid: integer, cname: string(50 chars), credit: integer)
Enroll (studentID: integer, courseID: integer, departmentID: integer, enrollmentDate: date, finalGrade: real)
studentID, courseID, and departmentID in Enroll are foreign keys referencing the primary keys of the student, course, and department relations, respectively. Did in Student is a foreign key referencing the primary key of the department relation.
Using SQL, develop and execute the following queries:
1- For each student with GPA between 2.5 (included) and 3.5 (included), display the student’s name, GPA,
and his/her department ID. Sort the results by the students’ IDs in descending order.
2- For each student who joined the Computer Science department between 2018 (included) and 2020
(included), display the student’s name and date of birth. Sort the results by the students’ GPA in
ascending order. Note: Computer science is a name of a department, not an ID
3- Display students, by name and GPA, enrolled in the
date in ascending order. Note: Operating Systems is a name of a course, not an ID
4- Display the students, by IDs, enrolled in the Marketing course and received grades between 80 and 100.
The results should be sorted by students’ GPA in descending order and then students’ names in
Ascending order. Note: Marketing is a name of a course, not an ID
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
