You can see part 11 here.
166)Display the department name along with total salary in each department.
SQL>select dname,sum(sal) from emp,dept where emp.deptno=dept.deptno group by dname;
167) Display itemname and total sales amount for each item.
SQL>select itemname,sum(amount) from item group by itemname;
168) Write a Query To Delete The Repeted Rows from emp table;
SQL>Delete from emp where rowid not in(select min(rowid)from emp group by ename);
169) TO DISPLAY 5 TO 7 ROWS FROM A TABLE
SQL>select ename from emp
where rowid in(select rowid from emp where rownum<=7
minus select rowid from empi where rownum<5);
170) DISPLAY TOP N ROWS FROM TABLE?
SQL>SELECT * FROM (SELECT * FROM EMP ORDER BY ENAME DESC) WHERE ROWNUM <10;
171) DISPLAY TOP 3 SALARIES FROM EMP.
SQL>SELECT SAL FROM ( SELECT * FROM EMP ORDER BY SAL DESC ) WHERE ROWNUM <4;
172) DISPLAY 9th FROM THE EMP TABLE?
SQL>SELECT ENAME FROM EMP WHERE ROWID=(SELECT ROWID FROM EMP WHERE ROWNUM<=10 MINUS SELECT ROWID FROM EMP WHERE ROWNUM <10);
173) Select second max salary from emp;
SQL> select max(sal) fromemp where sal<(select max(sal) from emp);
174) Provide a commission(10% Comm Of Sal) to employees who are not earning any commission.
SQL>select sal*0.1 from emp where comm is null;
175) Display those employee whose joining of month and grade is equal.
SQL>SELECT ENAME FROM EMP WHERE SAL BETWEEN
(SELECT LOSAL FROM SALGRADE WHERE
GRADE=TO_CHAR(HIREDATE,’MM’)) AND
(SELECT HISAL FROM SALGRADE WHERE
GRADE=TO_CHAR(HIREDATE,’MM’));
176) Display the names of the employees who earn highest salary in their respective departments.
SQL>select ename,sal,deptno from emp where sal in(select max(sal) from emp group by deptno);
Questions/Suggestions
Have any question or suggestion for us?Please feel free to post in
Q&A Forum
I dont know about this blog before feeling regret that i found late but such a useful blog for ETl testers who wanna build career.Tq once again
Thanks Kishore