SQL QUESTIONS AND ANSWERS


1. Select duplicate value from the table?

select no, name from tt group by no, name having count(no) > 1;

2. Delete duplicate record from the table.

delete from tt a where rowid <> (select max(rowid) from tt b where a.no=b.no);


3. Delete duplicate record from the table which is recent

delete from tt a where rowid <> (select min(rowid) from tt b where a.no=b.no);

4. Delete duplicate record from the table which is old fear.

delete from tt a where rowid <> (select max(rowid) from tt b where a.no=b.no);

5. select top 5 salary

SELECT ENAME, SAL FROM ( SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM <=5;

6. select senior 5 people

SELECT ENAME, HIREDATE FROM EMP WHERE ROWNUM <=5

7. select top 2 salary for each dept

SELECT ENAME, SAL, DEPTNO FROM( SELECT ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RN, ENAME, SAL, DEPTNO FROM EMP) WHERE RN <=2;

8. select top 2 salary using rank()

SELECT ENAME, SAL FROM(SELECT RANK() OVER(ORDER BY SAL DESC) RN, ENAME, SAL FROM EMP) WHERE RN<=2;

9. What is rank and dense rank?
rank()

1 100
2 50
2 50
4 30

DENSE_rank()

1 100
2 50
2 50
3 30

10. how many employes are working in each deptment table

SELECT D.DEPTNO, COUNT(E.EMPNO) "Number Of Employees" FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO GROUP BY D.DEPTNO;

12. select name, manager_Name for each employee.

 SELECT E.ENAME, E1.ENAME FROM EMP E, EMP E1 WHERE E.MGR=E1.EMPNO;


SELECT E.ENAME, E1.ENAME FROM EMP E LEFT OUTER JOIN EMP E1 ON   E.MGR=E1.EMPNO;

13. How to convert column to column in oracle?


SELECT *FROM (
SELECT NAME,
CASE PIVOT
WHEN 1
THEN CUST_VALUE_1
WHEN 2
THEN CUST_VALUE_2
WHEN 3
THEN CUST_VALUE_3
END SALARY FROM TR1,(SELECT ROWNUM PIVOT FROM DUAL CONNECT BY LEVEL <=3))
ORDER BY NAME;

14. Select First and Second Half Record:

select *from emp where rownum <= (select count(*)/2 from emp) --------------  First Half


select *from emp minus select *from emp where rownum <= (select count(*)/2 from emp)   --------------- Second Half