ORACLE & SQL

• DBMS
• QUERIES & ANSWERS
• ORACLE INTERVIEW PRACTICAL QUERIES PART 1
• ORACLE INTERVIEW PRACTICAL QUERIES PART 2
• ORACLE INTERVIEW PRACTICAL QUERIES PART 3
• ORACLE INTERVIEW PRACTICAL QUERIES PART 4
• ORACLE QUERIES
• OPERATIONS & DATES
• ORACLE TABLES & TRANSACTIONS QUERIES
• FUNCTIONS
• VIEWS
• SUB QUERIES

ORACLE SQL Interview Practical Questions with Queries With Answers Part 4

51) What is SET?

SET command changes the system variables affecting the report environment.

52) What is SPOOL?

SPOOL command creates a print file of the report.

53) What is a JOIN?

JOIN is the form of SELECT command that combines info from two or more tables.

Types of Joins are Simple (Equijoin & Non-Equijoin), Outer & Self join.

Equijoin returns rows from two or more tables joined together based upon a equality condition in the WHERE clause.

Non-Equijoin returns rows from two or more tables based upon a relationship other than the equality condition in the WHERE clause.

Outer Join combines two or more tables returning those rows from one table that have no direct match in the other table.

Self Join joins a table to itself as though it were two separate tables.

54) What is Union?

Union is the product of two or more tables.

55) What is Intersect?

Intersect is the product of two tables listing only the matching rows.

56) What is Minus?

Minus is the product of two tables listing only the non-matching rows.

57)What is Correlated Subquery?

Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete. Use CRSQ to answer multipart questions whose answer depends on the value in each row processed by parent statement.

58) What is Multiple columns?

Multiple columns can be returned from a Nested Subquery.

59) What is Sequences?

Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost. 

Sequence is a database object used to generate unique sequential integer values.

60) What is Synonyms?

Synonyms is the alias name for table, views, sequences & procedures and are created for reasons of Security and Convenience.Two levels are Public - created by DBA & accessible to all the users. Private - Accessible to creator only. Advantages are referencing without specifying the owner and Flexibility to customize a more meaningful naming convention.

61) What is Data types?

Max. columns in a table is 255. Max. Char size is 255, Long is 64K & Number is 38 digits.

Cannot Query on a long column.

Char, Varchar2 Max. size is 2000 & default is 1 byte.

Number(p,s) p is precision range 1 to 38, s is scale -84 to 127.

Long Character data of variable length upto 2GB.

Date Range from Jan 4712 BC to Dec 4712 AD.

Raw Stores Binary data (Graphics Image & Digitized Sound). Max. is 255 bytes.

Mslabel Binary format of an OS label. Used primarily with Trusted Oracle.

62) What is Transaction?

Transaction is defined as all changes made to the database between successive commits.

63) What is Commit?

Commit is an event that attempts to make data in the database identical to the data in the form. It involves writing or posting data to the database and committing data to the database. Forms check the validity of the data in fields and records during a commit. Validity check are uniqueness, consistency and db restrictions.

64) What is Posting?

Posting is an event that writes Inserts, Updates & Deletes in the forms to the database but not committing these transactions to the database.

65) What is Savepoint?

Savepoint is a point within a particular transaction to which you may rollback without rolling back the entire transaction.

66) What is Set Transaction?

Set Transaction is to establish properties for the current transaction.

67) What is Locking?

Locking are mechanisms intended to prevent destructive interaction between users accessing data. Locks are used to achieve.

68) What is SQL Deadlock?

Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first user. To avoid dead locks, avoid using exclusive table lock and if using, use it in the same sequence and use Commit frequently to release locks

.

69) Give the structure of the procedure ?

PROCEDURE name (parameter list.....)

is

local variable declarations

BEGIN

Executable statements.

Exception.

exception handlers

end;

basically procedure has three

parts

1.variable declaretion(optional)

2.body(mandetory)

3.Exception(optional)

suppose ex

CREATE OR REPLACEPROCEDURE emp_pro( p_id IN employees.employee_id%TYPE)

IS

v_name employees.last_name%TYPE;

v_mail employees.email%TYPE;

BEGIN

SELECT last_name,email INTO v_name,v_mail FROM employees

WHERE employee_id:=p_id;

DBMS_OUTPUT.PUT_LINE('NAME:'||v_name ||'MAILID:'||v_mail);

END;

70) What is Mutating SQL Table?

Mutating Table is a table that is currently being modified by an Insert, Update or Delete statement. Constraining Table is a table that a triggering statement might need to read either directly for a SQL statement or indirectly for a declarative Referential Integrity constraints. Pseudo Columns behaves like a column in a table but are not actually stored in the table. E.g. Currval, Nextval, Rowid, Rownum, Level etc.

71) What is SQL*Loader?

SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader : the data itself and the control file. The control file describes the data to be loaded. It describes the Names and format of the data files, Specifications for loading data and the Data to be loaded (optional). Invoking the loader sqlload username/password controlfilename <options>.

72) What are the return values of functions SQLCODE and SQLERRM ?

SQLCODE returns the latest code of the error that has occurred.

SQLERRM returns the relevant error message of the SQLCODE.

SQLCODE returns the latest code of the error that has occured.

SQLERRM returns relevant massege

of the error code

73) The most important DDL statements in SQL are?

CREATE TABLE - creates a new database table

ALTER TABLE - alters (changes) a database table

DROP TABLE - deletes a database table

CREATE INDEX - creates an index (search key)

DROP INDEX - deletes an index

74) Operators used in SELECT statements are?

= Equal

<> or != Not equal

> Greater than

< Less than

>= Greater than or equal

<= Less than or equal

BETWEEN Between an inclusive range

LIKE Search for a pattern

75) SELECT statements in SQL?

SELECT column_name(s) FROM table_name

SELECT DISTINCT column_name(s) FROM table_name

SELECT column FROM table WHERE column operator value

SELECT column FROM table WHERE column LIKE pattern

SELECT column,SUM(column) FROM table GROUP BY column

SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value

Note that single quotes around text values and numeric values should not be enclosed in quotes. Double quotes may be acceptable in some databases.

76) 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.

77) The SELECT INTO Statement is most often used to create backup copies of tables or for archiving records?

SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source

SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source WHERE column_name operator value.

78) The INSERT INTO Statements in SQL?

INSERT INTO table_name VALUES (value1, value2,....)

INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)

79) The Update Statement in SQL.

UPDATE table_name SET column_name = new_value WHERE column_name = some_value

80) What is the Delete Statements in SQL?

Delete statement in SQL is used to delete partial/all data.

Especially delete statement is useful in case of partial delete depending upon our criterias otherwise use TRUNCATE to delete whole data from table.

When delete command fires then:

1) Triggers will fire (If created on that table)

2) This will not auto commit changes made So there is one chance to rollback.

3) If u delete whole data then HWM (Highest Water Mark) will not change which gets changed in case of Truncate.

81) How to sort the rows in SQL?

SELECT column1, column2, ... FROM table_name ORDER BY columnX, columnY, ..

SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC

SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC, columnY ASC

82) The IN operator may be used if you know the exact value you want to return for at least one of the columns.

SELECT column_name FROM table_name WHERE column_name IN (value1,value2,..)

                 *******Thank you for reading and All the best**************

Your comments will help me a lot...please give your comments which will encourage me to post more.....

ORACLE SQL Interview Practical Questions with Queries With Answers Part 3

31) In pl/sql functions what is use of out parameter even though we have return statement.

With out parameters you can get the more than one out values in the calling program. It is recommended not to use out parameters in functions. If you need more than one out values then use procedures instead of functions.

We can't use OUT paramter in function.We must have to use RETURN to pass values out of function otherwise use procs

32) 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.

 One more differene is cursor declared in a package specification must have RETURN type 

A cursor declare in the package

specification that can be accessed

in the other procedure or procedures of the package.

A cursor declare in the procedure

that can't be accessed by other procedure.

33) What is Character Functions?

Character Functions are INITCAP, UPPER, LOWER, SUBSTR & LENGTH. Additional functions are GREATEST & LEAST. Group Functions returns results based upon groups of rows rather than one result per row, use group functions. They are AVG, COUNT, MAX, MIN & SUM.

34) 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 featched.

These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.

35) What are the datatypes a available in PL/SQL ?

1. Character Datatypes:

char(size), nchar(size),

varchar2(size), nvarchar2(size),

long, raw, longraw

2. Numeric Datatypes

number(p,s), numeric(p,s),

float, dec(p,s), decimal(p,s),

integer, int, smallint,real,

double precision

3. Date/Time Datatypes:

date, timestamp,

timestamp with local time zone,

timestamp with time zone,

interval year to month,

interval day to seconds

4. LOB data types:

bfile,blob,clob,nclob

5. Rowid Data types:

rowid,urowid

36) What are the components of a PL/SQL block ?

A set of related declarations and procedural statements is called block.

---

components of PL/SQL are:
declare:
(optional) variable declare 

Begin:
(Mandatory)Procedural statement

Exception:
(optional)
error to be trapped 

End: 
(Mandatory)

So BEGIN and END are required in PL/SQL block

37) How to disable multiple triggers of a table at at a time?

ALTER TABLE<TABLE NAME> DISABLE ALL TRIGGER

ALTER TABLE TT_DCB DISABLE ALL TRIGGERS

38) What are the components of a PL/SQL Block ?

Declarative part, Executable part and Exception part.

Datatypes PL/SQL

39) What is Consistency?

Consistency : Assures users that the data they are changing or viewing is not changed until the are thro' with it.

40) What are advantages fo Stored Procedures

Extensibility,Modularity, Reusability, Maintainability and one time compilation.

Easy maintenance

? Improved data security and integrity

? Improved performance

Modularity, Reusability, one time compilation.

Faster in execution: As Procedure is a complied schema object and stored in database, hence it takes less time to execution.

41) What is PL/SQL table ?

Objects of type TABLE are called "PL/SQL tables", which are modeled 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.

Cursors

TYPE tab IS TABLE OF VARCHAR2(30);

This way we can make declaration of PL/SQL tables. They are also reffed as Nested Table and are pat of PLSQL collections. They are used for bulk data processing.

42) In a Distributed Database System Can we execute two queries simultaneously ? Justify ?

As Distributed database system based on 2 phase commit,one query is independent of 2 nd query so of course we can run.

43) What is Indexes?

Indexes are optional structures associated with tables used to speed query execution and/or guarantee uniqueness. Create an index if there are frequent retrieval of fewer than 10-15% of the rows in a large table and columns are referenced frequently in the WHERE clause. Implied tradeoff is query speed vs. update speed. Oracle automatically update indexes. Concatenated index max. is 16 columns.

44) Give the structure of the function ?

FUNCTION name (argument list .....) Return datatype is

local variable declarations

Begin

executable statements

Exception

execution handlers

End;

create or replace function <name>(arg1,arg2,....)

return datatype

as

....

variable declaration

begin

...

program code

...

return <variable>

exception

exception statement

end;

45) 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.

The oracle server uses works areas

called private sql area.Here all the DML statement is executed and to processing statement.basically

it's a implicit cursor.

there are two types of cursor

1.Implicit cursor.

2.Explicit cursor.

Implicit cursor is open for all DML statement.after execute the statement cursor is atomatically closed.

Explicit cursor is created by programmer.

explicit cursor is needed when

query returns more than one rows.

In that case,programmer creates

explicit cursor.open the cursor.

then fetch the value from the active set.

after fetching all the value,

cursor is closed by programmer.

46) What is the use of CASCADE CONSTRAINTS?

When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

47) What is Date Functions?

48) What is TTITLE and BTITLE?

TTITLE & BTITLE are commands to control report headings & footers.

ttitle and btitle used in sql * in reports . to show the header and footer of the reports.

49) What is COLUMN?

COLUMN command define column headings & format data values.

50) What is COMPUTE?

command control computations on subsets created by the BREAK command.

                 *******Thank you for reading and All the best**************

Your comments will help me a lot...please give your comments which will encourage me to post more.....

ORACLE SQL Interview Practical Questions with Queries With Answers Part 2

15) 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.

16) 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');

Function can be called from SQL query + explicitly as well

e.g 1)select empno,salary,fn_comm(salary)from employee;

2)commision=fn_comm(salary);

Procedure can be called from begin-end clause.

e.g.

Begin

(

proc_comm(salary);

)end

17) What is ref cursor?

Ref Cursor is cursor variable. It is a pointer to a result set. It is useful in scenarios when result set is created in one program and the processing of the same in some other, might be written in different language, e.g. firing the select is done in PL/SQL and the processing will be done in Java.

Its a run time query binding with the cursor variable. Normal cursors are static cursors becaz they get acquited of query at the compile time.

Ref cursor is user defined type.Ref cursor allows any type of query for the cursor.Ref cursor is a global declaration of the cursor.Ref cursor is re-usable.

Ref cursor reduces the number of declarations of the cursor in the entire plsql program.Ref cursor is either static or dynamic.

18) What are two parts of package ?

1)package specification
2)package body
package specification :
where the variables r global and all the packages can access with that variable.it contains the declaration of variables.(variables r global)
package body:
where the procedures and functions are collected in that packages.the variable declared with in package body of a function or procedure they are not used outside of that procedure.(the variables are private used by that specific procedure or function.

create or replace package taxes
is
tax number;
----declare all public procedure/funcation
end taxes;

create or replace package body taxes
is
---declare all private variable
---declare all public/private procedure/funcations

begin
select rate_value into tax from tax_rates where rate_name='TAX';
end taxes;

19) What is a stored procedure ?

A stored procedure is a named pl/sql block which performs an action.It is stored in the database as a schema object and can be repeatedly executed.It can be invoked, parameterised and nested.

20) 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.

---

%TYPE provides datatypes of the particular column of the table.

%ROWTYPE attribute is useful 
When we need to fetch entire row from the table.
secondly if we don't know the data type of some column %rowtype is useful for that.
finally, if we change any datatypes of the column,%rowtype automatically change the datatypes for the variable which is created by user

21) What are the modes of parameters that can be passed to a procedure ?

IN,OUT,IN-OUT parameters.

IN parameter is the default mode which acts as a constant inside calling environment.value passed in this parameter can not be changed.OUT parameter mode is used to pass value from calling environment into main block,here we can change the value.It acts as a variable inside calling environment.

INOUT parameter mode which pass value into calling environment and will get the value back in main block.

IN parameter mode uses call by reference method to pass value from formal parameter to actual parameter.

OUT & INOUT parameter mode uses call by value method to pass values.

22) How we can create a table through procedure ?

create procedure p1 is

begin

EXECUTE IMMEDIATE 'CREATE TABLE temp AS

SELECT * FROM emp ' ;

END;

23) What will happen after commit statement ?

After commit statement,all the transaction will be ended.

all the changes data are parmanently stored in the database.

24) 1)What is the starting "oracle error number"?

2)What is meant by forward declaration in functions?

One must declare an identifier before referencing it. Once it is declared it can be referred even before defining it in the PL/SQL. This rule applies to function and procedures also

---

ORACLE ERROR NO starts with ORA 00001

25) Explain the two type of Cursors ?

There are two types of cursors, Implicit Cursor and Explicit Cursor.

PL/SQL uses Implicit Cursors for queries. User defined cursors are called Explicit Cursors. They can be declared and used.

there are two types of cursor

1.implicit cursor

2. Explicit cursor

26) What is Overloading of procedures ?

Overloading of procedure

name of the procedure is

same but the number of parameters should be different.In that case,procedure will be overloaded.

2. if the number of parameters are same in that case,data type should be different.

if the two rules are satisfied in that case procedure will be overloaded.

Same procedure name with different parameters called procedure overloading, parameters may different by their datatypes, 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)

27) 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)

The PRAGMA_EXCEPTION_INIT

tells the compiler to assosiate

an exception with an oracle error.

28) What is pl/sql?what are the advantages of pl/sql?

PL/SQL is Very Usefully Language & Tools of Oracle to Manipulate,Restrict,Validate & Control the Unauthorized Access of Data From the Database.

We Can easily show multiple records of the multiple table at same time.

And Using control statement like Loops & If else & Select case we control the database.

29) What is Rollback?

Rollback causes work in the current transaction to be undone.

30) How to avoid using cursors? What to use instead of cursor and in what cases to do so?

When the number of rows returned by query is small (around 100), then explicit cursor can be avoided safely since in those cases using explicit cursor is performance degrading(CPU overhead).

                 *******Thank you for reading and All the best**************

Your comments will help me a lot...please give your comments which will encourage me to post more.....