Interview Question - SQL/SQL plus

[SN060619-1202]
1. How can variable be passed to a SQL routine?
ans: by use of & symbol(ampersands)
- e.g. "select * from dba_tables where owner=&owner_name"
- double ampersands tells SQLPLUS to resubstitute the value for each subsequent use of the variable

2. How to include a carriage return /linefeed in your output from a SQL script?
ans: using CHR(10) e.g. select 'hello' chr(10) 'world!' from user_tables;k

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?

ANS: 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);

10. What is a Cartesian product?
ANS: result of an unrestricted join of two or more tables. number of rows = #row of tab1 x #row of tab2 ...

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?,
Ans: 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?
Ans: Ascending

13. What is tkprof and how is it used?
Ans:
- The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements
- First setting timed_statistics to true in the initialization file; then turning on tracing for either the entire database via sql_trace parameter or for the session using 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 aslo be used to generate explain planb output.

14. What is explain plan and how is it used?
Ans: The explain plan is a tool to tune SQL statements
- to use it you must have an explain_table generated
- created using utlplan.sql script
- run the explain commmand giving as its argument the SQL statement to be explained
- can be run using tkprof.