Oracle Interview Queries

1. Rank the emp details according to their salary[Highest - Lowest].

Analytical Functions – Rank & Dense_Rank :

ANS>

SELECT  LNAME, DEPTID, JOBID, SAL,

RANK() OVER(ORDER BY SAL DESC)                RANK,

              DENSE_RANK() OVER(ORDER BY SAL DESC) DENSERANK

FROM EMP;

LNAME	DEPTID	JOBID	SAL	RANK	DENSERANK
king	90	ad_pres	24000	1	1
sundar	20	ad_vp	24000	1	1
prathap	60	ad_acc	15500	3	2
mourgos	50	st_man	14500	4	3
hunold	60	it_prog	12000	5	4
kocher	20	ad_vp	10000	6	5

2. Populate the emp details along with the cumulative sum of the salary as an another column:

ANS>

SELECT  LNAME, DEPTID, SAL,

              SUM(SAL) OVER(ORDER BY DEPTID, LNAME ) RUNNING_TOAL

FROM EMP;

LNAME	DEPTID	SAL	RUNNING_TOAL
kocher	20	10000	10000
sundar	20	24000	34000
mourgos	50	14500	48500
hunold	60	12000	60500
prathap	60	15500	76000
king	90	24000	100000

3. Populate dept. wise cumulative salary along with the emp name, dept id, sal:

ANS>

SELECT UPPER(LNAME), DEPTID, SAL,

       SUM(SAL) OVER(PARTITION BY DEPTID ORDER BY FNAME) DEPT_TOTAL

FROM EMP;

LNAME	DEPTID	SAL	DEPT_TOTAL
sundar	20	24000	24000
kocher	20	10000	34000
mourgos	50	14500	14500
prathap	60	15500	15500
hunold	60	12000	27500
king	90	24000	24000

4. Query the emp details along with their dept using ON clause:

ANS>

SELECT EMPID, UPPER(LNAME) NAME, SAL, JOBID, DEPTID, DEPNAM

  FROM EMP A JOIN DEPT B

                             ON(A.DEPTID=B.DEPID);

EMPID	NAME	SAL	JOBID	DEPTID	DEPNAM
102	SUNDAR	24000	ad_vp	20	MARKETING
101	KOCHER	10000	ad_vp	20	MARKETING
124	MOURGOS	14500	st_man	50	IT DEPT
105	PRATHAP	15500	ad_acc	60	RMG
103	HUNOLD	12000	it_prog	60	RMG
100	KING	24000	ad_pres	90	QMG

5. Provide the total marks for the each student from the below structured table:

SNAME	M1	M2	M3	GRADE
LOKHS	95	85	100	A
PRATHAB	99	87	99	A
SUNDAR	50	50	50	C
KUMAR	100	99	80	A

ANS>

SELECT SNAME, M1, M2, M3, SUM(M1+M2+M3) TOTAL

FROM STUD_MARK

GROUP BY SNAME, M1, M2, M3 ORDER BY TOTAL;

SNAME	M1	M2	M3	TOTAL
SUNDAR	50	50	50	150
KUMAR	100	99	80	279
LOKHS	95	85	100	280
PRATHAB	99	87	99	285

6. Provide me the top 3 highest salaried employee details:

ANS>

SELECT * FROM EMP A WHERE 3>=(SELECT COUNT(DISTINCT SAL) FROM EMP B WHERE A.SAL<=B.SAL) ORDER BY SAL DESC;

7. Delete the duplicate record which exists in the emp table:

ANS>

DELETE FROM EMP A WHERE ROWID >

(SELECT MIN(ROWID) FROM EMP B WHERE A.EMPID=B.EMPID);

DELETE FROM EMP A WHERE ROWID NOT IN

(SELECT MAX(ROWID) FROM EMP B WHERE A.EMPID=B.EMPID);

DELETE FROM EMP WHERE ROWID NOT IN

(SELECT MAX(ROWID) FROM EMP GROUP BY EMPID);

8. Query the salary for the each employee in words:

ANS>

SELECT  UPPER(LNAME) NAME, DEPTID, SAL,

              TO_CHAR(TO_DATE(SAL,'J'),'JSP')     SAL_IN_WORDS

FROM EMP;

NAME	DEPTID	SAL	SAL_IN_WORDS
KING	90	24000	TWENTY-FOUR THOUSAND
KOCHER	20	10000	TEN THOUSAND
HUNOLD	60	12000	TWELVE THOUSAND
MOURGOS	50	14500	FOURTEEN THOUSAND FIVE HUNDRED
SUNDAR	20	24000	TWENTY-FOUR THOUSAND
PRATHAP	60	15500	FIFTEEN THOUSAND FIVE HUNDRED

9. Query to find the free tablespace:

ANS>

select tablespace_name, round(sum(bytes)/(1024*1024),0) MB

  from dba_free_space

group by tablespace_name;