151 ) find the employees with sal more than manager

151 ) find the employees with sal more than manager

------------------------------------------------------------------------

SELECT    e.name  

FROM     Employee   e

WHERE     e.salary   > (

        SELECT  m.salary  FROM  Employee m 

        WHERE m.id = e.managerId

    );    

------------------------------------------------------------------------

 Below is DDL and DML for same 

### DDL (Create Table)

------------------------------------------------------------------------

 CREATE TABLE employee

  (
     id        INT PRIMARY KEY,
     NAME      VARCHAR(50),
     salary    INT,
     managerid INT
  ); 

------------------------------------------------------------------------

### DML (Insert Data)

------------------------------------------------------------------------

INSERT INTO Employee (id, name, salary, managerId) 

VALUES 

  (1, 'Joe', 70000, 3), 

  (2, 'Henry', 80000, 4), 

  (3, 'Sam', 60000, NULL), 

  (4, 'Max', 90000, NULL);

INSERT INTO Employee (id, name, salary, managerId) 

VALUES 

  (5, 'Alice', 85000, 3), 

  (6, 'Bob', 95000, 4), 

  (7, 'Charlie', 62000, 1), 

  (8, 'Diana', 78000, 2), 

  (9, 'Ethan', 91000, NULL), 

  (10, 'Fiona', 72000, 3), 

  (11, 'George', 68000, 1), 

  (12, 'Hannah', 88000, 4), 

  (13, 'Ian', 99000, NULL), 

  (14, 'Julia', 75000, 2);

------------------------------------------------------------------------

select * from Employee order by 1  

------------------------------------------------------------------------

output  : 

------------------------------------------------------------------------

1	Joe	70000	3
2	Henry	80000	4
3	Sam	60000
4	Max	90000
5	Alice	85000	3
6	Bob	95000	4
7	Charlie	62000	1
8	Diana	78000	2
9	Ethan	91000
10	Fiona	72000	3
11	George	68000	1
12	Hannah	88000	4
13	Ian	99000
14	Julia	75000	2

------------------------------------------------------------------------

--------------------- # Method 1  : using joins  ---------

------------------------------------------------------------------------

 

 SELECT 

    e1.name AS Employee, 

    e1.salary AS salary, 

    e2.name AS manager, 

    e2.salary AS manager_salary

FROM 

    employee e1

JOIN 

    employee e2 ON e1.managerid = e2.id

WHERE 

    e1.salary > e2.salary;

------------------------------------------------------------------------

output : 

------------------------------------------------------------------------

id, name, salary, managerId

Joe	70000	Sam	60000
Alice	85000	Sam	60000
Bob	95000	Max	90000
Fiona	72000	Sam	60000

    

--------------------------------------------------------------------------

--------------------------------- # Method 2 : using  SUBQUERY ---------

------------------------------------------------------------------------

SELECT     e.name AS Employee

FROM     Employee e

WHERE     m.salary > (

        SELECT  m.salary  FROM Employee m 

        WHERE m.id = e.managerId

    );