<sub>2025-05-24</sub> <sub>#data-visualization #data-management #sql #hmp669</sub>
<sup>[[maps-of-content|🌐 Maps of Content — All Notes]] </sup>
<sup>Series: [[hmp669|Data Management and Visualization]]</sup>
# SQL Practice — Family Tree Database
![[family-tree-db-exercise-1748081346635.webp]]
![[family-tree-db-exercise-1748081482607.webp]]
![[family-tree-db-exercise-1748085512626.webp]]
SQL Questions — Family Ties
### 1. List all parents with their children
```sql
SELECT
P.M_NAME AS [Parent],
C.M_NAME AS [Child]
FROM MEMBER AS P
INNER JOIN MEMBER AS C ON P.M_ID = C.M_PARENT;
```
![[family-tree-db-exercise-1748082537880.webp]]
### 2. List all people with their children
```sql
SELECT
P.M_NAME AS [Person],
C.M_NAME AS [Child]
FROM MEMBER AS P
LEFT JOIN MEMBER AS C ON P.M_ID = C.M_PARENT;
```
![[family-tree-db-exercise-1748082724027.webp]]
### 3. List all people with their number of children
```sql
SELECT
P.M_NAME AS [Person],
COUNT(C.M_NAME) AS [Number of Children]
FROM MEMBER AS P
LEFT JOIN MEMBER AS C ON P.M_ID = C.M_PARENT
GROUP BY
P.M_NAME;
```
![[family-tree-db-exercise-1748083054417.webp]]
### 4. List all people with no children
```sql
SELECT
P.M_NAME AS [Person],
COUNT(C.M_NAME) AS [Number of Children]
FROM MEMBER AS P
LEFT JOIN MEMBER AS C ON P.M_ID = C.M_PARENT
GROUP BY
P.M_NAME
HAVING
COUNT(C.M_NAME) = 0;
```
![[family-tree-db-exercise-1748083082149.webp]]
### 5. List all of Kerry's children
```sql
SELECT
C.M_NAME AS [Kerry's Children]
FROM MEMBER AS P
LEFT JOIN MEMBER AS C ON P.M_ID = C.M_PARENT
WHERE
P.M_NAME = "Kerry";
```
![[family-tree-db-exercise-1748083284416.webp]]
### 6. List Martin's parent
```sql
SELECT
P.M_NAME AS [Martin's Parent]
FROM MEMBER AS P
LEFT JOIN MEMBER AS C ON P.M_ID = C.M_PARENT
WHERE
C.M_NAME = "Martin";
```
![[family-tree-db-exercise-1748083515764.webp]]
### 7. List all parents with at least three children
```sql
SELECT
P.M_NAME AS [Parent with at least 3 Children]
FROM MEMBER AS P
LEFT JOIN MEMBER AS C ON P.M_ID = C.M_PARENT
GROUP BY
P.M_NAME
HAVING
COUNT(C.M_NAME) >= 3;
```
![[family-tree-db-exercise-1748083752323.webp]]
### 8. List all people and their siblings
```sql
SELECT
M.M_NAME AS [Member Name],
SIBTABLE.SIBLING AS [Sibling]
FROM MEMBER AS M
LEFT JOIN (
SELECT
P.M_ID,
P.M_NAME AS [Person],
S.M_NAME AS [Sibling]
FROM MEMBER AS P
INNER JOIN MEMBER AS S ON P.M_PARENT = S.M_PARENT
WHERE
P.M_ID <> S.M_ID) AS SIBTABLE
ON M.M_ID = SIBTABLE.M_ID;
```
![[family-tree-db-exercise-1748084933621.webp]]
### 9. List all people and their number of siblings
```sql
SELECT
M.M_NAME AS [Member Name],
COUNT(SIBTABLE.SIBLING) AS [Number of Siblings]
FROM MEMBER AS M
LEFT JOIN (
SELECT
P.M_ID,
P.M_NAME AS [Person],
S.M_NAME AS [Sibling]
FROM MEMBER AS P
INNER JOIN MEMBER AS S ON P.M_PARENT = S.M_PARENT
WHERE
P.M_ID <> S.M_ID) AS SIBTABLE
ON M.M_ID = SIBTABLE.M_ID
GROUP BY
M.M_NAME;
```
![[family-tree-db-exercise-1748085062692.webp]]
### 10. List all people with no siblings
```sql
SELECT
M.M_NAME AS [Person with no Siblings]
FROM MEMBER AS M
LEFT JOIN (
SELECT
P.M_ID,
P.M_NAME AS [Person],
S.M_NAME AS [Sibling]
FROM MEMBER AS P
INNER JOIN MEMBER AS S ON P.M_PARENT = S.M_PARENT
WHERE
P.M_ID <> S.M_ID) AS SIBTABLE
ON M.M_ID = SIBTABLE.M_ID
GROUP BY
M.M_NAME
HAVING
COUNT(SIBTABLE.SIBLING) = 0;
```
![[family-tree-db-exercise-1748085204789.webp]]
### 11. List all people with more than two siblings.
```sql
SELECT
M.M_NAME AS [Person with more than 2 Siblings],
COUNT(SIBTABLE.SIBLING) AS [Number of Siblings]
FROM MEMBER AS M
LEFT JOIN (
SELECT
P.M_ID,
P.M_NAME AS [Person],
S.M_NAME AS [Sibling]
FROM MEMBER AS P
INNER JOIN MEMBER AS S ON P.M_PARENT = S.M_PARENT
WHERE
P.M_ID <> S.M_ID) AS SIBTABLE
ON M.M_ID = SIBTABLE.M_ID
GROUP BY
M.M_NAME
HAVING
COUNT(SIBTABLE.SIBLING) > 2;
```
![[family-tree-db-exercise-1748085386582.webp]]
### 12. List all grandparents with their grandchildren
```sql
SELECT
GP.M_NAME AS [Grandparent],
GC.M_NAME AS [Grandchild]
FROM (MEMBER AS GP
INNER JOIN MEMBER AS P ON GP.M_ID = P.M_PARENT)
INNER JOIN MEMBER AS GC ON P.M_ID = GC.M_PARENT
ORDER BY
GP.M_NAME,
GC.M_NAME;
```
![[family-tree-db-exercise-1748086287357.webp]]
```sql
SELECT
GP.M_NAME AS [Grandparent],
GC.M_NAME AS [Grandchild]
FROM (MEMBER AS GP
LEFT JOIN MEMBER AS P ON GP.M_ID = P.M_PARENT)
LEFT JOIN MEMBER AS GC ON P.M_ID = GC.M_PARENT
ORDER BY
GP.M_NAME,
GC.M_NAME;
```
![[family-tree-db-exercise-1748086006048.webp]]
### 13. List all grandparents with their number of grandchildren
```sql
SELECT
GP.M_NAME AS [Grandparent],
COUNT(GC.M_NAME) AS [Number of Grandchildren]
FROM (MEMBER AS GP
INNER JOIN MEMBER AS P ON GP.M_ID = P.M_PARENT)
INNER JOIN MEMBER AS GC ON P.M_ID = GC.M_PARENT
GROUP BY
GP.M_NAME
ORDER BY
GP.M_NAME;
```
![[family-tree-db-exercise-1748086452422.webp]]
### 14. List all of Lawry's first cousins
```sql
SELECT
C.M_NAME AS [Lawry's First Cousins]
FROM (((MEMBER AS LAWRY
INNER JOIN MEMBER AS P ON LAWRY.M_PARENT = P.M_ID)
INNER JOIN MEMBER AS GP ON P.M_PARENT = GP.M_ID)
INNER JOIN MEMBER AS PS ON GP.M_ID = PS.M_PARENT)
INNER JOIN MEMBER AS C ON PS.M_ID = C.M_PARENT
WHERE
LAWRY.M_NAME = "Lawry"
AND C.M_NAME <> "Lawry"
AND C.M_PARENT <> LAWRY.M_PARENT
ORDER BY
C.M_NAME;
```
![[family-tree-db-exercise-1748087540675.webp]]
### 15. List all children with at least two cousins.
```sql
SELECT
X.M_NAME AS [Children with at least 2 cousins],
COUNT(C.M_ID) AS [Number of Cousins]
FROM (((MEMBER AS X
INNER JOIN MEMBER AS P ON X.M_PARENT = P.M_ID)
INNER JOIN MEMBER AS GP ON P.M_PARENT = GP.M_ID)
INNER JOIN MEMBER AS PS ON GP.M_ID = PS.M_PARENT)
INNER JOIN MEMBER AS C ON PS.M_ID = C.M_PARENT
WHERE
X.M_ID <> C.M_ID
AND X.M_PARENT <> C.M_PARENT
GROUP BY
X.M_NAME
HAVING
COUNT(C.M_ID) >= 2;
```
![[family-tree-db-exercise-1748088424938.webp]]