# SQL Notes: Exercise 5 - SELECT Using Self-Joins

Name: _________________________________

Write a SELECT statement which produces the results. Put each clause (FROM, WHERE, ...) on a separate line. Assume the following table definition:

    CREATE TABLE People (
       id         INT         PRIMARY KEY,
       name       VARCHAR(20) NOT NULL,
       gender     CHAR(1)     NOT NULL,  -- M=Male, F=Female
       mother_id  INT         REFERENCES People (id),
       father_id  INT         REFERENCES People (id)
    );

    INSERT INTO People VALUES(1, 'Mickey Mouse' , 'M',    2,    3);
    INSERT INTO People VALUES(2, 'Maria Raton'  , 'F', NULL,    7); -- mother
    INSERT INTO People VALUES(3, 'Walt X'       , 'M',    8, NULL); -- father
    INSERT INTO People VALUES(4, 'Bob Mouse  '  , 'M',    2,    3); -- brother
    INSERT INTO People VALUES(5, 'Maria Mouse'  , 'F',    2,    3); -- sister
    INSERT INTO People VALUES(6, 'Jorge X'      , 'M', NULL,    3); -- brother
    INSERT INTO People VALUES(7, 'Armando Raton', 'M', NULL, NULL); -- m gfather
    INSERT INTO People VALUES(8, 'Brenda Beegoode','F',NULL, NULL); -- p gmother

1. List the brothers and sisters of Mickey Mouse. To make this a better exercise try three versions: full siblings (two common parents), half siblings (only one common parent), and all siblings (half or full).
   - Half siblings. Answer:

         SELECT sib.name
           FROM People mm, People sib
           WHERE ((mm.mother_id =  sib.mother_id AND mm.father_id <> sib.father_id)
               OR (mm.mother_id <> sib.mother_id AND mm.father_id =  sib.father_id))
             AND mm.name = 'Mickey Mouse'
             AND mm.id != sib.id;

   - Full siblings. Answer:

         SELECT sib.name
           FROM People mm, People sib
           WHERE (mm.mother_id = sib.mother_id AND mm.father_id = sib.father_id)
             AND mm.name = 'Mickey Mouse'
             AND mm.id != sib.id;

   - All siblings. Answer:

         SELECT sib.name
           FROM People mm, People sib
           WHERE (mm.mother_id =  sib.mother_id OR mm.father_id = sib.father_id)
             AND mm.name = 'Mickey Mouse'
             AND mm.id != sib.id;

2. List the grandparents of Mickey Mouse. Answer:

       SELECT gp.name
         FROM People mm, People p, People gp
         WHERE (    (mm.mother_id = p.id OR mm.father_id = p.id)
                AND (p.mother_id = gp.id OR p.father_id = gp.id)
               )
           AND mm.name = 'Mickey Mouse';

3. Are there any mother or father indexes that don't point to any record? This is a check for referential integrity, which many databases will perform automatically.
4. Are there any mothers who are not 'F' or fathers who are not 'M'. This is enforcing a business rule on the database.