Advertisement
desislava_topuzakova

Subqueries and JOINs - Exercise

Oct 3rd, 2022
1,483
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 4.80 KB | None | 0 0
  1. # 1. Employee Address
  2. SELECT e.`employee_id`, e.`job_title`, e.`address_id`, a.`address_text`
  3. FROM `employees` AS e
  4. INNER JOIN `addresses` AS a
  5. ON e.`address_id` = a.`address_id`
  6. ORDER BY a.`address_id`
  7. LIMIT 5;
  8.  
  9. # 2. Addresses with Towns
  10. SELECT e.`first_name`, e.`last_name`, t.`name`, a.`address_text`
  11. FROM `employees` AS e
  12. INNER JOIN `addresses` AS a
  13. ON e.`address_id` = a.`address_id`
  14. INNER JOIN `towns` AS t
  15. ON a.`town_id` = t.`town_id`
  16. ORDER BY e.`first_name`, e.`last_name`
  17. LIMIT 5;
  18.  
  19. # 3. Sales Employee
  20. SELECT e. `employee_id`, e.`first_name`, e.`last_name`, d.`name`
  21. FROM `employees` AS e
  22. INNER JOIN `departments` AS d
  23. ON e.`department_id` = d.`department_id`
  24. WHERE d.`name` = 'Sales'
  25. ORDER BY e.`employee_id` DESC;
  26.  
  27. # 4. Employee Departments
  28. SELECT e. `employee_id`, e.`first_name`, e.`salary`, d.`name`
  29. FROM `employees` AS e
  30. INNER JOIN `departments` AS d
  31. ON e.`department_id` = d.`department_id`
  32. WHERE e.`salary` > 15000
  33. ORDER BY d.`department_id` DESC
  34. LIMIT 5;
  35.  
  36. # 5. Employees Without Project
  37. SELECT e.`employee_id`, e.`first_name`
  38. FROM `employees` AS e
  39. LEFT JOIN `employees_projects` AS p
  40. ON e.`employee_id` = p.`employee_id`
  41. WHERE p.`project_id` IS NULL
  42. ORDER BY e.`employee_id` DESC
  43. LIMIT 3;
  44.  
  45. # 6. Employees Hired After
  46. SELECT e.`first_name`, e.`last_name`, e.`hire_date`, d.`name`
  47. FROM `employees` AS e
  48. INNER JOIN `departments` AS d
  49. ON e.`department_id` = d.`department_id`
  50. WHERE d.`name` IN ('Sales', 'Finance') AND e.`hire_date` > '1999-01-01 00:00:00'
  51. ORDER BY e.`hire_date`;
  52.  
  53. # 7. Employees with Project
  54. SELECT
  55.     e.`employee_id`, e.`first_name`, p.`name` AS `project_name`
  56. FROM
  57.     `employees` AS e
  58.         JOIN
  59.     `employees_projects` AS ep ON ep.`employee_id` = e.`employee_id`
  60.         JOIN
  61.     `projects` AS p ON ep.`project_id` = p.`project_id`
  62. WHERE
  63.     DATE(p.`start_date`) > '2002-08-13'
  64.         AND p.`end_date` IS NULL
  65. ORDER BY e.`first_name` , p.`name`
  66. LIMIT 5;
  67.  
  68. # 8. Employee 24
  69. SELECT
  70.     e.`employee_id`,
  71.     e.`first_name`,
  72.     IF(YEAR(p.`start_date`) >= 2005, NULL, p.`name`) AS 'project_name'
  73. FROM
  74.     `employees` AS e
  75.         JOIN
  76.     `employees_projects` AS ep ON ep.`employee_id` = e.`employee_id`
  77.         JOIN
  78.     `projects` AS p ON ep.`project_id` = p.`project_id`
  79. WHERE e.`employee_id` = 24
  80. ORDER BY p.`name`;
  81.  
  82. # 9. Employee Manager
  83. SELECT e.`employee_id`, e.`first_name`, e.`manager_id`, m.`first_name`
  84. FROM `employees` AS e
  85. JOIN `employees` AS m ON e.`manager_id` = m.`employee_id`
  86. WHERE e.`manager_id` IN (3 , 7)
  87. ORDER BY e.`first_name`;
  88.  
  89. # 10. Employee Summary
  90. SELECT
  91.     e.`employee_id`,
  92.     CONCAT(e.`first_name`, ' ', e.`last_name`) AS 'employee_name',
  93.     CONCAT(m.`first_name`, ' ', m.`last_name`) AS 'manager_name',
  94.     d.`name` AS 'department_name'
  95. FROM `employees` AS e
  96. JOIN `employees` AS m ON e.`manager_id` = m.`employee_id`
  97. JOIN `departments` AS d ON e.`department_id` = d.`department_id`
  98. ORDER BY e.`employee_id`
  99. LIMIT 5;
  100.  
  101. # 11. Min Average Salary
  102. SELECT AVG(`salary`) AS 'min_average_salary'
  103. FROM `employees`
  104. GROUP BY `department_id`
  105. ORDER BY `min_average_salary`
  106. LIMIT 1;
  107.  
  108. # 12. Highest Peaks in Bulgaria
  109. SELECT c.`country_code`, m.`mountain_range`, p.`peak_name`, p.`elevation`
  110. FROM `peaks` AS p
  111. INNER JOIN `mountains` AS m ON p.`mountain_id` = m.`id`
  112. INNER JOIN `mountains_countries` AS c ON m.`id` = c.`mountain_id`
  113. WHERE c.`country_code` = 'BG' AND p.`elevation` > 2835
  114. ORDER BY p.`elevation` DESC;
  115.  
  116. # 13. Count Mountain Ranges
  117. SELECT c.`country_code`, COUNT(m.`mountain_range`) AS `mountain_range` FROM `mountains` AS m
  118. INNER JOIN `mountains_countries` AS c ON m.`id` = c.`mountain_id`
  119. WHERE c.`country_code` IN ('BG', 'RU', 'US')
  120. GROUP BY c.`country_code`
  121. ORDER BY `mountain_range` DESC;
  122.  
  123. # 14. Countries with Rivers
  124. SELECT c.`country_name`, r.`river_name`
  125. FROM `countries` AS c
  126. LEFT JOIN `countries_rivers` AS cr ON c.`country_code` = cr.`country_code`
  127. LEFT JOIN `rivers` AS r ON r.`id` = cr.`river_id`
  128. WHERE c.`continent_code` = 'AF'
  129. ORDER BY c.`country_name`
  130. LIMIT 5;
  131.  
  132. # 16. Countries without any Mountains
  133. SELECT COUNT(c.`country_code`)
  134. FROM `countries` AS c
  135. LEFT JOIN `mountains_countries` AS mc ON c.`country_code` = mc.`country_code`
  136. LEFT JOIN `mountains` AS m ON mc.`mountain_id` = m.`id`
  137. WHERE m.`id` IS NULL;
  138.  
  139. # 17. Highest Peak and Longest River by Country
  140. SELECT c.`country_name`, MAX(p.`elevation`) AS 'highest_peak_elevation',MAX(r.`length`) AS 'longest_river_length'
  141. FROM `countries` AS c
  142. LEFT JOIN `mountains_countries` AS mc ON c.`country_code` = mc.`country_code`
  143. LEFT JOIN `peaks` AS p ON mc.`mountain_id` = p.`mountain_id`
  144. LEFT JOIN `countries_rivers` AS cr ON c.`country_code` = cr.`country_code`
  145. LEFT JOIN `rivers` AS r ON cr.`river_id` = r.`id`
  146. GROUP BY c.`country_name`
  147. ORDER BY `highest_peak_elevation` DESC , `longest_river_length` DESC , c.`country_name`
  148. LIMIT 5;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement