09-mysql

select first_name, last_name FROM employees
where left(first_name, 2) = 'Sa'
order by employee_id;

select first_name, last_name FROM employees
where last_name like '%ei%'
order by employee_id;

select first_name FROM employees
where department_id in(3, 10)
and year(hire_date) between 1995 and 2005
order by employee_id;

select first_name, last_name FROM employees
where job_title not like '%engineer%'
order by employee_id;

select name from towns
where char_length(name) in (5,6)
order by name;

select town_id, name from towns
where left(name, 1) in ('M','K','B','E')
order by name;

select town_id, name from towns
where left(name, 1) not in ('R','D','B')
order by name;

create view v_employees_hired_after_2000 as
select first_name, last_name from employees
where year(hire_date) > 2000;
select * from v_employees_hired_after_2000;

select first_name, last_name from employees
where char_length(last_name) = 5;

select country_name, iso_code FROM countries
where country_name like '%a%a%a%'
order by iso_code;

select p.peak_name, r.river_name,
lower(concat(peak_name, substring(river_name, 2))) as mix
from peaks as p, rivers as r
where right(peak_name, 1) = left(river_name, 1)
order by mix;

select name, substring(start, 1,10) as start FROM games
where start between 20110101 and 20130101
order by start limit 50;

select user_name,
substring(email, locate('@', email) + 1) as provider from users
order by provider, user_name;

select user_name, ip_address from users
where ip_address like '___.1%.%.___'
order by user_name;

select name,
(case when hour(start) between 0 and 11 then 'Morning'
when hour(start) between 12 and 17 then 'Afternoon'
else 'Evening' end) as 'Part of the Day',
(case when duration between 0 and 3 then 'Extra Short'
when duration between 4 and 6 then 'Short'
when duration between 7 and 10 then 'Long'
else 'Extra Long' end)
as 'Duration'
FROM games;

select product_name, order_date,
date_add(order_date, interval 3 day) as pay_due,
date_add(order_date, interval 1 month) as deliver_due
from orders;