# SQL QUESTIONS – Part 2

## 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.```