Sequention Query Language II . 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.
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. List the employee details if and only if more than 10 employees are present in department no 10.
SELECT * FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(EMPNO)>10 AND DEPTNO=10);
3. List the name of the employees with their immediate higher authority.
SELECT A.ENAME "EMPLOYEE", B.ENAME "REPORTS TO" FROM EMP A, EMP B WHERE A.MGR=B.EMPNO;
4. List all the employees who do not manage any one.
SELECT * FROM EMP WHERE EMPNO IN ( SELECT EMPNO FROM EMP MINUS SELECT MGR FROM EMP);
5. List the employee details whose salary is greater than the lowest salary of an employee belonging to deptno 20.
SELECT * FROM EMP WHERE SAL > ( SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO=20);
6. List the details of the employee earning more than the highest paid manager.
SELECT * FROM EMP WHERE SAL > ( SELECT MAX(SAL) FROM EMP GROUP BY JOB HAVING JOB = 'MANAGER' );
7. List the highest salary paid for each job.
SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB;
8. Find the most recently hired employee in each department.
SELECT * FROM EMP WHERE (DEPTNO, HIREDATE) IN (SELECT DEPTNO, MAX(HIREDATE) FROM EMP GROUP BY DEPTNO);
9. In which year did most people join the company? Display the year and the number of employees.
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. Which department has the highest annual remuneration bill?
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. Write a query to display a ‘*’ against the row of the most recently hired employee.
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. Write a correlated sub-query to list out the employees who earn more than the average salary of their department.
SELECT ENAME,SAL FROM EMP E WHERE SAL > (SELECT AVG(SAL) FROM EMP F WHERE E.DEPTNO = F.DEPTNO);
13. Find the nth maximum salary.
SELECT ENAME, SAL FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT(SAL)) FROM EMP B WHERE A.SAL<=B.SAL);
14. Select the duplicate records (Records, which are inserted, that already exist) in the EMP table.
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. Write a query to list the length of service of the employees (of the form n years and m months).
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;
Leave a Reply
You must be logged in to post a comment.