create TABLE Department(
    dept_id int NOT NULL PRIMARY KEY,
    dept_name varchar(50) not null,
    );
INSERT INTO Department VALUES(101,'DEVELOPER'),
      (102,'SALESMAN  '),
      (103,'ANALYST'),
      (104,'MANAGER '),
      (105,'MARKETING');
      

create TABLE EMPLOYEE(
    emp_id int NOT NULL PRIMARY KEY,
    dept_id int FOREIGN KEY REFERENCES Department(dept_id),
    mngr_id int NOT NULL,
    emp_name varchar(50) not null,
    salary float NOT NULL,
    );
    
    INSERT INTO EMPLOYEE VALUES(501,102,1001,'TURNER',15000),
      (502,104,1004,'FORD',18000),
      (503,102,1001,'FRANK',35000),
      (504,105,1005,'BLAKE',22000),
      (505,103,1003,'SCOTT',28000),
      (506,102,1003,'RAJ',25000);
/*Q1. write a SQL query to find Employees who have the biggest salary in their Department*/
    -- select D.dept_id,MAX(E.salary) AS MAXIMUM_SALARY from Employee E JOIN Department D ON E.dept_id=D.dept_id group BY D.dept_id
/*Q2. write a SQL query to find Departments that have less than 3 people in it*/
-- select D.dept_name,COUNT(E.dept_id) AS TOTAL 
-- FROM Employee E 
-- JOIN Department D ON E.dept_id=D.dept_id
-- group BY D.dept_name
-- HAVING TOTAL>2




SELECT E.emp_name,E.salary FROM Employee E JOIN Department D ON E.dept_id=D.dept_id
WHERE E.salary IN (select MAX(salary) from Employee group BY dept_id)

-- SELECT e.emp_name,
--       d.dept_name,
-- 	   e.salary
-- FROM employee e
-- JOIN department d
-- ON d.dept_id=e.dept_id
-- WHERE e.salary IN
--     (SELECT max(salary)
--     FROM employee
--     GROUP BY dept_id) ;

-- SELECT dept_name AS Department, e1.emp_name AS Employee, e1.Salary AS Salary
-- FROM Department JOIN Employee e1 ON Department.dept_id = e1.dept_id
-- WHERE e1.Salary = (SELECT Max(Salary) FROM Employee e2 where e2.dept_id = e1.dept_id)