Built-in Functions - Exercises

SELECT first_name,last_name FROM employees
WHERE first_name LIKE 'Sa%' ORDER BY employee_id;
---------1
SELECT first_name,last_name FROM employees
WHERE last_name LIKE '%ei%' ORDER BY employee_id;
---------2
SELECT first_name FROM employees WHERE(department_id=3
OR department_id=10)
AND (year(hire_date)>=1995 AND year(hire_date) <=2005)
ORDER BY employee_id;
--------3
SELECT first_name,last_name FROM employees WHERE job_title
NOT LIKE '%engineer%' ORDER BY employee_id;
--------4
SELECT name FROM towns WHERE LENGTH(name) = 5 OR LENGTH (name) = 6
ORDER BY name ASC;
-------5
1). SELECT town_id,name FROM towns WHERE SUBSTRING(name,1,1)
IN('M','K','B','E') ORDER BY name ASC;
2). SELECT town_id,name FROM towns WHERE name LIKE 'M%' OR name LIKE 'K%'
OR name LIKE 'B%' OR name LIKE 'E%' ORDER BY name ASC;
----------6
SELECT town_id, name FROM towns WHERE name NOT LIKE 'R%'
AND name NOT LIKE 'B%' ANd name NOT LIKE 'D%' ORDER BY name ASC;
----------7
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;
-----------8
SELECT first_name,last_name FROM employees WHERE LENGTH(last_name)=5;
---------9
1). SELECT country_name,iso_code FROM countries WHERE (LENGTH(country_name) -
LENGTH(REPLACE(country_name,'a',''))+1)>=3 ORDER BY iso_code;
2). SELECT country_name,iso_code FROM countries WHERE country_name
LIKE '%a%a%a%' ORDER BY iso_code;
-----------10
SELECT p.peak_name,r.river_name,lower(concat(p.peak_name,substring(r.river_name,2)))AS mix
FROM peaks as p,rivers as r
WHERE right(p.peak_name,1) = left(r.river_name,1)
ORDER BY mix;
---------11
SELECT name,DATE_FORMAT(start,'%Y-%m-%d') FROM games WHERE
year(start) = 2011 OR year(start) = 2012 ORDER BY start,name
LIMIT 50;
---------12
SELECT user_name,SUBSTRING_INDEX(email,'@',-1) AS `email provider`
FROM users ORDER BY `email provider`, user_name;
---------13
SELECT user_name,ip_address FROM users
WHERE ip_address LIKE '___.1%.%.___'
ORDER BY user_name ASC;
----------14
SELECT name AS `Game`,
CASE
WHEN HOUR(`start`) BETWEEN 0 AND 11 THEN 'Morning'
WHEN HOUR(`start`) BETWEEN 12 AND 17 THEN 'Afternoon'
WHEN HOUR(`start`) BETWEEN 18 AND 23 THEN 'Evening'
END AS `Part of the Day`,
CASE
WHEN duration <= 3 THEN 'Extra Short'
WHEN duration > 3 AND duration <= 6 THEN 'Short'
WHEN duration > 6 AND duration <= 10 THEN 'Long'
ELSE 'Extra Long'
END AS `Duration`
FROM games;
------------15
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;