Create a new database (named MyUniversity) 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.
Create a new
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:
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.
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
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
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 with 1 images
