gRqMAUU3

11. How do you find related data held in two separate data tables?
2	- Within SQL you can utilize the JOIN methods to 'combine' the data between two tables. Utiizing the Primary Key in one table to relate to the foreign key in another.
3
4
52. Explain, in your own words, the difference between an INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN. Give a real-world example for each.
6	- INNER JOIN
7		- Returns ONLY connected rows from both tables. 
8		- Example: Querying to pull out "serial numbers" from one table and the "SKU" from another table and match the rows in a new table. 
9
10	- LEFT OUTER JOIN
11		- Returns all connected rows, and unconnected from the 'left table'. Where data does not match right, null will be provided. 
12		- Example: You want to see ALL of the data in relation to incoming inventory. But you also want to compare current inventory numbers. And recognize if there are any new items being added to your stock that have not existed prior. 
13
14	- RIGHT OUTER JOIN
15		- RETURNS all connected rows, and unconnected from the 'right table`. Where data does not match left, null will be provided. 
16		- Example: You want to see ALL of the data in relation to current inventory and compare that to available inventory in the warehouse so you know what you need to order and compare that to what is available in the warehouse, if an item does not exist in the warehouse you will be able to recognize that.  
17
18	(Bonus from my research: FULL JOIN returns all connected and unconnected rows from both the left and right table)
19
20
213. Define primary key and foreign key. Give a real-world example for each.
22	- Primary Key is the unique identifier that only exists once within the table and allows you to select a particular row of data.
23		- Example: Continuing our store inventory. A primary key would be the 'SKU' a store uses to indentify specific items on the shelves.  
24	- Foreign Key is what is utilized in the second table to link the data together. A primary key in one table may be the foreign key in another. 
25		- Example: Using the above example, a foreign key here would be "model number". This would allow you to match your store inventory to the warehouse inventory and see where your stock nubmers are at. 
26
27
284. Define aliasing.
29	- Aliasing is the process of assigning an alias to a table to allow for more efficient queries and less typing on your part.
30
315. Change this query so that you are using aliasing:
32
33	SELECT p.name, c.salary, c.vacation_days
34		FROM professor AS p
35		JOIN compensation AS c
36		ON p.id = c.professor_id;
37
38
396. Why would you use a NATURAL JOIN? Give a real-world example.
40	- Anytime you where sure that there where matches between table columns and you want to implicitly return ALL matches. For example, in the case of the Warehouse and the Stock Inventory you could ask to return all matches between them to see not only what you have in stock that exists within the warehouse, but also what is available in the warehouse that is available to order that you might not have ordered in the past. 
41
42
437. Using this Employee schema and data, write queries to find the following information:
44	- List all employees and all shifts.
45
46		SELECT e.name, s.date, s.start_time, s.end_time
47			FROM employees AS e
48			RIGHT OUTER JOIN shifts AS s
49			ON e.id = s.id;
50
51
528. Using this Adoption schema and data, please write queries to retrieve the following information and include the results:
53
54	- Create a list of all volunteers. If the volunteer is fostering a dog, include each dog as well.
55
56		SELECT v.first_name, d.name
57			FROM volunteers AS v
58			LEFT OUTER JOIN dogs AS d
59			ON v.foster_dog_id = d.id;
60
61	- 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.
62
63		SELECT cats.name AS cat_name, adopters.first_name AS adopter_first_name, 
64		adopters.last_name AS adopter_last_name, cat_adoptions.date AS adoption_date
65			FROM cats
66			JOIN cat_adoptions ON cats.id = cat_adoptions.cat_id
67			JOIN adopters ON cat_adoptions.adopter_id = adopters.id
68			WHERE cat_adoptions.date >= (CURRENT_DATE - 30);
69
70	- Create a list of adopters who have not yet chosen a dog to adopt.
71
72		SELECT a.first_name, a.last_name
73			FROM adopters AS a
74			LEFT OUTER JOIN dog_adoptions AS d
75			ON a.id = d.adopter_id
76			WHERE d.adopter_id IS NULL;
77
78	- Lists of all cats and all dogs who have not been adopted.
79
80		SELECT c.name
81			FROM cats AS c
82			LEFT OUTER JOIN cat_adoptions AS cA
83			ON c.id = cA.cat_id
84			WHERE cA.cat_id IS NULL
85			UNION
86		SELECT d.name 
87			FROM dogs AS d
88			LEFT OUTER JOIN dog_adoptions AS dA
89			ON d.id = dA.dog_id
90			WHERE dA.dog_id IS NULL;
91
92	- The name of the person who adopted Rosco.
93
94		SELECT a.first_name, a.last_name
95		FROM adopters AS a
96		JOIN dog_adoptions AS d
97		ON a.id = d.adopter_id
98		WHERE d.dog_id = 10007;
99	
100
1019. Using this Library schema and data, write queries applying the following scenarios and include the results:
102	
103	- 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".
104
105		SELECT p.name, h.rank 
106			FROM patrons AS p 
107			JOIN holds AS h 
108			ON p.id = h.patron_id 
109			WHERE isbn = '9136884926';
110
111
112	- List all of the library patrons. If they have one or more books checked out, list the books with the patrons.
113
114		SELECT DISTINCT patrons.name, (SELECT book.title FROM (SELECT transactions.checked_in_date, transactions.patron_id, books.title FROM transactions LEFT OUTER JOIN books ON books.isbn = transactions.isbn WHERE transactions.checked_in_date IS NULL) AS book WHERE book.patron_id = patrons.id) FROM transactions LEFT OUTER JOIN patrons ON transactions.patron_id = patrons.id LEFT OUTER JOIN books ON books.isbn = transactions.isbn;
115
116		(I did the one above with no formatting for readability to see personally what it looks like long form and without being split up. And that is pretty much the worst imo)