MySQL employees sample database challange

challange.md

Problem 1

-- Find the number of Male (M) and Female (F) employees in the database and order the counts in descending order.

SELECT 
    gender, COUNT(*) AS totalGender
FROM
    employees
GROUP BY gender
ORDER BY totalGender DESC;

Problem 2

-- Find the average salary by employee title, round to 2 decimal places and order by descending order.

SELECT 
    title, ROUND(AVG(s.salary), 2) AS avg_salary
FROM
    titles t
        INNER JOIN
    salaries s
WHERE
    t.emp_no = s.emp_no
GROUP BY title
ORDER BY avg_salary DESC;

Problem 3

-- Find all the employees that have worked in at least 2 departments. Show their first name, last_name and the number of departments they work in. Display all results in ascending order.

SELECT 
    concat(e.first_name,' ', e.last_name) as fullName, COUNT(*) AS total_dept
FROM
    employees e
        INNER JOIN
    dept_emp de
WHERE
    e.emp_no = de.emp_no
GROUP BY fullName
having count(*) >=2;

Problem 4

-- Display the first name, last name, and salary of the highest payed employee.

select 
	first_name,last_name, salary
from
	employees e
    inner join 
    salaries s
where e.emp_no = s.emp_no
order by salary desc
limit 1;

Problem 5

-- Display the first name, last name, and salary of the second highest payed employee.

select 
	first_name,last_name, salary
from
	employees e
    inner join 
    salaries s
where e.emp_no = s.emp_no
order by salary desc
limit 1, 1;

Problem 6

-- Display the month and total hires for the month with the most hires.

SELECT 
    DATE_FORMAT(hire_date, '%M') AS month,
    COUNT(*) AS total_hires
FROM
    employees
GROUP BY month
ORDER BY total_hires DESC
LIMIT 1;

Problem 7

-- Display each department and the age of the youngest employee at hire date.

SELECT 
    dept_name,
    MIN(TIMESTAMPDIFF(YEAR,
        e.birth_date,
        e.hire_date)) AS min_age
FROM
    employees e
        INNER JOIN
    dept_emp de ON e.emp_no = de.emp_no
        INNER JOIN
    departments d ON de.dept_no = d.dept_no
GROUP BY dept_name;

-- Problem 8 -- Find all the employees that do not contain vowels in their first name and display the department they work in.