Oracle- SQL interview Question Answer Part -8 - ETL- iNFORMATICA DEVELOPER

Friday, July 12, 2019

Oracle- SQL interview Question Answer Part -8

              
www.ktbluehost.com
                       
                               
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;

1 comment: