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;
