>>PREVIOUS>>
161) Display employee name and department name for each employee.
SQL>select empno,dname from emp,dept where emp.deptno=dept.deptno
162)Display employee number,name and location of the department in which he is working.
SQL>select empno,ename,loc,dname from emp,dept
where emp.deptno=dept.deptno;
163) Display ename,dname even if there are no employees working in a particular department(use outer join).
SQL>select ename,dname from emp,dept where emp.deptno=dept.deptno(+)
164) Display employee name and his manager name.
SQL>select p.ename,e.ename from emp e,emp p where e.empno=p.mgr;
165) Display the department name and total number of employees in each department.
SQL>select dname,count(ename) from emp,dept where
emp.deptno=dept.deptno group by dname;
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>
SQL>SELECT ENAME FROM EMP
WHERE ROWID=(SELECT ROWID FROM EMP WHERE ROWNUM<=10 MINUS SELECT ROWID FROM EMP WHERE ROWNUM <10)>
>>NEXT>>