What is normalization? what is the advantage of normalization?

Normalization is the process of removing redundant data from your tables

What are the types of SQL Statement ?

DDL - Create, Alter.. DML - Insert, Update ..

What is a join ? Explain the different types of joins ?

Self Join - Joining the table with itself. Equi Join - Joining two tables by equating two common columns. Non-Equi Join - Joining two tables by equating two common columns. Outer Join - Joining two tables in such a way that query can also retrive rows that do not have corresponding join value in the other table.

What are Nested and Correlated subqueries ?

- The subquery used in WHERE clause is a nested subquery. - If this subquery refers to any column in the parent statement, it becomes a correlated subquery.

What is difference between TRUNCATE & DELETE ?

Truncate is a DDL Statement, cannot rollback Delete is a DML statement, can issue rollback

Why use Truncate over Delete while deleting all rows ?

- Truncate is efficient. Triggers are not fired.

How can you tell if an UPDATE updated no rows? SQL%NOTFOUND. How can you tell if a SELECT returned no rows. NO_DATA_FOUND exception. What is a cursor ? ( Basic)
Name or handle to a private SQL area where Oracle parses and fetches query results.

Oracle cursor : Implicit & Explicit cursors Oracle uses work areas called private SQL areas to create SQL statements. PL/SQL construct to identify each and every work are used, is called as Cursor. For SQL queries returning a single row, PL/SQL declares all implicit cursors. For queries that returning more than one row, the cursor needs to be explicitly declared. 9. Explicit Cursor attributes There are four cursor attributes used in Oracle cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN

10. Implicit Cursor attributes Same as explicit cursor but prefixed by the word SQL SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after executing SQL statements. : 2. All are Boolean attributes. 16. Any three PL/SQL Exceptions? Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others 17. What are PL/SQL Cursor Exceptions? Cursor_Already_Open, Invalid_Cursor How to control how many cursors are open ?(Intermediate)
Set OPEN_CURSORS parameter in initialization parameters It deallocates space (Unless REUSE STORAGE is given).

Infinite Loop How will you stop an infinite loop without closing the program?
use the EXIT statement This will stop the looping and proceed to the next executable statement. or EXIT WHEN condition

Indexed Select Statement How can we find the select statement is indexed or not?
AUTO Trace - Trace only command Use "Explain Plan" command SQL> SET AUTOTRACE ON TRACEONLY SELECT *FROM emp WHERE emp_name 'ABC'; 2. Using EXPLAIN PLAN SQL> EXPLAIN PLAN for SELECT *FROM emp WHERE emp_name 'ABC';

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.

A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package

on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.

Procedure in package perfomes fastly over normal procedure, Explain. Explain the use of table functions.
Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.

Package Function You have a package called A and one function in that packgae called XYZ. If you need to call that function in second packaged B, How will you call?
Yes You can call the function from another package as below: Pakagename.funciton name 46. You have just compiled a PL/SQL package but got errors, how would you view the errors? SHOW ERRORS

What is difference between a PROCEDURE & FUNCTION ?

Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter. 2. Function can be called from SQL statements where as procedure can not be called from the sql statements 3. Functions are normally used for computations where as procedures are normally used for executing business logic. 4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query. 5. Function returns 1 value only. Procedure can return multiple values (max 1024). 6.Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution. 7.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values 8. Stored procedure is precompiled execution plan where as functions are not. 9.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.

How many types of database triggers can be specified on a table? What are they ?
There are a total of 12 possible types: 3 Statements (INSERT UPDATE OR DELETE) 2Timing (BEFORE or AFTER) 2 Levels (ROW or STATEMENT) So 3*2*2 12 possible types. A table can have 12 triggers defined on it. There are five types of database triggers Row Level Trigger Statement Level Trigger Instead of Trigger Schema Trigger Database Trigger A trigger may be a 1. DML Trigger on tables 2. Instead of triggers on views 3. System triggers on database or schema Based on the way it executes statements triggers are of two types 1. Statement leve trigger 2. Row level trigger A trigger fires for three actions 1. Insert 2. Delete 3.Update and the trigger can the fired 1. Before action 2. After action.

Which command can you issue to enable this trigger?

The CHECK_THEATER trigger of the THEATER table has been disabled. Which command can you issue to enable this trigger? 1. ALTER TRIGGER check_theater ENABLE; 2. ENABLE TRIGGER check_theater;

3. ALTER TABLE check_theater ENABLE check_theater; 4. ENABLE check_theater;

What is event trigger ?

An event trigger, a segment of code which is associated with each event and is fired when the event occurs

When you need to disable all triggers on the a particular table. Which command accomplishes this?
Answer: ALTER TABLE <Table_Name> DISABLE ALL TRIGGERS; 36. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace? A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database. 37. Name a tablespace automatically created when you create a database. The SYSTEM tablespace. 38. When creating a user, what permissions must you grant to allow them to connect to the database? Grant the CONNECT to the user.

In a Client/Server context, what does API (Application Programming Interface) refer to ?

An API, in a Client/Server context, is a specification of a set of functions for communication between the client and the server.

What's SQL*Loader SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader : the data itself and the control file. The control file describes the data to be loaded. It describes the Names and format of the data files, Specifications for loading data and the Data to be loaded (optional). Invoking the loader sqlload username/password controlfilename <options>.

Tell some new features in PL/SQL in 10g (Intermediate to Advanced) - Regular expression functions REGEXP_LIKE , REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR - Compile time warnings - Conditional compilation - Improvement to native compilation

- BINARY_INTEGER made similar to PLS_INTEGER - INDICES OF , VALUES OF in FORALL lets you work on non-consecutive indices - Quoting mechanism . Instead of quoting single quotes twice everytime, give your own delimiter to go on using single quotes. Ex: q'!I'm a string, you're a string.!' - Flashback Query functions. SCN_TO_TIMESTAMP, TIMESTAMP_TO_SCN - Implicit conversion between CLOB and NCLOB - Improved Overloading - New datatypes BINARY_FLOAT, BINARY_DOUBLE - Global optimization enabled - PLS_INTEGER range increased to 32bit - DYNAMIC WRAP using DBMS_DDL