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.

SQL: Tips of the Day

Grouped LIMIT in PostgreSQL: Show the first N rows for each group?

db=# SELECT * FROM xxx;
 id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  3 |          1 | C
  4 |          1 | D
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
  8 |          2 | H
(8 rows)

I need the first 2 rows (ordered by name) for each section_id, i.e. a result similar to:

id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
(5 rows)

PostgreSQL v9.3 you can do a lateral join

select distinct t_outer.section_id, t_top.id, t_top.name from t t_outer
join lateral (
    select * from t t_inner
    where t_inner.section_id = t_outer.section_id
    order by t_inner.name
    limit 2
) t_top on true
order by t_outer.section_id;

Database: PostgreSQL

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

 





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