· 7 years ago · Oct 25, 2018, 01:54 AM
11. How do you find related data held in two separate data tables?
2A: An SQL JOIN statement can be used to find and retrieve related data stored in multiple tables.
3
42. 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.
5A: Lets consider the tables, Developer (id integer, name varchar(32), experience integer) and Client (id int, developer_id integer) to explain the real world examples.
6
7Inner Join: The INNER JOIN ( or just JOIN) keyword creates a results table with all the rows from the querying tables where the condition satisfies, that is the value of the common field will be the same. This is the default JOIN.
8Example: List the names of developers currently working with a client.
9
10Left Outer Join: LEFT OUTER JOIN (or LEFT JOIN) returns all the rows of the table on the left side of the JOIN and matching rows for the table on the right side of the join. The rows for which there is no matching row on the right side, the results table will contain null.
11Example : List the names, years of experience of all the developers and the clients they are working with. If a developer currently has no clients NULL will be returned for the corresponding client column.
12
13Right Outer Join: RIGHT OUTER JOIN (or RIGHT JOIN) returns all the rows of the table on the right side of the JOIN and matching rows from the table on the left side. The rows for which there is no matching row on the left side, the results table will contain null.
14Example : List all the clients and the developer they are working with. If a client is still looking for a developer(not working with anyone currently), NULL will be returned for the corresponding developer column.
15
163. Define primary key and foreign key. Give a real-world example for each.
17A: Primary key: A primary key is a unique identifier that only exists once in the entire table. It allows you to select a single row of data.
18 Example: In the above ‘Developer’ table, id would be the primary key.
19
20 Foreign key: The primary key of one table that is included as a non-unique attribute in another table. The primary key- foreign key relationship, allows us to link the two tables.
21 In the above ‘Client’ table, id would be the primary key and the developer_id would be the foreign key.
22
234. Define aliasing.
24A: Aliasing is the technique of creating short variable names, usually a single letter, to replace the table name in a query.
25
265. Change this query so that you are using aliasing:
27SELECT professor.name, compensation.salary, compensation.vacation_days
28FROM professor
29JOIN compensation
30ON professor.id =
31compensation.professor_id;
32
33A: 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
386. Why would you use a NATURAL JOIN? Give a real-world example.
39A: A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables. A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join. The default is INNER join.
40
417. Using this Employee schema and data, write queries to find the following information:
42
437a. List all employees and all shifts.
44A:
45 SELECT employees.name, scheduled_shifts.shift_id
46 FROM employees
47 LEFT OUTER JOIN scheduled_shifts
48 ON employees.id = scheduled_shifts.employee_id;
49
50| name | shift_id |
51| ------------------ | -------- |
52| Hermione Granger | 2 |
53| Hermione Granger | 7 |
54| Hermione Granger | 12 |
55| Hermione Granger | 17 |
56| Hermione Granger | 22 |
57| Ronald Weasley | 8 |
58| Ronald Weasley | 18 |
59| Luna Lovegood | 3 |
60| Luna Lovegood | 13 |
61| Luna Lovegood | 23 |
62| Draco Malfoy | 15 |
63| Draco Malfoy | 20 |
64| Draco Malfoy | 25 |
65| Padma Patil | 4 |
66| Padma Patil | 9 |
67| Padma Patil | 14 |
68| Padma Patil | 1 |
69| Padma Patil | 6 |
70| Padma Patil | 11 |
71| Cho Chang | 19 |
72| Cho Chang | 24 |
73| Dean Thomas | 5 |
74| Dean Thomas | 10 |
75| Neville Longbottom | Null |
76| Cedric Diggory | Null |
77
788. Using this Adoption schema and data, please write queries to retrieve the following information and include the results:
79
808a. Create a list of all volunteers. If the volunteer is fostering a dog, include each dog as well.
81A:
82 SELECT volunteers.first_name, volunteers.last_name, dogs.breed, dogs.name
83 FROM volunteers
84 LEFT OUTER JOIN dogs
85 ON volunteers.foster_dog_id = dogs.id;
86
87| first_name | last_name | breed | name |
88| ---------- | ---------- | ------------------ | --------- |
89| Rubeus | Hagrid | dachsund chihuahua | Munchkin |
90| Marjorie | Dursley | great dane | Marmaduke |
91| Sirius | Black | null | null |
92| Remus | Lupin | null | null |
93| Albus | Dumbledore | null | null |
94
958b. 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.
96A:
97 SELECT cats.name, adopters.first_name, adopters.last_name, cat_adoptions.date AS adoption_date
98 FROM cat_adoptions
99 JOIN adopters ON cat_adoptions.adopter_id = adopters.id
100 JOIN cats ON cat_adoptions.cat_id = cats.id
101 WHERE cat_adoptions.date >= (CURRENT_DATE - INTERVAL '30 DAYS');
102
103| name | first_name | last_name | adoption_date |
104| -------- | ---------- | --------- | ------------------------ |
105| Mushi | Arabella | Figg | 2018-10-03T00:00:00.000Z |
106| Victoire | Argus | Filch | 2018-10-08T00:00:00.000Z |
107
1088c. Create a list of adopters who have not yet chosen a dog to adopt.
109A:
110 SELECT adopters.id, adopters.first_name, adopters.last_name
111 FROM adopters
112 LEFT JOIN dog_adoptions
113 ON adopters.id = dog_adoptions.adopter_id
114 WHERE dog_adoptions.adopter_id is NULL;
115
116| id | first_name | last_name |
117| --- | ---------- | --------- |
118| 1 | Hermione | Granger |
119| 2 | Arabella | Figg |
120
1218d. Lists of all cats and all dogs who have not been adopted.
122A:
123 SELECT cats.name
124 FROM cats
125 LEFT JOIN cat_adoptions
126 ON cat_adoptions.cat_id = cats.id
127 WHERE cat_adoptions.cat_id is NULL
128 UNION
129 SELECT dogs.name
130 FROM dogs
131 LEFT JOIN dog_adoptions
132 ON dog_adoptions.dog_id = dogs.id
133 WHERE dog_adoptions.dog_id is NULL;
134
135| name |
136| --------- |
137| Boujee |
138| Lassie |
139| Marley |
140| Marmaduke |
141| Munchkin |
142| Nala |
143| Seashell |
144
1458e.The name of the person who adopted Rosco.
146A:
147 SELECT dogs.name AS dog_name, adopters.first_name AS adopter_first_name, adopters.last_name AS adopter_last_name
148 FROM adopters
149 JOIN dog_adoptions ON adopters.id = dog_adoptions.adopter_id
150 JOIN dogs on dogs.id = dog_id
151 WHERE dogs.name = 'Rosco';
152
153| dog_name | adopter_first_name | adopter_last_name |
154| -------- | ------------------ | ----------------- |
155| Rosco | Argus | Filch |
156
1579. Using this Library schema and data, write queries applying the following scenarios and include the results:
158
1599a. 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".
160
161A:
162 SELECT patrons.name, holds.patron_id, books.title, holds.rank AS position
163 FROM patrons
164 JOIN holds
165 ON holds.patron_id = patrons.id
166 JOIN books ON books.title = 'Advanced Potion-Making'
167 WHERE holds.isbn = '9136884926' ORDER BY holds.rank ASC;
168
169| name | patron_id | title | position |
170| -------------- | --------- | ---------------------- | -------- |
171| Terry Boot | 2 | Advanced Potion-Making | 1 |
172| Cedric Diggory | 5 | Advanced Potion-Making | 2 |
173
1749b. List all of the library patrons. If they have one or more books checked out, list the books with the patrons.
175
176A:
177 SELECT patrons.name AS patron_name, books.title AS checked_out_books
178 FROM patrons
179 JOIN transactions
180 ON patrons.id = transactions.patron_id
181 JOIN books
182 ON books.isbn = transactions.isbn ORDER BY patrons.id;
183
184| patron_name | checked_out_books |
185| ---------------- | --------------------------------------- |
186| Hermione Granger | Fantastic Beasts and Where to Find Them |
187| Hermione Granger | Hogwarts: A History |
188| Terry Boot | Advanced Potion-Making |
189| Terry Boot | Fantastic Beasts and Where to Find Them |
190| Padma Patil | Fantastic Beasts and Where to Find Them |
191| Cho Chang | Advanced Potion-Making |
192| Cedric Diggory | Fantastic Beasts and Where to Find Them |