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) SQL Questions 1
Solution : SELECT p.Z AS Z FROM P AS p, Q AS q 
           WHERE p.Y != Q.Y
(b) SQL Questions 2
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) SQL Questions 3
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.

Previous Home Next
Questions on SQL – Part 1 Questions on SQL – Part 3

     

Leave a Reply