You can see part 9 here.
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) Delete the 10th record of emp table;
SQL>DELETE FROM EMP WHERE EMPNO=(SELECT EMPNO FROM EMP WHERE ROWNUM<11 MINUS SELECT EMPNO FROM EMP WHERE ROWNUM<10);
138) Create a copy of emp table;
SQL>create table new_table as select * from emp where 1=2;
139) Select ename if ename exists more than once.
SQL>select ename from emp e group by ename having count(*)>1;
140) Display all enames in reverse order?e.g. (SMITH:HTIMS).
SQL>SELECT REVERSE(ENAME)FROM EMP;
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;
Questions/Suggestions
Have any question or suggestion for us?Please feel free to post in
Q&A Forum
