## SQL Questions – Part 2

Question 1 : You are given the following relational schema:
Person(PersonID, Name, Sex, CityOfBirth)
Parent(ParentID, ChildID)
ParentID and ChildID are foreign keys referring to Person.PersonID.
Write the following queries in SQL:

(a) Find the names of grandparents of all the people who were born in
San Francisco.

Solution : SELECT p2.PersonID, p2.Name
FROM Parent AS p1, Person AS p2, Parent AS p3,
Person AS p4
WHERE p1.ParentID = p2.PersonID
AND p1.ChildID = p3.ParentID
AND p3.ChildID = p4.PersonID
AND p4.CityOfBirth = ‘San Francisco’

(b) Find the names of all people who were born in the same city as
their father.

Solution : SELECT p1.Name
FROM Parent AS p1, Person AS p2, Person AS p3
WHERE p1.ChildId = p2.PersonID
AND p1.ParentID = p3.PersonID
AND p3.CityOfBirth = p2.CityOfBirth
AND p3.Sex = ’Male’

Question 3 : Consider the following relational schema P(X,Y,Z), Q(U,X,Y).
Express the following relational algebra queries in SQL:

(a)

Solution : SELECT p.Z AS Z FROM P AS p, Q AS q
WHERE p.Y != Q.Y

(b)

Solution : SELECT X
FROM ( (SELECT p1.X AS X, q1.U as U FROM P AS p1, Q AS q1
WHERE p1.X = q1.X AND p1.Y = q1.Y AND p1.X = 20)
UNION
(SELECT p2.X AS X, q1.U AS U FROM P AS p2, Q AS q2
WHERE p2.X = q2.X AND p2.Y = q2.Y AND p2.X = 15))

(c)

Solution : SELECT DISTINCT p1.Z FROM P AS p1
WHERE NOT EXISTS
((SELECT q1.X, q1.Y FROM Q AS q1)
EXCEPT
(SELECT p2.X, p2.Y FROM P AS p2
WHERE p2.Z = p1.Z))
OR
SELECT DISTINCT p1.Z FROM P AS p1
WHERE NOT EXISTS
((SELECT * FROM Q AS q1
WHERE NOT EXISTS
(SELECT * FROM P AS p2
WHERE p2.X = q1.X AND p2.Y=q1.Y AND p2.Z = p1.Z))

Question 4 : Consider the following SQL query over a relational
schema R(A, B), S(B, C), T(C,D):
SELECT DISTINCT r.A
FROM R r, S s, T t
WHERE r.B = s.B AND r.A = 10 AND s.C > t.C AND t.D = 20
Do the following relational algebra expressions compute
the same result? Provide an explanation for each case:
(a) D4
(b) D5
(c) D6

Solution : The expression (a) does not compute the same result
because the join conditions do not match with that of
the SQL statement.
The expressions (b) and (c) compute a result that is same
as the result computed by the SQL statement.

*Related*