1.
Write a query to get EmpNum, Name of all the employees who are not
yet assigned to any department.
SELECT empnum, empname
FROM emp
WHERE dept_id IS NULL;
2. Write a query which gives the following data,
SELECT e.empnum, e.empname, d.dept_name
FROM emp e, dept d
WHERE e.dept_id = d.dept_id
ORDER BY e.empname;
3. Insert a new Department with Dept_Id=0 and Dept_Name=’Yet To
Assigned’ to the DEPT table.
INSERT INTO dept
(dept_id, dept_name, loc
)
VALUES (0, 'Yet To Assigned', NULL
);
4. Create a table ‘EMP_BACKUP’ similar to that of ‘EMP’ table while
inserting all the data from EMP using a single statement.
CREATE TABLE emp_backup AS SELECT * FROM emp;
5. Assign all the employees whose Dept_Id is null to newly created
Department “Yet To Assigned”.
UPDATE emp
SET dept_id = (SELECT dept_id
FROM dept
WHERE dept_name = 'Yet To Assigned')
WHERE dept_id IS NULL;
6. Write a query to get all employees of sales department whose name
ends with ‘N’.
SELECT e.*
FROM emp e, dept d
WHERE e.dept_id = e.dept_id
AND d.dept_name = 'SALES'
AND e.emp_name LIKE '%N';
7. Write a query to get number of managers.
SELECT DISTINCT COUNT (manager_id)
FROM emp;
8. Write a query to get all employees who have joined in the month of
June.
SELECT *
FROM emp
WHERE TO_CHAR (hire_date, 'MON') = 'JUN';
OR
SELECT *
FROM emp
WHERE TO_CHAR (hiredate, 'MM') = '06';
9. Write a query to get the number of employees in Sales & Operation
departments.
SELECT d.dept_name, COUNT (e.empnum) emp_cnt
FROM dept d, emp e
WHERE d.dept_id = e.dept_id
AND d.dept_name IN ('SALES', 'OPERATIONS')
GROUP BY d.dept_name;
10. Write a query which gives the count of employees for each of
the salary grade.
SELECT s.grade, s.losal, s.hisal, COUNT (e.empno)
FROM salgrade s, emp e
WHERE e.sal BETWEEN s.losal AND s.hisal
GROUP BY s.grade, s.losal, s.hisal
ORDER BY 1;
11. For research department get employees along with their manager
name.
SELECT d.dname, e.ename employee, e1.ename manager
FROM emp e, emp e1, dept d
WHERE e.mgr = e1.empno(+)
AND e.deptno = d.deptno
AND d.dname = 'RESEARCH';
12. Select all the employee who are reporting to the same manager
as that of the JAMES.
SELECT e.ename employee, e1.ename manager
FROM emp e, emp e1
WHERE e.mgr = e1.empno(+)
AND e1.ename='JAMES';
13. Get the employees who are elder than their managers.
SELECT e.ename employee, e.age emp_age,
e1.ename manager, e1.age mgr_age
FROM emp e, emp e1
WHERE e.mgr = e1.empno(+)
AND e.age < e1.age;
14. Write a query to get output as below,
SELECT d.dept_name, SUM (DECODE (e.sex, 'M', 1, 0)) male,
SUM (DECODE (e.sex, 'F', 1, 0)) female
FROM emp e, dept d
WHERE e.dept_id = e.dept_id
GROUP BY d.dept_name;
15. Get all the employees who earn more than the average of the
salaries.
SELECT *
FROM emp
WHERE sal > (SELECT AVG (sal)
FROM emp)
16 .if salary of each female employee is increased by 10% then what will
be the new average of the salaries of all employees. Get this using a
single query.
SELECT AVG (sal) "old avg of salary",
AVG (CASE
WHEN sex = 'F'
THEN sal + ((sal * 10) / 100)
WHEN sex = 'M'
THEN sal
END
) "new avg of salary"
FROM emp;
17. Write a query to list all the manager names along with total
salary earned by his immediate subordinates.
SELECT e1.ename manager, SUM (e.sal) "Sum Of Subordinate Sal"
FROM emp e, emp e1
WHERE e.mgr = e1.empno(+)
AND e1.ename IS NOT NULL
GROUP BY e1.ename
18. Select top 3 salaried employees
SELECT *
FROM (SELECT ename, sal
FROM emp
ORDER BY sal DESC)
WHERE ROWNUM <= 3;
19. Select top salaried employees in each department.
SELECT ename, deptno, sal
FROM emp
WHERE (deptno, sal) IN (SELECT deptno, MAX (sal)
FROM emp
GROUP BY deptno)
ORDER BY deptno;
20. From the given table, find those employees who are more than 21
years of age.
SELECT fa.*, TRUNC ((SYSDATE - dob) / 365) age
FROM find_age fa
WHERE TRUNC ((SYSDATE - dob) / 365) > 21;
