Oracle Questions

Q:

What are joins? Explain its characteristic features .

Answer

Joins are used to combine data of one or more tables. Joins should be used when there is abundant data. Joins can be LEFT, RIGHT, OUTER, INNER or even SELF JOIN. The purpose is to bind data from multiple tables without any receptivity

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 1000
Q:

Explain how to create users through SQL PLUS.

Answer

We need to first login to the data base as: SQLPLUS followed by user_name/password. Then we need to execute the following query:


CREATE USER user_name IDENTIFIED BY abcd; in this query a user_name suggest the user that is created which has a password abcd which is required for login in database.

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 989
Q:

Explain how to limit the rows that are retrieved by a query.

Answer

The number of rows returned by a select query can be restricted by the LIMIT clause.
Example:
SELECT * from employee
LIMIT 20

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 978
Q:

What does cache and no cache options mean while creating a sequence?

Answer

The CACHE option means how many sequences will be stored in memory for access by the application objects. The performance is faster. However in case of the database is down the data is memory is lost for the sequence.


The NO CACHE option means values are not stored in memory. So there might be some performance issue. 

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 946
Q:

How can we read and write operating system files from PL/SQL program?

Answer

The UTL_FILE database package can be used to read and write operating system files. You need to have read /write access rights in that directory before the package can be used.


Example to write file:


Fhandler is a variable of type UTL_FILE.FILE_TYPE


UTL_FILE.PUTF(fHandler, 'Im writing to a file\n');


 


Example to read file:


UTL_FILE.GET_LINE(fHandler, buf);

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 934
Q:

What are Number Functions in SQL?

Answer

- ABS(number)

Returns the absolute positive value of an expression.
Syntax:
ABS(expression)
Example:
SELECT ABS(-1.0), ABS(0.0), ABS(1.0)
Output:
1.0    .0    1.0

- CEIL(number)

Returns the smallest integer greater than, or equal to, the specified numeric expression.
Syntax:
CEILING(expression)
Example:
SELECT CEILING($223.45), CEILING($-223.45), CEILING($0.0)
Output:
224.00    -223.00        0.00

- FLOOR(number)

Returns the largest integer less than, or equal to, the specified numeric expression.
Syntax:
FLOOR(expression)
Example:
SELECT FLOOR($223.45), CEILING($-223.45), CEILING($0.0)
Output:
223.00      -224.00        0.00

- MOD(number, divisor)

Returns the remainder of the division from 2 integer values.
Syntax:
MOD(dividend, divisor)
Example:
SELECT MOD(20,3)
Output:
2

- POWER(number, power)

Returns the exponential value for the numeric expression.
Syntax:
POWER(number, power)
Example:
SELECT POWER(2.0, 3.0)
Output:
8.0

- SIGN(number)

Returns the sign i.e. positive or negative value for the numeric expression. It returns -1 for negative expressions, a value of 0 for zero
Syntax:
SIGN(number)
Example:
SELECT SIGN(4)
Output:
1

- ROUND(number, precision)

Returns the numeric value rounded off to the next value specified.
Syntax:
ROUND(number, number of places)
Example:
SELECT ROUND(1.3456, 2)

- SQRT(number)

Returns the square root value of the expression.
Syntax:
SQRT(number)
Example:
SELECT SQRT(4.0)
Output:
2.0

- TRUNC(number, precision)

Returns a numeric value that truncate to the specific places
Syntax:
TRUNCATE(number,places)
Example:
SELECT TRUNCATE(1.3456, 2)
Output:
1.34

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 864
Q:

Explain unique Constraint.

Answer

A unique constraint on a column uniquely identifies the record by a combination of one or more fields. Few unique constraint fields can have a NULL value as long as the combination of values is unique.


Example:
create table employee ( id number NOT NULL, dob DATE, professor_id NOT NULL, Name varchar(200) Constraint id_unique UNIQUE(id,dob) );

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 861
Q:

What is a PL/SQL Record data type?

Answer

A record data type represents a data type for that row in a database table. It lets u define your own records and not your own fields.

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 840