Question

Write a query that returns the department id and the number of employees (in the associated department) salary is over $2500 and, at the end, result page has to show only department ids that have more than 5 employees in it. Please Note: (we don't interest all of department ids in our grouping action and also we don’t want to see any kind of total employee number in the result page we want to see limited result set)

Hint1: SELECT part has to be like below. SELECT department, COUNT(*) as "Number of employees"

Hint2: Don't forget the use of WHERE and please distinguish the WHERE and HAVING

SQL SCHEMA (oracle):
https://pastebin.com/raw/vaesFLWM

Answer 1

When I checked the tables in your database I get to know that there are employees table and department table. employees table contains department_id and salary. Since we need only department_id and not it's name so no need to use department table. Only with employees table we can execute the query.

Since we need to group the total count with respect to department_id we use GROUP BY on department_id.

To check whether the code is working properly or not I created a table names employees with only 2 columns department_id and salary.

The query:

SELECT department_id, COUNT(*) as "Number of Employees" from employees WHERE salary>25000 GROUP BY department_id;

will produce the department_id and their respective count of employees whose salary is more than 25000.

Since we need only department ids where the employees count is more than 5.

We can use HAVING clause to show the result but it prints both columns as shown below

SELECT department_id,COUNT(*) AS "Number of employees" FROM `employees` WHERE salary>25000 GROUP BY department_id HAVING COUNT(*)>5

If it is fine then we can use the above query.

But if we want only department id but not count column then we can use VIEWS.

CREATE VIEW view_employees_department_id AS SELECT department_id, COUNT(*) as "Number of Employees" from employees WHERE salary>25000 GROUP BY department_id HAVING COUNT(*)>5;

The above query will generate the VIEW with the given select statement. It contains 2 columns namely department_id and Number of employees where count>5.

Now since we need only department_id column we will use the above view as our new table(here the VIEW table is a virtual one). i.e;

SELECT department_id from view_employees_department_id;

Difference between WHERE and HAVING:

1. Apart from SELECT queries, you can use WHERE clause with UPDATE and DELETE clause but HAVING clause can only be used with SELECT query.
2. WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in SQL.
3. One syntax level difference between WHERE and HAVING clause is that, former is used before GROUP BY clause, while later is used after GROUP BY clause.
4. When WHERE and HAVING clause are used together in a SELECT query with aggregate function,  WHERE clause is applied first on individual rows and only rows which pass the condition is included for creating groups. Once group is created, HAVING clause is used to filter groups based upon condition specified.

If you have doubts kindly ask me. Thank you