[go: up one dir, main page]
Scribd
Upload
100 views2 pages

MySQL GroupBy Having Practice Complete

The document contains a series of MySQL practice questions and answers focused on using GROUP BY and HAVING clauses. It includes queries to count employees by office, job title, and manager, as well as to find offices with specific employee counts and averages. The document serves as a resource for practicing SQL aggregation techniques.

Uploaded by

Praveen Reddy Daka
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
100 views2 pages

MySQL GroupBy Having Practice Complete

The document contains a series of MySQL practice questions and answers focused on using GROUP BY and HAVING clauses. It includes queries to count employees by office, job title, and manager, as well as to find offices with specific employee counts and averages. The document serves as a resource for practicing SQL aggregation techniques.

Uploaded by

Praveen Reddy Daka
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

MySQL GROUP BY & HAVING - Practice Questions & Answers

-- 1. Count the number of employees in each office.

SELECT officeCode, COUNT(*) AS employee_count FROM employees GROUP BY


officeCode;

-- 2. Find the number of employees per job title.

SELECT jobTitle, COUNT(*) AS job_count FROM employees GROUP BY jobTitle ORDER BY


job_count DESC;

-- 3. Count how many employees report to each manager.

SELECT reportsTo, COUNT(*) AS reportee_count FROM employees WHERE reportsTo IS


NOT NULL GROUP BY reportsTo ORDER BY reportee_count DESC;

-- 4. Find the total number of employees in each department (job title-based).

SELECT jobTitle, COUNT(*) AS employee_count FROM employees GROUP BY jobTitle;

-- 5. Find offices with more than 5 employees.

SELECT officeCode, COUNT(*) AS employee_count FROM employees GROUP BY officeCode


HAVING COUNT(*) > 5;

-- 6. Count the number of employees in each city.

SELECT officeCode, COUNT(*) AS employee_count FROM employees GROUP BY


officeCode;

-- 7. Find the job title with the maximum number of employees.

SELECT jobTitle, COUNT(*) AS total FROM employees GROUP BY jobTitle ORDER BY


total DESC LIMIT 1;

-- 8. Find managers who manage more than 3 employees.

SELECT reportsTo, COUNT(*) AS reportee_count FROM employees GROUP BY reportsTo


HAVING COUNT(*) > 3;

-- 9. Retrieve offices where the average employee number is greater than 500.

SELECT officeCode, AVG(employeeNumber) FROM employees GROUP BY officeCode HAVING


AVG(employeeNumber) > 500;

Page 1
MySQL GROUP BY & HAVING - Practice Questions & Answers

-- 10. Count employees who have unique job titles.

SELECT jobTitle, COUNT(*) FROM employees GROUP BY jobTitle HAVING COUNT(*) = 1;

-- 11. Find the highest and lowest employee count per office.

SELECT officeCode, COUNT(*) AS employee_count FROM employees GROUP BY officeCode


ORDER BY employee_count DESC;

-- 12. Retrieve the office with the most managers.

SELECT officeCode, COUNT(*) AS manager_count FROM employees WHERE jobTitle LIKE


'%Manager%' GROUP BY officeCode ORDER BY manager_count DESC LIMIT 1;

-- 13. Count employees per job title but exclude those with fewer than 2 employees.

SELECT jobTitle, COUNT(*) FROM employees GROUP BY jobTitle HAVING COUNT(*) > 1;

-- 14. Find employees who work in offices with an average employee number greater than
700.

SELECT officeCode, AVG(employeeNumber) FROM employees GROUP BY officeCode HAVING


AVG(employeeNumber) > 700;

-- 15. Retrieve employees grouped by first name length.

SELECT CHAR_LENGTH(firstName) AS name_length, COUNT(*) FROM employees GROUP BY


name_length;

Page 2

You might also like