Dd0XG0Fy

1JOIN Statements
2
3
41. How do you find related data that is held in two separate data tables?
5* We find related data held in separate data tables by using SQL JOIN clauses.
6
7
8
91. Explain, in your own words, the difference between a CROSS JOIN, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. Give a real world example for each.
10
11A CROSS JOIN outputs all the combinations possible of every column in Table 1 and every column in table two. If we had a matchmaking database, for example, with columns (Gender, Age, Name) Table 1 and the same columns for Table 2, we would see all the potential dating matches possible for each individual.
12
13 INNER JOIN is the default method of joining tables. This method produces one row for each of the rows in the table being queried. This means that we are making a primary key from one table, the foreign key for the other table because they are essentially the same column of values. 
14
15LEFT OUTER JOIN starts with an INNER JOIN and produces rows for null values where a row in table1 does not meet the conditions of any row in table2.  Each row from the first table and the corresponding match from the second table would be produces.
16For example, in a list of cars in a car gallery, we can have two tables cars(year, model, owner) and owners(name, race_trophies). In a left outer join selecting owners.name,  cars.model, owners.race_trophies   from cars LEFT OUTER JOIN owners ON  cars.owner = owners.name and one of the cars does not have an owner specified, we would still see the car listed. 
17
18
19RIGHT OUTER JOIN combines two tables and produces a results table that includes each matching row between the queried tables and each row in the second table. Using the example above, the right outer join would produce a row of all the owners. If there is a car with a “null” non-listed owner, we would see that row.  
20
21
22FULL OUTER JOIN begins with an INNER JOIN. Once the inner join is completed, a Left outer join takes place. One row for each of the rows in the table to left of join statement is created even if a null value exists in its corresponding table 2 match. Then a Right outer join occurs and each row in table 2 that does not meet the join condition in table 1 produces a row with null value in column 1. In my example, if there was a car listing with no owner, a FULL OUTER JOIN, would produce a row with just the table 1 car values and null, null for owner.name and owner.race_trophies. Additionally, if there was a prospective owner in the car gallery database who did not have a car in the gallery, we would see a row of null, null, null, owner.name, owner.race_trophies.
23
24
25PRIMARY KEY is a unique identifier that for a row in a data table that exists only once. For example in a passport database, there may be many people with same first and last names but only one social security number. A social security number would be a good Primary key.
26
27FOREIGN KEY is the value that allows you to link on table to another. For example, if one table lists social security number, name, city_of_residence and another table lists city, state. City_of_residence would be the foreign key to match with the City,State table.
28
29ALIASING is the use of abbreviations for table names. It allows us to more quickly type out queries. We can use the first letter of a table name instead of writing out the whole title.
30
31
32
33Change this query so that you are using aliasing:
34SELECT professor.name, compensation.salary, compensation.vacation_days FROM professor 
35JOIN compensation 
36ON professor.id = compensation.professor_id;
37
38
39SELECT p.name, c.salary, c.vacation_days FROM professor AS p
40JOIN compensation AS c 
41ON p.id = c.professor_id;
42
43
44NATURAL JOIN creates an implicit join based on the same column names in joined tables.
45A NATURAL JOIN uses INNER JOIN by default but we can specify LEFT or RIGHT JOIN as options as well. For example, if one table lists dogs by name, weight, breed  and another table lists breed, weight then a NATURAL JOIN default would join the tables using breed. 
46
47
48SELECT employees.name, shifts.date, shifts.start_time, shifts.end_time
49FROM employees
50FULL OUTER JOIN scheduled_shifts
51ON employees.id = scheduled_shifts.employee_id
52FULL OUTER JOIN shifts
53ON shifts.id = scheduled_shifts.shift_id
54
55
56* All volunteers. If the volunteer is fostering a dog, include each dog as well.
57SELECT volunteers.first_name, volunteers.last_name, dogs.name
58FROM volunteers
59LEFT OUTER JOIN dog_adoptions
60ON adopter_id = volunteers.id
61LEFT OUTER JOIN dogs
62ON dog_id = dogs.id
63
64
65* The cat's name, adopter's name, and adopted date for each cat adopted within the past month to be displayed as part of the "Happy Tail" social media promotion which posts recent successful adoptions.
66SELECT cats.name, cat_adoptions.date, adopters.first_name, adopters.last_name 
67FROM cats
68LEFT OUTER JOIN cat_adoptions
69ON cats.id = cat_adoptions.cat_id
70RIGHT OUTER JOIN adopters
71ON cat_adoptions.adopter_id = adopters.id
72WHERE CURRENT_DATE - cat_adoptions.date <  31 
73
74
75* Adopters who have not yet chosen a dog to adopt and generate all possible combinations of adopters and available dogs.
76
77SELECT adopters.first_name, dogs.name, dog_adoptions.date
78FROM adopters
79LEFT OUTER JOIN dog_adoptions 
80ON dog_adoptions.adopter_id = adopters.id
81CROSS JOIN dogs
82WHERE dog_adoptions.date IS NULL
83
84
85
86* Lists of all cats and all dogs who have not been adopted.
87SELECT dogs.name, dog_adoptions.date
88FROM dogs
89LEFT OUTER JOIN dog_adoptions 
90ON dogs.id = dog_adoptions.dog_id
91WHERE dog_adoptions.date IS NULL
92
93SELECT cats.name, cat_adoptions.date
94FROM cats
95LEFT OUTER JOIN cat_adoptions
96ON cats.id = cat_adoptions.cat_id
97WHERE cat_adoptions.date IS NULL
98
99
100* Volunteers who are available to foster. If they currently are fostering a dog, include the dog. Also include all dogs who are not currently in foster homes.
101
102
103SELECT volunteers.first_name, volunteers.available_to_foster, dogs.name, dogs.in_foster
104FROM volunteers
105FULL OUTER JOIN dogs
106ON volunteers.foster_dog_id = dogs.id
107
108* The name of the person who adopted Rosco.
109
110SELECT adopters.first_name, adopters.last_name, dogs.name
111FROM adopters
112LEFT OUTER JOIN dog_adoptions
113ON adopters.id = dog_adoptions.adopter_id 
114LEFT OUTER JOIN dogs
115ON dogs.id = dog_adoptions.dog_id
116WHERE dogs.name = 'Rosco'
117
118
119
120* To determine if the library should buy more copies of a given book, please provide the names and position, in order, of all of the patrons with a hold (request for a book with all copies checked out) on "Advanced Potion-Making".
121SELECT patrons.name, holds.rank, books.title
122FROM patrons 
123FULL OUTER JOIN holds
124ON patrons.id = holds.patron_id
125FULL OUTER JOIN books
126ON holds.isbn = books.isbn
127WHERE books.title = 'Advanced Potion-Making'
128ORDER BY holds.rank DESC
129
130
131* Make a list of all book titles and denote whether or not a copy of that book is checked out.
132
133SELECT books.title, transactions.checked_out_date, transactions.checked_in_date
134FROM books
135FULL OUTER JOIN transactions
136ON books.isbn = transactions.isbn
137WHERE checked_in_date IS NULL
138
139* In an effort to learn which books take longer to read, the librarians would like you to create a list of average checked out time by book name in the past month.
140
141SELECT books.title, AVG(transactions.checked_in_date-transactions.checked_out_date) as read_time_avg_days
142FROM books
143FULL OUTER JOIN transactions
144ON books.isbn = transactions.isbn
145WHERE transactions.checked_in_date IS NOT NULL AND CURRENT_DATE - transactions.checked_out_date < 31 
146GROUP BY books.title
147
148* In order to learn which items should be retired, make a list of all books that have not been checked out in the past 5 years.
149SELECT books.title, books.isbn, transactions.checked_out_date
150FROM books
151FULL OUTER JOIN transactions
152ON books.isbn = transactions.isbn
153WHERE 5*365 <CURRENT_DATE -  transactions.checked_out_date
154
155* List all of the library patrons. If they have one or more books checked out, correspond the books to the patrons.
156SELECT patrons.name, books.title, transactions.checked_out_date, transactions.checked_in_date
157FROM patrons
158FULL OUTER JOIN transactions
159ON patrons.id = transactions.patron_id
160RIGHT OUTER JOIN books
161ON books.isbn = transactions.isbn
162WHERE transactions.checked_in_date IS NULL