daily pastebin goal
38%
SHARE
TWEET

Untitled

a guest Feb 16th, 2019 78 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. //1 You use JOIN statements to combine related data together.
  2.  
  3. //2
  4. INNER JOIN combines tables whihc gives one row in the new table for each common row from the tables being called upon.
  5. 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.
  6.  
  7. LEFT OUTER JOIN Results in a new table with all rows from first table and the matching roows from the second table.
  8.  
  9. RIGHT OUTER JOIN  Results in each matched row between two tables and each row from the second table.
  10.  
  11. 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.
  12.  
  13. //3
  14. primary key is the unique identifer for each row in a table. One example is the flight number for a airliner data.
  15. 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.
  16.  
  17. //4 Method of shortening queries by using variables to replace table names.
  18.  
  19. //5
  20. SELECT professor.name, compensation.salary,
  21. compensation.vacation_days FROM professor JOIN
  22. compensation ON professor.id =
  23. compensation.professor_id;
  24.  
  25.  
  26. SELECT p.name, c.salary, c.vacation_days
  27. FROM professor AS p
  28. JOIN compensation AS c
  29. ON p.id = c.professor_id;
  30.  
  31. //6
  32. 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.
  33.  
  34. //7
  35. SELECT employees.name, shifts.date, shifts.start_time, shifts.end_time
  36. FROM employees
  37. JOIN shifts
  38. ON employees.id = shifts.id;
  39.  
  40. //8
  41. 8a.
  42.     SELECT volunteers.first_name, volunteers.last_name, dogs.name, dogs.breed
  43.     FROM volunteers
  44.     LEFT OUTER JOIN dogs
  45.     ON volunteers.foster_dog_id = dogs.id;
  46.  
  47. | first_name | last_name  | name      | breed              |
  48. | ---------- | ---------- | --------- | ------------------ |
  49. | Rubeus     | Hagrid     | Munchkin  | dachsund chihuahua |
  50. | Marjorie   | Dursley    | Marmaduke | great dane         |
  51. | Sirius     | Black      |           |                    |
  52. | Remus      | Lupin      |           |                    |
  53. | Albus      | Dumbledore |           |                    |
  54.  
  55. 8b.
  56.     SELECT cats.name, cat_adoptions.date, adopters.first_name, adopters.last_name
  57.     FROM cats
  58.     RIGHT OUTER JOIN cat_adoptions ON cats.id = cat_adoptions.cat_id
  59.     INNER JOIN adopters ON cat_adoptions.adopter_id = adopters.id
  60.     WHERE cat_adoptions.date > CURRENT_DATE - INTERVAL '30 DAYS';
  61.  
  62. | name     | date                     | first_name | last_name |
  63. | -------- | ------------------------ | ---------- | --------- |
  64. | Mushi    | 2019-01-25T00:00:00.000Z | Arabella   | Figg      |
  65. | Victoire | 2019-01-30T00:00:00.000Z | Argus      | Filch     |
  66.  
  67. 8c.
  68.     SELECT adopters.first_name, adopters.last_name, adopters.phone_number
  69.     FROM adopters
  70.     LEFT JOIN dog_adoptions
  71.     ON adopters.id = dog_adoptions.adopter_id
  72.     WHERE dog_adoptions IS NULL;
  73.  
  74. | first_name | last_name | phone_number |
  75. | ---------- | --------- | ------------ |
  76. | Arabella   | Figg      | 843-228-5239 |
  77. | Hermione   | Granger   | 676-464-7837 |
  78.  
  79. 8d.
  80.     SELECT cats.name, cats.intake_date
  81.     FROM cats
  82.     LEFT JOIN cat_adoptions
  83.     ON cats.id = cat_adoptions.cat_id
  84.     WHERE cat_adoptions IS NULL;
  85.  
  86. | name     | intake_date              |
  87. | -------- | ------------------------ |
  88. | Seashell | 2016-01-09T00:00:00.000Z |
  89. | Nala     | 2016-01-12T00:00:00.000Z |
  90.  
  91. ---
  92. **Query #2**
  93.  
  94.     SELECT dogs.name, dogs.intake_date
  95.     FROM dogs
  96.     LEFT JOIN dog_adoptions
  97.     ON dogs.id = dog_adoptions.dog_id
  98.     WHERE dog_adoptions IS NULL;
  99.  
  100. | name      | intake_date              |
  101. | --------- | ------------------------ |
  102. | Munchkin  | 2017-01-13T00:00:00.000Z |
  103. | Boujee    | 2017-06-22T00:00:00.000Z |
  104. | Lassie    | 2016-07-05T00:00:00.000Z |
  105. | Marley    | 2017-05-04T00:00:00.000Z |
  106. | Marmaduke | 2016-03-22T00:00:00.000Z |
  107.  
  108. 8e.
  109.     SELECT adopters.first_name, adopters.last_name
  110.     FROM adopters
  111.     INNER JOIN dog_adoptions
  112.     ON adopters.id = dog_adoptions.adopter_id;
  113.  
  114. | first_name | last_name |
  115. | ---------- | --------- |
  116. | Argus      | Filch     |
  117.  
  118. //9
  119. 9a.
  120.     SELECT patrons.name, holds.rank, holds.date
  121.     FROM patrons
  122.     JOIN holds
  123.     ON patrons.id = holds.patron_id
  124.     WHERE isbn = '9136884926';
  125.  
  126. | name           | rank | date                     |
  127. | -------------- | ---- | ------------------------ |
  128. | Terry Boot     | 1    | 2019-02-12T00:00:00.000Z |
  129. | Cedric Diggory | 2    | 2019-02-13T00:00:00.000Z |
  130.  
  131. 9b.
  132.     SELECT patrons.name, books.title
  133.     FROM patrons
  134.     LEFT JOIN (SELECT *
  135.                FROM transactions
  136.                WHERE checked_in_date IS NULL
  137.                ) as t
  138.     ON patrons.id = t.patron_id
  139.     LEFT JOIN books
  140.     ON t.isbn = books.isbn;
  141.  
  142. | name             | title                                   |
  143. | ---------------- | --------------------------------------- |
  144. | Hermione Granger |                                         |
  145. | Terry Boot       | Advanced Potion-Making                  |
  146. | Padma Patil      |                                         |
  147. | Cho Chang        |                                         |
  148. | Cedric Diggory   | Fantastic Beasts and Where to Find Them |
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top