I. SCHEMA :
Table 1 : DEPT
DEPTNO (NOT NULL , NUMBER(2)), DNAME (VARCHAR2(14)),
LOC (VARCHAR2(13)
Table 2 : EMP
EMPNO (NOT NULL
, NUMBER(4)), ENAME (VARCHAR2(10)),
JOB (VARCHAR2(9)), MGR (NUMBER(4)),
HIREDATE (DATE),
SAL (NUMBER(7,2)), COMM (NUMBER(7,2)),
DEPTNO (NUMBER(2))
MGR is the empno
of the employee whom the employee reports to. DEPTNO is a foreign key.
QUERIES
1. List all the employees who
have at least one person reporting to them.
2. List the employee details
if and only if more than 10 employees are present in department no 10.
3. List the name of the
employees with their immediate higher authority.
4. List all the employees who
do not manage any one.
5. List the employee details
whose salary is greater than the lowest salary of an employee belonging to
deptno 20.
6. List the details of the
employee earning more than the highest paid manager.
7. List the highest salary
paid for each job.
8. Find the most recently
hired employee in each department.
9. In which year did most
people join the company? Display the year and the number of employees.
10. Which department has the
highest annual remuneration bill?
11. Write a query to display a
‘*’ against the row of the most recently hired employee.
12. Write a correlated
sub-query to list out the employees who earn more than the average salary of
their department.
13. Find the nth maximum
salary.
14. Select the duplicate
records (Records, which are inserted, that already exist) in the EMP table.
15. Write a query to list the
length of service of the employees (of the form n years and m months).
KEYS:
1.
SELECT DISTINCT(A.ENAME) FROM
EMP A, EMP B WHERE A.EMPNO = B.MGR;
or SELECT ENAME FROM EMP WHERE
EMPNO IN (SELECT MGR FROM EMP);
2.
SELECT * FROM EMP WHERE DEPTNO
IN (SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(EMPNO)>10 AND
DEPTNO=10);
3.
SELECT A.ENAME
"EMPLOYEE", B.ENAME "REPORTS TO" FROM EMP A, EMP B WHERE
A.MGR=B.EMPNO;
4.
SELECT * FROM EMP WHERE EMPNO
IN ( SELECT EMPNO FROM EMP MINUS SELECT MGR FROM EMP);
5.
SELECT * FROM EMP WHERE SAL
> ( SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO=20);
6.
SELECT * FROM EMP WHERE SAL
> ( SELECT MAX(SAL) FROM EMP GROUP BY JOB HAVING JOB = 'MANAGER' );
7.
SELECT JOB, MAX(SAL) FROM EMP
GROUP BY JOB;
8.
SELECT * FROM EMP WHERE
(DEPTNO, HIREDATE) IN (SELECT DEPTNO, MAX(HIREDATE) FROM EMP GROUP BY DEPTNO);
9.
SELECT TO_CHAR(HIREDATE,'YYYY')
"YEAR", COUNT(EMPNO) "NO. OF EMPLOYEES" FROM EMP GROUP BY
TO_CHAR(HIREDATE,'YYYY') HAVING COUNT(EMPNO) = (SELECT MAX(COUNT(EMPNO)) FROM
EMP GROUP BY TO_CHAR(HIREDATE,'YYYY'));
10.
SELECT DEPTNO,
LPAD(SUM(12*(SAL+NVL(COMM,0))),15) "COMPENSATION" FROM EMP GROUP BY
DEPTNO HAVING SUM( 12*(SAL+NVL(COMM,0))) = (SELECT
MAX(SUM(12*(SAL+NVL(COMM,0)))) FROM EMP GROUP BY DEPTNO);
11.
SELECT ENAME, HIREDATE,
LPAD('*',8) "RECENTLY HIRED" FROM EMP WHERE HIREDATE = (SELECT
MAX(HIREDATE) FROM EMP) UNION SELECT ENAME NAME, HIREDATE, LPAD(' ',15)
"RECENTLY HIRED" FROM EMP WHERE HIREDATE != (SELECT MAX(HIREDATE)
FROM EMP);
12.
SELECT ENAME,SAL FROM EMP E
WHERE SAL > (SELECT AVG(SAL) FROM EMP F WHERE E.DEPTNO = F.DEPTNO);
13.
SELECT ENAME, SAL FROM EMP A
WHERE &N = (SELECT COUNT (DISTINCT(SAL)) FROM EMP B WHERE A.SAL<=B.SAL);
14.
SELECT * FROM EMP A WHERE
A.EMPNO IN (SELECT EMPNO FROM EMP GROUP BY EMPNO HAVING COUNT(EMPNO)>1) AND
A.ROWID!=MIN (ROWID));
15.
SELECT ENAME
"EMPLOYEE",TO_CHAR(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12))||'
YEARS '|| TO_CHAR(TRUNC(MOD(MONTHS_BETWEEN (SYSDATE, HIREDATE),12)))||' MONTHS
' "LENGTH OF SERVICE" FROM EMP;
Note: SQLs can be written in multiple ways, we are giving just one way for the desired output.
No comments:
Post a Comment