w3resource

SQL exercises on employee Database: List the ID, name, salary, and job_name of the employees

SQL employee Database: Exercise-39 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

39. Write a query in SQL to list the ID, name, salary, and job_name of the employees for -
1. Annual salary is below 34000 but receiving some commission which should not be more than the salary,
2. And designation is SALESMAN and working for department 3001.

Sample table: employees


Pictorial Presentation:

SQL exercises on employee Database: List the ID, name, salary, and job_name of the employees

Sample Solution:

SELECT emp_id,
       emp_name,
       salary,
       job_name
FROM employees
WHERE 12*(salary+commission) < 34000
  AND commission IS NOT NULL
  AND commission < salary
  AND job_name = 'SALESMAN'
  AND dep_id = 3001;

Sample Output:

 emp_id | emp_name | salary  | job_name
--------+----------+---------+----------
  64989 | ADELYN   | 1700.00 | SALESMAN
  65271 | WADE     | 1350.00 | SALESMAN
  68454 | TUCKER   | 1600.00 | SALESMAN
(3 rows)

Practice Online


Sample Database: employee

employee database structure

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query in SQL to list all the employees of designation CLERK in department no 2001.
Next: Write a query in SQL to list the employees who are either CLERK or MANAGER.

What is the difficulty level of this exercise?



Inviting useful, relevant, well-written and unique guest posts