SQL Queries ask in Interviews Part 10

136) Display the 10th record of emp table without using group by and rowid?
SQL>SELECT * FROM EMP WHERE ROWNUM<11 MINUS SELECT * FROM EMP WHERE ROWNUM<10;

137) Create a copy of emp table;
SQL>create table new_table as select * from emp where 1=2;

138) Select ename if ename exists more than once.
SQL>select ename  from emp e group by ename having count(*)>1;

139) Display all enames in reverse order?(SMITH:HTIMS).
SQL>SELECT REVERSE(ENAME)FROM EMP;

140) Display those employee whose joining of month and grade is equal.
SQL>SELECT ENAME FROM EMP WHERE SAL BETWEEN
(SELECT LOSAL FROM SALGRADE WHERE
GRADE=TO_CHAR(HIREDATE,’MM’)) AND
(SELECT HISAL FROM SALGRADE WHERE
GRADE=TO_CHAR(HIREDATE,’MM’));

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 varchar2(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;

<<<Previous    Continue>>>