sql_in_class1

/* 1 Write a SQL query that outputs all information about all departments.*/
SELECT *
FROM departments;


/* 2 Write a SQL query that outputs all department names.*/
SELECT name
FROM departments;


/* 3 Write a SQL query that outputs first and last name of each employee, along with their salary.*/
SELECT first_name, last_name, salary
FROM employees;


/* 4 Write a SQL query that outputs the full name of each employee.*/
SELECT CONCAT_WS(' ', first_name, middle_name, last_name)
FROM employees;


/* 5 Write a SQL query to generate an email addresses for each employee.
   Consider that the email domain is company.com. For example,John Doe's email would be
   "John.Doe@company.com". The produced column should be named "Full Email Addresses".*/
SELECT CONCAT(first_name, '.', last_name, '@company.com') as 'full email addresses'
FROM employees;


/* 6 Write a SQL query to find all the different employee salaries.*/
SELECT DISTINCT ROUND(salary) as salary
FROM employees
ORDER BY salary desc;


/* 7 Write a SQL query that outputs all information about the employees
   whose job title is "Sales Representative".*/
SELECT *
FROM employees
WHERE job_title = 'Sales Representative';


/* 8 Write a SQL query to find all employees who have a salary that is bigger than their manager's.*/
SELECT CONCAT(e.first_name, ' ', e.last_name) as name, e.salary, m.salary AS manager_salary
FROM employees e
         JOIN employees m
              ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;


/* 9 Write a SQL query to find the names of all employees whose first name starts with "SA".*/
SELECT first_name, last_name
FROM employees
WHERE first_name LIKE 'SA%'
ORDER BY first_name;


/* 10 Write a SQL query to find the names of all employees whose last name contains "ei".*/
SELECT first_name, last_name
FROM employees
WHERE last_name LIKE '%ei%';


/* 11 Write a SQL query to find all employees whose salary is in the range [20000…30000].*/
SELECT CONCAT(first_name, ' ', last_name) as name, ROUND(salary) as salary
FROM employees
WHERE salary BETWEEN 20000 AND 30000
ORDER BY salary;


/* 12 Write a SQL query to find the names of all employees
   whose salary is 25000, 14000, 12500 or 23600.*/
SELECT first_name, last_name, ROUND(salary) as salary
FROM employees
WHERE salary in (25000, 14000, 12500, 23600)
ORDER BY employees.salary;


/* 13 Write a SQL query to find all employees that do not have manager.*/
SELECT CONCAT(first_name, ' ', last_name) as employees_with_no_manager
FROM employees
WHERE manager_id IS NULL;


/* 14 Write a SQL query to find the names of all employees who were hired before their managers.*/
SELECT CONCAT(e1.first_name, ' ', e1.last_name) as employee
FROM employees e1
WHERE e1.hire_date < (SELECT e2.hire_date
                      FROM employees e2
                      WHERE e1.manager_id = e2.employee_id);


/* 15 Write a SQL query to find all employees whose salary is more than 50000.
   Order them in decreasing order, based on their salary.*/
SELECT CONCAT(first_name, ' ', last_name) as name, ROUND(salary) as salary
FROM employees
WHERE salary > 50000
ORDER BY salary desc;


/* 16 Write a SQL query to find the top 5 best paid employees.*/
SELECT CONCAT(first_name, ' ', last_name) as name, ROUND(salary) as salary
FROM employees
ORDER BY salary desc
LIMIT 5;


/* 17 Write a SQL query that outputs all employees along their address.*/
SELECT CONCAT(e.first_name, ' ', e.last_name) as employee_name, t.name as town, a.text as address
FROM employees e
         JOIN addresses a
              ON e.address_id = a.address_id
         JOIN towns t ON a.town_id = t.town_id
ORDER BY t.name, e.first_name;


/* 18 Write a SQL query to find all employees whose middle name is the same
   as the first letter of their town.*/
SELECT CONCAT_WS(' ', e.first_name, middle_name, last_name) as full_name, t.name
FROM employees e
         JOIN addresses a ON e.address_id = a.address_id
         JOIN towns t ON a.town_id = t.town_id
WHERE LEFT(t.name, 1) = e.middle_name;
/* ------------------------------------------------------------------------------------- */
/* 18 Write a SQL query to find all employees whose !FIRST! middle name !LETTER! is the same
   as the first letter of their town.*/
/*
SELECT CONCAT_WS(' ', e.first_name, middle_name, last_name) as full_name, t.name
FROM employees e
         JOIN addresses a ON e.address_id = a.address_id
         JOIN towns t ON a.town_id = t.town_id
WHERE LEFT(t.name, 1) = left(e.middle_name, 1); */


/* 19 Write a SQL query that outputs all employees (first and last name) that have a manager,
   along with their manager (first and last name).*/
SELECT CONCAT(e.first_name, ' ', e.last_name) as employee, CONCAT(m.first_name, ' ', m.last_name) as manager
FROM employees e
         JOIN employees m ON e.manager_id = m.employee_id
WHERE e.manager_id IS NOT NULL;


/* 20 Write a SQL query that outputs all employees that have a manager (first and last name),
   along with their manager (first and last name) and the employee's address.*/
SELECT CONCAT(e.first_name, ' ', e.last_name) as employee,
       CONCAT(m.first_name, ' ', m.last_name) as manager,
       a.text                                 as address
FROM employees e
         JOIN employees m ON e.manager_id = m.employee_id
         JOIN addresses a ON e.address_id = a.address_id;


/* 21 Write a SQL query to find all departments and all town names in a single column.*/
SELECT d.name as department_or_town_name
FROM departments d
UNION
SELECT t.name as department_or_town_name
FROM towns t;


/* 22 Write a SQL query to find all employees and their manager,
   along with the employees that do not have manager.
   If they do not have a manager, output "n/a".*/
SELECT CONCAT(e.first_name, ' ', e.last_name)                        as employee,
       CONCAT(IFNULL(CONCAT(m.first_name, ' ', m.last_name), 'n/a')) as manager
FROM employees e
         LEFT JOIN employees m ON e.manager_id = m.employee_id;


/* 23 Write a SQL query to find the names of all employees from the departments
   "Sales" AND "Finance" whose hire year is between 1995 and 2005.*/
SELECT CONCAT(e.first_name, ' ', e.last_name) as employee, d.name as department
FROM employees e
         JOIN departments d ON e.department_id = d.department_id
WHERE d.name = 'Finance'
   OR d.name = 'Sales'
    AND YEAR(e.hire_date) BETWEEN 1995 AND 2005;