Oracle FAQ's

INTERSECT - returns all distinct rows selected by both queries. MINUS - returns all distinct rows selected by the first query but not by the second. UNION - returns all distinct rows selected by either query UNION ALL - returns all rows selected by either query, including all duplicates.

QUESTION -What is ROWID?

ANS- ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, rownumber are the components of ROWID.

QUESTION -What is the fastest way of accessing a row in a table?
ANS -Using ROWID CONSTRAINTS

QUESTION-Can a view be updated/inserted/deleted? If Yes - under what conditions? ANS-A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.

QUESTION-What are the advantages of VIEW?
ANS- To protect some of the columns of a table from other users.
- To hide complexity of a query.
- To hide complexity of calculations.

QUESTION-What is CYCLE/NO CYCLE in a Sequence?

ANS-CYCLE specifies that the sequence continue to generate values after reaching either maximum or minimum value. After pan-ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.

NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum value.

 QUESTION-How to access the current value and next value from a sequence? Is it possible to access the current value in a session before accessing next value?

ANS-Sequence name CURRVAL, sequence name NEXTVAL. It is not possible. Only if you access next value in the session, current value can be accessed

QUESTION-If unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE? ANS-It won't, Because SYSDATE format contains time attached with it.

QUESTION-How will you activate/deactivate integrity constraints? ANS-The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE CONSTRAINT / DISABLE CONSTRAINT.

QUESTION-Where the integrity constraints are stored in data dictionary?
ANS-The integrity constraints are stored in USER_CONSTRAINTS.

QUESTION-What are the pre-requisites to modify data type of a column and to add a column with NOT NULL constraint?
ANS- To modify the data type of a column the column must be empty.
- To add a column with NOT NULL constrain, the table must be empty.

QUESTION-What is difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?
ANS-CHAR pads blank spaces to the maximum length.
VARCHAR2 does not pad blank spaces.
For CHAR the maximum length is 255 and 2000 for VARCHAR2

QUESTION-What are the data types allowed in a table?
ANS-CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG and LONG RAW.

QUESTION-What is ON DELETE CASCADE? ANS-When ON DELETE CASCADE is specified Oracle maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.

QUESTION-What is the usage of SAVEPOINTS? ANS-SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.

QUESTION-What is referential integrity constraint? ANS-Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.

QUESTION-What is an integrity constraint? ANS-Integrity constraint is a rule that restricts values to a column in a table.

QUESTION-Which Oracle supplied package can you use to output values and messages from database triggers, stored procedures and functions within SQL*Plus?
ANS- 1. DBMS_DISPLAY
         2. DBMS_OUTPUT
         3. DBMS_LIST
         4. DBMS_DESCRIBE

QUESTION-Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed? 1. When the transaction is committed 2. During the data manipulation statement 3. When an Oracle supplied package references the trigger 4. During a data manipulation statement and when the transaction is committed

QUESTION-Under which circumstance must you recompile the package body after recompiling the package specification? ANS 1. Altering the argument list of one of the package constructs 2. Any change made to one of the package constructs 3. Any SQL statement change made to one of the package constructs 4. Removing a local variable from the DECLARE section of one of the package constructs

QUESTION-What is the advantage of specifying WITH GRANT OPTION in the GRANT command? ANS-The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.

QUESTION-TRUNCATE TABLE EMP; DELETE FROM EMP; Will the outputs of the above two commands differ?
ANS-Both will result in deleting all the rows in the table EMP..

QUESTION-What are the wildcards used for pattern matching.?
ANS- Under score ( _) for single character substitution and % for multi-character substitution

QUESTION-What is difference between TRUNCATE & DELETE ? ANS-TRUNCATE commits after deleting entire table i.e., cannot be rolled back. Database triggers do not fire on TRUNCATE DELETE allows the filtered deletion. Deleted records can be rolled back or committed. Database triggers fire on DELETE.

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

ANS-Join is a query, which retrieves related columns or rows from multiple tables.
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 retrieve rows that do not have corresponding join value in the other table.

QUESTION-What is the sub-query?

ANS-Sub-query is a query whose return values are used in filtering conditions of the main query.

QUESTION-What is correlated sub-query?

ANS-Correlated sub-query is a sub-query, which has reference to the main query.

QUESTION-Which three of the following are implicit cursor attributes? 1. %found 2. %too_many_rows 3. %notfound 4. %rowcount 5. %rowtype

QUESTION-Which command displays the SQL command in the SQL buffer, and then executes it?
ANS- RUN.

QUESTION-What command is used to get back the privileges offered by the GRANT command?
ANS- REVOKE

QUESTION-What operator performs pattern matching? ANS-LIKE operator.

QUESTION-What is the use of the DROP option in the ALTER TABLE command? ANS-It is used to drop constraints specified on the table.

QUESTION- What are the privileges that can be granted on a table by a user to others?                                  ANS-Insert, update, delete, select, references, index, execute, alter, all.

QUESTION-Which function is used to find the largest integer less than or equal to a specific value?
ANS- FLOOR.

QUESTION- What is the difference between Truncate and Delete interms of Referential Integrity?                                    

ANS- DELETE removes one or more records in a table, checking referential Constraints (to see if there are dependent child records) and firing any DELETE triggers. In the order you are deleting (child first then parent) There will be no problems.                                                                                                                             TRUNCATE removes ALL records in a table. It does not execute any triggers. Also, it only checks for the existence (and status) of another foreign key Pointing to the table. If one exists and is enabled, then you will get The following This is true even if you do the child tables first.  Unique/primary keys in table referenced by enabled foreign keys
you should disable the foreign key constraints in the child tables before issuing the TRUNCATE command, and then re-enable them afterwards.

QUESTION- How to Select last N records from a Table? ANS- select * from (select rownum a, CLASS_CODE,CLASS_DESC from EMP) where a > ( select (max(rownum)-10) from EMP) Here N = 10

QUESTION- What is the most important requirement for OLTP? ANS- OLTP requires real time response.

QUESTION- What is event trigger?
ANS- An event trigger, a segment of code which is associated with each event and is fired when the event occurs.

QUESTION- Why do stored procedures reduce network traffic ?                                                                                          ANS- When a stored procedure is called, only the procedure call is sent to the server and not the statements that the procedure contains.

QUESTION- What is a event handler?
ANS- An event handler is a routine that is written to respond to a particular event.

QUESTION- What is an integrity constraint?                                                                                                                         ANS- An integrity constraint allows the definition of certain restrictions, at the table level, on the data that is entered into a table.

QUESTION- What are the various uses of database triggers?                                                                                                                     ANS- Database triggers can be used to enforce business rules, to maintain derived values and perform value-based auditing.

QUESTION- Why are the integrity constraints preferred to database triggers?
ANS- Because it is easier to define an integrity constraint than a database trigger.

QUESTION- Why is it better to use an integrity constraint to validate data in a table than to use a stored procedure. ANS- Because an integrity constraint is automatically checked while data is inserted into a table. A stored has to be specifically invoked.

QUESTION- Which system table contains information on constraints on all the tables created? ANS-USER_CONSTRAINTS.

QUESTION- What is the use of CASCADE CONSTRAINTS?
ANS-When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

QUESTION- What is the default ordering of an ORDER BY clause in a SELECT statement?  ANS- Ascending

QUESTION- What is a Cartesian product? ANS- 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.

QUESTION- NVL ANS- Null value function converts a null value to a non-null value for the purpose of evaluating an expression. Numeric Functions accept numeric I/P & return numeric values. They are MOD, SQRT, ROUND, TRUNC & POWER.

QUESTION- BREAK ANS- BREAK command clarify reports by suppressing repeated values, skipping lines & allowing for controlled break points.

QUESTION- SPOOL ANS- SPOOL command creates a print file of the report.

QUESTION- What is the difference between the Boolean & operator and the && operator?                        ANS- If an expression involving the Boolean & operator is evaluated, both operands are evaluated. Then the & operator is applied to the operand. When an expression involving the && operator is evaluated, the first.

Operand is evaluated. If the first operand returns a value of true then the second operand is evaluated. The && operator is then applied to the first and second operands. If the first operand evaluates to false, the evaluation of the second operand is skipped.
Operator & has no chance to skip both sides evaluation and && operator does. If asked why, give details as above.

Data Warehousing

DWH  Is a RDBMS used for analytical propose.

Characteristics of DWH

·          

Su subject oriented

·         Time variant

·         Integrated data

·         Non volatile

·         Consistent DB.

Four stages of DWH

·         Data profiling

·         Data Clancy

·         Data quality

·         Data sampling

Data mining

It is used to dig the DWH to detect hidden pattern and relationship

Data mart

Data mart is subset of DWH. It contains departmental data. RALPH KIMBALL.

ì  Business data mart

ì  Technical data mart

Approaches of DWH

1.       BILL INMON / TOP DOWN -                             DWH to  data mart

2.       RALPH KIMBALL/ BOTTOM UP-                       Data mart to DWH

Data modeling

Data modeling refers to the process data structured and organized’

E-R modeling-

·         Transactional table – grow rapidly (sales)                                                                      [fact]

·         Semi transitional table- grow over a period of time (customer & product)             [dimension]

·         Non transactional table- never change (region, sub region)                                        [dimension]

Dimension modeling

Fact table

·         Fact table will have measure

·         Every column may have corresponding table relation.

·         They will not be any critical column.

·         Only numeric data.

·         Contain million of traction.

Dimension table

A.      Detail 

B.      Level

C.      Critical column

D.      Surrogate key

E.       No measures

F.       It contains more columns.

Types of schema                                           

Snow flake- A dimension table is splitting into one or more dimension which result is partially normalized or fully normalized.

A single fact table surrounded by multiple dimension tables.

Star schema- the fact table consist of numeric measurement is join to set dimension table field which distractive attributes.

Star schema is DB design which contain the centrally located fact table surrounded by dimension table.

Galaxy schema-a dimension table is shared by multiple fact table is known as confirmed dimension.

Fact less fact -   a fact table without numeric fact columns is called fact less fact fact table.

Non additive fact- fact that cannot be summed up for any of the dimension.

Additive fact- fact that can be summed up for any dimension.

Erwin (entity relation window)- It provide user friendly relation with database

·         ER win is computer associate

·         It is a product of logical words

·         It support 22 different type of DB

Logical model- logical model handled by business person. (The terminology is entity, attributes, relationship).

Physical model- it is handled by technical person. (Table, Column, Constraint).

Modeling technique

Forward Engineering- We create the model in the modeling tool and which is not present  in DB. If we want to this model to be present in db then we have to perform forward engineering.

Reverse engineering- It is a reverse process of forward engineering. We pull the structure from Db to model to make some modification.

 Whenever you perform reverse engineering you will get the model in physical window.

Complete compare- after the modification the table move in the DB through the complete compare.

Tab- to switch one area to another area.

Enter- to move within the area.

Active transformation- Number of record  in the source table may not be equal to the number of records in the target table . filter and aggregator

Passive transformation- Number of records should be same in both table.

SCD ( Slowly changing dimension)

Dimension that change over a date of time are called SCD

SCD Type 1-

·         We are not maintain any historical data . here the data will get over write. Hence there is no way to find out historical data.

·         The dimension table will contain only current data.

SCD Type 2-

·         In this old value will not be replaced but a new row contain the new value  will be added to the table.

SCD Type 3-

·         In this we can be able to see the latest update.

·          Only the problem with type 3 is we can maintain only partial history.