quote:

---

Originally posted by omairbang


h) Give an employee 8% salary increases if he or she is hired before 1981 and has salary less than 1500.

UPDATE employee SET SAL = SAL * 1.08 WHERE e.HIREDATE< '19830101' AND e.SAL<1500;

i) Find the minimum salary of each department from EMP.

SELECT d.name, MIN(sal) as “Lowest Salary” FROM DepartmentTable GROUP BY d.name;

j) Find the number of employees of all the departments located in DALLAS

[]
SELECT COUNT(e.EMPNO) FROM employee e, department d WHERE e.DEPTNO = d.DEPTNO AND d.LOCATION = 'Dallas';

k) Find the employees supervised by King and Blake

SELECT e1.name 
FROM Employee e1
JOIN Employee e2
ON e2.empno = e1.mgr
WHERE e2.empname IN ('King','Blake')

l) Find those employees who make more than BLAKE does

SELECT *
FROM Employee
WHERE Salary > (SELECT Salary
FROM Employee
WHERE empname = 'Blake'
)

m) Find the maximum salary among the people who work in Dallas

same as j but use max over salary






n) Give everybody 10% salary increase in the departments whose maximum salaries are less than 2000

UPDATE e
SET Sal = Sal * 1.1
FROM Employee e
INNER JOIN (SELECT DeptNo
            FROM Employee e1
            GROUP BY DeptNo
            HAVING MAX(Salary) < 2000
           )d
ON d.DeptNo = e.DeptNo

---