BxzpYEic

1How do you find related data held in two separate data tables?
2You can find related data held in two separate data table by using an SQL JOIN clause which combines two tables into one and gives you access to data as you call it.
3
4Explain, in your own words, the difference between an INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN. Give a real-world example for each.
5INNER JOIN
6- selects all rows from both tables where there is a match between the columns.
7- shows all members at the golf club and combines it with the handicap table to see who who should be charged for handicaps
8
9LEFT OUTER JOIN
10-keeps unmatched rows from the left or first table and joins them with a null row in the right or second table.
11-shows all members at the golf club and combines with all members who have registered for the app so the club can determine who to target to register
12
13RIGHT OUTER JOIN
14- opposite of LEFT OUTER JOIN where it keeps unmatched rows form the right or second table and joins them with a null row in the left or first table
15- combining a construction project to do list and assigned workers to determine what is left to do on a project
16
17Define primary key and foreign key. Give a real-world example for each.
18Primary key 
19-an identifier that only exists once in the entire table which will allow you to select a each individual row.
20- an id in a table of list of users for a website
21
22Foreign Key
23- an identifier that is used to link to tables together and refers to the primary key in the other table
24- a user_id in a list of transactions for that website
25
26Define aliasing.
27-aliasing is giving table names nicknames using AS so you can refer to tables easier.
28
29Change this query so that you are using aliasing:
30SELECT professor.name, compensation.salary, compensation.vacation_days 
31FROM professor AS p
32JOIN compensation as c
33ON p.id =c.professor_id;
34
35Why would you use a NATURAL JOIN? Give a real-world example.
36-You would use a NATURAL JOIN to join two tables that have many shared columns to make a master output.
37-An example of this would be combining joining a team roster with statistic tables. You could use the name column to join the two tables.
38
39
40Using this Employee schema and data, write queries to find the following information:
41List all employees and all shifts.
42SELECT e.name, s.date, s.start_time, s.end_time
43FROM employees AS e 
44JOIN scheduled_shifts AS ss
45ON  e.id = ss.employee_id
46JOIN shifts AS s
47ON ss.shift_id = s.id;
48
49Using this Adoption schema and data, please write queries to retrieve the following information and include the results:
50
51Create a list of all volunteers. If the volunteer is fostering a dog, include each dog as well.
52SELECT v.first_name, v.last_name, d.name
53FROM volunteers AS v
54LEFT OUTER JOIN dogs AS d
55ON v.foster_dog_id = d.id;
56
57The 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.
58SELECT c.name, a.first_name, a.last_name, ca.date
59FROM cat_adoptions AS ca
60JOIN cats AS c
61ON c.id = ca.cat_id
62JOIN adopters as a
63ON a.id = ca.adopter_id
64WHERE ca.date >= '01/16/2019';
65
66Create a list of adopters who have not yet chosen a dog to adopt.
67SELECT a.first_name, a.last_name
68FROM adopters AS a
69JOIN dog_adoptions as d
70ON a.id != d.adopter_id;
71
72Lists of all cats and all dogs who have not been adopted.
73
74DELETE FROM cats c
75USING cat_adoptions ca
76WHERE c.id = ca.cat_id;
77
78SELECT c.name
79FROM cats c;
80
81DELETE FROM dogs d
82USING volunteers v
83WHERE d.id = v.foster_dog_id;
84
85SELECT d.name
86FROM dogs d
87JOIN dog_adoptions da
88ON d.id != da.dog_id;
89
90
91The name of the person who adopted Rosco.
92SELECT a.first_name, a.last_name
93FROM adopters AS a
94JOIN dog_adoptions AS da
95ON a.id = da.adopter_id;
96
97Using this Library schema and data, write queries applying the following scenarios and include the results:
98
99To 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".
100SELECT DISTINCT h.rank , p.name
101FROM patrons p
102JOIN holds h
103ON p.id = h.patron_id
104JOIN books b
105ON h.isbn = b.isbn
106WHERE b.title LIKE 'A%';
107
108List all of the library patrons. If they have one or more books checked out, list the books with the patrons.
109
110SELECT p.name, b.title
111FROM patrons p
112JOIN transactions t
113ON p.id = t.patron_id
114JOIN books b
115ON t.isbn = b.isbn
116WHERE t.checked_in_date IS NULL;