|
SQL/ SQLPlus
PL/SQL Interview Questions and Answers
(Continued from previous question...)
SQL/ SQLPlus
1. How can variables be passed to a SQL routine?
Level: Low
Expected answer: By use of the & symbol. For passing in variables the numbers 1-8 can be used (&1, &2,...,&8) to pass the values after the command into the SQLPLUS session. To be prompted for a specific variable, place the ampersanded variable in the code itself:
"select * from dba_tables where owner=&owner_name;" . Use of double ampersands tells SQLPLUS to resubstitute the value for each subsequent use of the variable, a single ampersand will cause a reprompt for the value unless an ACCEPT statement is used to get the value from the user.
2. You want to include a carriage return/linefeed in your output from a SQL script, how can you do this?
Level: Intermediate to high
Expected answer: The best method is to use the CHR() function (CHR(10) is a return/linefeed) and the concatenation function "||". Another method, although it is hard to document and isn?t always portable is to use the return/linefeed as a part of a quoted string.
3. How can you call a PL/SQL procedure from SQL?
Level: Intermediate
Expected answer: By use of the EXECUTE (short form EXEC) command.
4. How do you execute a host operating system command from within SQL?
Level: Low
Expected answer: By use of the exclamation point "!" (in UNIX and some other OS) or the HOST (HO) command.
5. You want to use SQL to build SQL, what is this called and give an example
Level: Intermediate to high
Expected answer: This is called dynamic SQL. An example would be:
set lines 90 pages 0 termout off feedback off verify off
spool drop_all.sql
select ?drop user ?||username||? cascade;? from dba_users
where username not in ("SYS?,?SYSTEM?);
spool off
Essentially you are looking to see that they know to include a command (in this case DROP USER...CASCADE;) and that you need to concatenate using the ?||? the values selected from the database.
6. What SQLPlus command is used to format output from a select?
Level: low
Expected answer: This is best done with the COLUMN command.
7. You want to group the following set of select returns, what can you group on?
Max(sum_of_cost), min(sum_of_cost), count(item_no), item_no
Level: Intermediate
Expected answer: The only column that can be grouped on is the "item_no" column, the rest have aggregate functions associated with them.
8. What special Oracle feature allows you to specify how the cost based system treats a SQL statement?
Level: Intermediate to high
Expected answer: The COST based system allows the use of HINTs to control the optimizer path selection. If they can give some example hints such as FIRST ROWS, ALL ROWS, USING INDEX, STAR, even better.
9. You want to determine the location of identical rows in a table before attempting to place a unique index on the table, how can this be done?
Level: High
Expected answer: Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick. For example:
select rowid from emp e
where e.rowid > (select min(x.rowid)
from emp x
where x.emp_no = e.emp_no);
In the situation where multiple columns make up the proposed key, they must all be used in the where clause.
10. What is a Cartesian product?
Level: Low
Expected answer: 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.
11. You are joining a local and a remote table, the network manager complains about the traffic involved, how can you reduce the network traffic?
Level: High
Expected answer: Push the processing of the remote data to the remote instance by using a view to pre-select the information for the join. This will result in only the data required for the join being sent across.
12. What is the default ordering of an ORDER BY clause in a SELECT statement?
Level: Low
Expected answer: Ascending
13. What is tkprof and how is it used?
Level: Intermediate to high
Expected answer: The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.
14. What is explain plan and how is it used?
Level: Intermediate to high
Expected answer: The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.
15. How do you set the number of lines on a page of output? The width?
Level: Low
Expected answer: The SET command in SQLPLUS is used to control the number of lines generated per page and the width of those lines, for example SET PAGESIZE 60 LINESIZE 80 will generate reports that are 60 lines long with a line width of 80 characters. The PAGESIZE and LINESIZE options can be shortened to PAGES and LINES.
16. How do you prevent output from coming to the screen?
Level: Low
Expected answer: The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output. This option can be shortened to TERM.
17. How do you prevent Oracle from giving you informational messages during and after a SQL statement execution?
Level: Low
Expected answer: The SET options FEEDBACK and VERIFY can be set to OFF.
18. How do you generate file output from SQL?
Level: Low
Expected answer: By use of the SPOOL command
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
|