Oracle interview question and answer

Explain the difference between a hot backup and a cold backup and the benefits associated with each.

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. cold backup is that it is typically easier to administer the backup and recovery process The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a

Compare TRUNCATE and DELETE for a table.

Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces few rollback data. The delete command, on the other hand, is a DML operation, which will produce rollback data and thus take longer to complete.

Difference between a FUNCTION, PROCEDURE and PACKAGE?

They are all named PL/SQL blocks.

·         Function must return a value. Can be called inside a query.

·         Procedure may or may not return value.

·         Package is the collection of functions, procedures, variables which can be logically grouped together.

What are various joins used while writing SUBQUERIES
Self join-Its a join foreign key of a table references the same table.
Outer Join--Its a join condition used where One can query all the rows of one of the tables in the join condition even though they don't satisfy the join condition.
Equi-join--Its a join condition that retrieves rows from one or more tables in which one or more columns in one table are equal to one or more columns in the second table.
What is difference between Rename and Alias?
Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which does not exist once the SQL statement is executed.
What is a view?
A view is stored procedure based on one or more tables, it’s a virtual table.
What is difference between UNIQUE and PRIMARY KEY constraints?
A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The columns that compose PK are automatically define NOT NULL, whereas a column that compose a UNIQUE is not automatically defined to be mandatory must also specify the column is NOT NULL.
How you will avoid duplicating records in a query? By using DISTINCT
When you use WHERE clause and when you use HAVING clause
HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause The WHERE clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.
What is a pseudo column. Give some examples
It is a column that is not an actual column in the table.
eg USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL.
What is the purpose of a cluster?
Oracle does not allow a user to specifically locate tables, since that is a part of the function of the RDBMS. However, for the purpose of increasing performance, oracle allows a developer to create a CLUSTER. A CLUSTER provides a means for storing data from different tables together for faster retrieval than if the table placement were left to the RDBMS.
What is a cursor?
Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQL block.
Difference between an implicit & an explicit cursor.
PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including queries that return only one row. However, queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop.
Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR...IS statement. An implicit cursor is used for all SQL statements Declare, Open, Fetch, Close. Explicit cursors are used to process multi row SELECT statements. An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.
What are cursor attributes
%ROWCOUNT %NOTFOUND %FOUND %ISOPEN
How you open and close a cursor variable. Why it is required.
OPEN cursor variable FOR SELECT...Statement CLOSE cursor variable In order to associate a cursor variable with a particular SELECT statement OPEN syntax is used. In order to free the resources used for the query CLOSE statement is used.

TRIGGERS IN ORACLE

Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events:

·         A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).

·         A database definition (DDL) statement (CREATE, ALTER, or DROP).

·         A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers could be defined on the table, view, schema, or database with which the event is associated.

Interfaces	Recommended Use
Statement	Use for general-purpose access to your database. Useful when you are using static SQL statements at runtime. The Statement interface cannot accept parameters.
PreparedStatement	Use when you plan to use the SQL statements many times. The PreparedStatement interface accepts input parameters at runtime.
CallableStatement	Use when you want to access database stored procedures. The CallableStatement interface can also accept runtime input parameters.

INDEX

Index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns.

With a clustered index the rows are stored physically on the disk in the same order as the index. There can therefore be only one clustered index.
With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indexes, although each new index will increase the time it takes to write new records.
It is generally faster to read from a clustered index if you want to get back all the columns. You do not have to go first to the index and then to the table. Writing to a table with a clustered index can be slower, if there is a need to rearrange the data.
DDL
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

CREATE - to create objects in the database

ALTER - alters the structure of the database

DROP - delete objects from the database

TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

COMMENT - add comments to the data dictionary

RENAME - rename an object

DML
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

SELECT - retrieve data from the a database

INSERT - insert data into a table

UPDATE - updates existing data within a table

DELETE - deletes all records from a table, the space for the records remain

MERGE - UPSERT operation (insert or update)

CALL - call a PL/SQL or Java subprogram

EXPLAIN PLAN - explain access path to data

LOCK TABLE - control concurrency

DCL
Data Control Language (DCL) statements. Some examples:

GRANT - gives user's access privileges to database

REVOKE - withdraw access privileges given with the GRANT command

SELF JOIN

SELECT a.emp_id AS "Emp_ID",a.emp_name AS "Employee Name",  

b.emp_name AS "Supervisor Name"  FROM employee a, employee b  

WHERE a.emp_supv = b.emp_id;