SQL Queries ask in Interviews Part 11

SQL Queries ask in Interviews, www.askhareesh.com
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;




*/