|
Coder
|
Posted: Sunday 25, May 2008 04:51:56 AM
|
|
|
PL SQL Interview question and answer for TCS.
1. Write the Sql query for creating database backup?
2. What is TABLE SPACE?
3. Write a query to delete similar records in same table
4. What is the order of pre-defined exceptions.
5. What is mean by forward declaration and where we'll use it.
6. What is bulk bind
7. Can i call procedure in package
8. How to import .dmp file in lower version of oracle from high
er version ?
9. How do u call in & out parameters for stored procedures?
10. What are nested triggers ?
11. Types of joins ?
12. Write a query to get 2nd maximum salary in an employee tabl
e ?
13. What is difference between triggers and stored procedures.
And advantages of SP over triggers ?
14. What is INSTEAD OF trigger ?
15. Types of backups ?
16. Can we create non-clustered index on a clustered index ?
17. What is clustered, non-clustered and unique index. How many
indexes can be created on a table ?
18. Types of indexes. What is the default key created when a pr
imary key is created in a table ?
19. Types of locks in database ?
20. What are the types of triggers ?
21. What are the forced views
22. Does SQL*Plus contains pl/sql Engine?
23. How a reference cursor works? What all advantages are gaine
d with it? Specify the situation?
24. How to create a primary key with out creating an index?
25. When a procedure /package are getting invalidated?
26. What is the fastest way of accessing a row in a table?
27. What is difference between TRUNCATE & DELETE?
28. What is a join? Explain the different types of joins?
29. What is the result, when NULL is compared with NULL?
Please post your questions, if you know the above questions ans
wer post it to for others.
|
|
Back to Top
^ |
|
| prabha | Posted: Monday 26, May 2008 05:15:42 AM | | Q.13- Difference between triggers and stored procedure
--- both are group of related queries that executes in a batch,
but the difference is that the procedure is executed when we
explicitly call it and triggers are fired automatically when we
perform ISERT, UPDATE, DELETE query.
The advantage of stored procedure is - we can group multiple qu
eries or command in a batch and save it so we can reuse it.And
also implement progrmming features in stored procedure, can giv
e the input and output parameters.
| | Back to Top ^ | | |
| prabha | Posted: Monday 26, May 2008 08:32:02 AM | | Q. 12 query to get 2nd maximum salary in an employee table
-----
SELECT MIN(salary) FROM employee
WHERE empid IN
( SELECT TOP 2 empid FROM employee
ORDER BY salary DESC)
it will give 2nd highest salary.
IF any other way to find it then please tell me.
| | Back to Top ^ | | |
| Ashok | Posted: Monday 26, May 2008 03:23:42 PM | | Answer 3: Duplicate record deletion.
Case(i) : If your table contain less number of records then...
delete from EMP
where rowid not in (select min(rowid)
from emp group by ename);
Case(ii): If you are working with large amount of data and t
o get good performance then do the following.
select count(*), count(distinct ENAME) from emp;
COUNT(*) COUNT(DISTINCT ENAME)
---------- ---------------------
28 14
delete from emp
where rowid in
(select rid
from
(select rowid rid,
row_number()
over (
partition by ENAME
order by rowid
) rn
from emp
)
where rn <> 1
);
14 rows deleted.
Let's look closer to the solution - the inner query shows the g
roups:
SELECT ename Ename, deptno Deptno, rowid Rid,
ROW_NUMBER()
OVER (
PARTITION BY ename
ORDER BY rowid
) RNo
FROM emp
/
Ename Deptno Rid RNo
---------- ---------- ------------------ ----------
ADAMS 20 AAABrzAAEAAAAASAAK 1
ADAMS 20 AAABrzAAEAAAAASAAY 2
ALLEN 30 AAABrzAAEAAAAASAAB 1
ALLEN 30 AAABrzAAEAAAAASAAP 2
BLAKE 30 AAABrzAAEAAAAASAAF 1
BLAKE 30 AAABrzAAEAAAAASAAT 2
CLARK 10 AAABrzAAEAAAAASAAG 1
CLARK 10 AAABrzAAEAAAAASAAU 2
FORD 20 AAABrzAAEAAAAASAAM 1
FORD 20 AAABrzAAEAAAAASAAa 2
JAMES 30 AAABrzAAEAAAAASAAL 1
JAMES 30 AAABrzAAEAAAAASAAZ 2
JONES 20 AAABrzAAEAAAAASAAD 1
JONES 20 AAABrzAAEAAAAASAAR 2
KING 10 AAABrzAAEAAAAASAAI 1
KING 10 AAABrzAAEAAAAASAAW 2
MARTIN 30 AAABrzAAEAAAAASAAE 1
MARTIN 30 AAABrzAAEAAAAASAAS 2
MILLER 10 AAABrzAAEAAAAASAAN 1
MILLER 10 AAABrzAAEAAAAASAAb 2
SCOTT 20 AAABrzAAEAAAAASAAH 1
SCOTT 20 AAABrzAAEAAAAASAAV 2
SMITH 20 AAABrzAAEAAAAASAAA 1
SMITH 20 AAABrzAAEAAAAASAAO 2
TURNER 30 AAABrzAAEAAAAASAAJ 1
TURNER 30 AAABrzAAEAAAAASAAX 2
WARD 30 AAABrzAAEAAAAASAAC 1
WARD 30 AAABrzAAEAAAAASAAQ 2
Now show only the Rows which must be deleted, these are all Row
s with RNo <> 1.
SELECT Ename, Deptno, Rid, RNo
FROM
(SELECT ename Ename, deptno Deptno, rowid Rid,
ROW_NUMBER()
OVER (
PARTITION BY ename
ORDER BY rowid
) RNo
FROM emp
)
WHERE RNo <> 1
/
ENAME DEPTNO RID RNO
---------- ---------- ------------------ ----------
ADAMS 20 AAABrzAAEAAAAASAAY 2
ALLEN 30 AAABrzAAEAAAAASAAP 2
BLAKE 30 AAABrzAAEAAAAASAAT 2
CLARK 10 AAABrzAAEAAAAASAAU 2
FORD 20 AAABrzAAEAAAAASAAa 2
JAMES 30 AAABrzAAEAAAAASAAZ 2
JONES 20 AAABrzAAEAAAAASAAR 2
KING 10 AAABrzAAEAAAAASAAW 2
MARTIN 30 AAABrzAAEAAAAASAAS 2
MILLER 10 AAABrzAAEAAAAASAAb 2
SCOTT 20 AAABrzAAEAAAAASAAV 2
SMITH 20 AAABrzAAEAAAAASAAO 2
TURNER 30 AAABrzAAEAAAAASAAX 2
WARD 30 AAABrzAAEAAAAASAAQ 2
Now these Rows can easily be deleted:
DELETE FROM emp
WHERE rowid IN
(SELECT Rid
FROM
(SELECT rowid Rid,
ROW_NUMBER()
OVER (
PARTITION BY ename
ORDER BY rowid
) RNo
FROM emp
)
where RNo <> 1
);
14 rows deleted.
| | Back to Top ^ | | |
| AJProfessionals | Posted: Monday 26, May 2008 05:44:45 PM | | Thanks Ashok, Its nice explanation.
Here you are talking about Oracle database, can you give
me same query for SQl Server.
- AJ
| | Back to Top ^ | | |
| chidrupi | Posted: Thursday 29, May 2008 08:15:35 AM | | 12. select max(sal) from emp
where sal<(select max(sal) from emp);
this is the easiest way
| | Back to Top ^ | | |
| Coder | Posted: Friday 06, June 2008 12:12:46 PM | | Thanks
| | Back to Top ^ | | |
| Coder | Posted: Tuesday 24, June 2008 03:23:46 AM | | 1. What is Segmentation Error in D2K form ?
2. What is XML Parser in PL/SQL ?
| | Back to Top ^ | | |
| S. Kavitha | Posted: Saturday 05, July 2008 02:50:17 PM | | 29. What is the result, when NULL is compared with NULL?
Nothing. it will not go into the if condition at all.
Try out this,
Private Sub Command1_Click()
a = Null
b = Null
If a = b Then
MsgBox (Err.Description)
End If
End Sub
If any body find other answer pls reply
Thanks
| | Back to Top ^ | | |
|