deyanmalinov

09-mysql

Jun 13th, 2021
767
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select first_name, last_name FROM employees
  2. where left(first_name, 2) = 'Sa'
  3. order by employee_id;
  4.  
  5. select first_name, last_name FROM employees
  6. where last_name like '%ei%'
  7. order by employee_id;
  8.  
  9. select first_name FROM employees
  10. where department_id in(3, 10)
  11. and year(hire_date) between 1995 and 2005
  12. order by employee_id;
  13.  
  14. select first_name, last_name FROM employees
  15. where job_title not like '%engineer%'
  16. order by employee_id;
  17.  
  18. select name from towns
  19. where char_length(name) in (5,6)
  20. order by name;
  21.  
  22. select town_id, name from towns
  23. where left(name, 1) in ('M','K','B','E')
  24. order by name;
  25.  
  26. select town_id, name from towns
  27. where left(name, 1) not in ('R','D','B')
  28. order by name;
  29.  
  30. create view v_employees_hired_after_2000 as
  31. select first_name, last_name from employees
  32. where year(hire_date) > 2000;
  33. select * from v_employees_hired_after_2000;
  34.  
  35. select first_name, last_name from employees
  36. where char_length(last_name) = 5;
  37.  
  38. select country_name, iso_code FROM countries
  39. where country_name like '%a%a%a%'
  40. order by iso_code;
  41.  
  42. select p.peak_name, r.river_name,
  43. lower(concat(peak_name, substring(river_name, 2))) as mix
  44. from peaks as p, rivers as r
  45. where right(peak_name, 1) = left(river_name, 1)
  46. order by mix;
  47.  
  48. select name, substring(start, 1,10) as start FROM games
  49. where start between 20110101 and 20130101
  50. order by start limit 50;
  51.  
  52. select user_name,
  53. substring(email, locate('@', email) + 1) as provider from users
  54. order by provider, user_name;
  55.  
  56. select user_name, ip_address from users
  57. where ip_address like '___.1%.%.___'
  58. order by user_name;
  59.  
  60. select name,
  61. (case when hour(start) between 0 and 11 then 'Morning'
  62. when hour(start) between 12 and 17 then 'Afternoon'
  63. else 'Evening' end) as 'Part of the Day',
  64. (case when duration between 0 and 3 then 'Extra Short'
  65. when duration between 4 and 6 then 'Short'
  66. when duration between 7 and 10 then 'Long'
  67. else 'Extra Long' end)
  68. as 'Duration'
  69. FROM games;
  70.  
  71. select product_name, order_date,
  72. date_add(order_date, interval 3 day) as pay_due,
  73. date_add(order_date, interval 1 month) as deliver_due
  74. from orders;
  75.  
RAW Paste Data