Scribd
Upload
18 views2 pages

AdvancedDatabaseConcepts Assignment 2 Solution

The document contains homework questions and SQL queries related to advanced database concepts at Indiana University. It includes tasks such as finding students with high grades, those who have only taken required courses, identifying students with the highest GPA in each department, and determining eligibility for degrees based on credit hours and GPA. Additionally, there is a bonus question about courses required by multiple departments.

Uploaded by

Phan Duc Tri
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
18 views2 pages

AdvancedDatabaseConcepts Assignment 2 Solution

The document contains homework questions and SQL queries related to advanced database concepts at Indiana University. It includes tasks such as finding students with high grades, those who have only taken required courses, identifying students with the highest GPA in each department, and determining eligibility for degrees based on credit hours and GPA. Additionally, there is a bonus question about courses required by multiple departments.

Uploaded by

Phan Duc Tri
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

INDIANA UNIVERSITY

B561 Advanced Database Concepts


Enrique Areyan

Homework 2

(Q1) Find the sids and names of all students who never got a grade lower than A- (A- is OK).

RA: πsid,name (Students ./ T ake) − πsid,name (Students ./grade!=“A+”∧ T ake)


grade!=“A”∧
grade!=“A−”

RC: {s ∈ Students|∃t ∈ Students([Link] = [Link] ∧ [Link] = [Link] ∧ ∀a ∈ T ake([Link] =


[Link] ∧ ([Link] = “A + ” ∨ [Link] = “A” ∨ [Link] = “A − ”)))}
SQL: SELECT [Link],[Link] FROM Students as S,Take as T
WHERE [Link] = [Link]
EXCEPT
SELECT [Link],[Link] FROM Students as S,Take as T
WHERE [Link] = [Link] and [Link]!=’A+’ and
[Link]!=’A’ and [Link]!=’A-’

Q2 Find the students who have never taken any course that is not required by his/her department

RA: πsid (Student) − πsid (πsid,cid (T ake) − πsid,cid (Students ./ RequiredCourses))


RC: {t ∈ Students|∃s ∈ Students([Link] = [Link] ∧ ∀a ∈ T aken([Link] = [Link] ∧
∃r ∈ RequiredCourse([Link] = [Link] ∧ [Link] = [Link])))}
SQL: SELECT [Link] FROM Students as St
EXCEPT
SELECT [Link] FROM (SELECT [Link],[Link] FROM Take as T
EXCEPT
SELECT [Link],[Link] FROM Students as S,RequiredCourse as R
WHERE [Link] = [Link]) as St

Q3 For each department, find the name of the students with the highest GPA (if more than one
student has the same GPA, return them all)

SQL: SELECT name,dept,GPA FROM Students as S1


WHERE GPA = (SELECT max(GPA) FROM Students as S2 WHERE [Link]=[Link])
For each department, as stored in the Student’s table, we calculate the max GPA and
test if the student’s GPA matches the max GPA. If it does, the query returns the student.

Q4 A student can get the degree from his/her department if he/she has taken a total of 90 credit
hours’ courses, has a overall GPA over 2.0, and has taken all required courses. Please find the
sids and names of the students who can get the degree from their department.

SQL: SELECT [Link],[Link] FROM Students as S1 WHERE GPA >2 and


NOT EXISTS(
SELECT [Link],[Link] Students as S,RequiredCourse as RC
WHERE [Link] = [Link] and [Link]=[Link]

1
INTERSECT
SELECT [Link],[Link] FROM Take as T,RequiredCourse as RC
WHERE [Link] = [Link] and [Link]=[Link]) and
EXISTS(
SELECT SUM([Link]) FROM Students as S,Take as T, Courses as C
WHERE [Link] = [Link] and [Link]=[Link] and [Link] = [Link]
GROUP BY [Link]
HAVING SUM([Link])>=90)
A student will be returned if GPA is greater than two; and does not exists any recored
in the intersection between the required courses of the student’s department and the
required courses the student has taken; and we can calculate the student’s sum of courses’
credits to be greater or equal than 90.

Q5 (bonus) Find the courses (cid and name) that are required by more than 10 departments for
its degree program.

SQL: SELECT [Link],[Link] FROM Courses as C,RequiredCourse as RC


WHERE [Link]=[Link]
GROUP BY [Link] HAVING COUNT([Link])>10
The query groups the required courses by the course id and counts the dept column for
each group. If the count is more than 10, the course’s id and name is returned.

You might also like