w3resource

SQL Exercise: Employees earning 60000 or not working as analysts

SQL employee Database: Exercise-53 with Solution

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

53. From the following table, write a SQL query to identify those employees who earn 60000 or more per year or do not work as ANALYST. Return employee name, job name, (12*salary) as Annual Salary, department ID, and grade.

Sample table: employees


Sample table: department


Sample table: salary_grade


Pictorial Presentation:

SQL exercises on employee Database: List the name, job name, annual salary, department id, department name and grade of the employees who earn 60000 in a year or not working as an ANALYST

Sample Solution:

SELECT e.emp_name,
       e.job_name,
       (12*e.salary)"Annual Salary",
       e.dep_id,
       d.dep_name,
       s.grade
FROM employees e,
     department d,
     salary_grade s
WHERE e.dep_id = d.dep_id
  AND e.salary BETWEEN s.min_sal AND s.max_sal
  AND (((12*e.salary)>= 60000)
       OR (e.job_name != 'ANALYST'))

Sample Output:

 emp_name | job_name  | Annual Salary | dep_id | dep_name  | grade
----------+-----------+---------------+--------+-----------+-------
 SANDRINE | CLERK     |      10800.00 |   2001 | AUDIT     |     1
 ADNRES   | CLERK     |      14400.00 |   2001 | AUDIT     |     1
 JULIUS   | CLERK     |      12600.00 |   3001 | MARKETING |     1
 WADE     | SALESMAN  |      16200.00 |   3001 | MARKETING |     2
 MADDEN   | SALESMAN  |      16200.00 |   3001 | MARKETING |     2
 MARKER   | CLERK     |      16800.00 |   1001 | FINANCE   |     2
 ADELYN   | SALESMAN  |      20400.00 |   3001 | MARKETING |     3
 TUCKER   | SALESMAN  |      19200.00 |   3001 | MARKETING |     3
 BLAZE    | MANAGER   |      33000.00 |   3001 | MARKETING |     4
 CLARE    | MANAGER   |      30600.00 |   1001 | FINANCE   |     4
 JONAS    | MANAGER   |      35484.00 |   2001 | AUDIT     |     4
 KAYLING  | PRESIDENT |      72000.00 |   1001 | FINANCE   |     5
(12 rows)

Explanation:

The given query in SQL that selects the employee name, job name, annual salary (which is calculated as 12 times the monthly salary), department ID, department name, and salary grade for all employees from the employees, department, and salary_grade tables.

The WHERE clause joins the employees and department tables based on the common column dep_id.

It then matches the monthly salary of employees with the salary grade they belong to based on min_sal and max_sal in salary_grade table and selects the employees whose annual salary is greater than or equal to 60000, or employees who do not have the job name of 'ANALYST'.

Practice Online


Sample Database: employee

employee database structure

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

Previous SQL Exercise: List the employees along with department name.
Next SQL Exercise: Employees whose salary is greater than their managers.

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.