Oracle Interview Ques

Oracle frequently used queries:

Oracle Query to display column names and their types:

 

Desc pp_temp_myffx_load

Count the number of rows in the table

Select count(*) from pp_temp_myffx_load

Oracle Interview Questions

Source:https://stackoverflow.com/questions/4378068/when-to-use-a-view-instead-of-a-table

Views for selection:

1. Views provide abstraction over tables. You can add/remove fields easily in a view without modifying your underlying schema
2. Views can model complex joins easily.
3. Views can hide database-specific stuff from you. E.g. if you need to do some checks using Oracles SYS_CONTEXT function or many other things
4. You can easily manage your GRANTS directly on views, rather than the actual tables. It's easier to manage if you know a certain user may only access a view.
5. Views can help you with backwards compatibility. You can change the underlying schema, but the views can hide those facts from a certain client.

Views for insertion/updates:

1. You can handle security issues with views by using such functionality as Oracle's "WITH CHECK OPTION" clause directly in the view

Drawbacks

1. You lose information about relations (primary keys, foreign keys)
2. It's not obvious whether you will be able to insert/update a view, because the view hides its underlying joins from you

• Views can represent a subset of the data contained in a table.
• Views can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.
• Views can join and simplify multiple tables into a single virtual table.
• Views can act as aggregated tables, where the database engine aggregates data (sum, average, etc.) and presents the calculated results as part of the data.
• Views can hide the complexity of data. For example, a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table.
• Views take very little space to store; the database contains only the definition of a view, not a copy of all the data that it presents.
• Views can provide extra security, depending on the SQL engine used.

Materialized view

1. The materialized view will stay synchronized with the base relations on which it depends.
2. If the materialized view is updatable, when you modify the materialized view, it will also modify the base relation on which it depends.

There are no loops in pl/sql. So we need to use cursor. It helps to loop through the resultset of select statement.

Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

Declaring the Cursor

Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example −

CURSOR c_customers IS 
   SELECT id, name, address FROM customers; 

Opening the Cursor

Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open the above defined cursor as follows −

OPEN c_customers; 

Fetching the Cursor

Fetching the cursor involves accessing one row at a time. For example, we will fetch rows from the above-opened cursor as follows −

FETCH c_customers INTO c_id, c_name, c_addr; 

Closing the Cursor

Closing the cursor means releasing the allocated memory. For example, we will close the above-opened cursor as follows −

CLOSE c_customers;

When you define and establish relationships between the tables it ensure that parent record has to exist before child key. If ssn in primary key...its unique key. In case we dont have one unique key to represent data we can have composite keys

INTEGRITY CONSTRAINTS DEF

Select * into one table from another table.

There is a deleted table which is a system table.

A transaction is a sequence of one or more SQL statements that Oracle Database treats as a unit: either all of the statements are performed, or none of them are. A transaction implicitly begins with any operation that obtains a TX lock:

• When a statement that modifies data is issued (e.g., insert, update, delete, merge)
• When a SELECT ... FOR UPDATE statement is issued
• When a transaction is explicitly started with a SET TRANSACTION statement or the DBMS_TRANSACTION package

Issuing either a COMMIT or ROLLBACK statement explicitly ends the current transaction.

BLOB is datatype used to store images within database.

In new cloud technology they are moving away from rdms. Which is slow.