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. It is causes by specifying a table in the FROM clause without joining it to another table.
2. What is an advantage to using a stored procedure as opposed to passing an SQL query from an application.
Ans: 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.
3. What is the difference of a LEFT JOIN and an INNER JOIN statement?
Ans: 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
4. When a query is sent to the database and an index is not being used, what type of execution is taking place?
Ans: A table scan.
5. What are the pros and cons of using triggers?
Ans: 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.
6. What are the pros and cons of using stored procedures. When would you use them?
7. What are the pros and cons of using cursors? When would you use
them?
What are the different types of joins?
Explain normalization with examples.
What cursor type do you use to retrieve multiple recordsets?
Diffrence between a "where" clause and a "having" clause
What is the difference between "procedure" and "function"?
How will you copy the structure of a table without copying the data?
How to find out the database name from SQL*PLUS command prompt?
Tadeoffs with having indexes
Talk about "Exception Handling" in PL/SQL?
What is the diference between "NULL in C" and "NULL in Oracle?"
What is Pro*C? What is OCI?
Give some examples of Analytical functions.
What is the difference between "translate" and "replace"?
Difference between "ORACLE" and "MICROSOFT ACCESS" databases.
How to create a database link ?
What is DYNAMIC SQL method 4?
How to remove duplicate records from a table?
What is the use of ANALYZing the tables?
How to run SQL script from a Unix Shell?
What is a "transaction"? Why are they necessary?
Explain Normalizationa dn Denormalization with examples.
When do you get contraint violtaion? What are the types of constraints?
How to convert RAW datatype into TEXT?
Difference - Primary Key and Aggregate Key
How functional dependency is related to database table design?
What is a "trigger"?
Why can a "group by" or "order by" clause be expensive to process?
What are "HINTS"? What is "index covering" of a query?
What is a VIEW? How to get script for a view?
What are the Large object types suported by Oracle?
What is SQL*Loader?
Difference between "VARCHAR" and "VARCHAR2" datatypes.
What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table.