subqueries in Oracle SQL

Question

I want to use subqueries in SQL in Oracle Express for getting the salary_id of the highest salaries that the employees get in their departments. The tables that I have are:

 Employee
 Emp_id  Salary  Dept_id
 100     1000    a101
 200     2000    a101
 300     2500    b102

 Salary
 Grade   LowSal HiSal
 1       500    900
 2       901    2000
 3       2001   3000

First I extracted the maximum salary in each department with this query:

SELECT Max(e.SALARY) 
FROM   EMPLOYEE e 
GROUP  BY e.DEPT_ID

The results are two records: 2000 and 2500

The next step is to get the grades of those salaries, so I use the following:

SELECT GRADE 
FROM   SALARY 
WHERE  (SELECT Max(e.SALARY) 
        FROM   EMPLOYEE e 
        GROUP  BY e.DEPT_ID) BETWEEN LOWSAL AND HIGHSAL;

The error that I received is: ORA-01427: single-row sub-query returns more than one row

How I can do that SQL command with sub-queries in SQL?

How does the salary table link to the employee table? Where is the connection? — user330315
– user330315, Commented Oct 14, 2012 at 7:11

Lukáš Lalinský · Accepted Answer · 2012-10-14 07:18:38Z

1

There are multiple ways to write such queries. You can add the subquery to the FROM clause:

SELECT grade
FROM (SELECT max(salary) AS sal FROM employee GROUP BY dept_id) ms
JOIN salary ON ms.sal BETWEEN salary.lowsal AND salary.hisal;

Or you can use a correlated subquery:

SELECT (SELECT grade FROM salary WHERE max(salary) BETWEEN lowsal AND hisal)
FROM employee
GROUP BY dept_id

Correlated subqueries are normally slower, so the first form is preferred.

edited Oct 14, 2012 at 7:18

user330315

answered Oct 14, 2012 at 6:11

Lukáš Lalinský

41.5k6 gold badges109 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

@Manolo: the first one does work, see here: sqlfiddle.com/#!4/0a1ab/1 you must have made an error when copying.

Layla Over a year ago

sorry, I make a mistake in the one the fields, the solution works great, thanks everybody and also to the last post that web page seems really handy.

Collectives™ on Stack Overflow

subqueries in Oracle SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related