﻿ SQL: Employees earning 60000 or not working as analysts

# 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

Pictorial Presentation:

Sample Solution:

``````SELECT e.emp_name,
e.job_name,
(12*e.salary)"Annual Salary",
e.dep_id,
d.dep_name,
FROM employees e,
department d,
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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

## 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