Collect data up to your four grandparents and back down to your generation or beyond to create
Fantastic news! We've Found the answer you've been seeking!
Question:
- Collect data up to your four grandparents and back down to your generation or beyond to create three tables (for ease of assessment, please use these exact names - don't worry about constraints): All IDs should be numeric
- Person, with two columns: PersonID, Name, Gender
- Parent, with two columns: ParentID and ChildID
- Spouse, with two columns: Spouse1ID and Spouse2ID
- With those three tables, write queries that will answer the following questions (in every case, you should return the names, not the IDs):
- You are having a family reunion. Everyone will be there. You expect everyone to hug one another. I need a query that will show all the hugs that will take place. Note: make sure you only get one direction of the hug (if you aren't careful, it will show up twice).
- I need a query to return the two parents of each child (if you aren't careful, the rows will show up twice).
- You notice there might be siblings in there. Instead or returning the pair of parents for each child, modify the query to return pairs of people who have at least one child together.
- Extend the prior query to count how many children they have together
- I need a query to connect each female in the dataset to her children (one child per row). If she has no children, it should return NULL.
- I need a query to determine if each mother has a spouse. If they do not have a spouse, the return should be NULL.
- I need a query to determine all of the grandsons.
- Using a semi-join, determine all the People who are fathers.
- Using an anti-join, determine all of the unwed people.
- Determine all brother-sister combinations.
- Note: you do not need to use your family, but it will make it easier for you to spot check that your answers are correct if you do. Each task should be a join. When creating the data, please neglect stepparents, i.e., each person should have two parents. Only work in current marriages/unions. If they are not married now, do not put them in the dataset as married.
Related Book For
Financial Accounting and Reporting a Global Perspective
ISBN: 978-1408076866
4th edition
Authors: Michel Lebas, Herve Stolowy, Yuan Ding
Posted Date: