w3resource

SQL Exercise: Departments more than 10 employees who get commissions

SQL SORTING and FILTERING on HR Database: Exercise-31 with Solution

31. From the following table, write a SQL query to find the departments where more than ten employees receive commissions. Return department id.

Sample table : employees


Sample Solution:

SELECT department_id 
	FROM employees 
		WHERE commission_pct IS NOT NULL
			GROUP BY department_id 
				HAVING COUNT(commission_pct)>10;

Sample Output:

 department_id
---------------
            80
            50
(2 rows)

N.B.: For necessary region structure of the table have changed. Result may vary.

Code Explanation:

The said query in SQL that selects the department_id of employees who have a commission and groups them by department_id. It then only returns departments where the count of employees with a commission_pct is greater than 10.

Relational Algebra Expression:

Relational Algebra Expression: Display those departments where more than ten employees work who got a commission percentage.

Relational Algebra Tree:

Relational Algebra Tree: Display those departments where more than ten employees work who got a commission percentage.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display those departments where more than ten employees work who got a commission percentage - Duration

Rows:

Query visualization of Display those departments where more than ten employees work who got a commission percentage - Rows

Cost:

Query visualization of Display those departments where more than ten employees work who got a commission percentage - Cost

Contribute your code and comments through Disqus.

Previous SQL Exercise: Departments with managers manage more than 4 employees.
Next SQL Exercise: The date when he left his last job and his employee ID.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Difference between natural join and inner join

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-

Consider:

TableA                           TableB
+------------+----------+        +--------------------+    
|Column1     | Column2  |        |Column1  |  Column3 |
+-----------------------+        +--------------------+
| 1          |  2       |        | 1       |   3      |
+------------+----------+        +---------+----------+

The INNER JOIN of TableA and TableB on Column1 will return

SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1);
SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;
+------------+-----------+---------------------+    
| a.Column1  | a.Column2 | b.Column1| b.Column3|
+------------------------+---------------------+
| 1          |  2        | 1        |   3      |
+------------+-----------+----------+----------+

The NATURAL JOIN of TableA and TableB on Column1 will return:

SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+    
|Column1     | Column2  | Column3  |
+-----------------------+----------+
| 1          |  2       |   3      |
+------------+----------+----------+

Ref: https://bit.ly/3AG5CId

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook