SQL,PL/SQL Interview Questions part-3

What is SQL sequence and what are their attributes?

Sequence is an Oracle object that is using for generating the sequence of number. IT=T is normally using for column that have primary key.

“CREATE SEQUENCE TRANS_ID_SEQ

START WITH 1

MAXVALUE 99999999

MINVALUE 1

NOCYCLE

NOCACHE

NOORDER”

What is CYCLE AND NO CYCLE in sequence?

When we use “CYCLE” option in sequence and sequence reaches its MAXVALUE, it will start again from the MINVALUE. That is not good when we are using the sequence for primary key. In “NOCYCLE” option cursor did not start from minimum value again.

Can we design one view on another view?

Yes we can design one view on the other view.

What are aggregate functions in Oracle?

Aggregate function are using in oracle with group by clause and with having clause. Avg, count, max, min, sum,

How can we sort out our data in oracle?

We can sort out or data in oracle by using Order by clause. If we sort our data then we use this Order by Roll_Num AEC and fro descending order we will use Order by Roll_Num Desc.

What is a PL/SQL package and what are the advantages of PL/SQL Packages?

A package is a collection of PL/SQL elements that are grouped together within a special BEGIN-END syntax. A package is a schema object that groups logically related PL/SQL types, items, and subprograms.

Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. The specification (spec for short) is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the spec.

What are the query and how many types of queries?

A database query is some type of syntax that is sent to database and get some data or information from the database. It is some type of communicator that is using for giving something and taking something from database.

●    Simple Query

●    Advanced Query

●    Saved Query

●    Cross-project Query

What is the union, intersect and minus operation in oracle?

Create table MY_A

(

My_ID number

)

Table A values: (1, 2, 3)

Create table MY_B

(

My_ID number

)

Table A values: (1, 2, 5)

For UNION:

Select MY_ID from MY_A;

UNION

Select MY_ID from MY_B;

Result: 1, 2, 3,5

For INTERSECT:

Select MY_ID from MY_A;

INTERSECT

Select MY_ID from MY_B;

Result: 1, 2

For MINUS:

Select MY_ID from MY_A;

MINUS

Select MY_ID from MY_B;

Result: 3

What is the difference between UNION and UNION ALL?

In UNION if two rows will match then result come only of two rows but if we use UNION ALL then result will be four rows.

Create table MY_A

(

My_ID number

)

Table A values: (1, 2, 3)

Create table MY_B

(

My_ID number

)

Table B values: (1, 2, 5)

For UNION:

Select MY_ID from MY_A;

UNION

Select MY_ID from MY_B;

Result: 1, 2, 3,5

For UNION ALL:

Select MY_ID from MY_A;

UNION ALL

Select MY_ID from MY_B;

Result: 1,1, 2,2, 3,5

Explain in detail use of OCI in oracle?

OCI is an API that provides functions you can use to access the database server and control SQL execution. OCI supports the data types, calling conventions, syntax, and semantics of the C and C++ programming languages. You compile and link an OCI program much as you would any C or C++ program.

Oracle function can take OUT parameter?

Yes we use OUT parameter in function.

In form Parameter can we define default value?

Yes you can define initial value in form parameter.

What is the difference between NO_DATA_FOUND and %NOTFOUND?

“NO_DATA_FOUND” is a system defined exception. It is generated when no record found in implicit cursor. “%NOTFOUND” is used in cursor. If cursor returns no row then %NOTFOUND returns true and if returns row then %NOTFOUND is false.

Write a statement to find out the no. of rows in oracle?

Select count(*) from emp;

Which function is use for display the numeric figure in words?

SELECT TO_CHAR(TO_DATE(123,'J'),'JSP') to_words FROM   dual;

What is the difference between WHERE clause and HAVING clause?

WHERE clause use in simple select statement on the other hand HAVING clause use in group function statement like.

Select emp_name from WHEER dept_code=123;

Select emp_name from emp HAVING count(emp_cod) > 2;

Write a difference between SUBSTR and INSTR function?

SUBSTR provide some portion of string like from one word to 5th word and INSTR gives us location of that particular word.