PLSQL

PLSQL in 21 Hours

Wishing Thanks to My Readers.
Hi reader’s,
I’m very much happy to see that lot of people from across the globe visited my blog and visitor’s count has crossed 3000 + with in 40 days of starting the blog. I o that I can make the blog more informative &  interactive . For viewers info-would request my readers to start posting your valuable comments and requests sublimation who visit my Informatica blog, to visit my SQL or Data warehousing blogs too as I spent a lot of time posting the information, which adherence guide 
before taking up any interview.is very useful for the job seekers or Data warehousing professionals in your day today activities at work or can also be used as the quick ref
I’m planning to publish a book on informatica soon which will cover all major concepts of the data warehousing in detail, including the SQL concepts used by an ETL consultants, Basic Unix Commands and Introduction to the Scheduler tools like Autosys, Tivoli and off course Informatica with simple exam-ples.
I would highly appreciate if few people come forward and post the interview questions or the scenarios which they have encountered in there day to day work for discussion at the discussion board. 
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;


166)Display the department name
along with total salary in each department.


SQL>select dname,sum(sal) from
emp,dept where emp.deptno=dept.deptno group by dname;


167) Display itemname and total
sales amount for each item.


SQL>select itemname,sum(amount)
from item group by itemname;


168) Write a Query To Delete The
Repeated Rows from emp table;


SQL>Delete from emp where rowid
not in(select min(rowid)from emp group by ename)


169) TO DISPLAY 5 TO 7 ROWS FROM A
TABLE


SQL>select ename from emp where
rowid in(select rowid from emp where rownum<=7 minus select rowid from empi
where rownum<5)


170) DISPLAY TOP N ROWS FROM TABLE?


SQL>SELECT * FROM (SELECT * FROM
EMP ORDER BY ENAME DESC)WHERE ROWNUM <10;


171) DISPLAY TOP 3 SALARIES FROM
EMP;


SQL>SELECT SAL FROM ( SELECT *
FROM EMP ORDER BY SAL DESC ) WHERE ROWNUM <4


172) DISPLAY 9th FROM THE EMP TABLE?


SQL>SELECT ENAME FROM EMPWHERE
ROWID=(SELECT ROWID FROM EMP WHERE ROWNUM<=10 MINUS SELECT ROWID FROM EMP
WHERE ROWNUM <10)


173) select second max salary from
emp;


select max(sal) fromemp where
sal<(select max(sal) from emp);