9. Find out nth highest salary from emp table
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B
WHERE a.sal<=b.sal);
Enter value for n:
2 SAL --------- 3700
10. Explicit Cursor attributes
There are four cursor attributes used in Oracle cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN
11. Implicit Cursor attributes
Same as explicit cursor but prefixed by the word SQL SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after executing SQL statements. 2. All are Boolean attributes.
12.Display the number value in Words
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
the output like,
SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal "Salary ",
(' Rs. ' (to_char(to_date(sal,'j'), 'Jsp')) ' only.'))
"Sal in Words" from emp
/
13. Display Odd/ Even number of records
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6
Tuesday, January 4, 2011
Monday, January 3, 2011
Interview Questions
1. What is difference between a PROCEDURE & FUNCTION?
a) A function can be called from SQL statements and queries while procedure can be called in a begin end block only.
b) In case of function, it must have return type. In case of procedure, it may or may not have return type.
c) Functions and Procedures both can return values using OUT parameter.But Functions has return clause to return value and Procedures does not have return statement.
d) Function mainly used for calculation purpose. Procedure used to perform DML operation.
2. State the difference between implicit and explicit cursors.
a) Implicit cursor: It is automatically created by oracle for all sql dml statements including the query that returns one row.
b) Explicit cursor: These are created and managed by the user and also used for multi row select statement.
3. Explian rowid,rownum?What are the pseduocolumns we have?
a) ROWID : Every record in a database is uniquely identified by system generated value called Rowid. It Is a 18 character hexma decimal value. These Rowid's are physically existence.
b) ROWNUM: It is a pseduocolumn which generates the sequence of numeric values based on the position of the records in the output. These ROWNUM'S logically generated.
c) NEXTVAL,CURRVAL,SYSDATE,LEVEL ARE PSEDUOCOLUMNS
4. What are advantages of Stored Procedures?
a) Easy maintenance
b) Improved data security and integrity
c) Improved performance
d) Modularity, Reusability, one time compilation.
e) Faster in execution: As Procedure is a complied schema object and stored in database, hence it takes less time to execution.
f) By writing procedure redundant coding can be avoid, increasing productivity.
g) The amount of memory used reduces as SP has shared memory capability. Once a copy of a SP is opened in the oracle engine's memory, other user who has appropriate permission may access it when required.
5. What are the two parts of a procedure
a) Declaration and execution (Exception Handling).
6. What is difference between a Cursor declared in a procedure and Cursor declared in a package specification?
A cursor declared in a package specification is global and can be accessed by other procedures or functions in a package.A cursor declared in a procedure is local to the procedure that cannot be accessed by other procedures.
7. Name the tables where characteristics of Package, procedure and functions are stored?
a) USER_OBJECTS
b) USER_SOURCE
c) USER_ERROR.
d) USER_PROCEDURES
8. What is Overloading of procedures?
a) Same procedure name with different parameters called procedure overloading; parameters may different by their data types, sequence and position.eg1:get_employee(Emp_id number);get_employee(Emp_id Varchar(20));eg2:get_employee(ID number, name char(20))get_employee(ID Number, name char(20), dept char(20))get_employee(name char(20), dept char(20),ID Number)
a) A function can be called from SQL statements and queries while procedure can be called in a begin end block only.
b) In case of function, it must have return type. In case of procedure, it may or may not have return type.
c) Functions and Procedures both can return values using OUT parameter.But Functions has return clause to return value and Procedures does not have return statement.
d) Function mainly used for calculation purpose. Procedure used to perform DML operation.
2. State the difference between implicit and explicit cursors.
a) Implicit cursor: It is automatically created by oracle for all sql dml statements including the query that returns one row.
b) Explicit cursor: These are created and managed by the user and also used for multi row select statement.
3. Explian rowid,rownum?What are the pseduocolumns we have?
a) ROWID : Every record in a database is uniquely identified by system generated value called Rowid. It Is a 18 character hexma decimal value. These Rowid's are physically existence.
b) ROWNUM: It is a pseduocolumn which generates the sequence of numeric values based on the position of the records in the output. These ROWNUM'S logically generated.
c) NEXTVAL,CURRVAL,SYSDATE,LEVEL ARE PSEDUOCOLUMNS
4. What are advantages of Stored Procedures?
a) Easy maintenance
b) Improved data security and integrity
c) Improved performance
d) Modularity, Reusability, one time compilation.
e) Faster in execution: As Procedure is a complied schema object and stored in database, hence it takes less time to execution.
f) By writing procedure redundant coding can be avoid, increasing productivity.
g) The amount of memory used reduces as SP has shared memory capability. Once a copy of a SP is opened in the oracle engine's memory, other user who has appropriate permission may access it when required.
5. What are the two parts of a procedure
a) Declaration and execution (Exception Handling).
6. What is difference between a Cursor declared in a procedure and Cursor declared in a package specification?
A cursor declared in a package specification is global and can be accessed by other procedures or functions in a package.A cursor declared in a procedure is local to the procedure that cannot be accessed by other procedures.
7. Name the tables where characteristics of Package, procedure and functions are stored?
a) USER_OBJECTS
b) USER_SOURCE
c) USER_ERROR.
d) USER_PROCEDURES
8. What is Overloading of procedures?
a) Same procedure name with different parameters called procedure overloading; parameters may different by their data types, sequence and position.eg1:get_employee(Emp_id number);get_employee(Emp_id Varchar(20));eg2:get_employee(ID number, name char(20))get_employee(ID Number, name char(20), dept char(20))get_employee(name char(20), dept char(20),ID Number)
Subscribe to:
Comments (Atom)