Set - 7

Question 6 :

You want to use SQL to build SQL, what is this called and give an example

Answer :

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.


Question 7 :

What SQLPlus command is used to format output from a select?

Answer :

Level: low
Expected answer: This is best done with the COLUMN command.


Question 8 :

You want to group the following set of select returns, what can you group on?

Answer :

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.


Question 9 :

What special Oracle feature allows you to specify how the cost based system treats a SQL statement?

Answer :

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.


Question 10 :

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?

Answer :

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.