w3resource

SQL Exercise: Employees who does not earn any commission

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

8. From the following table, write a SQL query to find those employees who do not earn any commission. Return full name (first and last name), and salary.

Sample table : employees


Sample Solution:

SELECT first_name ||' '||last_name AS Full_Name, salary
 FROM  employees
  WHERE commission_pct IS NULL;

Sample Output:

 full_name | salary
-----------+--------
(0 rows)

Code Explanation:

The said query in SQL that retrieves the first name, last name concatenated as "Full_Name" and salary columns from the 'employees' table where the commission_pct is null.

Practice Online


N.B.: In certain instances not null is removed in table structure, so results may vary.

HR database model

Query Visualization:

Duration:

Query visualization of Display the full name, and salary for all employees who does not earn any commission - Duration

Rows:

Query visualization of Display the full name, and salary for all employees who does not earn any commission - Rows

Cost:

Query visualization of Display the full name, and salary for all employees who does not earn any commission - Cost

Contribute your code and comments through Disqus.

Previous SQL Exercise: Employees with salaries of given range and commission.
Next SQL Exercise: Find employees whose salary is within 9000 to 17000.

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