Explore topic-wise InterviewSolutions in .

To achive this USE rownum PSEUDOCOLUMN with query, like
SQL> select rownum, ename from emp;

Output:
-----------------
1 SCOTT
2 Millor
3 Jiyo
4 Smith

To achive this use rownum pseudocolumn with query, like
SQL> select rownum, ename from emp;

Output:
-----------------
1 Scott
2 Millor
3 Jiyo
4 Smith

There are four cursor ATTRIBUTES USED in ORACLE

cursor_name%Found,
cursor_name%NOTFOUND,
cursor_name%ROWCOUNT,
cursor_name%ISOPEN

There are four cursor attributes used in Oracle

cursor_name%Found,
cursor_name%NOTFOUND,
cursor_name%ROWCOUNT,
cursor_name%ISOPEN

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (B.sal)) FROM EMP B WHERE a.sal<=b.sal);

Enter VALUE for n: 2
SAL
---------------
3700

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);

Enter value for n: 2
SAL
---------------
3700

SQL&GT; select banner from V$version;

SQL> select banner from v$version;

Odd number of records:
select * from EMP where (ROWID,1) in (select rowid, MOD(rownum,2) from emp);
1
3
5

Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6

Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5

Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6

months_between

months_between

Too_many_rows,
No_Data_Found,
Value_Error,
Zero_Error,
OTHERS

Too_many_rows,
No_Data_Found,
Value_Error,
Zero_Error,
Others

Cursor_Already_Open, Invalid_Cursor

Cursor_Already_Open, Invalid_Cursor

SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM

SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM

1 for POSITIVE value,
0 for Zero,
-1 for Negative value.

1 for positive value,
0 for Zero,
-1 for Negative value.

12 TRIGGERS.

12 triggers.

A Cartesian product is the result of an UNRESTRICTED join of TWO or more tables. The result set of a three table Cartesian product will have X * y * z number of ROWS where x, y, z correspond to the number of rows in each table involved in the join. It is causes by specifying a table in the FROM clause without joining it to ANOTHER table.

A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join. It is causes by specifying a table in the FROM clause without joining it to another table.

A STORED procedure is pre-loaded in memory for FASTER execution. It allows the DBMS control of PERMISSIONS for security PURPOSES. It ALSO eliminates the need to recompile components when minor changes occur to the database.

A stored procedure is pre-loaded in memory for faster execution. It allows the DBMS control of permissions for security purposes. It also eliminates the need to recompile components when minor changes occur to the database.

A LEFT JOIN will take ALL values from the first DECLARED table and MATCHING values from the second declared table based on the COLUMN the join has been declared on. An INNER JOIN will take only matching values from both tables

A LEFT JOIN will take ALL values from the first declared table and matching values from the second declared table based on the column the join has been declared on. An INNER JOIN will take only matching values from both tables

A table SCAN.

A table scan.

A trigger is one or more statements of SQL that are being executed in event of data modification in a table to which the trigger belongs.

Triggers enhance the security, efficiency, and standardization of databases.

Triggers can be beneficial when used:

• to check or modify VALUES before they are actually updated or inserted in the database. This is useful if you need to transform data from the way the user sees it to some internal database format.
• to RUN other non-database operations coded in user-defined functions
• to update data in other tables. This is useful for maintaining relationships between data or in keeping audit trail INFORMATION.
• to check against other data in the table or in other tables. This is useful to ENSURE data integrity when referential integrity constraints aren’t appropriate, or when table check constraints limit checking to the current table only.

A trigger is one or more statements of SQL that are being executed in event of data modification in a table to which the trigger belongs.

Triggers enhance the security, efficiency, and standardization of databases.

Triggers can be beneficial when used:

You can script the PROCESS to include it in a SET of install scripts you deliver with a product.

You can PUT your create database script in CVS for version control, so as you make changes or adjustments to it, you can track them like you do changes to software code.

You can LOG the output and review it for errors. You learn more about the process of database creation, such as what OPTIONS are available and why.

You can script the process to include it in a set of install scripts you deliver with a product.

You can put your create database script in CVS for version control, so as you make changes or adjustments to it, you can track them like you do changes to software code.

You can log the output and review it for errors. You learn more about the process of database creation, such as what options are available and why.

The UNION operation, you will recall, BRINGS two sets of DATA together. It will *NOT* HOWEVER produce duplicate or redundant rows. To perform this feat of magic, a SORT operation is done on both tables. This is obviously computationally intensive, and uses significant memory as well. A UNION ALL conversely just dumps collection of both sets together in random ORDER, not worrying about duplicates.

The union operation, you will recall, brings two sets of data together. It will *NOT* however produce duplicate or redundant rows. To perform this feat of magic, a SORT operation is done on both tables. This is obviously computationally intensive, and uses significant memory as well. A UNION ALL conversely just dumps collection of both sets together in random order, not worrying about duplicates.

Oracle 10G is a version of the Oracle DATABASE. The G stands for "grid" to indicate that 10g is "grid-computing ready".

Oracle 10g is a version of the Oracle Database. The g stands for "grid" to indicate that 10g is "grid-computing ready".