API tools faq
paste
Advertisement
Evgeny_Baulin

Untitled

Evgeny_Baulin
Oct 29th, 2024
13
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 44.87 KB | None | 0 0
raw download clone embed print report
  1. ## Задача 1
  2.  
  3. ```postgresql
  4. SELECT employee_id,
  5. salary,
  6. MAX(salary) OVER () AS max_salary,
  7. ROUND(MAX(salary) OVER () / salary, 2) AS t
  8. FROM employees;
  9. ```
  10.  
  11. | employee_id | salary | max_salary | t |
  12. |-------------|----------|------------|-------|
  13. | 100 | 24000.00 | 24000 | 1.00 |
  14. | 101 | 17000.00 | 24000 | 1.41 |
  15. | 102 | 17000.00 | 24000 | 1.41 |
  16. | 103 | 9000.00 | 24000 | 2.67 |
  17. | 104 | 6000.00 | 24000 | 4.00 |
  18. | 105 | 4800.00 | 24000 | 5.00 |
  19. | 107 | 4200.00 | 24000 | 5.71 |
  20. | 108 | 12008.00 | 24000 | 2.00 |
  21. | 109 | 9000.00 | 24000 | 2.67 |
  22. | 110 | 8200.00 | 24000 | 2.93 |
  23. | 111 | 7700.00 | 24000 | 3.12 |
  24. | 112 | 7800.00 | 24000 | 3.08 |
  25. | 113 | 6900.00 | 24000 | 3.48 |
  26. | 114 | 11000.00 | 24000 | 2.18 |
  27. | 115 | 3100.00 | 24000 | 7.74 |
  28. | 116 | 2900.00 | 24000 | 8.28 |
  29. | 117 | 2800.00 | 24000 | 8.57 |
  30. | 118 | 2600.00 | 24000 | 9.23 |
  31. | 119 | 2500.00 | 24000 | 9.60 |
  32. | 120 | 8000.00 | 24000 | 3.00 |
  33. | 121 | 8200.00 | 24000 | 2.93 |
  34. | 122 | 7900.00 | 24000 | 3.04 |
  35. | 123 | 6500.00 | 24000 | 3.69 |
  36. | 124 | 5800.00 | 24000 | 4.14 |
  37. | 125 | 3200.00 | 24000 | 7.50 |
  38. | 126 | 2700.00 | 24000 | 8.89 |
  39. | 127 | 2400.00 | 24000 | 10.00 |
  40. | 128 | 2200.00 | 24000 | 10.91 |
  41. | 129 | 3300.00 | 24000 | 7.27 |
  42. | 130 | 2800.00 | 24000 | 8.57 |
  43. | 131 | 2500.00 | 24000 | 9.60 |
  44. | 132 | 2100.00 | 24000 | 11.43 |
  45. | 133 | 3300.00 | 24000 | 7.27 |
  46. | 134 | 2900.00 | 24000 | 8.28 |
  47. | 135 | 2400.00 | 24000 | 10.00 |
  48. | 136 | 2200.00 | 24000 | 10.91 |
  49. | 137 | 3600.00 | 24000 | 6.67 |
  50. | 138 | 3200.00 | 24000 | 7.50 |
  51. | 139 | 2700.00 | 24000 | 8.89 |
  52. | 140 | 2500.00 | 24000 | 9.60 |
  53. | 141 | 3500.00 | 24000 | 6.86 |
  54. | 142 | 3100.00 | 24000 | 7.74 |
  55. | 143 | 2600.00 | 24000 | 9.23 |
  56. | 144 | 2500.00 | 24000 | 9.60 |
  57. | 145 | 14000.00 | 24000 | 1.71 |
  58. | 146 | 13500.00 | 24000 | 1.78 |
  59. | 147 | 12000.00 | 24000 | 2.00 |
  60. | 148 | 11000.00 | 24000 | 2.18 |
  61. | 149 | 10500.00 | 24000 | 2.29 |
  62. | 150 | 10000.00 | 24000 | 2.40 |
  63. | 151 | 9500.00 | 24000 | 2.53 |
  64. | 152 | 9000.00 | 24000 | 2.67 |
  65. | 153 | 8000.00 | 24000 | 3.00 |
  66. | 154 | 7500.00 | 24000 | 3.20 |
  67. | 155 | 7000.00 | 24000 | 3.43 |
  68. | 156 | 10000.00 | 24000 | 2.40 |
  69. | 157 | 9500.00 | 24000 | 2.53 |
  70. | 158 | 9000.00 | 24000 | 2.67 |
  71. | 159 | 8000.00 | 24000 | 3.00 |
  72. | 160 | 7500.00 | 24000 | 3.20 |
  73. | 161 | 7000.00 | 24000 | 3.43 |
  74. | 162 | 10500.00 | 24000 | 2.29 |
  75. | 163 | 9500.00 | 24000 | 2.53 |
  76. | 164 | 7200.00 | 24000 | 3.33 |
  77. | 165 | 6800.00 | 24000 | 3.53 |
  78. | 166 | 6400.00 | 24000 | 3.75 |
  79. | 167 | 6200.00 | 24000 | 3.87 |
  80. | 168 | 11500.00 | 24000 | 2.09 |
  81. | 169 | 10000.00 | 24000 | 2.40 |
  82. | 170 | 9600.00 | 24000 | 2.50 |
  83. | 171 | 7400.00 | 24000 | 3.24 |
  84. | 172 | 7300.00 | 24000 | 3.29 |
  85. | 173 | 6100.00 | 24000 | 3.93 |
  86. | 174 | 11000.00 | 24000 | 2.18 |
  87. | 175 | 8800.00 | 24000 | 2.73 |
  88. | 176 | 8600.00 | 24000 | 2.79 |
  89. | 177 | 8400.00 | 24000 | 2.86 |
  90. | 179 | 6200.00 | 24000 | 3.87 |
  91. | 180 | 3200.00 | 24000 | 7.50 |
  92. | 181 | 3100.00 | 24000 | 7.74 |
  93. | 182 | 2500.00 | 24000 | 9.60 |
  94. | 183 | 2800.00 | 24000 | 8.57 |
  95. | 184 | 4200.00 | 24000 | 5.71 |
  96. | 185 | 4100.00 | 24000 | 5.85 |
  97. | 186 | 3400.00 | 24000 | 7.06 |
  98. | 187 | 3000.00 | 24000 | 8.00 |
  99. | 188 | 3800.00 | 24000 | 6.32 |
  100. | 189 | 3600.00 | 24000 | 6.67 |
  101. | 190 | 2900.00 | 24000 | 8.28 |
  102. | 191 | 2500.00 | 24000 | 9.60 |
  103. | 192 | 4000.00 | 24000 | 6.00 |
  104. | 193 | 3900.00 | 24000 | 6.15 |
  105. | 194 | 3200.00 | 24000 | 7.50 |
  106. | 195 | 2800.00 | 24000 | 8.57 |
  107. | 196 | 3100.00 | 24000 | 7.74 |
  108. | 197 | 3000.00 | 24000 | 8.00 |
  109. | 198 | 2600.00 | 24000 | 9.23 |
  110. | 199 | 2600.00 | 24000 | 9.23 |
  111. | 200 | 4400.00 | 24000 | 5.45 |
  112. | 201 | 13000.00 | 24000 | 1.85 |
  113. | 202 | 6000.00 | 24000 | 4.00 |
  114. | 203 | 6500.00 | 24000 | 3.69 |
  115. | 204 | 10000.00 | 24000 | 2.40 |
  116. | 205 | 12008.00 | 24000 | 2.00 |
  117. | 206 | 8300.00 | 24000 | 2.89 |
  118. | 106 | 7000.00 | 24000 | 3.43 |
  119. | 500 | 1000.00 | 24000 | 24.00 |
  120. | 178 | 7000.00 | 24000 | 3.43 |
  121.  
  122. ## Задача 2
  123.  
  124. ```postgresql
  125. SELECT employee_id,
  126. salary,
  127. AVG(salary) OVER (PARTITION BY department_id) AS avg_salary_by_department,
  128. ROUND(salary / AVG(salary) OVER (PARTITION BY department_id), 2) AS t
  129. FROM employees;
  130. ```
  131.  
  132. | employee_id | salary | avg_salary_by_department | t |
  133. |-------------|----------|--------------------------|------|
  134. | 200 | 4400.00 | 4400 | 1.00 |
  135. | 201 | 13000.00 | 9500 | 1.37 |
  136. | 202 | 6000.00 | 9500 | 0.63 |
  137. | 115 | 3100.00 | 4150 | 0.75 |
  138. | 114 | 11000.00 | 4150 | 2.65 |
  139. | 119 | 2500.00 | 4150 | 0.60 |
  140. | 118 | 2600.00 | 4150 | 0.63 |
  141. | 117 | 2800.00 | 4150 | 0.67 |
  142. | 116 | 2900.00 | 4150 | 0.70 |
  143. | 203 | 6500.00 | 6500 | 1.00 |
  144. | 127 | 2400.00 | 3421.74 | 0.70 |
  145. | 120 | 8000.00 | 3421.74 | 2.34 |
  146. | 121 | 8200.00 | 3421.74 | 2.40 |
  147. | 122 | 7900.00 | 3421.74 | 2.31 |
  148. | 123 | 6500.00 | 3421.74 | 1.90 |
  149. | 124 | 5800.00 | 3421.74 | 1.70 |
  150. | 125 | 3200.00 | 3421.74 | 0.94 |
  151. | 126 | 2700.00 | 3421.74 | 0.79 |
  152. | 128 | 2200.00 | 3421.74 | 0.64 |
  153. | 129 | 3300.00 | 3421.74 | 0.96 |
  154. | 130 | 2800.00 | 3421.74 | 0.82 |
  155. | 131 | 2500.00 | 3421.74 | 0.73 |
  156. | 132 | 2100.00 | 3421.74 | 0.61 |
  157. | 133 | 3300.00 | 3421.74 | 0.96 |
  158. | 134 | 2900.00 | 3421.74 | 0.85 |
  159. | 135 | 2400.00 | 3421.74 | 0.70 |
  160. | 136 | 2200.00 | 3421.74 | 0.64 |
  161. | 137 | 3600.00 | 3421.74 | 1.05 |
  162. | 138 | 3200.00 | 3421.74 | 0.94 |
  163. | 139 | 2700.00 | 3421.74 | 0.79 |
  164. | 140 | 2500.00 | 3421.74 | 0.73 |
  165. | 141 | 3500.00 | 3421.74 | 1.02 |
  166. | 142 | 3100.00 | 3421.74 | 0.91 |
  167. | 143 | 2600.00 | 3421.74 | 0.76 |
  168. | 144 | 2500.00 | 3421.74 | 0.73 |
  169. | 180 | 3200.00 | 3421.74 | 0.94 |
  170. | 181 | 3100.00 | 3421.74 | 0.91 |
  171. | 182 | 2500.00 | 3421.74 | 0.73 |
  172. | 183 | 2800.00 | 3421.74 | 0.82 |
  173. | 184 | 4200.00 | 3421.74 | 1.23 |
  174. | 185 | 4100.00 | 3421.74 | 1.20 |
  175. | 186 | 3400.00 | 3421.74 | 0.99 |
  176. | 187 | 3000.00 | 3421.74 | 0.88 |
  177. | 188 | 3800.00 | 3421.74 | 1.11 |
  178. | 189 | 3600.00 | 3421.74 | 1.05 |
  179. | 190 | 2900.00 | 3421.74 | 0.85 |
  180. | 191 | 2500.00 | 3421.74 | 0.73 |
  181. | 192 | 4000.00 | 3421.74 | 1.17 |
  182. | 193 | 3900.00 | 3421.74 | 1.14 |
  183. | 194 | 3200.00 | 3421.74 | 0.94 |
  184. | 195 | 2800.00 | 3421.74 | 0.82 |
  185. | 196 | 3100.00 | 3421.74 | 0.91 |
  186. | 197 | 3000.00 | 3421.74 | 0.88 |
  187. | 198 | 2600.00 | 3421.74 | 0.76 |
  188. | 199 | 2600.00 | 3421.74 | 0.76 |
  189. | 500 | 1000.00 | 3421.74 | 0.29 |
  190. | 103 | 9000.00 | 6200 | 1.45 |
  191. | 104 | 6000.00 | 6200 | 0.97 |
  192. | 106 | 7000.00 | 6200 | 1.13 |
  193. | 105 | 4800.00 | 6200 | 0.77 |
  194. | 107 | 4200.00 | 6200 | 0.68 |
  195. | 204 | 10000.00 | 10000 | 1.00 |
  196. | 163 | 9500.00 | 8900 | 1.07 |
  197. | 156 | 10000.00 | 8900 | 1.12 |
  198. | 158 | 9000.00 | 8900 | 1.01 |
  199. | 159 | 8000.00 | 8900 | 0.90 |
  200. | 160 | 7500.00 | 8900 | 0.84 |
  201. | 161 | 7000.00 | 8900 | 0.79 |
  202. | 162 | 10500.00 | 8900 | 1.18 |
  203. | 146 | 13500.00 | 8900 | 1.52 |
  204. | 147 | 12000.00 | 8900 | 1.35 |
  205. | 148 | 11000.00 | 8900 | 1.24 |
  206. | 149 | 10500.00 | 8900 | 1.18 |
  207. | 150 | 10000.00 | 8900 | 1.12 |
  208. | 151 | 9500.00 | 8900 | 1.07 |
  209. | 152 | 9000.00 | 8900 | 1.01 |
  210. | 153 | 8000.00 | 8900 | 0.90 |
  211. | 154 | 7500.00 | 8900 | 0.84 |
  212. | 155 | 7000.00 | 8900 | 0.79 |
  213. | 157 | 9500.00 | 8900 | 1.07 |
  214. | 164 | 7200.00 | 8900 | 0.81 |
  215. | 165 | 6800.00 | 8900 | 0.76 |
  216. | 166 | 6400.00 | 8900 | 0.72 |
  217. | 167 | 6200.00 | 8900 | 0.70 |
  218. | 168 | 11500.00 | 8900 | 1.29 |
  219. | 169 | 10000.00 | 8900 | 1.12 |
  220. | 170 | 9600.00 | 8900 | 1.08 |
  221. | 171 | 7400.00 | 8900 | 0.83 |
  222. | 172 | 7300.00 | 8900 | 0.82 |
  223. | 173 | 6100.00 | 8900 | 0.69 |
  224. | 174 | 11000.00 | 8900 | 1.24 |
  225. | 175 | 8800.00 | 8900 | 0.99 |
  226. | 176 | 8600.00 | 8900 | 0.97 |
  227. | 177 | 8400.00 | 8900 | 0.94 |
  228. | 179 | 6200.00 | 8900 | 0.70 |
  229. | 178 | 7000.00 | 8900 | 0.79 |
  230. | 145 | 14000.00 | 8900 | 1.57 |
  231. | 102 | 17000.00 | 19333.33 | 0.88 |
  232. | 101 | 17000.00 | 19333.33 | 0.88 |
  233. | 100 | 24000.00 | 19333.33 | 1.24 |
  234. | 113 | 6900.00 | 8601.33 | 0.80 |
  235. | 112 | 7800.00 | 8601.33 | 0.91 |
  236. | 110 | 8200.00 | 8601.33 | 0.95 |
  237. | 109 | 9000.00 | 8601.33 | 1.05 |
  238. | 111 | 7700.00 | 8601.33 | 0.90 |
  239. | 108 | 12008.00 | 8601.33 | 1.40 |
  240. | 206 | 8300.00 | 10154 | 0.82 |
  241. | 205 | 12008.00 | 10154 | 1.18 |
  242.  
  243. ## Задача 3
  244.  
  245. ```postgresql
  246. SELECT e.employee_id,
  247. e.salary,
  248. AVG(e.salary) OVER (PARTITION BY e.department_id) AS avg_salary_by_department,
  249. AVG(e.salary) OVER (PARTITION BY e.job_id) AS avg_salary_by_job,
  250. ROUND(AVG(e.salary) OVER (PARTITION BY e.department_id) /
  251. AVG(e.salary) OVER (PARTITION BY e.job_id), 2) AS ratio
  252. FROM employees e;
  253. ```
  254.  
  255. | employee_id | salary | avg_salary_by_department | avg_salary_by_job | ratio |
  256. |-------------|----------|--------------------------|-------------------|-------|
  257. | 200 | 4400.00 | 4400 | 4400 | 1.00 |
  258. | 202 | 6000.00 | 9500 | 6000 | 1.58 |
  259. | 201 | 13000.00 | 9500 | 13000 | 0.73 |
  260. | 117 | 2800.00 | 4150 | 2483.33 | 1.67 |
  261. | 119 | 2500.00 | 4150 | 2483.33 | 1.67 |
  262. | 118 | 2600.00 | 4150 | 2483.33 | 1.67 |
  263. | 116 | 2900.00 | 4150 | 2483.33 | 1.67 |
  264. | 115 | 3100.00 | 4150 | 2483.33 | 1.67 |
  265. | 114 | 11000.00 | 4150 | 11000 | 0.38 |
  266. | 203 | 6500.00 | 6500 | 6500 | 1.00 |
  267. | 132 | 2100.00 | 3421.74 | 2785 | 1.23 |
  268. | 144 | 2500.00 | 3421.74 | 2785 | 1.23 |
  269. | 143 | 2600.00 | 3421.74 | 2785 | 1.23 |
  270. | 124 | 5800.00 | 3421.74 | 7280 | 0.47 |
  271. | 123 | 6500.00 | 3421.74 | 7280 | 0.47 |
  272. | 142 | 3100.00 | 3421.74 | 2785 | 1.23 |
  273. | 500 | 1000.00 | 3421.74 | 2483.33 | 1.38 |
  274. | 141 | 3500.00 | 3421.74 | 2785 | 1.23 |
  275. | 140 | 2500.00 | 3421.74 | 2785 | 1.23 |
  276. | 139 | 2700.00 | 3421.74 | 2785 | 1.23 |
  277. | 138 | 3200.00 | 3421.74 | 2785 | 1.23 |
  278. | 137 | 3600.00 | 3421.74 | 2785 | 1.23 |
  279. | 136 | 2200.00 | 3421.74 | 2785 | 1.23 |
  280. | 135 | 2400.00 | 3421.74 | 2785 | 1.23 |
  281. | 134 | 2900.00 | 3421.74 | 2785 | 1.23 |
  282. | 125 | 3200.00 | 3421.74 | 2785 | 1.23 |
  283. | 126 | 2700.00 | 3421.74 | 2785 | 1.23 |
  284. | 127 | 2400.00 | 3421.74 | 2785 | 1.23 |
  285. | 128 | 2200.00 | 3421.74 | 2785 | 1.23 |
  286. | 133 | 3300.00 | 3421.74 | 2785 | 1.23 |
  287. | 187 | 3000.00 | 3421.74 | 3215 | 1.06 |
  288. | 186 | 3400.00 | 3421.74 | 3215 | 1.06 |
  289. | 185 | 4100.00 | 3421.74 | 3215 | 1.06 |
  290. | 184 | 4200.00 | 3421.74 | 3215 | 1.06 |
  291. | 183 | 2800.00 | 3421.74 | 3215 | 1.06 |
  292. | 182 | 2500.00 | 3421.74 | 3215 | 1.06 |
  293. | 181 | 3100.00 | 3421.74 | 3215 | 1.06 |
  294. | 196 | 3100.00 | 3421.74 | 3215 | 1.06 |
  295. | 199 | 2600.00 | 3421.74 | 3215 | 1.06 |
  296. | 195 | 2800.00 | 3421.74 | 3215 | 1.06 |
  297. | 180 | 3200.00 | 3421.74 | 3215 | 1.06 |
  298. | 194 | 3200.00 | 3421.74 | 3215 | 1.06 |
  299. | 188 | 3800.00 | 3421.74 | 3215 | 1.06 |
  300. | 189 | 3600.00 | 3421.74 | 3215 | 1.06 |
  301. | 190 | 2900.00 | 3421.74 | 3215 | 1.06 |
  302. | 192 | 4000.00 | 3421.74 | 3215 | 1.06 |
  303. | 191 | 2500.00 | 3421.74 | 3215 | 1.06 |
  304. | 193 | 3900.00 | 3421.74 | 3215 | 1.06 |
  305. | 198 | 2600.00 | 3421.74 | 3215 | 1.06 |
  306. | 197 | 3000.00 | 3421.74 | 3215 | 1.06 |
  307. | 122 | 7900.00 | 3421.74 | 7280 | 0.47 |
  308. | 121 | 8200.00 | 3421.74 | 7280 | 0.47 |
  309. | 120 | 8000.00 | 3421.74 | 7280 | 0.47 |
  310. | 129 | 3300.00 | 3421.74 | 2785 | 1.23 |
  311. | 130 | 2800.00 | 3421.74 | 2785 | 1.23 |
  312. | 131 | 2500.00 | 3421.74 | 2785 | 1.23 |
  313. | 104 | 6000.00 | 6200 | 6000 | 1.03 |
  314. | 105 | 4800.00 | 6200 | 6000 | 1.03 |
  315. | 107 | 4200.00 | 6200 | 6000 | 1.03 |
  316. | 103 | 9000.00 | 6200 | 6000 | 1.03 |
  317. | 106 | 7000.00 | 6200 | 7000 | 0.89 |
  318. | 204 | 10000.00 | 10000 | 10000 | 1.00 |
  319. | 177 | 8400.00 | 8900 | 8350 | 1.07 |
  320. | 176 | 8600.00 | 8900 | 8350 | 1.07 |
  321. | 175 | 8800.00 | 8900 | 8350 | 1.07 |
  322. | 174 | 11000.00 | 8900 | 8350 | 1.07 |
  323. | 173 | 6100.00 | 8900 | 8350 | 1.07 |
  324. | 172 | 7300.00 | 8900 | 8350 | 1.07 |
  325. | 171 | 7400.00 | 8900 | 8350 | 1.07 |
  326. | 170 | 9600.00 | 8900 | 8350 | 1.07 |
  327. | 169 | 10000.00 | 8900 | 8350 | 1.07 |
  328. | 178 | 7000.00 | 8900 | 8350 | 1.07 |
  329. | 166 | 6400.00 | 8900 | 8350 | 1.07 |
  330. | 165 | 6800.00 | 8900 | 8350 | 1.07 |
  331. | 164 | 7200.00 | 8900 | 8350 | 1.07 |
  332. | 163 | 9500.00 | 8900 | 8350 | 1.07 |
  333. | 162 | 10500.00 | 8900 | 8350 | 1.07 |
  334. | 161 | 7000.00 | 8900 | 8350 | 1.07 |
  335. | 160 | 7500.00 | 8900 | 8350 | 1.07 |
  336. | 159 | 8000.00 | 8900 | 8350 | 1.07 |
  337. | 158 | 9000.00 | 8900 | 8350 | 1.07 |
  338. | 157 | 9500.00 | 8900 | 8350 | 1.07 |
  339. | 156 | 10000.00 | 8900 | 8350 | 1.07 |
  340. | 155 | 7000.00 | 8900 | 8350 | 1.07 |
  341. | 154 | 7500.00 | 8900 | 8350 | 1.07 |
  342. | 153 | 8000.00 | 8900 | 8350 | 1.07 |
  343. | 152 | 9000.00 | 8900 | 8350 | 1.07 |
  344. | 151 | 9500.00 | 8900 | 8350 | 1.07 |
  345. | 150 | 10000.00 | 8900 | 8350 | 1.07 |
  346. | 168 | 11500.00 | 8900 | 8350 | 1.07 |
  347. | 145 | 14000.00 | 8900 | 12200 | 0.73 |
  348. | 147 | 12000.00 | 8900 | 12200 | 0.73 |
  349. | 148 | 11000.00 | 8900 | 12200 | 0.73 |
  350. | 149 | 10500.00 | 8900 | 12200 | 0.73 |
  351. | 146 | 13500.00 | 8900 | 12200 | 0.73 |
  352. | 167 | 6200.00 | 8900 | 8350 | 1.07 |
  353. | 179 | 6200.00 | 8900 | 8350 | 1.07 |
  354. | 101 | 17000.00 | 19333.33 | 17000 | 1.14 |
  355. | 102 | 17000.00 | 19333.33 | 17000 | 1.14 |
  356. | 100 | 24000.00 | 19333.33 | 24000 | 0.81 |
  357. | 112 | 7800.00 | 8601.33 | 7920 | 1.09 |
  358. | 111 | 7700.00 | 8601.33 | 7920 | 1.09 |
  359. | 113 | 6900.00 | 8601.33 | 7920 | 1.09 |
  360. | 108 | 12008.00 | 8601.33 | 12008 | 0.72 |
  361. | 110 | 8200.00 | 8601.33 | 7920 | 1.09 |
  362. | 109 | 9000.00 | 8601.33 | 7920 | 1.09 |
  363. | 205 | 12008.00 | 10154 | 12008 | 0.85 |
  364. | 206 | 8300.00 | 10154 | 8300 | 1.22 |
  365.  
  366. ## Задача 4
  367.  
  368. ```postgresql
  369. WITH min_salary_by_dept AS (SELECT department_id,
  370. MIN(salary) AS min_salary
  371. FROM employees
  372. GROUP BY department_id)
  373. SELECT e.employee_id, e.last_name, e.salary, m.department_id
  374. FROM employees e
  375. JOIN min_salary_by_dept m
  376. ON e.department_id = m.department_id AND e.salary = m.min_salary
  377. ORDER BY e.department_id, e.last_name;
  378. ```
  379.  
  380. | employee_id | last_name | salary | department_id |
  381. |-------------|------------|----------|---------------|
  382. | 200 | Whalen | 4400.00 | 10 |
  383. | 202 | Fay | 6000.00 | 20 |
  384. | 119 | Colmenares | 2500.00 | 30 |
  385. | 203 | Mavris | 6500.00 | 40 |
  386. | 500 | B | 1000.00 | 50 |
  387. | 107 | Lorentz | 4200.00 | 60 |
  388. | 204 | Baer | 10000.00 | 70 |
  389. | 173 | Kumar | 6100.00 | 80 |
  390. | 102 | De Haan | 17000.00 | 90 |
  391. | 101 | Kochhar | 17000.00 | 90 |
  392. | 113 | Popp | 6900.00 | 100 |
  393. | 206 | Gietz | 8300.00 | 110 |
  394.  
  395. ## Задача 5
  396.  
  397. ```postgresql
  398. SELECT manager_id,
  399. COUNT(employee_id) AS subordinates_count
  400. FROM employees
  401. WHERE manager_id IS NOT NULL
  402. GROUP BY manager_id
  403. ORDER BY subordinates_count DESC
  404. LIMIT 3;
  405. ```
  406.  
  407. | manager_id | subordinates_count |
  408. |------------|--------------------|
  409. | 100 | 15 |
  410. | 121 | 8 |
  411. | 122 | 8 |
  412.  
  413. ## Задача 6
  414.  
  415. ```postgresql
  416. WITH sorted_employees AS (SELECT e.employee_id,
  417. e.salary,
  418. e.last_name,
  419. NTILE(5) OVER (ORDER BY e.last_name) AS group_num
  420. FROM employees e)
  421. SELECT employee_id,
  422. last_name,
  423. salary,
  424. group_num,
  425. AVG(salary) OVER (PARTITION BY group_num) AS avg_salary_by_group,
  426. salary - AVG(salary) OVER (PARTITION BY group_num) AS salary_diff_from_group_avg
  427. FROM sorted_employees;
  428. ```
  429.  
  430. | employee_id | last_name | salary | group_num | avg_salary_by_group | salary_diff_from_group_avg |
  431. |-------------|-------------|----------|-----------|---------------------|----------------------------|
  432. | 174 | Abel | 11000.00 | 1 | 6336.36 | 4663.64 |
  433. | 166 | Ande | 6400.00 | 1 | 6336.36 | 63.64 |
  434. | 130 | Atkinson | 2800.00 | 1 | 6336.36 | -3536.36 |
  435. | 105 | Austin | 4800.00 | 1 | 6336.36 | -1536.36 |
  436. | 500 | B | 1000.00 | 1 | 6336.36 | -5336.36 |
  437. | 204 | Baer | 10000.00 | 1 | 6336.36 | 3663.64 |
  438. | 116 | Baida | 2900.00 | 1 | 6336.36 | -3436.36 |
  439. | 167 | Banda | 6200.00 | 1 | 6336.36 | -136.36 |
  440. | 172 | Bates | 7300.00 | 1 | 6336.36 | 963.64 |
  441. | 192 | Bell | 4000.00 | 1 | 6336.36 | -2336.36 |
  442. | 151 | Bernstein | 9500.00 | 1 | 6336.36 | 3163.64 |
  443. | 129 | Bissot | 3300.00 | 1 | 6336.36 | -3036.36 |
  444. | 169 | Bloom | 10000.00 | 1 | 6336.36 | 3663.64 |
  445. | 185 | Bull | 4100.00 | 1 | 6336.36 | -2236.36 |
  446. | 187 | Cabrio | 3000.00 | 1 | 6336.36 | -3336.36 |
  447. | 148 | Cambrault | 11000.00 | 1 | 6336.36 | 4663.64 |
  448. | 154 | Cambrault | 7500.00 | 1 | 6336.36 | 1163.64 |
  449. | 110 | Chen | 8200.00 | 1 | 6336.36 | 1863.64 |
  450. | 188 | Chung | 3800.00 | 1 | 6336.36 | -2536.36 |
  451. | 119 | Colmenares | 2500.00 | 1 | 6336.36 | -3836.36 |
  452. | 142 | Davies | 3100.00 | 1 | 6336.36 | -3236.36 |
  453. | 102 | De Haan | 17000.00 | 1 | 6336.36 | 10663.64 |
  454. | 186 | Dellinger | 3400.00 | 2 | 6582.18 | -3182.18 |
  455. | 189 | Dilly | 3600.00 | 2 | 6582.18 | -2982.18 |
  456. | 160 | Doran | 7500.00 | 2 | 6582.18 | 917.82 |
  457. | 104 | Ernst | 6000.00 | 2 | 6582.18 | -582.18 |
  458. | 147 | Errazuriz | 12000.00 | 2 | 6582.18 | 5417.82 |
  459. | 193 | Everett | 3900.00 | 2 | 6582.18 | -2682.18 |
  460. | 109 | Faviet | 9000.00 | 2 | 6582.18 | 2417.82 |
  461. | 202 | Fay | 6000.00 | 2 | 6582.18 | -582.18 |
  462. | 197 | Feeney | 3000.00 | 2 | 6582.18 | -3582.18 |
  463. | 181 | Fleaur | 3100.00 | 2 | 6582.18 | -3482.18 |
  464. | 170 | Fox | 9600.00 | 2 | 6582.18 | 3017.82 |
  465. | 121 | Fripp | 8200.00 | 2 | 6582.18 | 1617.82 |
  466. | 190 | Gates | 2900.00 | 2 | 6582.18 | -3682.18 |
  467. | 135 | Gee | 2400.00 | 2 | 6582.18 | -4182.18 |
  468. | 183 | Geoni | 2800.00 | 2 | 6582.18 | -3782.18 |
  469. | 206 | Gietz | 8300.00 | 2 | 6582.18 | 1717.82 |
  470. | 199 | Grant | 2600.00 | 2 | 6582.18 | -3982.18 |
  471. | 178 | Grant | 7000.00 | 2 | 6582.18 | 417.82 |
  472. | 108 | Greenberg | 12008.00 | 2 | 6582.18 | 5425.82 |
  473. | 163 | Greene | 9500.00 | 2 | 6582.18 | 2917.82 |
  474. | 152 | Hall | 9000.00 | 2 | 6582.18 | 2417.82 |
  475. | 201 | Hartstein | 13000.00 | 2 | 6582.18 | 6417.82 |
  476. | 205 | Higgins | 12008.00 | 3 | 6941.27 | 5066.73 |
  477. | 118 | Himuro | 2600.00 | 3 | 6941.27 | -4341.27 |
  478. | 103 | Hunold | 9000.00 | 3 | 6941.27 | 2058.73 |
  479. | 175 | Hutton | 8800.00 | 3 | 6941.27 | 1858.73 |
  480. | 179 | Johnson | 6200.00 | 3 | 6941.27 | -741.27 |
  481. | 195 | Jones | 2800.00 | 3 | 6941.27 | -4141.27 |
  482. | 122 | Kaufling | 7900.00 | 3 | 6941.27 | 958.73 |
  483. | 115 | Khoo | 3100.00 | 3 | 6941.27 | -3841.27 |
  484. | 100 | King | 24000.00 | 3 | 6941.27 | 17058.73 |
  485. | 156 | King | 10000.00 | 3 | 6941.27 | 3058.73 |
  486. | 101 | Kochhar | 17000.00 | 3 | 6941.27 | 10058.73 |
  487. | 173 | Kumar | 6100.00 | 3 | 6941.27 | -841.27 |
  488. | 137 | Ladwig | 3600.00 | 3 | 6941.27 | -3341.27 |
  489. | 127 | Landry | 2400.00 | 3 | 6941.27 | -4541.27 |
  490. | 165 | Lee | 6800.00 | 3 | 6941.27 | -141.27 |
  491. | 177 | Livingston | 8400.00 | 3 | 6941.27 | 1458.73 |
  492. | 107 | Lorentz | 4200.00 | 3 | 6941.27 | -2741.27 |
  493. | 133 | Mallin | 3300.00 | 3 | 6941.27 | -3641.27 |
  494. | 128 | Markle | 2200.00 | 3 | 6941.27 | -4741.27 |
  495. | 131 | Marlow | 2500.00 | 3 | 6941.27 | -4441.27 |
  496. | 164 | Marvins | 7200.00 | 3 | 6941.27 | 258.73 |
  497. | 143 | Matos | 2600.00 | 3 | 6941.27 | -4341.27 |
  498. | 203 | Mavris | 6500.00 | 4 | 5942.86 | 557.14 |
  499. | 194 | McCain | 3200.00 | 4 | 5942.86 | -2742.86 |
  500. | 158 | McEwen | 9000.00 | 4 | 5942.86 | 3057.14 |
  501. | 126 | Mikkilineni | 2700.00 | 4 | 5942.86 | -3242.86 |
  502. | 124 | Mourgos | 5800.00 | 4 | 5942.86 | -142.86 |
  503. | 125 | Nayer | 3200.00 | 4 | 5942.86 | -2742.86 |
  504. | 198 | OConnell | 2600.00 | 4 | 5942.86 | -3342.86 |
  505. | 153 | Olsen | 8000.00 | 4 | 5942.86 | 2057.14 |
  506. | 132 | Olson | 2100.00 | 4 | 5942.86 | -3842.86 |
  507. | 168 | Ozer | 11500.00 | 4 | 5942.86 | 5557.14 |
  508. | 146 | Partners | 13500.00 | 4 | 5942.86 | 7557.14 |
  509. | 106 | Pataballa | 7000.00 | 4 | 5942.86 | 1057.14 |
  510. | 140 | Patel | 2500.00 | 4 | 5942.86 | -3442.86 |
  511. | 191 | Perkins | 2500.00 | 4 | 5942.86 | -3442.86 |
  512. | 136 | Philtanker | 2200.00 | 4 | 5942.86 | -3742.86 |
  513. | 113 | Popp | 6900.00 | 4 | 5942.86 | 957.14 |
  514. | 141 | Rajs | 3500.00 | 4 | 5942.86 | -2442.86 |
  515. | 114 | Raphaely | 11000.00 | 4 | 5942.86 | 5057.14 |
  516. | 134 | Rogers | 2900.00 | 4 | 5942.86 | -3042.86 |
  517. | 145 | Russell | 14000.00 | 4 | 5942.86 | 8057.14 |
  518. | 184 | Sarchand | 4200.00 | 4 | 5942.86 | -1742.86 |
  519. | 111 | Sciarra | 7700.00 | 5 | 6328.57 | 1371.43 |
  520. | 139 | Seo | 2700.00 | 5 | 6328.57 | -3628.57 |
  521. | 161 | Sewall | 7000.00 | 5 | 6328.57 | 671.43 |
  522. | 159 | Smith | 8000.00 | 5 | 6328.57 | 1671.43 |
  523. | 171 | Smith | 7400.00 | 5 | 6328.57 | 1071.43 |
  524. | 138 | Stiles | 3200.00 | 5 | 6328.57 | -3128.57 |
  525. | 182 | Sullivan | 2500.00 | 5 | 6328.57 | -3828.57 |
  526. | 157 | Sully | 9500.00 | 5 | 6328.57 | 3171.43 |
  527. | 176 | Taylor | 8600.00 | 5 | 6328.57 | 2271.43 |
  528. | 180 | Taylor | 3200.00 | 5 | 6328.57 | -3128.57 |
  529. | 117 | Tobias | 2800.00 | 5 | 6328.57 | -3528.57 |
  530. | 150 | Tucker | 10000.00 | 5 | 6328.57 | 3671.43 |
  531. | 155 | Tuvault | 7000.00 | 5 | 6328.57 | 671.43 |
  532. | 112 | Urman | 7800.00 | 5 | 6328.57 | 1471.43 |
  533. | 144 | Vargas | 2500.00 | 5 | 6328.57 | -3828.57 |
  534. | 162 | Vishney | 10500.00 | 5 | 6328.57 | 4171.43 |
  535. | 123 | Vollman | 6500.00 | 5 | 6328.57 | 171.43 |
  536. | 196 | Walsh | 3100.00 | 5 | 6328.57 | -3228.57 |
  537. | 120 | Weiss | 8000.00 | 5 | 6328.57 | 1671.43 |
  538. | 200 | Whalen | 4400.00 | 5 | 6328.57 | -1928.57 |
  539. | 149 | Zlotkey | 10500.00 | 5 | 6328.57 | 4171.43 |
  540.  
  541. ## Задача 7
  542.  
  543. ```postgresql
  544. WITH hire_stats AS (SELECT e.employee_id,
  545. e.hire_date,
  546. COUNT(*)
  547. FILTER (WHERE e2.hire_date BETWEEN e.hire_date - INTERVAL '1 year'
  548. AND e.hire_date + INTERVAL '1 year') AS hire_count_within_1_year,
  549. COUNT(*) FILTER (WHERE e2.hire_date > e.hire_date AND
  550. EXTRACT(YEAR FROM e2.hire_date) =
  551. EXTRACT(YEAR FROM e.hire_date) OR
  552. (e2.hire_date = e.hire_date AND e2.employee_id >
  553. e.employee_id)) AS hire_count_same_year
  554. FROM employees e
  555. JOIN employees e2 ON e2.employee_id != e.employee_id
  556. GROUP BY e.employee_id, e.hire_date)
  557. SELECT *
  558. FROM hire_stats;
  559. ```
  560.  
  561. | employee_id | hire_date | hire_count_within_1_year | hire_count_same_year |
  562. |-------------|------------|--------------------------|----------------------|
  563. | 184 | 2004-01-27 | 16 | 9 |
  564. | 116 | 2005-12-24 | 51 | 0 |
  565. | 179 | 2008-01-04 | 29 | 10 |
  566. | 138 | 2005-10-26 | 48 | 4 |
  567. | 181 | 2006-02-23 | 50 | 18 |
  568. | 205 | 2002-06-07 | 8 | 4 |
  569. | 146 | 2005-01-05 | 39 | 28 |
  570. | 190 | 2006-07-11 | 49 | 5 |
  571. | 201 | 2004-02-17 | 19 | 6 |
  572. | 162 | 2005-11-11 | 49 | 2 |
  573. | 105 | 2005-06-25 | 46 | 15 |
  574. | 132 | 2007-04-10 | 39 | 11 |
  575. | 174 | 2004-05-11 | 26 | 4 |
  576. | 107 | 2007-02-07 | 44 | 17 |
  577. | 134 | 2006-08-26 | 44 | 4 |
  578. | 144 | 2006-07-09 | 49 | 6 |
  579. | 170 | 2006-01-24 | 51 | 21 |
  580. | 192 | 2004-02-04 | 18 | 7 |
  581. | 176 | 2006-03-24 | 48 | 14 |
  582. | 169 | 2006-03-23 | 47 | 15 |
  583. | 168 | 2005-03-11 | 39 | 20 |
  584. | 163 | 2007-03-19 | 42 | 13 |
  585. | 101 | 2005-09-21 | 48 | 8 |
  586. | 175 | 2005-03-19 | 40 | 19 |
  587. | 180 | 2006-01-24 | 51 | 20 |
  588. | 115 | 2003-05-18 | 18 | 4 |
  589. | 114 | 2002-12-07 | 12 | 0 |
  590. | 102 | 2001-01-13 | 0 | 0 |
  591. | 167 | 2008-04-21 | 21 | 1 |
  592. | 112 | 2006-03-07 | 49 | 17 |
  593. | 109 | 2002-08-16 | 10 | 2 |
  594. | 108 | 2002-08-17 | 10 | 1 |
  595. | 158 | 2004-08-01 | 27 | 1 |
  596. | 156 | 2004-01-30 | 18 | 8 |
  597. | 197 | 2006-05-23 | 48 | 9 |
  598. | 155 | 2007-11-23 | 30 | 3 |
  599. | 135 | 2007-12-12 | 29 | 1 |
  600. | 118 | 2006-11-15 | 38 | 1 |
  601. | 133 | 2004-06-14 | 26 | 3 |
  602. | 195 | 2007-03-17 | 42 | 14 |
  603. | 111 | 2005-09-30 | 49 | 6 |
  604. | 127 | 2007-01-14 | 42 | 18 |
  605. | 124 | 2007-11-16 | 30 | 4 |
  606. | 199 | 2008-01-13 | 29 | 9 |
  607. | 123 | 2005-10-10 | 48 | 5 |
  608. | 128 | 2008-03-08 | 25 | 3 |
  609. | 500 | 2022-01-01 | 1 | 1 |
  610. | 126 | 2006-09-28 | 43 | 3 |
  611. | 194 | 2006-07-01 | 49 | 7 |
  612. | 173 | 2008-04-21 | 21 | 0 |
  613. | 149 | 2008-01-29 | 28 | 7 |
  614. | 200 | 2003-09-17 | 15 | 1 |
  615. | 166 | 2008-03-24 | 23 | 2 |
  616. | 103 | 2006-01-03 | 51 | 22 |
  617. | 189 | 2005-08-13 | 47 | 12 |
  618. | 161 | 2006-11-03 | 39 | 2 |
  619. | 142 | 2005-01-29 | 40 | 27 |
  620. | 121 | 2005-04-10 | 44 | 17 |
  621. | 117 | 2005-07-24 | 48 | 13 |
  622. | 188 | 2005-06-14 | 46 | 16 |
  623. | 152 | 2005-08-20 | 47 | 9 |
  624. | 206 | 2002-06-07 | 8 | 3 |
  625. | 125 | 2005-07-16 | 49 | 14 |
  626. | 119 | 2007-08-10 | 34 | 6 |
  627. | 113 | 2007-12-07 | 30 | 2 |
  628. | 153 | 2006-03-30 | 47 | 13 |
  629. | 157 | 2004-03-04 | 21 | 5 |
  630. | 147 | 2005-03-10 | 39 | 22 |
  631. | 202 | 2005-08-17 | 47 | 11 |
  632. | 196 | 2006-04-24 | 47 | 10 |
  633. | 183 | 2008-02-03 | 28 | 6 |
  634. | 120 | 2004-07-18 | 26 | 2 |
  635. | 100 | 2003-06-17 | 15 | 3 |
  636. | 136 | 2008-02-06 | 28 | 5 |
  637. | 150 | 2005-01-30 | 40 | 26 |
  638. | 140 | 2006-04-06 | 47 | 12 |
  639. | 139 | 2006-02-12 | 51 | 19 |
  640. | 193 | 2005-03-03 | 39 | 23 |
  641. | 187 | 2007-02-07 | 44 | 16 |
  642. | 164 | 2008-01-24 | 28 | 8 |
  643. | 137 | 2003-07-14 | 15 | 2 |
  644. | 191 | 2007-12-19 | 29 | 0 |
  645. | 160 | 2005-12-15 | 51 | 1 |
  646. | 159 | 2005-03-10 | 39 | 21 |
  647. | 172 | 2007-03-24 | 42 | 12 |
  648. | 141 | 2003-10-17 | 16 | 0 |
  649. | 122 | 2003-05-01 | 17 | 5 |
  650. | 186 | 2006-06-24 | 50 | 8 |
  651. | 154 | 2006-12-09 | 41 | 0 |
  652. | 131 | 2005-02-16 | 39 | 25 |
  653. | 198 | 2007-06-21 | 38 | 7 |
  654. | 177 | 2006-04-23 | 47 | 11 |
  655. | 182 | 2007-06-21 | 38 | 8 |
  656. | 204 | 2002-06-07 | 8 | 5 |
  657. | 106 | 2022-12-15 | 1 | 0 |
  658. | 104 | 2007-05-21 | 39 | 10 |
  659. | 185 | 2005-02-20 | 38 | 24 |
  660. | 110 | 2005-09-28 | 49 | 7 |
  661. | 171 | 2007-02-23 | 44 | 15 |
  662. | 178 | 2007-05-24 | 38 | 9 |
  663. | 145 | 2004-10-01 | 33 | 0 |
  664. | 148 | 2007-10-15 | 32 | 5 |
  665. | 130 | 2005-10-30 | 48 | 3 |
  666. | 129 | 2005-08-20 | 47 | 10 |
  667. | 165 | 2008-02-23 | 26 | 4 |
  668. | 143 | 2006-03-15 | 46 | 16 |
  669. | 203 | 2002-06-07 | 8 | 6 |
  670. | 151 | 2005-03-24 | 42 | 18 |
  671.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!