Oracle- SQL interview Question Answer Part -8

              

                       
                               

141) Display those employee whose joining DATE is available in deptno.

SQL>SELECT ENAME FROM EMP WHERE TO_CHAR(HIREDATE,'DD')=DEPTNO

142) Display those employees name as follows

               A ALLEN

               B BLAKE

SQL> SELECT SUBSTR(ENAME,1,1),ENAME FROM EMP;

143) List out the employees ename,sal,PF(20% OF SAL) from emp;

SQL>SELECT ENAME,SAL,SAL*.2 AS PF FROM EMP;             

144) Create table emp with only one column empno;

SQL>Create table emp as select empno from emp where 1=2;

145) Add this column to emp table ename vrachar2(20).

SQL>alter table emp add(ename varchar2(20));

146) Oops I forgot give the primary key constraint.  Add in now.

SQL>alter table emp add primary key(empno);

147) Now increase the length of ename column to 30 characters.

SQL>alter table emp modify(ename varchar2(30));

148) Add salary column to emp table.

SQL>alter table emp add(sal number(10));

149) I want to give a validation saying that salary cannot be greater 10,000

         (note give a name to this constraint)

SQL>alter table emp add constraint chk_001 check(sal<=10000)

150) For the time being I have decided that I will not impose this validation.My boss has agreed to pay more than 10,000.

SQL>again alter the table or drop constraint with  alter table emp drop constraint chk_001 (or)Disable the constraint by using  alter table emp modify constraint chk_001 disable;

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;