Oracle Interview Based Queries-4

151) My boss has changed his mind. Now he doesn't want to pay more than 10,000.so revoke that salary constraint.

SQL>alter table emp modify constraint chk_001 enable;

152) Add column called as mgr to your emp table;

SQL>alter table emp add(mgr number(5));

153) Oh! This column should be related to empno. Give a command to add this constraint.

SQL>ALTER TABLE EMP ADD CONSTRAINT MGR_DEPT FOREIGN KEY(MGR) REFERENCES EMP(EMPNO)

154) Add deptno column to your emp table;

SQL>alter table emp add(deptno number(5));

155) This deptno column should be related to deptno column of dept table;

SQL>alter table emp add constraint dept_001 foreign key(deptno)

reference dept(deptno) [deptno should be primary key]

156) Give the command to add the constraint.

SQL>alter table <table_name) add constraint <constraint_name>

<constraint type>

157) Create table called as newemp. Using single command create this table as well as get data into this table(use create table as);

SQL>create table newemp as select * from emp;

SQL>Create table called as newemp. This table should contain only

empno,ename,dname.

SQL>create table newemp as select empno,ename,dname from emp,dept where 1=2;

158) Delete the rows of employees who are working in the company for more than 2 years.

SQL>delete from emp where (sysdate-hiredate)/365>2;

159) 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

160) If any employee has commission his commission should be incremented by 10% of his salary.

SQL>update emp set comm=sal*.1 where comm is not null;

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 Repeated 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 EMPWHERE ROWID=(SELECT ROWID FROM EMP WHERE ROWNUM<=10 MINUS SELECT ROWID FROM EMP WHERE ROWNUM <10)

173) select second max salary from emp;

select max(sal) fromemp where sal<(select max(sal) from emp);