What's Indexes

PL/SQL Interview Questions and Answers

(Continued from previous question...)

What's Indexes

Indexes are optional structures associated with tables used to speed query execution and/or guarantee uniqueness. Create an index if there are frequent retrieval of fewer than 10-15% of the rows in a large table and columns are referenced frequently in the WHERE clause. Implied tradeoff is query speed vs. update speed. Oracle automatically update indexes. Concatenated index max. is 16 columns.

(Continued on next question...)

Other Interview Questions

• What's SQL
• What's SQL*Plus
• What's NVL
• What's Date Functions
• What's Character Functions
• What's TTITLE & BTITLE
• What's COLUMN
• What's BREAK
• What's COMPUTE
• What's SET
• What's SPOOL
• What's JOIN
• What's Union
• What's Intersect
• What's Minus
• What's Correlated Subquery
• What's Multiple columns
• What's Sequences
• What's Synonyms
• What's Indexes
• What's Data types
• Order of SQL statement execution
• What's Transaction
• What's Commit
• What's Posting
• What's Rollback
• What's Savepoint
• Set Transaction
• What's Locking
• What's Consistency
• What's Integrity
• What's Deadlock
• What's Mutating Table
• What's SQL*Loader
• The most important DDL statements in SQL are:
• Operators used in SELECT statements.
• SELECT statements:
• The SELECT INTO Statement is most often used to create backup copies of tables or for archiving records.
• The INSERT INTO Statements:
• The Update Statement:
• The Delete Statements:
• Sort the Rows:
• The IN operator may be used if you know the exact value you want to return for at least one of the columns.
• BETWEEN ... AND
• What is the use of CASCADE CONSTRAINTS?
• Why does the following command give a compilation error?
• Which system tables contain information on privileges granted and privileges obtained?
• Which system table contains information on constraints on all the tables created?obtained?
• What is the difference between TRUNCATE and DELETE commands?
• State true or false. !=, <>, ^= all denote the same operation?
• State true or false. EXISTS, SOME, ANY are operators in SQL?
• What will be the output of the following query?
• What does the following query do?
• What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
• Which command executes the contents of a specified file?
• What is the value of comm and sal after executing the following query if the initial value of ‘sal’ is 10000 UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1;?
• Which command displays the SQL command in the SQL buffer, and then executes it?
• What command is used to get back the privileges offered by the GRANT command?
• What will be the output of the following query?
• Which date function is used to find the difference between two dates?
• What operator performs pattern matching?
• What is the use of the DROP option in the ALTER TABLE command?
• What operator tests column for the absence of data?
• What are the privileges that can be granted on a table by a user to others?
• Which function is used to find the largest integer less than or equal to a specific value?
• Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
• What is the use of DESC in SQL?
• What command is used to create a table by copying the structure of another table?
• TRUNCATE TABLE EMP; DELETE FROM EMP; Will the outputs of the above two commands differ?
• What is the output of the following query SELECT TRUNC(1234.5678,-2) FROM DUAL;?
• What are the wildcards used for pattern matching.?
• What is the parameter substitution symbol used with INSERT INTO command?
• What's an SQL injection?
• What is difference between TRUNCATE & DELETE
• What is a join? Explain the different types of joins?
• What is the sub-query?
• What is correlated sub-query?
• Explain CONNECT BY PRIOR?
• Difference between SUBSTR and INSTR?
• Explain UNION, MINUS, UNION ALL and INTERSECT?
• What is ROWID?
• What is the fastest way of accessing a row in a table?
• What is an integrity constraint?
• What is referential integrity constraint?
• What is the usage of SAVEPOINTS?
• What is ON DELETE CASCADE?
• What are the data types allowed in a table?
• What is difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?
• How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY?
• What are the pre-requisites to modify datatype of a column and to add a column with NOT NULL constraint?
• Where the integrity constraints are stored in data dictionary?
• How will you activate/deactivate integrity constraints?
• If unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE?
• What is a database link?
• 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?
• What is CYCLE/NO CYCLE in a Sequence?
• What are the advantages of VIEW?
• Can a view be updated/inserted/deleted? If Yes - under what conditions?
• If a view on a single base table is manipulated will the changes be reflected on the base table?
• Which of the following statements is true about implicit cursors?
• Which of the following is not a feature of a cursor FOR loop?
• How would the developer define this variable using referential datatypes?
• Which three of the following are implicit cursor attributes?
• If left out, which of the following would cause an infinite loop to occur in a simple loop?
• Which line in the following statement will produce an error?
• The command used to open a CURSOR FOR loop is
• What happens when rows are found using a FETCH statement
• Read the following code:
• Read the following code:
• What is the maximum number of handlers processed before the PL/SQL block is exited when an exception occurs?
• 77. For which trigger timing can you reference the NEW and OLD qualifiers?
• Read the following code 2:
• When invoking this procedure, you encounter the error
• Read the following code 3
• Which procedure can be used to create a customized error message?
• The CHECK_THEATER trigger of the THEATER table has been disabled. Which command can you issue to enable this trigger?
• Examine this database trigger
• Examine this function:
• Under which circumstance must you recompile the package body after recompiling the package specification?
• Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?
• Which Oracle supplied package can you use to output values and messages from database triggers, stored procedures and functions within SQL*Plus?
• What occurs if a procedure or function terminates with failure without being handled?
• Examine this code
• Which Oracle supplied package will enable this feature?
• Which Oracle supplied package will enable this feature?
• How to implement ISNUMERIC function in SQL *Plus ?
• How to Select last N records from a Table?
• What is the difference between Truncate and Delete interms of Referential Integrity?
• CLIENT/SERVER
• What does the OLTP stands for ?
• What is the most important requirement for OLTP ?
• In a client server environment, what would be the major work that the client deals with ?
• Why is the most of the processing done at the sever ?
• What does teh term upsizing refer to ?
• What does one do when one is rightsizing ?
• What does the term downsizing refer to ?
• What is event trigger ?
• Why do stored procedures reduce network traffic ?
• What are the types of processes that a server runs ?
• What is a event handler ?
• What is an integrity constraint ?
• What are the various uses of database triggers ?
• What is a transaction ?
• Why are the integrity constraints preferred to database triggers ?
• Why is it better to use an integrity constraint to validate data in a table than to use a stored procedure ?
• What are the three components of a client server model ?
• What are the advantages of client/server model ?
• What are the disadvantages of the client/server model ?
• What are the different topologies available for network ?
• What is the first work of Client process ?
• What are the responsibilities of a Server ?
• In a Client/Server context, what does API (Application Programming Interface) refer to ?
• Give some examples of standard API??s ?
• What is the main advantage of developing an application using an API ?
• What is the main disadvantage of developing an application using an API ?
• Why is an event driven program referred to a passive program ?
• What are the four types of events ?
• What is the difference between file server and a database server ?
• What is inheritance ?
• What are the two components of ODBC ?
• What is the function of a ODBC manager ?
• What is the function of a ODBC Driver ?
• What description of a data source is required for ODBC ?
• How is a connection establised by ODBC ?
• PL/SQL Questions:
• SQL/ SQLPlus