//1 You use JOIN statements to combine related data together.

//2
INNER JOIN combines tables whihc gives one row in the new table for each common row from the tables being called upon.
Example can be like if you have Weather data of cities and you have another table with cites and sunset times, you can inner join them with cities column.

LEFT OUTER JOIN Results in a new table with all rows from first table and the matching roows from the second table.

RIGHT OUTER JOIN  Results in each matched row between two tables and each row from the second table.

For both LEFT and RIGHT, you could do similar senarios for each. One example could be that you want to see which customers havent placed orders. Some rows can have data and others will return which null values because order info wont be present.

//3
primary key is the unique identifer for each row in a table. One example is the flight number for a airliner data.
foreign key is the primary key in one table that is present in another table but not a unique identifer for that table. An example can be the state name in a table of data for number of jobs created, but in a another table you have a states column but in a table of home addresses of people who registered for unemployment.

//4 Method of shortening queries by using variables to replace table names.

//5
SELECT professor.name, compensation.salary,
compensation.vacation_days FROM professor JOIN
compensation ON professor.id =
compensation.professor_id;


SELECT p.name, c.salary, c.vacation_days
FROM professor AS p
JOIN compensation AS c
ON p.id = c.professor_id;

//6
To implicitly join tables based on shared columns so that they only appear once in a table. An example can be a company table with company id and a foods table with company id of where it was purchased from.

//7
SELECT employees.name, shifts.date, shifts.start_time, shifts.end_time
FROM employees
JOIN shifts
ON employees.id = shifts.id;

//8
8a.
    SELECT volunteers.first_name, volunteers.last_name, dogs.name, dogs.breed
    FROM volunteers
    LEFT OUTER JOIN dogs
    ON volunteers.foster_dog_id = dogs.id;

| first_name | last_name  | name      | breed              |
| ---------- | ---------- | --------- | ------------------ |
| Rubeus     | Hagrid     | Munchkin  | dachsund chihuahua |
| Marjorie   | Dursley    | Marmaduke | great dane         |
| Sirius     | Black      |           |                    |
| Remus      | Lupin      |           |                    |
| Albus      | Dumbledore |           |                    |

8b.
    SELECT cats.name, cat_adoptions.date, adopters.first_name, adopters.last_name
    FROM cats
    RIGHT OUTER JOIN cat_adoptions ON cats.id = cat_adoptions.cat_id
    INNER JOIN adopters ON cat_adoptions.adopter_id = adopters.id
    WHERE cat_adoptions.date > CURRENT_DATE - INTERVAL '30 DAYS';

| name     | date                     | first_name | last_name |
| -------- | ------------------------ | ---------- | --------- |
| Mushi    | 2019-01-25T00:00:00.000Z | Arabella   | Figg      |
| Victoire | 2019-01-30T00:00:00.000Z | Argus      | Filch     |

8c.
    SELECT adopters.first_name, adopters.last_name, adopters.phone_number
    FROM adopters
    LEFT JOIN dog_adoptions
    ON adopters.id = dog_adoptions.adopter_id
    WHERE dog_adoptions IS NULL;

| first_name | last_name | phone_number |
| ---------- | --------- | ------------ |
| Arabella   | Figg      | 843-228-5239 |
| Hermione   | Granger   | 676-464-7837 |

8d.
    SELECT cats.name, cats.intake_date
    FROM cats
    LEFT JOIN cat_adoptions
    ON cats.id = cat_adoptions.cat_id
    WHERE cat_adoptions IS NULL;

| name     | intake_date              |
| -------- | ------------------------ |
| Seashell | 2016-01-09T00:00:00.000Z |
| Nala     | 2016-01-12T00:00:00.000Z |

---
**Query #2**

    SELECT dogs.name, dogs.intake_date
    FROM dogs
    LEFT JOIN dog_adoptions
    ON dogs.id = dog_adoptions.dog_id
    WHERE dog_adoptions IS NULL;

| name      | intake_date              |
| --------- | ------------------------ |
| Munchkin  | 2017-01-13T00:00:00.000Z |
| Boujee    | 2017-06-22T00:00:00.000Z |
| Lassie    | 2016-07-05T00:00:00.000Z |
| Marley    | 2017-05-04T00:00:00.000Z |
| Marmaduke | 2016-03-22T00:00:00.000Z |

8e.
    SELECT adopters.first_name, adopters.last_name
    FROM adopters
    INNER JOIN dog_adoptions
    ON adopters.id = dog_adoptions.adopter_id;

| first_name | last_name |
| ---------- | --------- |
| Argus      | Filch     |

//9
9a.
    SELECT patrons.name, holds.rank, holds.date
    FROM patrons
    JOIN holds
    ON patrons.id = holds.patron_id
    WHERE isbn = '9136884926';

| name           | rank | date                     |
| -------------- | ---- | ------------------------ |
| Terry Boot     | 1    | 2019-02-12T00:00:00.000Z |
| Cedric Diggory | 2    | 2019-02-13T00:00:00.000Z |

9b.
    SELECT patrons.name, books.title
    FROM patrons
    LEFT JOIN (SELECT *
               FROM transactions
               WHERE checked_in_date IS NULL
               ) as t
    ON patrons.id = t.patron_id
    LEFT JOIN books
    ON t.isbn = books.isbn;

| name             | title                                   |
| ---------------- | --------------------------------------- |
| Hermione Granger |                                         |
| Terry Boot       | Advanced Potion-Making                  |
| Padma Patil      |                                         |
| Cho Chang        |                                         |
| Cedric Diggory   | Fantastic Beasts and Where to Find Them |