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:

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

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.