Looking for an IT job..

        Just out of college or packing your baggage to get out soon. We talked to various freshers and digged out some very common myths or misconception they have regarding IT job interview process. Though there are millions (okay…thousands..or even hundreds..no less) of them we are only putting the most common ones here. This might be difficult for for a 10+ yrs experience professional to digest, but this how freshers (or most of them) think. Can we help their thinking…yes..may be.
Myth #10: Lowering Your Salary Demands Will Increase your chances of selection.
When you are struggling for a job, sometimes you may be tempted to lower your demand and grab the offer letter immediately. Unfortunately or fortunately an IT company does not think in the same way. It is more interested in getting value from you (I mean your work..) rather than saving money on your salary. While asking for a much higher salary may show you a greedy, lowering expectation may lower the value perception. If you know what the company offers for a similar skillset and experience, try to be in +/- 20% range.
Myth #9: A tie is a must for an entry level job interview.
While it is important to be neatly dressed in formals, wearing a tie will not add any point to your score.Only if you feel comfortable with a tie, wear it.
Myth #8: Entry level salaries are fixed and cannot be negotiated.
While most of the big companies advertise their entry level salaries, it does vary based on your college, past experience, marks and your performance in the interview process. If you are being asked your salary expectation you may have some room to negotiate.
Myth #7: Only the best person gets hired.
Hiring a large number of freshers (from an even larger number of freshers) is so strenuous that most people resort to selection by elimination. It may be on the basis of your marks, degree, FCFS (first come first serve) or anything. Do you really think that getting a 69% guy is in any way inferior to a 71% guy. As long as you have done full preparation you should not get unnecessary complexes. If you have not done any preparation for job then any person getting selected is better (in fact much better) than you.
Myth #6: If I write many projects in CV it will catch their attention.
No, It will get you more trouble than you can handle. Highlight only bigger projects (rather than every term papers you would have done in every alternate course). And, be fully prepared to answer every question about these projects.
Myth #5: Writing both C#.NET and Java/J2EE will boost my chances of CV getting selected.
Half of the world is .NET and the other half is Java. If I write both skills in my CV I have the full universe (superset) with me. It is going to exactly double my chances of selection…Wrong. It would reduce your probability to exactly half if not less. The only conclusion a sane (read experience IT professional) person would draw is that you do not know any of these. Write (and also prepare) only one of them. Which one ? I will answer in a future post. So keep coming to my blog.
Myth #4: A flashy resume is more likely to get attention than a simpler one.
Unfortunately the flash in only limited to pan. A person who is filtering has played it enough.Keep it simple and do not promise that you are going to change the face of the company. More tips on Resume Writing later.
Myth #3: If a company is not currently hiring I do not stand a chance.
Though it may look very contradictory, but only 20-25% of overall IT jobs are publicized in advertisement or otherwise. Rest all is filled through references, unsolicited CVs and job portals. Now you know what to do.
Myth #2: If I have potential, it will get recognized in interview.
If you do NOT have potential, it will definitely get recognized in interview but if you have potential there is no guarantee of recognition. Confusing..okay let me put it very simply. What if you have very good technical knowledge and you also have good (interesting) hobbies and your extra curricular record is shining. There is a definite chance of discussion leading to some other path and you not being able to tell the interviewer that you have it (what it takes to make a good software engineer). If needed interrupt him and try to take the discussion to your strong areas. In the worst case simply ask for it by specifically telling him your strengths.
Myth #1: IT companies are more inclined towards students who have advance knowledge of C#.NET or Java/J2EE
This one is the most common (and the bad one too). Kindly undertsand that technologies will change completely at least 4-5 times in your software career. What is important for you is in-depth understanding of programming fundamentals and analytical skills. IT companies realize that once your fundamentals are at right place you can quickly learn new emerging technologies and be productive very fast. Take my word, you are definitely not going to end you career with any of these two. It is important for you to spend sufficient time on your analytical skills and programming fundamentals which will serve as a base for your entire career.

Basics of PL/SQL required for a DBA

1. What is PL/SQL ?
PL/SQL is a procedural language that has both interactive SQL and procedural programming language
constructs such as iteration, conditional branching.
2. What is the basic structure of PL/SQL ?PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
3. What are the components of a PL/SQL block ?
A set of related declarations and procedural statements is called block.
4. What are the components of a PL/SQL Block ?
Declarative part, Executable part and Execption part.
5. What are the datatypes a available in PL/SQL ?
Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN.
Some composite data types such as RECORD & TABLE.
6. What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?% TYPE provides the data type of a variable or a database column to that variable.
% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in
the cursor.
The advantages are : 
I. Need not know about variable's data type
ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.
7. What is difference between % ROWTYPE and TYPE RECORD ?
% ROWTYPE is to be used whenever query returns a entire row of a table or view.
TYPE rec RECORD is to be used whenever query returns columns of different
table or views and variables.
E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type);
e_rec emp% ROWTYPE
cursor c1 is select empno,deptno from emp;
e_rec c1 %ROWTYPE.
8. What is PL/SQL table ?
Objects of type TABLE are called "PL/SQL tables", which are modelled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.
9. What is a cursor ? Why Cursor is required ?
Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.
10. Explain the two type of Cursors ?
There are two types of cursors, Implict Cursor and Explicit Cursor.
PL/SQL uses Implict Cursors for queries.
User defined cursors are called Explicit Cursors. They can be declared and used.
11. What are the PL/SQL Statements used in cursor processing ?
DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types,
CLOSE cursor name.
12. What are the cursor attributes used in PL/SQL ?
%ISOPEN - to check whether cursor is open or not
% ROWCOUNT - number of rows fetched/updated/deleted.
% FOUND - to check whether cursor has fetched any row. True if rows are fetched.
% NOT FOUND - to check whether cursor has fetched any row. True if no rows are fetched.
These attributes are proceded with SQL for Implict Cursors and with Cursor name for Explict Cursors.
13. What is a cursor for loop ?
Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from
active set into fields in the record and closes
when all the records have been processed.
eg. FOR emp_rec IN C1 LOOP
salary_total := salary_total +emp_rec sal;
END LOOP;
14. What will happen after commit statement ?
Cursor C1 is
Select empno,
ename from emp;
Begin
open C1; loop
Fetch C1 into
eno.ename;
Exit When
Page 14 of 259
C1 %notfound;-----
commit;
end loop;
end;
The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.
The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.
15. Explain the usage of WHERE CURRENT OF clause in cursors ?
WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a
cursor.
Database Triggers
16. What is a database trigger ? Name some usages of database trigger ?
Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit
data modificateions, Log events transparently, Enforce complex business rules Derive column values
automatically, Implement complex security authorizations. Maintain replicate tables.
17. How many types of database triggers can be specified on a table ? What are they ?
Insert Update Delete
Before Row o.k. o.k. o.k.
After Row o.k. o.k. o.k.
Before Statement o.k. o.k. o.k.
After Statement o.k. o.k. o.k.
If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement.
If WHEN clause is specified, the trigger fires according to the retruned boolean value.
18. Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?
It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.
19. What are two virtual tables available during database trigger execution ?
The table columns are referred as OLD.column_name and NEW.column_name.
For triggers related to INSERT only NEW.column_name values only available.
For triggers related to UPDATE only OLD.column_name NEW.column_name values only available. For triggers related to DELETE only OLD.column_name values only available.
20. What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?
Mutation of table occurs.
21. Write the order of precedence for validation of a column in a table ?
I. done using Database triggers.
ii. done using Integarity Constraints.
22. What is an Exception ? What are types of Exception ?
Exception is the error handling part of PL/SQL block. The types are Predefined and user_defined. Some of
Predefined execptions are.
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
INVALID_NUMBER
LOGON_DENIED
NOT_LOGGED_ON
PROGRAM-ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
VALUE_ERROR
ZERO_DIVIDE
OTHERS.
23. What is Pragma EXECPTION_INIT ? Explain the usage ?
The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.
e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)
24. What is Raise_application_error ?
Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an
user_defined error messages from stored sub-program or database trigger.
25. What are the return values of functions SQLCODE and SQLERRM ?
SQLCODE returns the latest code of the error that has occured.
SQLERRM returns the relevant error message of the SQLCODE.
26. Where the Pre_defined_exceptions are stored ?
In the standard package.
Procedures, Functions & Packages ;
27. What is a stored procedure ?
A stored procedure is a sequence of statements that perform specific function.
28. What is difference between a PROCEDURE & FUNCTION ?
A FUNCTION is alway returns a value using the return statement.
A PROCEDURE may return one or more values through parameters or may not return at all.
29. What are advantages fo Stored Procedures /
Extensibility,Modularity, Reusability, Maintainability and one time compilation.
30. What are the modes of parameters that can be passed to a procedure ?
IN,OUT,IN-OUT parameters.
31. What are the two parts of a procedure ?
Procedure Specification and Procedure Body.
32. Give the structure of the procedure ?
PROCEDURE name (parameter list.....)
is
local variable declarations
BEGIN
Executable statements.
Exception.
exception handlers
end;
33. Give the structure of the function ?
FUNCTION name (argument list .....) Return datatype is
local variable declarations
Begin
executable statements
Exception
execution handlers
End;
34. Explain how procedures and functions are called in a PL/SQL block ?
Function is called as part of an expression.
sal := calculate_sal ('a822');
procedure is called as a PL/SQL statement
calculate_bonus ('A822');
35. What is Overloading of procedures ?
The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.
e.g. DBMS_OUTPUT put_line
36. What is a package ? What are the advantages of packages ?
Package is a database object that groups logically related procedures.
The advantages of packages are Modularity, Easier Applicaton Design, Information. Hiding,. reusability and
Better Performance.
37.What are two parts of package ?
The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY.
Package Specification contains declarations that are global to the packages and local to the schema.
Package Body contains actual procedures and local declaration of the procedures and cursor declarations.
38. 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 procedures in a package.
A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.
39. How packaged procedures and functions are called from the following?
a. Stored procedure or anonymous block
b. an application program such a PRC *C, PRO* COBOL
c. SQL *PLUS
a. PACKAGE NAME.PROCEDURE NAME (parameters);
variable := PACKAGE NAME.FUNCTION NAME (arguments);
EXEC SQL EXECUTE
b.
BEGIN
PACKAGE NAME.PROCEDURE NAME (parameters)
variable := PACKAGE NAME.FUNCTION NAME (arguments);
END;
END EXEC;
c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any
out/in-out parameters. A function can not be called.
40. Name the tables where characteristics of Package, procedure and functions are stored ?
User_objects, User_Source and User_error.

Oracle Interview Questions and Answers for freshers

1. To see current user nameSql> show user;
2. Change SQL prompt name
SQL> set sqlprompt “Manimara > “
Manimara >
Manimara >
3. Switch to DOS prompt
SQL> host
4. How do I eliminate the duplicate rows ?
SQL> delete from table_name where rowid not in (select max(rowid) from table group by
duplicate_values_field_name);
or
SQL> delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from
table_name tb where ta.dv=tb.dv);
Example.
Table Emp
Empno Ename
101 Scott
102 Jiyo
103 Millor
104 Jiyo
105 Smith
delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename);
or
delete from t1 a where rowid not in (select max(rowid) from ti b where a.no=b.n0)
The output like,
Empno Ename
101 Scott
102 Millor
103 Jiyo
104 Smith
5. How do I display row number with records?To achive this use rownum pseudocolumn with query, like SQL> SQL> select rownum, ename from emp;
Output:
1 Scott
2 Millor
3 Jiyo
4 Smith
6. Display the records between two range
select rownum, empno, ename from emp where rowid in
(select rowid from emp where rownum <=&upto
minus
select rowid from emp where rownum<&Start);
Enter value for upto: 10
Enter value for Start: 7
ROWNUM EMPNO ENAME
--------- --------- ----------
1 7782 CLARK
2 7788 SCOTT
3 7839 KING
4 7844 TURNER
7. I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of blank space. How do I write the query?
SQL> select nvl(to_char(comm.),'NA') from emp;
Page 11 of 259
Output :
NVL(TO_CHAR(COMM),'NA')
-----------------------
NA
300
500
NA
1400
NA
NA
8. Oracle cursor : 
Implicit & Explicit cursors
Oracle uses work areas called private SQL areas to create SQL statements.
PL/SQL construct to identify each and every work are used, is called as Cursor.
For SQL queries returning a single row, PL/SQL declares all implicit cursors.
For queries that returning more than one row, the cursor needs to be explicitly declared.
9. Explicit Cursor attributes
There are four cursor attributes used in Oracle
cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN
10. 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.
11. 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);
or
select min(sal) from(select distinct sal from emp order by sal desc where rownum<=&n)
Enter value for n: 2
SAL
---------
3700
12. To view installed Oracle version information
SQL> select banner from v$version;
13. 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
/
Salary Sal in Words
------- ------------------------------------------------------
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.
14. Display Odd/ Even number of recordsOdd 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
Page 12 of 259
4
6
15. Which date function returns number value?
months_between
16. Any three PL/SQL Exceptions?
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others
17. What are PL/SQL Cursor Exceptions?
Cursor_Already_Open, Invalid_Cursor
18. Other way to replace query result null value with a text
SQL> Set NULL ‘N/A’
to reset SQL> Set NULL ‘’
19. What are the more common pseudo-columns?
SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM
20. What is the output of SIGN function?1 for positive value,
0 for Zero,
-1 for Negative value.
21. What is the maximum number of triggers, can apply to a single table?
12 triggers.

oracle interview questions for freshers(1)

1.What is the difference between a hot backup and a cold backup and the benefits associated with each?

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode.

Benefits:

The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any ball in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

2.I know the nvl function only allows the same data type(ie. number or char or date

Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead ofblank space. How do I write the query?

SQL> select nvl(to_char(comm.),’NA’) from emp;

Output :NVL(TO_CHAR(COMM),’NA’)

———————–

NA

300

500

NA

1400

NA

NA

3. Oracle cursor : Implicit & Explicit cursors

Oracle uses work areas called private SQL areas to create SQL statements.

PL/SQL construct to identify each and every work are used, is called as Cursor.

For SQL queries returning a single row, PL/SQL declares all implicit cursors.

For queries that returning more than one row, the cursor needs to be explicitly declared.

4. Explicit Cursor attributes

There are four cursor attributes used in Oracle

cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN

5. 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.

6. What is the maximum number of triggers, can apply to a single table?

              12 triggers.

7.what are actual and formal parameters

      Actual Parameters

The variables or expressions referenced in the parameter list of a subprogram call are actual parameters.
Following procedure call lists two actual parameters named empno and amt:

raise_sal(empno, amt);

      Formal Parameters

The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters.
Following procedure declares two formal parameters named empid and amt:

PROCEDURE raise_sal(empid INTEGER, amt REAL) IS current_salary REAL;

8.Explain an exception and its types

Exception is the error handling part of PL/SQL block. 

Exception is of two types: Predefined and user defined.

Some of Predefined exceptions are

• ZERO_DIVIDE
• NO_DATA_FOUND
• TOO_MANY_ROWS
• LOGON_DENIED
• CURSOR_ALREADY_OPEN
• INVALID_NUMBER
• NOT_LOGGED_ON
• STORAGE_ERROR
• PROGRAM-ERROR
• TIMEOUT_ON_RESOURCE
• VALUE_ERROR
• DUP_VAL_ON_INDEX
• INVALID_CURSOR. 

oracle interview questions for freshers(2)

1. What is a transaction ?Answer: A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.
   
2. What is implicit cursor and how is it used by Oracle ?Answer: An implicit cursor is a cursor which is internally created by Oracle.It is created by Oracle for each individual SQL.
   
3. Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ?Answer: Public synonyms
   
4. What is PL/SQL?Answer: PL/SQL is Oracle's Procedural Language extension to SQL.The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.
   
5. Is there a PL/SQL Engine in SQL*Plus?Answer: No.Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine.Thus, all your PL/SQL are send directly to the database engine for execution.This makes it much more efficient as SQL statements are not stripped off and send to the database individually.
   
6. Is there a limit on the size of a PL/SQL block?Answer: Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K.You can run the following select statement to query the size of an existing package or procedure. SQL> select * from dba_object_size where name = 'procedure_name'
   
7. Can one read/write files from PL/SQL?Answer: Included in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=...parameter).Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
   DECLARE
   fileHandler UTL_FILE.FILE_TYPE;
   BEGIN
   fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput','W');
   UTL_FILE.PUTF(fileHandler, 'Value of func1 is %sn', func1(1));
   UTL_FILE.FCLOSE(fileHandler);
   END;
   
8. How can I protect my PL/SQL source code?Answer: PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original).This way you can distribute software without having to worry about exposing your proprietary algorithms and methods.SQL*Plus and SQL*DBA will still understand and know how to execute such scripts.Just be careful, there is no "decode" command available. The syntax is: wrap iname=myscript.sql oname=xxxx.yyy
   
9. Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ?Answer: From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.
   Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS
   cur integer;
   rc integer;
   BEGIN
   cur := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
   rc := DBMS_SQL.EXECUTE(cur);
   DBMS_SQL.CLOSE_CURSOR(cur);
   END;