SQL Queries ask in Interviews Part 11

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;

<<<Previous    Continue>>>