SQL Help Using NESTED SELECTION

Question

Here's my table

CREATE TABLE emp (num INTEGER NOT NULL, 
              name VARCHAR(20) NOT NULL, 
              dept VARCHAR(20) NOT NULL, 
              salary INTEGER NOT NULL,
              boss INTEGER NOT NULL,
              PRIMARY KEY (num),
              FOREIGN KEY (boss) REFERENCES emp (num)
              );

INSERT INTO emp
VALUES ('1', 'PRESIDENT', 'gh', '10000', '1');
INSERT INTO emp
VALUES ('2', 'Bob', 'Slave', '6456', '3');
INSERT INTO emp
VALUES ('3', 'Matthew', 'M', '1', '1');
INSERT INTO emp
VALUES ('4', 'Marl', 'P', '534465', '2');
INSERT INTO emp
VALUES ('5', 'Apple', 'P', '554545646512', '2');
INSERT INTO emp
VALUES ('6', 'Roy', 'Slave', '125', '1');
INSERT INTO emp
VALUES ('7', 'Marth', 'Slave', '56456', '1');
INSERT INTO emp
VALUES ('8', 'Mart', 'Slave', '98', '3');

Here are my Queries:

SELECT * FROM emp;

SELECT * FROM emp 
  WHERE boss = (SELECT num FROM emp
              WHERE num = boss) AND num != boss;

SELECT e1.num,e1.name FROM emp e1
  WHERE
(SELECT e2.salary FROM emp e2 
  WHERE e2.boss = (SELECT e3.num FROM emp e3
                  WHERE e3.num = e3.boss) AND e2.num != e2.boss) < 98;

So the first output prints out everything as expected. The second output prints out: Matthew, Roy and Marth as expected.

But the final output prints out one.

This is a practice test question I was given My goal is print the num and name of everyone working under the president who makes less than MIN of people who aren't working for the president.

I can calculate the MIN with the following code:

SELECT min(salary) FROM emp 
            WHERE boss != (SELECT num FROM emp
                        WHERE num = boss);

Ideally I want to replace 98 in the previous query with this statement, but I decided it would be best if I broke it down and tried one thing at a time.

CL. · Accepted Answer · 2016-02-28 07:49:35Z

1

Your problem is too many subqueries. e2 is completely independent from e1; the e2 subquery returns all salaries of those working under the president; the comparison is then against the first value returned.

You could use a correlated subquery to tie e2 to e1, but you don't need a subquery at all:

SELECT e1.num,
       e1.name
FROM emp AS e1
WHERE e1.boss = (SELECT e3.num
                 FROM emp AS e3
                 WHERE e3.num = e3.boss)
  AND e1.num != e1.boss
  AND e1.salary < 98;

answered Feb 28, 2016 at 7:49

CL.

182k18 gold badges241 silver badges282 bronze badges

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

2 Comments

The Muffin Boy Over a year ago

Thanks it worked. Just a few questions though. Any reason you used 'AS' instead of emp e1? I also went ahead and erased all the AS commands got the same answer. Is there a benefit in creating e1, e2 and e3 here?

CL. Over a year ago

AS is optional, but why would you want to omit it?

Collectives™ on Stack Overflow

SQL Help Using NESTED SELECTION

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