MySQL Advanced Queries

1.a Write a query to find the top 3 highest salaries in the company.

SELECT salary
FROM employee
ORDER BY salary DESC
LIMIT 3;

1.b Write a query to find the top 3 highest salary employees in the company.

SELECT *
FROM employee
ORDER BY salary DESC
LIMIT 3;

1.c Write a query to find the top 3rd highest salary employee in the company.

SELECT *
FROM employee
ORDER BY salary DESC
-- LIMIT offset, count
LIMIT 2, 1;

1.d Write an SQL query to find the maximum, minimum, and average salary of the employees.

SELECT 
    Max(salary), 
    Min(salary), 
    AVG(salary) 
FROM employee;

2. Write a query to find the number of employees who have been with the company for more than 5 years.

– Use (CURRENT_DATE – hired_date) / 365 to calculate 1 year

SELECT COUNT(*) as _5YearAndOlder
FROM employee
WHERE ((CURRENT_DATE - hired_date) / 365) >= 5;

3.a Write a query to find the average salary for each department in the company. Sort the average_salary from highest to lowest

SELECT department_id, AVG(salary) AS average_salary
FROM employee
GROUP BY department_id
ORDER BY average_salary DESC;

3.b Write a query to find the count of employees per deparment. 

SELECT department_id, COUNT(id) AS departmentCount
FROM employee
GROUP BY department_id
ORDER BY departmentCount DESC;

3.c Write a query to find the employees that have salary that is above average salary for each department in the company.

• Get the average salary per department in a subquery
• Use the average found above to filter employees by their salary

SELECT emp.id, emp.first_name, emp.last_name, emp.salary, avg_salary.average_salary
FROM employee as emp
JOIN (
    SELECT department_id, AVG(salary) AS average_salary
    FROM employee
    GROUP BY department_id
) as avg_salary ON avg_salary.department_id = emp.department_id
WHERE emp.salary >= avg_salary.average_salary;

4. Write a query to find the employees who are working on more than 1 project.

SELECT pe.employee_id, emp.first_name, emp.last_name, COUNT(pe.project_id) as projectCount 
FROM project_employees as pe
JOIN employee as emp ON pe.employee_id = emp.id -- join employee just to display name
GROUP BY pe.employee_id
HAVING projectCount > 1;

5. Write a query to find the employees who are not working on any projects.

SELECT id, first_name, last_name
FROM employee
WHERE id NOT IN (
  SELECT DISTINCT employee_id
  FROM project_employees
);

6.a Write a query to find the employees who have joined last year.

SELECT id, first_name, last_name, hired_date
FROM employee
WHERE YEAR(hired_date) = YEAR(CURRENT_DATE()) - 1;

6.b Write a query to find the employees who have joined in the last year.

SELECT id, first_name, last_name, hired_date
FROM employee
WHERE hired_date > CURRENT_DATE - INTERVAL 1 YEAR;

7. Write a query to find the employees who have submitted more than 1 bug reports in the last month.

SELECT br.reporter_id, emp.first_name, emp.last_name, COUNT(br.id) as bugCount
FROM employee_bug_report as br
JOIN employee as emp ON emp.id = br.reporter_id
WHERE br.reported_at > CURRENT_DATE - INTERVAL 1 MONTH
GROUP BY br.reporter_id
HAVING bugCount > 1;

8.a Write a query to find the employee who has submitted the most bug reports.

SELECT br.reporter_id, emp.first_name, emp.last_name, COUNT(br.id) as bugCount
FROM employee_bug_report as br
JOIN employee as emp ON emp.id = br.reporter_id
GROUP BY br.reporter_id
ORDER BY bugCount DESC
LIMIT 0, 1;

8.b Write a query to find the employee who has submitted the most bug reports in the last month.

SELECT br.reporter_id, emp.first_name, emp.last_name, (CURRENT_DATE - INTERVAL 1 MONTH) as from_date, COUNT(br.id) as bugCount
FROM employee_bug_report as br
JOIN employee as emp ON emp.id = br.reporter_id
WHERE br.reported_at > CURRENT_DATE - INTERVAL 1 MONTH
GROUP BY br.reporter_id
ORDER BY bugCount DESC
LIMIT 0, 1;

9.a Write a query to find the employees who have duplicated first_names

• This question can be worded differently in many ways. It can be asked for duplicate emails.

SELECT emp.first_name, COUNT(emp.id) as nameCount
FROM employee as emp
GROUP BY emp.first_name
HAVING nameCount > 1;

9.b Write a query to find the count of employees who have duplicated first_names

SELECT COUNT(duplicatedNames.idCount) as numberOfDuplicatedFirstName
FROM (
    SELECT COUNT(emp.id) as idCount
    FROM employee as emp
    GROUP BY emp.first_name
    HAVING idCount > 1
) as duplicatedNames;

9.c Write a query to find duplicated first_names of over 2 employees or more

SELECT COUNT(duplicatedNames.idCount) as numberOfDuplicatedFirstName
FROM (
    SELECT COUNT(emp.id) as idCount
    FROM employee as emp
    GROUP BY emp.first_name
    HAVING idCount >= 2
) as duplicatedNames;

10. Write a query to find the employees that their first_name starts with Sh

— start with is LIKE ‘xxx%’

SELECT emp.*
FROM employee as emp
WHERE emp.first_name LIKE 'Sh%';

11. Write a query to find the employees that their first_name ends with n

— end with is LIKE ‘%xxx’

SELECT emp.*
FROM employee as emp
WHERE emp.first_name LIKE '%n';

12. Write a query to find the employees that their first_name has or contains an

— contain is LIKE ‘%xxx%

SELECT emp.*
FROM employee as emp
WHERE emp.first_name LIKE '%an%';

13. Write a query to find the employees that their first_name starts with t and last_name ends with n

SELECT emp.*
FROM employee as emp
WHERE emp.first_name LIKE 't%' AND emp.last_name LIKE '%n';

14. Write a query to put together employee first_name and last_name as full_name

SELECT CONCAT(emp.first_name, ' ', emp.last_name) as full_name
FROM employee as emp;

15. Write an SQL query to create a new table(employee1) with data and structure copied from another table(employee).

CREATE TABLE employee1 
SELECT * FROM employee;

16. Find the total number of bugs reported per day, ordered by chronological date

• Use DATE to GROUP BY date out of the date field.

SELECT DATE(br.reported_at) as dateReported, COUNT(br.id) as bugCount
FROM employee_bug_report as br
GROUP BY DATE(br.reported_at)
ORDER BY DATE(br.reported_at) DESC;

Github Project