Oracle SQL/PLSQL Questions & Answers3:

Oracle SQL/PLSQL Questions & Answers:

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

Back (Page 2)                   Next (Page 4)

Other Related Questions & Answers: