samedi 27 février 2016

SQL Help Using NESTED SELECTION

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.

Aucun commentaire:

Enregistrer un commentaire