sql_in_class2

/* 1 Write a SQL query to find the average salary of employees who have been hired before
   year 2000 incl. Round it down to the nearest integer.*/
SELECT ROUND(AVG(salary)) AS avg_salary
FROM employees
WHERE YEAR(hire_date) < 2000;


/* 2 Write a SQL query to find all town names that end with a vowel (a,o,u,e,i).*/
SELECT name
FROM towns
WHERE RIGHT(name, 1) IN ('e', 'o', 'u', 'e', 'i');


/* 3 Write a SQL query to find all town names that start with a vowel (a,o,u,e,i).*/
SELECT name
FROM towns
WHERE LEFT(name, 1) IN ('e', 'o', 'u', 'e', 'i');


/* 4 Write a SQL query that outputs the name and the length of the town with the longest name.*/
SELECT name, LENGTH(name) AS name_length
FROM towns
ORDER BY LENGTH(name) DESC
LIMIT 1;


/* 5 Write a SQL query that outputs the name and the length of the town with the shortest name.*/
SELECT name, LENGTH(name) AS name_length
FROM towns
ORDER BY LENGTH(name)
LIMIT 1;


/* 6 Write a SQL query to find all employees with even IDs.*/
SELECT *
FROM employees
WHERE employee_id % 2 = 0;


/* 7 Write a SQL query to find the names and salaries of the employees
     that take the minimal salary in the company.*/
SELECT *
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);


/* 8 Write a SQL query to find the names and salaries of the employees
   that have a salary that is up to 10% higher than the minimal salary for the company.*/
SELECT *
FROM employees
WHERE salary <= (SELECT MIN(salary) * 1.1 FROM employees);


/* 9 Write a SQL query to find the full name, salary and department of the employees
   that take the minimal salary in their department.*/
SELECT e.first_name, e.last_name, e.salary, m.name
FROM employees e
         JOIN departments m
              ON e.department_id = m.department_id
WHERE e.salary = (SELECT MIN(salary)
                  FROM employees
                  WHERE department_id = e.department_id
                  GROUP BY department_id);


/* 10 Write a SQL query to find the average salary in department #1.*/
SELECT AVG(salary) AS average_salary_dep_1
FROM employees e
WHERE department_id = 1;


/* 11 Write a SQL query to find the average salary in the "Sales" department.*/
SELECT AVG(salary) AS avg_salary_sales
FROM employees e
         JOIN departments d
              ON e.department_id = d.department_id
WHERE d.name = 'Sales';


/* 12 Write a SQL query that outputs the number of employees in the "Sales" department.*/
SELECT COUNT(*) AS employees_in_sales
FROM employees e
         JOIN departments d
              ON e.department_id = d.department_id
WHERE d.name = 'Sales';


/* 13 Write a SQL query that outputs the number of employees that have manager.*/
SELECT COUNT(*) AS have_manager
FROM employees
WHERE manager_id IS NOT NULL;


/* 14 Write a SQL query that outputs the number of employees that don't have manager.*/
SELECT COUNT(*) AS dont_have_manager
FROM employees
WHERE manager_id IS NULL;


/* 15 Write a SQL query to find all departments, along with the average salary for each of them.*/
SELECT d.name, AVG(e.salary) AS avg_salary
FROM employees e
         JOIN departments d
              ON d.department_id = e.department_id
GROUP BY d.name;


/* 16 Write a SQL query to find all projects that took less than 1 year (365 days) to complete.*/
SELECT projects.name
FROM projects
WHERE datediff(end_date, start_date) < 365;


/* 17 Write a SQL query that outputs the number for employees from each town, grouped by department.
   For example how many people from Bellevue work in Sales.
   How many people from Calgary work in Marketing, and so on...*/
SELECT t.name AS town, d.name AS department, COUNT(e.employee_id) AS number_of_employees
FROM employees e
         JOIN towns t ON e.employee_id = t.town_id
         JOIN departments d ON e.department_id = d.department_id
GROUP BY d.name, t.name;


/* 18 Write a SQL query that outputs the first and last name of all managers that have exactly 5 employees.*/
SELECT m.first_name, e.last_name
FROM employees m
         JOIN employees e
              ON m.employee_id = e.manager_id
GROUP BY m.employee_id
HAVING COUNT(e.employee_id) = 5;


/* 19 Write a SQL query to find all employees along with their managers.
   For employees that do not have manager display the value "(no manager)".*/
SELECT CONCAT(e.first_name, ' ', e.last_name)                       AS empoloyee,
       IFNULL(CONCAT(m.first_name, ' ', m.last_name), 'no manager') AS manager
FROM employees e
         LEFT JOIN employees m
                   ON m.employee_id = e.manager_id;


/* 20 Write a SQL query that outputs the names of all employees whose last name is exactly 5 characters long.*/
SELECT first_name, last_name
FROM employees
WHERE LENGTH(last_name) = 5;


/* 21 Write a SQL query that outputs the current date and time in the following format
   "day.month.year hour:minutes:seconds:milliseconds". */
SELECT DATE_FORMAT(NOW(), '%d.%m.%Y %H:%i:%s');


/* 22 Write a SQL query to display the average employee salary by department and job title.
   For example, the average salary in Engineering for Design Engineer is 32,700.*/
SELECT d.name, e.job_title, ROUND(avg(e.salary)) AS avg_salary
FROM employees e
         JOIN departments d
              ON e.department_id = d.department_id
GROUP BY d.name, e.job_title;


/* 23 Write a SQL query that outputs the town with most employees.*/
SELECT t.name AS town_name, COUNT(e.employee_id) AS employees_count
FROM employees e
         JOIN addresses a ON e.address_id = a.address_id
         JOIN towns t ON a.town_id = t.town_id
GROUP BY t.name
ORDER BY employees_count DESC
LIMIT 1;


/* 24 Write a SQL query that outputs the number of managers from each town.*/
SELECT t.name AS town_name, COUNT(DISTINCT e.employee_id) AS managers_count
FROM employees e
         JOIN addresses a ON e.address_id = a.address_id
         JOIN towns t ON a.town_id = t.town_id
WHERE e.employee_id IN (SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL)
GROUP BY t.name
ORDER BY managers_count DESC;


/* 25 Write a SQL query to find the manager who is in charge of the most employees
      and their average salary.*/
SELECT CONCAT(m.first_name, ' ', m.last_name) AS manager_name,
       COUNT(e.employee_id)                   AS num_employees,
       ROUND(AVG(m.salary))                   AS employee_avg_salary
FROM employees e
         JOIN employees m ON e.manager_id = m.employee_id
GROUP BY e.manager_id
ORDER BY num_employees DESC
LIMIT 1;


/* 26 Write a SQL query that outputs the names of the employees who have worked on the most projects.*/
WITH project_counts AS (SELECT e.employee_id, e.first_name, e.last_name, COUNT(ep.project_id) AS projects_count
                        FROM employees e
                                 JOIN employees_projects ep ON e.employee_id = ep.employee_id
                        GROUP BY e.employee_id)
SELECT CONCAT(first_name, ' ', last_name) AS empoloyee_name, projects_count
FROM project_counts
WHERE projects_count = (SELECT MAX(projects_count) FROM project_counts)
ORDER BY first_name;