deyanmalinov

10-mysql

Jun 13th, 2021
745
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select count(*) from wizzard_deposits;
  2.  
  3. select max(magic_wand_size) as longest_magic_wand from wizzard_deposits;
  4.  
  5. select deposit_group, max(magic_wand_size) as longest_magic_wand
  6. from wizzard_deposits
  7. group by deposit_group
  8. order by longest_magic_wand, deposit_group;
  9.  
  10. select deposit_group from wizzard_deposits
  11. group by deposit_group
  12. order by min(magic_wand_size)
  13. limit 1;
  14.  
  15. select deposit_group, sum(deposit_amount) as total_sum
  16. from wizzard_deposits
  17. group by deposit_group
  18. order by total_sum;
  19.  
  20. select deposit_group, sum(deposit_amount) as total_sum
  21. from wizzard_deposits
  22. where magic_wand_creator = 'Ollivander family'
  23. group by deposit_group
  24. order by deposit_group;
  25.  
  26. select deposit_group, sum(deposit_amount) as total_sum
  27. from wizzard_deposits
  28. where magic_wand_creator = 'Ollivander family'
  29. group by deposit_group
  30. having total_sum < 150000
  31. order by total_sum desc;
  32.  
  33. select deposit_group, magic_wand_creator, min(deposit_charge)
  34. from wizzard_deposits
  35. group by deposit_group, magic_wand_creator
  36. order by magic_wand_creator, deposit_group;
  37.  
  38. select (
  39. case
  40. when age between 0 and 10 then '[0-10]'
  41. when age between 11 and 20 then '[11-20]'
  42. when age between 21 and 30 then '[21-30]'
  43. when age between 31 and 40 then '[31-40]'
  44. when age between 41 and 50 then '[41-50]'
  45. when age between 51 and 60 then '[51-60]'
  46. else '[61+]'
  47. end)
  48. as age_group,
  49. count(*)
  50. from wizzard_deposits
  51. group by age_group
  52. order by age_group;
  53.  
  54. select left(first_name, 1) as f1 from wizzard_deposits
  55. where deposit_group = 'Troll Chest'
  56. group by f1
  57. order by f1;
  58.  
  59. select deposit_group, is_deposit_expired, avg(deposit_interest)
  60. from wizzard_deposits
  61. where deposit_start_date > '1985-01-01'
  62. group by deposit_group, is_deposit_expired
  63. order by deposit_group desc, is_deposit_expired;
  64.  
  65. select sum(diff.next) as sum_diff
  66. from (
  67. select deposit_amount -
  68. ( select deposit_amount
  69. from wizzard_deposits
  70. where id = wd.id + 1) as 'next'
  71. from wizzard_deposits as wd) as diff;
  72.  
  73. select department_id, min(salary)
  74. from employees
  75. where hire_date > '2000-01-01' and department_id in (2, 5, 7)
  76. group by department_id
  77. order by department_id;
  78.  
  79. create table empl as
  80. select * from employees
  81. where salary > 30000 and manager_id != 42;
  82. update empl
  83. set salary = salary + 5000
  84. where department_id = 1;
  85. select department_id, avg(salary)
  86. from empl
  87. group by department_id
  88. order by department_id;
  89.  
  90. select department_id, max(salary)
  91. from employees
  92. group by department_id
  93. having max(salary) not between 30000 and 70000
  94. order by department_id;
  95.  
  96. select count(first_name) from employees
  97. where manager_id is null;
  98.  
  99. select e.department_id,
  100. (select distinct e2.salary
  101. from employees as e2
  102. where e2.department_id = e.department_id
  103. order by e2.salary desc
  104. limit 2, 1) as three
  105. from employees as e
  106. group by e.department_id
  107. having three is not null
  108. order by department_id;
  109.  
  110. select e1.first_name, e1.last_name, e1.department_id
  111. from employees as e1
  112. where salary >
  113. (select avg(e2.salary) as sal
  114. from employees as e2
  115. where e2.department_id = e1.department_id
  116. group by e2.department_id)
  117. order by department_id, employee_id
  118. limit 10;
  119.  
  120. select department_id , sum(salary) from employees
  121. group by department_id
  122. order by department_id;
  123.  
RAW Paste Data