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
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)
select second max salary from emp;
select max(sal) fromemp where sal<(select max(sal) from emp);
Great Article
ReplyDeleteData Mining Projects
Python Training in Chennai
Project Centers in Chennai
Python Training in Chennai
Thank you much more giving the Great Post. I appreciate a good job and Keep it up.
ReplyDeletePrimavera Course in Chennai
Primavera Coaching in Chennai
Power BI Training in Chennai
Excel Training in Chennai
Oracle Training in Chennai
Tableau Training in Chennai
Pega Training in Chennai
Graphic Design Courses in Chennai
Placement Training in Chennai
Soft Skills Training in Chennai
Thank you because you have been willing to share information with us. we will always appreciate all you have done here because I know you are very concerned with our. questionquery.com
ReplyDelete