Oracle Questions

Q:

What are Cursors? Explain Types of cursors in PL/SQL

Answer

Cursors help you manipulate the information retrieved by select statements. This can be done by assigning a name to the cursor.


Example:


CURSOR emp_cur 


IS 


SELECT emp_number from employee_tbl where employee_name = name_in;


Types of Cursors:


Implicit cursors- These cursors are not declared by the programmer. They are issued when the SQL statement is executed. The open, close and fetching is done by itself.


Example:


UPDATE employee SET salary = salary * 2.1;


Here, an implicit cursor is issued to identify the set of rows in the table which would be affected by the update.


 


Explicit cursors- These cursors are defined by programmer. They are used in queries that return multiple rows.


Example:


CURSOR emp_cur 


IS 


SELECT emp_number from employee_tbl where employee_name = name_in;

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

1 1849
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 1838
Q:

Explain Not Null constraint.

Answer

Oracle NOT NULL is used on a column to ensure that the value for that column can never be NULL.
Example:
Below, the constraint is that the id should never be NULL. If it is, oracle throws an error.
create table employee ( id number NOT NULL, Name varchar(200) );

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 1821
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 1816
Q:

What are the basic SQL*Plus commands?

Answer

1) START- Used to run a SQL script.
2) ACCEPT- Accepts input from user.
3) GET- Gets the sql file from user to place in buffer.
4) LIST- Displays the last command executed.
5) RUN- Used to list and run the command in buffer
6) SHOW- Shows the environment settings. Example: SHOW PAGESIZE

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 1799
Q:

How to Add a new column to an existing table?

Answer

New column can be added using the ALTER TABLE command as shown below:
Example:
ALTER TABLE employee ADD DateOfBirth date

Report Error

View answer Workspace Report Error Discuss

Subject: Oracle - Technology

0 1747