Best practices and recommendations for preparing SQL Scripts

We have code release for our Oracle databases frequently. Typically it is implemented by executing various SQL or PL/SQL scripits on servers from SQL*Plus.  Many times I have observed there are simple syntax errors in the scripts we received from development team and also the format of the script is not consistent. I thus tried to compile a list of best practices and recommendations for preparing the release scripts for the team. Here are some points:

• Add spool to every script in the following format as minimum:

             set echo on timing on
             spool log/script_name.log
              ...
             spool off

• Separate DDL and DML SQL statements into different scripts.

              Recommend using suffix to distinguish DDL and DML script: e.g. script_ddl.sql, script_dml.sql.

• Separate different type of object creation into different scripts:

           i.e. table, index, package etc. Recommend using the following suffix to distinguish different script in   the format: script_xxx.sql. The ‘xxx’can be:

                      Package specification              pks
                      Package body                         pkb
                      Package sepc and body          pkg
                      Procdueure                             pro
                      Trigger                                    trg
                      Function                                  fun
                      Create table script(s)               tab or ddl
                      Synonym creation statements   syn
                      Index definition                        idx
                      Constraint definitions               con

• Use lower case ".sql" as a suffix for all scripts

        [Mar 16,2010 Update - I tried to do: grep -i create *.sql yesterday, I failed to find a new table creation  due to the script ended with ".SQL". ]

• Recommend consolidating the script in the execution sequence of DDL first followed by DML.

• Recommend using some GUI tools such as TOAD, SQL Developer etc to minimize the syntax errors.

                   At minimum, everyone should do the following:
                   - Check if missing "schema" prefix for table, index names etc.
                   - Check if missing ";" in SQL
                   - Check if missing "/" as the end the PL/SQL begin/end block
                   - Check if comma used properly in the table column list for example
                   - Check if upper or lower cases are used consistently

• Recommend including the back out method (CTAS) for DML script

• Recommend formatting and aligning SQL statements properly whenever possible for improving readability

• Add "show error;" at the end of PL/SQL procedure, function, packages units.

• Avoid using ampersand character ‘&’ within any comment lines in the SQL scripts.

• Avoid using space in the script file name.

• Avoid adding storage clause in the table or index creation statement

           Note: defer to primary DBA to make such a decision if defaults are not desirable

• Avoid using more than 30 characters for table name or column name.

• Avoid using system-generated constraint names.

           For example the following method to add a primary key constraint is not acceptable:
              alter table myschema.t modify (id primary key);

• Recommend using the following methods to create primary key

         (1) Within CREATE TABLE statement

              CREATE TABLE t
              ( id number,
               val varchar2(40),
                constraint t_pk primary key (id) using index tablespace tablespace_name
              ) tablespace  tablespace_name;

        (2) Through ALTER TABLE statement

              CREATE TABLE t
              ( id number,
               val varchar2(40)
               ) tablespace tablespace_name ;

              ALTER TABLE t
              add constraint t_pk primary key(id) using index tablespace tablespace_name;

Don't use PL/SQL to Do the Job of SQL

Several days ago, I was asked to review scripts from development team for an application consolidation effort. There is a particular script that uses PL/SQL, which run more than 3 hours. After I reviewed it, I believed they can be written by SQL statment.

For example, for the following PL/SQL block, it depends on the EXCEPTION condition to update a table. Though I am not an experienced PL/SQL programmer, I am suspicous this could be considered good practice in PL/SQL.

-- PL/SQL block

BEGIN
   FOR bas_rec IN bas_cur LOOP
      v_my_account_id:=bas_rec.my_account_id;

      BEGIN 
  SELECT DISTINCT ms.state_id INTO v_state_id
    FROM
      my_sch.tab_ms ms
    WHERE
      ms.account_id = v_my_account_id AND
      ms.state_id IS NOT NULL AND
      ms.svc_node_type_id  NOT IN (203,204,206,208,218,402) AND
      ms.is_pq ='N' AND
      ms.svc_status_id = 2;
     
  EXCEPTION
    WHEN TOO_MANY_ROWS THEN
      UPDATE my_sch.conv_2 bas
        SET bas.to_be_converted_status=v_exclusion_flag, 
     bas.exclusion_reason = v_exclusion_reason_multi, 
     bas.is_processed = v_processed_flag
        WHERE bas.my_account_id = v_my_account_id;
        v_is_updated:='Y';
      COMMIT;
    WHEN NO_DATA_FOUND THEN NULL; 
    WHEN OTHERS THEN 
    dbms_output.put_line('Multi State block OTHERS - v_ban:'||sqlerrm ); 
  COMMIT;
      END; 
  END LOOP;
END;

Anyway, I know that " Don't use PL/SQL to Do the Job of SQL". So I translated the above PL/SQL to the following SQL.

-- SQL code 

UPDATE my_sch.conv_2 bas
SET bas.to_be_converted_status='N', 
    bas.exclusion_reason = 'SERVICES IN MULTIPLE STATES', 
    bas.is_processed ='Y' 
WHERE bas.is_processed is null
  AND 1 < ( select count( DISTINCT ms.state_id)
      from my_ord.tab_ms ms
       WHERE ms.account_id =  bas.my_account_id
  AND ms.state_id IS NOT NULL 
  AND ms.svc_node_type_id  NOT IN (203,204,206,208,218,402) 
  AND ms.is_pq ='N' 
  AND ms.svc_status_id = 2 )
;

I also re-wrote the other part of the script with SQL. In a small scale test, original PL/SQL took 5 min. My SQL code took about 1.5 min. Sadly, developers are more comfortable with their PL/SQL code and are unwilling to do a thoroug test and verification about SQL method. So I will still use their PL/SQL code in the production implementation - just running 5 threads of them to speed up instead of 1 thread previously.

A SQL with a hidden error

This test case is built from a real life DBA task.

SQL> create table t as select rownum id, object_name from dba_objects where rownum <=10;

Table created.

SQL> create table t2 as select rownum id1, object_name from dba_objects where rownum <=5;

Table created.


Our intention was to do the following SQL with a subqeury:

SQL> select * from t where id in ( select id1 from t2);

ID OBJECT_NAME
---------- ------------------------------
1 TAB$
2 I_IND1
3 I_COBJ#
4 USER$
5 I_OBJ1


Instead, we executed the following SQL, which has a typo . However, it got executed without error:

SQL> select * from t where id in ( select id from t2);

ID OBJECT_NAME
---------- ------------------------------
1 TAB$
2 I_IND1
3 I_COBJ#
4 USER$
5 I_OBJ1
6 I_PROXY_ROLE_DATA$_2
7 C_FILE#_BLOCK#
8 C_OBJ#
9 BOOTSTRAP$
10 I_ICOL1

10 rows selected.

Isn't it better Oracle can throw error for this SQL?

DELETE from a view

Sometime, we need to do "scrub" against a big table, for example,delete (or update) some rows based on the condition in a second small table. We can probably perform this task through "delete from a view" if the second table has primary key constraint. The following test demonstrated the importance of table order if we do "delete from a view". We need to put the target big table first in the FROM list, otherwise we may end up deleting the small table.

SQL> create table t1
  2  as
  3  select rownum id,
  4      rpad('*', 50) pad
  5  from all_objects
  6  where rownum <=100;

Table created.

SQL>
SQL>
SQL> create table t2
  2  as
  3  select rownum*3 id
  4  from all_objects
  5  where rownum <=10;

Table created.

SQL>
SQL> alter table t1 add constraint t1_pk primary key(id);

Table altered.

SQL> alter table t2 add constraint t2_pk primary key(id);

Table altered.

SQL>
SQL> execute dbms_stats.gather_table_stats(user,'t1');

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_table_stats(user,'t2');

PL/SQL procedure successfully completed.

SQL>
SQL> -- Order: t1, t2
SQL> delete from plan_table;

3 rows deleted.

SQL> explain plan for
  2  delete from
  3  ( select t1.id
  4    from t1,
  5         t2
  6    where t2.id = t1.id
  7  );

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------
Plan hash value: 2755785190

-----------------------------------------------------------------------
 Id   Operation            Name   Rows   Bytes  Cost (%CPU) Time  
-----------------------------------------------------------------------
   0  DELETE STATEMENT               10     60      1   (0) 00:00:01
   1   DELETE              T1                                     
   2    NESTED LOOPS                 10     60      1   (0) 00:00:01
   3     INDEX FULL SCAN   T2_PK     10     30      1   (0) 00:00:01
*  4     INDEX UNIQUE SCAN T1_PK      1      3      0   (0) 00:00:01
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T2"."ID"="T1"."ID")

16 rows selected.

SQL>
SQL> -- Order: t2, t1
SQL>
SQL> delete from plan_table;

5 rows deleted.

SQL> explain plan for
  2  delete from
  3  ( select t1.id
  4    from t2,
  5         t1
  6    where t2.id = t1.id
  7  );

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
Plan hash value: 525412351

-----------------------------------------------------------------------
 Id   Operation            Name   Rows   Bytes  Cost (%CPU) Time  
-----------------------------------------------------------------------
   0  DELETE STATEMENT               10     60      1   (0) 00:00:01
   1   DELETE              T2                                     
   2    NESTED LOOPS                 10     60      1   (0) 00:00:01
   3     INDEX FULL SCAN   T2_PK     10     30      1   (0) 00:00:01
*  4     INDEX UNIQUE SCAN T1_PK      1      3      0   (0) 00:00:01
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T2"."ID"="T1"."ID")

16 rows selected.

SQL> spool off

Filtering first or constructing the hierarchical tree first?

A SQL with "start with" and "connect by" looks like:

select level, a.*
from t a
where a.ntid =2
start with a.vp_bid =9 or a.vp_bid is null
connect by prior a.bid= a.vp_bid;

My doubt is that Oracle will first apply the predicate: ntid=2 , or Oracle will
apply the "start with ... connect by" first, then do the filtiering? My test shows the latter is true


The rows in the table:

SQL> select * from t;

       BID       NTID     VP_BID
---------- ---------- ----------
         1          2
         2          2
         3          2          1
         4          2        100
         5          2          2
         6          7          2
         7          7          9
        10         10          9
        11         10         10
        12          2         10
        30         30         30

11 rows selected.

If filtering first, what we should see:

SQL> select level, a.* from
  2     ( select * from t where ntid=2) a
  3  start with a.vp_bid =9 or a.vp_bid is null
  4     connect by prior a.bid= a.vp_bid;

     LEVEL        BID       NTID     VP_BID
---------- ---------- ---------- ----------
         1          1          2
         2          3          2          1
         1          2          2
         2          5          2          2

If applying the hierarchical condition first, then the filtering, what we should see:

SQL> select * from (
  2     select level,  a.*
  3      from t a
  4      start with a.vp_bid =9 or a.vp_bid is null
  5     connect by prior a.bid= a.vp_bid
  6  )
  7  where ntid=2;

     LEVEL        BID       NTID     VP_BID
---------- ---------- ---------- ----------
         2         12          2         10
         1          1          2
         2          3          2          1
         1          2          2
         2          5          2          2

What we see from the original SQL confirms that Oracle applys the hierarchical condition first:

SQL> select level,  a.*
  2   from t a
  3   where a.ntid =2
  4   start with a.vp_bid =9 or a.vp_bid is null
  5  connect by prior a.bid= a.vp_bid;

     LEVEL        BID       NTID     VP_BID
---------- ---------- ---------- ----------
         2         12          2         10
         1          1          2
         2          3          2          1
         1          2          2
         2          5          2          2

Without the predicate, we should see:

SQL> select level,  a.*
  2       from t a
  3    start with a.vp_bid =9 or a.vp_bid is null
  4  connect by prior a.bid= a.vp_bid;

     LEVEL        BID       NTID     VP_BID
---------- ---------- ---------- ----------
         1          7          7          9
         1         10         10          9
         2         11         10         10
         2         12          2         10
         1          1          2
         2          3          2          1
         1          2          2
         2          5          2          2
         2          6          7          2

9 rows selected.

Doubts about UNION operation

I have some doubts about 'union' operation. I know that when two tables union together, duplicate rows will be removed in the result set. My doubts are that if there are duplicated rows in the same table, will Oracle remove those rows? I thus did a simple test. The answer is YES.

My test:

SQL> select * from t;

A
----------
1
2
3
4
5
2

6 rows selected.

Note: I have duplicated rows in table t;

SQL> select * from t1;

A
----------
5
3
8


Union all - Oracle preserves all the rows from table t and t1 as expected

SQL> select * from t1
2 union all
3 select * from t;

A
----------
5
3
8
1
2
3
4
5
2

9 rows selected.


Union - Note: Oracle removes duplicated rows from two tables as well as from the same table:

SQL> select * from t1
2 union
3 select * from t;

A
----------
1
2
3
4
5
8

6 rows selected.

Using Case When

Considering the following table, suppose I want to count how many values associated with the same key are greater than 10, what can I do?

SQL> select * from t;

       KEY        VAL
---------- ----------
         1          1
         1          2
         1         10
         1         11
         2          1
         2          2
         2          3
         3         13
         3         33
         3          3

10 rows selected.

If I use the following statement, the problem is it does not show key=2, in which case there are no values greater than 10:

SQL> select key, count(*) from t where val > 10 group by key;

       KEY   COUNT(*)
---------- ----------
         1          1
         3          2

Case when probably is what I want:

SQL> select key, sum(case when val > 10 then 1 else 0 end) from t group by key;

       KEY SUM(CASEWHENVAL>10THEN1ELSE0END)
---------- --------------------------------
         1                                1
         2                                0
         3                                2

Hierachical SQL used to combine multiple rows

A user asked a question about how to combine rows that have same key value into one rows on the Boobooke Oracle forum.
For example, for the table:

SQL> select * from t;

KEY VALUE
---------- --------------------
1 a
1 b
1 c

How to write a sql to give the output looks like:

KEY VALUE
--- -----
1 a/b/c

He actually gave the answer. Based on his input, I did a test. First I created the following table:

SQL> create table uavsub (num_prod number,
2 summary number,
3 cod_dep varchar2(20)
4 );

Table created.

SQ>
SQL> insert into uavsub values (3, 3, 'a');

1 row created.

SQL> insert into uavsub values (2, 9, 'b');

1 row created.

SQL> insert into uavsub values (1, 1, 'a');

1 row created.

SQL> insert into uavsub values (1, 3, 'a');

1 row created.

SQL> insert into uavsub values (3, 4, 'a');

1 row created.

SQL> insert into uavsub values (2, 1, 'a');

1 row created.

SQL> insert into uavsub values (3, 1, 'a');

1 row created.

SQL> insert into uavsub values (3, 2, 'a');

1 row created.

SQL> insert into uavsub values (1, 2, 'b');

1 row created.

SQL> insert into uavsub values (3, 3, 'a');

1 row created.

SQL> commit;

Commit complete.

SQ>
SQL> col summary format 999
SQL> select * from uavsub;

NUM_PROD SUMMARY COD_DEP
-------- ------- ---------
3         3        a
2         9        b
1         1        a
1         3        a
3         4        a
2         1        a
3         1        a
3         2        a
1         2        b
3         3        a

10 rows selected.

Then I executed the following sql, which only gave ouput for the case of num_prod=3:

SQL>
SQL> select t.num_prod num_prod,
2              max(substr(sys_connect_by_path(t.summary, '/'), 2)) summary
3  from (
4               select num_prod,
5                      summary,
6                      row_number() over (partition by  num_prod order by summary ) rn
7               from uavsub
8               where num_prod=3
9                and cod_dep is not null
10             ) t
11  start with rn = 1
12  connect by rn = prior rn + 1
13           and num_prod  = prior num_prod
14  group by t.num_prod;

NUM_PROD    SUMMARY                                                                    
--------    ------
3          1/2/3/3/4                                                                  
                                                                         

The below sql lift the constraint: num_prod=3

SQL>
SQL>
SQL> col summary format a20
SQL>
SQL> select t.num_prod num_prod,
2              max(substr(sys_connect_by_path(t.summary, '/'), 2)) summary
3  from (
4               select num_prod,
5                      summary,
6                      row_number() over (partition by  num_prod order by summary ) rn
7               from uavsub
8               where  cod_dep is not null
9             ) t
10  start with rn = 1
11  connect by rn = prior rn + 1
12           and num_prod  = prior num_prod
13  group by t.num_prod;


NUM_PROD SUMMARY                                                         
-------- --------------------                                            
      1 1/2/3                                                           
      2 1/9                                                             
      3 1/2/3/3/4

However, I did not fully understand the "connect by" sql at that time. I don't know if this is the best way to solve the problem, but this did urge me to understand hierarchical sql better. So I did a few more tests as follows:

For brevity, I create a view first

SQL> create view t_vw as
2 select key, value, row_number() over (partition by key order by value) rn from t;

View created.

SQL> select * from t_vw;

KEY VALUE RN
---------- --------- ----------
1 a 1
1 b 2
1 c 3

Then, I tested the meaning of sys_connect_by_path,

SQL>;
1 select key, value, sys_connect_by_path(value, '/') path from t_vw
2 start with rn = 1
3* connect by rn = prior rn + 1

SQL>

KEY VALUE PATH
---------- -------------------- ----------
1 a /a
1 b /a/b
1 c /a/b/c

Then:

SQL> ;
1 select key, max(sys_connect_by_path(value, '/')) path from t_vw
2 start with rn = 1
3 connect by rn = prior rn + 1
4* group by key
SQL> /

KEY PATH
---------- ------------------------------
1 /a/b/c


Finally,

1 select key, substr(max(sys_connect_by_path(value, '/')),2) path from t_vw
2 start with rn = 1
3 connect by rn = prior rn + 1
4* group by key
SQL> /

KEY PATH
---------- ------------------------------
1 a/b/c

Combine Multiples Scans with CASE Statements

Combining multiple scans into one scan can be done by moving the WHERE condition of each scan into a CASE statement, which filters the data for the aggregation.

hr@ORCL> select count(*) from employees where salary < 10000; 
 COUNT(*)
----------        
87

hr@ORCL>  select sum(salary) from employees where salary < 10000;
SUM(SALARY)
-----------   
463795.5

hr@ORCL> select avg(salary) from employees where salary < 10000;
AVG(SALARY)
----------- 
5330.98276

hr@ORCL> SELECT COUNT (CASE WHEN salary < 10000  
2                     THEN 1 ELSE null END) count1,  
3               SUM (CASE WHEN salary < 10000  
4                     THEN salary ELSE null END) sum1,  
5               AVG (CASE WHEN salary < 10000  
6                     THEN salary ELSE null END) avg1  
7        FROM employees;    

COUNT1       SUM1       AVG1
---------- ---------- ----------        
      87   463795.5    5330.98276

Understanding Oracle Analytical Function: row_number()

Purpose
-------
 Understanding Oracle analytical function: row_number()

Syntax
------
 row_number() over (order by col_1 [, col_2 ...])
 row_number() over (partition by col_n [, col_m ... ]
                    order by col_1 [, col_2 ...])

 row_number() returns an integer greater or equal to one



Test steps
-----------

1. Without partition 

 *** Run script: row_number_noprttn.tst from sqlplus

 scott@ORCL> ho cat row_number_noprttn.tst

 create table row_number_test (
   a number,
   b varchar2(20)
 );

 insert into row_number_test values (22, 'twenty two');
 insert into row_number_test values ( 1, 'one');
 insert into row_number_test values (13, 'thirteen');
 insert into row_number_test values ( 5, 'five');
 insert into row_number_test values ( 4, 'four');

 select a, b, row_number() over (order by b)
   from row_number_test
  order by a;

 drop table row_number_test;

 *** Results:

   A B                    ROW_NUMBER()OVER(ORDERBYB)
 ---------- -------------------- --------------------------
   1 one                                           3
   4 four                                          2
   5 five                                          1
  13 thirteen                                      4
  22 twenty two                                    5


 *** Notes: the value of ROW_NUMER col is obtained by order by B
 whith five the lowest and twenty two the highest


2. With partition

 *** Run script: row_number_prttn.tst from sqlplus

 scott@ORCL> ho cat row_number_prttn.tst
 create table row_number_test_2 (
   a number,
   b varchar2(20),
   c char(1)
 );

 insert into row_number_test_2 values (22, 'twenty two',   '*');
 insert into row_number_test_2 values ( 1, 'one',          '+');
 insert into row_number_test_2 values (13, 'thirteen',     '*');
 insert into row_number_test_2 values ( 5, 'five',         '+');
 insert into row_number_test_2 values ( 4, 'four',         '+');

 select
   a, b, row_number() over (partition by c order by b)
 from
   row_number_test_2
 order
   by a;

 drop table row_number_test_2;

 *** Output

       A B                    ROW_NUMBER()OVER(PARTITIONBYCORDERBYB)
---------- -------------------- --------------------------------------
       1 one                                                       3
       4 four                                                      2
       5 five                                                      1
      13 thirteen                                                  1
      22 twenty two                                                2

 *** Notes:
      In this case, there are two partitions resulted by partition by c
      and the row_number col value starts from 1 in each partition

Find tables exsiting in database A but not in database B in the same schema

repadmin@PRDQA> select table_name from dba_tables where owner='PRD'
minus
select table_name from dba_tables@prdp.world where owner='PRD';

TABLE_NAME
------------------------------
AUD$
PRD_AUDIT
PRD_AUDIT_SESSION

repadmin@PRDQA> select table_name from dba_tables@prdp.world where
owner='PRD'
minus
select table_name from dba_tables where owner='PRD';

TABLE_NAME
------------------------------
BK_PRDERAL_STATISTICS

Demonstrating FULL, LEFT, RIGHT Join

SQL> select * from movie;

       MID        DID
---------- ----------
       100          1
       101          2
       102          2
       103          3
       104          1
       105
       106

7 rows selected.

SQL> select * from director;

       DID NAME
---------- --------------------
         1 Jane
         2 Bob
         3 Denis
         4 Jack
         5 Dev
         6 Kay

6 rows selected.

SQL> select * from movie join director  using (did) order by mid;

       DID        MID NAME
---------- ---------- --------------------
         1        100 Jane
         2        101 Bob
         2        102 Bob
         3        103 Denis
         1        104 Jane

SQL> select * from movie left join director  using (did) order by mid;

       DID        MID NAME
---------- ---------- --------------------
         1        100 Jane
         2        101 Bob
         2        102 Bob
         3        103 Denis
         1        104 Jane
                  105
                  106

7 rows selected.

SQL> select * from movie right join director  using (did) order by mid;

       DID        MID NAME
---------- ---------- --------------------
         1        100 Jane
         2        101 Bob
         2        102 Bob
         3        103 Denis
         1        104 Jane
         4            Jack
         5            Dev
         6            Kay

8 rows selected.

SQL> select * from movie full join director  using (did) order by mid;

       DID        MID NAME
---------- ---------- --------------------
         1        100 Jane
         2        101 Bob
         2        102 Bob
         3        103 Denis
         1        104 Jane
                  105
                  106
         4            Jack
         5            Dev
         6            Kay

10 rows selected.

ALL, SOME and subquery -A Test

SQL> select * from tab1;

         A          B          C
---------- ---------- ----------
         1          2          3
         2          2          3
         3          2          3
         4          4          3

SQL> select * from tab1 where a = (select a from tab1);
select * from tab1 where a = (select a from tab1)
                              *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row


SQL> select * from tab1 where a = all (select a from tab1);

no rows selected

SQL> select * from tab1 where a = some (select a from tab1);

         A          B          C
---------- ---------- ----------
         1          2          3
         2          2          3
         3          2          3
         4          4          3

SQL> select * from tab1 where a = all (select b from tab1);

no rows selected

SQL> select * from tab1 where a = some (select b from tab1);

         A          B          C
---------- ---------- ----------
         2          2          3
         4          4          3

SQL> select * from tab1 where a = all (select c from tab1);

         A          B          C
---------- ---------- ----------
         3          2          3

SQL> select * from tab1 where a = some (select c from tab1);

         A          B          C
---------- ---------- ----------
         3          2          3

SQL> select * from tab1 where a = some (select b, c from tab1);
select * from tab1 where a = some (select b, c from tab1)
                                   *
ERROR at line 1:
ORA-00913: too many values

Function TRIM examples

SQL> select trim('*' from '***comments***') from dual;

TRIM('*'
--------
comments

SQL> select trim(leading '*' from '***comments***') from dual;

TRIM(LEADIN
-----------
comments***

SQL> select trim(trailing '*' from '***comments***') from dual;

TRIM(TRAILI
-----------
***comments

SQL> select trim(both '*' from '***comments***') from dual;

TRIM(BOT
--------
comments

SQL Chap 10 - User Access and Security

This is the last chapter of the book.


Review Questions


1. Which of the following assertions most correctly describes the privileges in force after the SQL below is executed?

connect athos/musketeer
grant select,insert,update,delete on
   athos.services to porthos
   with grant option;
grant all on athos.services to aramis;
connect porthos/musketeer
grant select,delete,insert,update on
   athos.services to aramis
   with grant option;
connect athos/musketeer
revoke all on athos.services from aramis;

A. Aramis can create an index on athos.services.
B. Aramis has no privileges on athos.services.
C. Aramis can select from athos.services.
D. Aramis can select, insert, update, and delete rows from athos.services.
-----
Object privileges can be obtained from more than one grantor. To completely remove object privileges from an account, all grantors must revoke these privileges. Aramis was granted the four privileges SELECT, INSERT, UPDATE, and DELETE on athos.services from Porthos, as well as ALL (SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, and REFERENCE) from Athos. After Athos revokes the privileges that he granted, Aramis still retains the privileges that were granted from Porthos.
Ans: D.


2. Which of the following assertions most correctly describes the privileges in force after the SQL below is executed?


connect system/manager
grant select any table to jon with admin option;
grant select any table to jason;
connect jon/seekrit
grant select any table to jason;
revoke select any table from jason;

A. Jason can select from any table regardless of any individual table privileges.
B. Jason can only select from tables that he has been granted SELECT privileges on or has acquired via a role.
C. Jason can only select from his own tables.
D. Jason continues to enjoy the SELECT ANY TABLE privilege.
---
Oracle does not retain the grantor on system privileges, so if anyone revokes a system privilege, that privilege is gone, even if the grantee obtained it from more than one grantor. This behavior is the same as role privileges, but different from object privileges, such as SELECT, INSERT, or EXECUTE.

Ans: B.


3. You need to create a database-authenticated account named selena. This account should have the password welcome, and Selena should be required to change this password as soon as she connects. Which of the following SQL statements most completely meets these requirements?


A. create user selena password welcome expired;
B. create user selena identified by welcome expire;
C. create user selena identified by welcome expire password;
D. create user selena identified by welcome password expire;

----
You create a database-authenticated account with the CREATE USER statement. You assign the password with the IDENTIFIED BY clause and expire the password with the PASSWORD EXPIRE clause. When the password expires, the user will be required to change it on the next connection to the database.
Ans: D.


4. You have an account called sales that owns the tables for an application. You have created the tables and need to ensure that no one will be able to connect as this account. Which of the following SQL statements most completely meets these requirements?


A. alter user sales account lock;
B. alter user sales disable account;
C. alter user sales lock account;
D. alter account sales lock;
----
To lock an account, disabling logons for that account, you alter the account with the ACCOUNT LOCK option.
Ans: A.


5. Which of the following queries will include the privileges on another user's procedure that you have granted to a third party?


A. SELECT owner, proc_name, grantor, grantee FROM all_sql_privs;

B. SELECT owner, sql_name, grantor, grantee FROM all_sql_privs;

C. SELECT owner, table_name, grantor, grantee, privilege FROM all_tab_privs_made;

D. SELECT owner, sql_name, grantor, grantee FROM user_table_privs;
----
Ans C. All of the other data dictionary tables are fictitious.


6. You have a few developers who insist on connecting to the database as the well-known table-owning account HR, which is reserved for system testing. These developers need to periodically connect to the HR account to promote changes, but the corporate guidelines say that development should be done in each of the developer's personal accounts so they don't conflict with each other. The development manager has asked you to enable any database settings that might help discourage these developers from all connecting to the HR account at the same time. Which of the following options will best assist the development manager?


A. Give the development manager SELECT privileges on the V$SESSION table, so she can monitor her team's connection activity.
B. Lock the HR account and make the developers come to a DBA when they need to promote changes to system test.
C. Use a profile to limit the number of concurrent sessions for user HR to one.
D. Create an after logon trigger that causes the logon to fail if someone else is logged into the HR account.
----
This one is really tricky. All of the options would work technically. However, the development manager probably has better things to do than monitor who on her team is connecting as which user. Unless the corporate standards say a DBA must promote changes to system test, the DBA probably has better things to do than slow down the development efforts by getting involved in promotions to system test. The after logon trigger is a clever bit of engineering, but it actually does the same thing as the profile with added complexity, overhead, and maintenance.
Ans: C


7. Which of the following actions cannot be done with an ALTER USER statement?


A. Expire a password.
B. Enable DBA privileges.
C. Set the default tablespace for tables.
D. Set different default tablespaces for indexes and tables.
----
It would be nice, but Oracle does not (yet) let you set a default tablespace for indexes. DBA privileges can be enabled by default with an ALTER USER statement if the role was granted to the user previously and set to disabled.
Ans: D.


8. Which init.ora parameter will limit the number of concurrent session from non-DBA accounts to 16?


A. sessions=16
B. license_max_sessions =16
C. processes=16
D. max_concurrent_logons=16
-----
Option A is a hard limit that includes restricted session logons. The processes setting includes such non-logon processes as pmon, lgwr, and parallel I/O slaves. The max_concurrent_logons parameter is fictitious. When the number of logon sessions reaches license_max_sessions, only restricted session (DBA) logons are allowed.
Ans: B.


9. What cannot be done with a profile?


A. Limit the number of physical reads per session to 100,000.
B. Limit the number of logical reads per session to 1,000,000.
C. Limit passwords to expire after 90 days.
D. Limit the duration of each session to 9 hours.
----
You can limit a number of resources with a profile, but the number of physical reads can be dependent on how warm the cache is and cannot be limited via a profile.
Ans: A.


10. Which of the following assertions most correctly describes the privileges in force after the SQL below is executed?


connect system/manager
grant dba to arsal with admin option;
grant dba to gretchen;
connect arsal/troodon
grant dba to gretchen;
revoke dba from gretchen;

A. Gretchen can exercise DBA privileges.

B. Gretchen can grant DBA privileges to other accounts.

C. Arsal loses DBA privileges.

D. Gretchen loses DBA privileges.
----
Oracle does not retain the grantor on role privileges, so if anyone revokes a role privilege, that privilege is gone, even if the grantee obtained it from more than one grantor. This behavior is the same as system privileges, but different from object privileges, such as SELECT, INSERT, or EXECUTE.
Ans: D.


11. Which statement will configure the principle_user profile to lock any account after three failed logon attempts?


A. alter profile principle_user set failed_logon_attempts=3;

B. alter profile principle_user limit failed_logon_attempts 3;

C. alter principle_user profile set failed_logon_attempts=3;

D. alter profile principle_user lock account when failed_logon_attempts=3;

E. You can't limit failed logon attempts.

-----
B. Know the syntax for changing resource limits in a profile.
Ans: B.


12. Which of the following SQL statements will give user Nikki the privileges to assign SELECT authority on HR.EMPLOYEES to other user accounts?


A. grant select on hr.employees to nikki;

B. grant select on hr.employees to nikki with grant option;

C. grant select on hr.employees to nikki with admin option;

D. grant select on hr.employees to nikki cascade;
----
The WITH GRANT OPTION clause is used to give the grantee the ability to grant the privilege to other accounts. The WITH ADMIN OPTION does the same thing with system and role privileges.
Ans: B.


13. Which statement will set a five-minute limit to the maximum time that a user with the default profile can remain idle?


A. alter user default set profile max_idle_time=300;

B. alter profile default limit max_idle_time 300;

C. alter profile default limit idle_time 5;

D. alter profile default limit idle_time 300;

-----
The ALTER PROFILE statement is used to change a profile, and the idle_time parameter is set in minutes, not seconds.
Ans: C.


14. Which init.ora parameter will assist you in enforcing named user licensing, by limiting the number of user accounts that can be created in your database?


A. max_users
B. license_max_users
C. max_named_users
D. named_users_max
----
license_max_users can be used to limit the number of user accounts created. The other options are fictitious.
Ans: B.


15. Which of the following statements will give user Zachary the privilege to modify only the COMMENTS column in the CUSTOMER table?


A. grant update on customer(comments) to zachary;
B. grant update (comments) on customer to zachary;
C. grant update on customer.comments to zachary;
D. grant update on customer columns(comments) to zachary;
---
Any additional columns would appear as a comma-delimited list within the parentheses.
Ans: B.


16. Mary has granted INSERT WITH GRANT OPTION, UPDATE WITH GRANT OPTION, and DELETE WITH GRANT OPTION privileges on the CHART_OF_ACCOUNTS table to Charlie. Charlie is changing jobs and should not have the grant option. How can Mary leave the INSERT, UPDATE, and DELETE privileges, but remove the WITH GRANT OPTION? Mary also wants to ensure that whomever Charlie granted the privileges to will retain the privileges.


A. Grant the privileges on CHART_OF_ACCOUNTS without the grant option, and then revoke the privileges WITH GRANT OPTION.
B. Simply revoke the grant option.
C. Revoke the privileges, so that the grant option goes away, and then grant the privileges without the grant option.
D. Extract all the grants that Charlie made from the data dictionary, revoke the privileges on CHART_OF_ACCOUNTS, grant the privileges on CHART_OF_ACCOUNTS without the grant option, and regrant all the extracted privileges.

-----
There is no simple and easy way to remove the WITH GRANT OPTION while retaining the privilege. Revoking a privilege from someone will cascade through and revoke it from all grantees, so it would be crucial to first extract these privileges before revoking them.
Ans: D.


17. You need to report on all of the column privileges that you have made on your BONUS table. You must include the name of the account receiving the privilege, which column, and which privilege. Which of the following statements will accomplish this task?


A.
select grantor, table_name, column_name, privilege
from user_col_privs_recd
where table_name ='BONUS';

B.
select * from all_col_privs_made
where table_name='BONUS';

C.
select table_name, column_name, privilege, grantee
from user_col_privs_made
where table_name ='BONUS';

D.
select grantee, table_name, column_name, privilege
from all_tab_col_privs
where owner=user and table_name='BONUS';

----
The grantee is the recipient of the privilege. Every one of the ALL_DATA dictionary views contains not only the user's own objects, but also those that the user has access to, so ALL_COL_PRIVS_MADE may contain privileges on other schemas' tables. ALL_TAB_COL_PRIVS is not a valid data dictionary view.
Ans: C.


18. EMP is a table. Mary is a user. Sales_mgr is a role. Which one of the following statements will fail?


A. grant sales_mgr to mary with admin option;
B. grant read on emp to mary;
C. grant insert,update,delete on emp to mary with grant option;
D. grant reference on emp to mary;
---
The READ privilege is valid only on directories.
Ans: B.


19. Which of the following table privileges cannot be granted to a role (can only be granted to a user)?


A. INDEX
B. ALTER
C. REFERENCE
D. TRUNCATE
-----
TRUNCATE is not a table privilege. INDEX and ALTER can be granted to either a user or a role, but REFERENCE can be granted only to a user.
Ans: C.


20. If Judy grants ALL on her table FORMAT_CODES to PUBLIC, which operation will user Jerry not be able to perform without being granted other privileges?


A. create index on judy.format_codes
B. alter table judy.format_codes
C. delete table judy.format_codes
D. truncate table judy.format_codes
----
Ans: D. TRUNCATE is not a table privilege.

SQL Chap 9 - Other Database Objects

Chapter 9 - Other Database Objects

OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas

Review Questions


1. Which statement will create a sequence that starts with 0 and gets smaller one whole number at a time?


A. create sequence desc_seq start with 0 increment by -1 maxvalue 1;
B. create sequence desc_seq increment by -1;
C. create sequence desc_seq start with 0 increment by -1;
D. Sequences can only increase.
----
For a descending sequence, the default START WITH value is -1, and the default MAXVALUE value is 0. To start the sequence with 0, you must explicitly override both of these defaults.

Ans A.


2. Which statement is most correct in describing what happens to a synonym when the underlying object is dropped?


A. The synonym's status is changed to INVALID.
B. You can't drop the underlying object if a synonym exists unless the CASCADE clause is used in the DROP statement.
C. The synonym is automatically dropped with the underlying object.
D. Nothing happens to the synonym.

----
Synonyms do not have a status. The CASCADE CONSTRAINTS option does not drop synonyms. Synonyms can point to nonexisting objects.
Ans: D.


3. The built-in packaged procedure DBMS_APPLICATION_INFO.SET_MODULE has, in the package specification, the following declaration:


PROCEDURE DBMS_APPLICATION_INFO.SET_MODULE
(module_name IN VARCHAR2
,action_name IN VARCHAR2);

Which of the following statements will successfully call this procedure passing 'Monthly Load' and 'Rebuild Indexes' for the MODULE_NAME and ACTION_NAME, respectively? (Choose all that apply.)

A.
dbms_application_info('Monthly Load'
'Rebuild Indexes');

B.
dbms_application_info(
module_name=>'Monthly Load'
,action_name=>'Rebuild Indexes');

C.
dbms_application_info('Rebuild Indexes'
,'Monthly Load');

D.
dbms_application_info(
module_name->'Monthly Load'
,action_name->'Rebuild Indexes');

----
Option A almost uses the correct positional notation, except the delimiting comma is missing. Option B uses the correct named notational style. Option C transposes the module and action name using positional notation. Option D uses the wrong assignment syntax.
Ans: B.


4. With which of the following statements could you expect improved performance over a full-table scan, when a B-tree index is created on the two columns HIRE_DATE and SALARY in the HR.EMPLOYEES table?


A.
select max(salary)
from hr.employees
where hire_date < sysdate -90;

B.
select last_name, first_name
from hr.employees
where salary > 90000;

C.
update hr.employees
set salary = salary * 1.05
where department_id = 102;

D.
None of these statements would benefit from the index.
----
The index could be used if a leading subset of columns in the index is referenced. Options B and C do not reference the leading subset of columns in their WHERE clauses.
Ans: A


5. Which of the following statements will raise an exception?


A. alter sequence emp_seq nextval 23050;
B. alter sequence emp_seq nocycle;
C. alter sequence emp_seq increment by -5;
D. alter sequence emp_seq maxvalue 10000;
----
You cannot explicitly change the next value of a sequence. You can set the MAXVALUE or INCREMENT BY value to a negative number, and NOCYCLE tells Oracle to not reuse a sequence number.
Ans: A.


6. Rajiv has created a private synonym NEW_PRODUCTS for the MEG.PRODUCTS table. Who can select from RAJIV.NEW_PRODUCTS?


A. The users that Rajiv has granted SELECT on NEW_PRODUCTS to and Meg has granted SELECT on PRODUCTS to.
B. The users that Rajiv has granted SELECT on NEW_PRODUCTS to.
C. The users that Meg has granted SELECT on PRODUCTS to, even if Rajiv does not grant privileges to his synonym.
D. The users that Rajiv has granted SELECT on NEW_PRODUCTS to, if Meg has granted him SELECT WITH ADMIN OPTION.
----
Private synonyms can be referenced by anyone who has privileges on the underlying objects. You cannot grant privileges on synonyms, only on the underlying object. Option D is close, but the WITH ADMIN OPTION is only for roles and system privileges, not for table privileges.
Ans: C.


7. Which type of stored program must return a value?


A. PL/SQL procedure
B. PL/SQL function
C. Java trigger
D. Java procedure
-----
Functions must include a RETURN statement and must return a value.
Ans: B.


8. What does the following SQL statement enable all users in the database to do?


create public synonym plan_table
for system.plan_table;

A. Use the EXPLAIN PLAN feature of the database

B. Save execution plans in the system repository

C. Reference a table as PLAN_TABLE instead of SYSTEM.PLAN_TABLE

D. Turn on SQL tracing

---------
This statement creates a public synonym or global alias, which allows users to reference the underlying table without needing to explicitly specify the owner. A table named PLAN_TABLE is needed to use the EXPLAIN PLAN feature, but the statement above creates a public synonym. Also, the existence of a public synonym does not grant to public any privileges on the underlying object. An ALTER SESSION statement is used to enable and disable SQL tracing.
Ans: C


9. There is a public synonym named PLAN_TABLE for SYSTEM.PLAN_TABLE. Which of the following statements will remove this public synonym from the database?


A. drop table system.plan_table;
B. drop synonym plan_table;
C. drop table system.plan_table cascade;
D. drop public synonym plan_table;
----
To remove a public synonym, use the DROP PUBLIC SYNONYM statement. The DROP TABLE statement will remove a table from the database, but will not affect any synonyms on the table.

Ans: D.


10. A developer reports that she is receiving the following error:


SELECT key_seq.currval FROM dual;

ERROR at line 1:
ORA-08002: sequence KEY_SEQ.CURRVAL is not yet defined

Which of the following statements does the developer need to run to fix this condition?

A. create sequence key_seq;
B. create synonym key_seq;
C. select key_seq.nextval from dual;
D. grant create sequence to public;
----
A sequence is not yet defined if NEXTVAL has not yet been selected from it within the current session. It has nothing to do with creating a sequence, creating a synonym, or granting privileges.

Ans: C.


11. A power user is running some reports and has asked you to put two new B-tree indexes on a large table so that her reports will run faster. You acknowledge that the indexes would speed up her reports. Can the proposed indexes slow other processes? (Choose the best answer.)

A. No, indexes only speed up queries.
B. Yes, the indexes will make the optimizer take longer to decide the best execution plan.
C. Yes, DML will run more slowly.
D. Yes, table reorganization operations will be slower.
----
This one's a little tricky. B, C, and D are all true, but C is the best answer. Two additional indexes should not appreciably slow the optimizer, and table reorganization in Oracle (unlike in other databases) is usually not needed. DML (INSERT, UPDATE, and DELETE) operations will definitely be slowed, as the new indexes will need to be maintained.
Ans: C.


12. Bitmapped indexes are best suited for which type of environment?


A. High-cardinality columns

B. Online transaction processing (OLTP) applications

C. Full-table scan access

D. Low- to medium-cardinality columns
----
Bitmapped indexes are not suited for high-cardinality columns (those with highly selective data). OLTP applications tend to need row-level locking, which is not available with bitmap indexes. Full-table scans do not use indexes. Bitmap indexes are best suited for multiple combinations of low- to medium-cardinality columns.
Ans: D



13. The INSURED_AUTOS table has one index on the columns YEAR, MAKE, and MODEL, and one index on VIN. Which of the following SQL statements could not benefit from using these indexes?


A.
select vin from insured_autos
where make='Ford' and model = 'Taurus';

B.
select count(*) from insured_autos
where make='Ford' and year = 1998;

C.
select vin from insured_autos
where year = 1998 and owner = 'Dahlman';

D.
select min(year) from insured_autos
where make='Ford' and model = 'Taurus';
----
Option A does not use a leading subset of columns in an index, nor do all of the columns come from the index. A full-table scan on the table will be needed. Options B and C use a leading subset of the three-column index, so that index could be used. Option D uses data that is found completely in the three-column index, and a full scan of this index would likely be faster than a full scan of the larger table.
Ans: A.


14. Which clauses in a SELECT statement can an index be used for? (Choose all that apply.)


A. SELECT

B. FROM

C. WHERE

D. HAVING
----
The obvious answer is C, but an index also can be used for the SELECT clause. If an index contains all of the columns needed to satisfy the query, the table does not need to be accessed.
Ans: A, C.


15. You need to generate artificial keys for each row inserted into the PRODUCTS table. You want the first row to use a sequence value of 1000, and you want to make sure that no sequence value is skipped. Which of the following statements will meet these requirements?


A.
CREATE SEQUENCE product_key2
START WITH 1000
INCREMENT BY 1
NOCACHE;

B.
CREATE SEQUENCE product_key2
START WITH 1000
NOCACHE;

C.
CREATE SEQUENCE product_key2
START WITH 1000
NEXTVAL 1
NOCACHE;

D. Options A and B meet the requirements.

E. None of the above statements meet all of the requirements.
-----
Both options A and B produce identical results, because the INCREMENT BY 1 clause is the default if it is not specified. Option C is invalid because NEXTVAL is not a valid keyword within a CREATE SEQUENCE statement.
Ans: D.


16. Which statement will display the last number generated from the EMP_SEQ sequence?


A. select emp_seq.curr_val from dual;
B. select emp_seq.currval from dual;
C. select emp_seq.lastval from dual;
D. select last_number from all_sequences where sequence_name ='EMP_SEQ';
E. You cannot get the last sequence number generated.
-----
Option D is close, but it shows the greatest number in the cache, not the latest generated. The correct answer is from the sequence itself, using the pseudo-column CURRVAL.
Ans: B.


17. Which statement will create a sequence that will rotate through 100 values in a round-robin manner?


A. create sequence roundrobin cycle maxvalue 100;

B. create sequence roundrobin cycle to 100;

C. create sequence max_value 100 roundrobin cycle;

D. create rotating sequence roundrobin min 1 max 100;
----
The keyword CYCLE will cause the sequence to wrap and reuse numbers. The keyword MAXVALUE will set the largest value the sequence will cycle to. The name roundrobin is there to confuse to you.
Ans: A.


18. The following statements are executed:


create sequence my_seq;
select my_seq.nextval from dual;
select my_seq.nextval from dual;
rollback;
select my_seq.nextval from dual;

What will be selected when the last statement is executed?

A. 0
B. 1
C. 2
D. 3
----
The CREATE SEQUENCE statement will create an increasing sequence that will start with 1, increment by 1, and be unaffected by the rollback. A rollback will never stuff vales back into a sequence.
Ans: D.


19. Which of the following can you not do with a package?


A. Overload procedures and functions
B. Hide data
C. Retain data across commits
D. Grant EXECUTE privileges on one procedure in a package
----
You can only grant EXECUTE privileges on the entire package, not on individual packaged programs.
Ans: D.


20. Which of the following calls to the stored function my_sine() will raise an exception?

 
A. Theta := my_sine(45); 

B. IF (my_sine(45) > .3 ) THEN 

C. DECLARE
      Theta   NUMBER DEFAULT my_sine(45);
   BEGIN ... 

D. my_sine(45); 

----

Functions cannot be called as stand-alone statements; only procedures can be called this way
Ans: D.

SQL Chap 8 - Managing Views

Chapter 8 - Managing Views

OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas


Review Questions


1. A view created with which option makes sure that rows added to the base table through the view are accessible to the view?


A. WHERE
B. WITH READ ONLY
C. WITH CHECK OPTION
D. CREATE OR REPLACE VIEW
----
WITH CHECK OPTION makes sure that the new rows added or the rows updated are accessible to the view. The WHERE clause in the view definition limits the rows selected in the view from the base table.
Ans: C.


2. A view is created using the following code. What operations are permitted on the view?


CREATE VIEW USA_STATES
AS SELECT * FROM STATE
WHERE CNT_CODE = 1
WITH READ ONLY;

A. SELECT
B. SELECT, UPDATE
C. SELECT, DELETE
D. SELECT, INSERT

---
When the view is created with the READ ONLY option, only reads are allowed from the view.
Ans: A.


3. How do you remove the view USA_STATES from the schema?


A. ALTER VIEW USA_STATES REMOVE;
B. DROP VIEW USA_STATES;
C. DROP VIEW USA_STATES CASCADE;
D. DROP USA_STATES;

-----
A view is dropped using the DROP VIEW view_name; command.
Ans: B.


4. Which data dictionary view has information on the columns in a view that are updatable?


A. USER_VIEWS
B. USER_UPDATABLE_COLUMNS
C. USER_COLUMNS
D. USER_COLUMNS_UPDATABLE
-----
The USER_UPDATABLE_COLUMNS view shows the columns that can be updated.
Ans: B.


5. Which option in view creation creates a view even if there are syntax errors?


A. CREATE FORCE VIEW ...
B. CREATE OR REPLACE VIEW ...
C. CREATE OR REPLACE VIEW FORCE ...
D. CREATE VIEW ... IGNORE ERRORS

----
The CREATE FORCE VIEW statement creates an invalid view, even if there are syntax errors. Normally, a view will not be created if there are compilation errors.
Ans: A.


6. In a join view, on how many base tables can you perform a DML operation (UPDATE/INSERT/DELETE) in a single step?


A. One
B. The number of base tables in the view definition
C. The number of base tables minus one
D. None

----
You can perform an INSERT, UPDATE, or DELETE operation on the columns involving only one base table at a time. There are also some restrictions on the DML operations you perform on a join view.
Ans: A.


7. The following code is used to define a view. The EMP table does not have a primary key or any other constraints.


CREATE VIEW MYVIEW AS
SELECT DISTINCT ENAME, SALARY
FROM EMP
WHERE DEPT_ID = 10;

Which operations are allowed on the view?

A. SELECT, INSERT, UPDATE, DELETE
B. SELECT, UPDATE
C. SELECT, INSERT, DELETE
D. SELECT
E. SELECT, UPDATE, DELETE
----
Since the view definition includes a DISTINCT clause, only queries are allowed on the view.
Ans: D.


8. Which two statements are used to modify a view definition?


A. ALTER VIEW
B. CREATE OR REPLACE VIEW
C. REPLACE VIEW
D. CREATE FORCE VIEW
E. CREATE OR REPLACE FORCE VIEW
-----
The OR REPLACE option in the CREATE VIEW statement is used to modify the definition of the view. The FORCE option can be used to create the view with errors. The ALTER VIEW statement is used to compile a view or to add or modify constraints on the view.
Ans: B, E.


9. You create a view based on the EMPLOYEES table using the following SQL.


CREATE VIEW MYVIEW AS SELECT * FROM EMPLOYEES;

You modify the table to add a column named EMP_SSN. What do you need to do to have this new column appear in the view?

A. Nothing, since the view definition is selecting all columns, the new column will appear in the view automatically.
B. Recompile the view using ALTER VIEW MYVIEW RECOMPILE.
C. Re-create the view using CREATE OR REPLACE VIEW.
D. Add the column to the view using ALTER VIEW MYVIEW ADD EMP_SSN.
----
When you modify the base table, the view becomes invalid. Recompiling the view will make it valid, but the new column will not be available in the view. This is because when you create the view using *, Oracle expands the column names and stores the column names in the dictionary.
Ans: C


10. You can view the constraints on the objects in your schema in the USER_CONSTRAINTS dictionary view. The CONSTRAINT_TYPE column shows the type of constraint. What is the type of constraint created when you create a view with the WITH CHECK OPTION clause?


A. R
B. C
C. V
D. F

----
The constraint type will be V for the constraints created on views with the WITH CHECK OPTION clause.
Ans: C.


11. Which types of constraints can be created on a view?


A. Check, NOT NULL
B. Primary key, foreign key, unique key
C. Check, NOT NULL, primary key, foreign key, unique key
D. No constraints can be created on a view.

----
You can create primary key, foreign key, and unique key constraints on a view. The constraints on views are not enforced by Oracle. To enforce a constraint it must be defined on a table.

Ans: B.


12. Which is a valid status of a constraint created on a view?


A. DISABLE VALIDATE
B. DISABLE NOVALIDATE
C. ENABLE NOVALIDATE
D. All of the above
----
Since the constraints on the view are not enforced by Oracle, the only valid status of a constraint can be DISABLE NOVALIDATE. You must specify this status when creating constraints on a view.
Ans: B.


13. The SALARY column of the EMPLOYEE table is defined as NUMBER (8,2), and the COMMISSION_PCT column is defined as NUMBER(2,2). A view is created with the following code.


CREATE VIEW EMP_COMM AS
SELECT LAST_NAME,
SALARY * NVL(COMMISSION_PCT,0) Commission
FROM EMPLOYEES;

What is the datatype of the COMMISSION column in the view?

A. NUMBER (8,2)
B. NUMBER (10,2)
C. NUMBER
D. FLOAT
----
When numeric operations are performed using numeric datatypes in the view definition, the resulting column will be a floating datatype, which is NUMBER without any precision or scale.
Ans: C.


14. Which clause in the SELECT statement is not supported in a view definition subquery?


A. GROUP BY
B. HAVING
C. CUBE
D. FOR UPDATE OF
E. ORDER BY
-----
The FOR UPDATE OF clause is not supported in the view definition. The FOR UPDATE clause locks the rows, so it is not allowed.
Ans: D.


15. The EMPLOYEE table has the following columns:


EMP_ID NUMBER (4)
EMP_NAME VARCHAR2 (30)
SALARY NUMBER (5,2)
DEPT_ID VARCHAR2 (2)

Which query will show the top-five highest paid employees?

A. 
SELECT * FROM
(SELECT EMP_NAME, SALARY
 FROM   EMPLOYEES
 ORDER BY SALARY ASC)
WHERE ROWNUM <= 5;

B. 
SELECT EMP_NAME, SALARY FROM
(SELECT *
 FROM   EMPLOYEES
 ORDER BY SALARY DESC)
WHERE ROWNUM < 5;

C. 
SELECT * FROM
(SELECT EMP_NAME, SALARY
 FROM   EMPLOYEES
 ORDER BY SALARY DESC)
WHERE ROWNUM <= 5;

D. 
SELECT EMP_NAME, SALARY
(SELECT *
 FROM   EMPLOYEES
 ORDER BY SALARY DESC)
WHERE ROWNUM = 5;

----
The top five salaries can be found using an inline view with the ORDER BY clause. Oracle9i optimizer understands the top-'n' rows query.
Ans: C.


16. The EMPLOYEE table has the following columns:


EMP_ID NUMBER (4) PRIMARY KEY
EMP_NAME VARCHAR2 (30)
SALARY NUMBER (5,2)
DEPT_ID VARCHAR2 (2)

A view is defined using the following SQL.

CREATE VIEW EMP_IN_DEPT10 AS
SELECT * FROM EMPLOYEE
WHERE DEPT_ID = 'HR';

Which INSERT statement will succeed through the view?

A.
INSERT INTO EMP_IN_DEPT10 VALUES (1000,
'JOHN',1500,'HR');

B.
INSERT INTO EMP_IN_DEPT10 VALUES (1001,
NULL,1700,'AM');

C.
INSERT INTO EMP_IN_DEPT10 VALUES (1002,
'BILL',2500,'AC');

D.
All of the above
----
The view is based on a single table and the only constraint on the table is the primary key. Although the view defined with a WHERE clause, we have not enforced that check while using DML statements through the WITH CHECK OPTION clause.
Ans: D.


17. To be able to modify a join view, the view definition should not contain which of the following in the top-level query? (Choose all that apply.)


A. DISTINCT operator
B. ORDER BY clause
C. Aggregate functions such as SUM, AVG, and COUNT
D. WHERE clause
E. GROUP BY clause
F. ROWNUM pseudo-column
----
To be able to update a base table using the view, the view definition should not have a DISTINCT clause, GROUP BY clause, START WITH clause, CONNECT BY clause, ROWNUM, set operators (UNION, UNION ALL, INTERSECT, or MINUS), or subquery in the SELECT clause.
Ans: A, C, E, F


18. What is an inline view?


A. A subquery appearing in the WHERE clause
B. A subquery appearing in the FROM clause
C. A view created using the same column names of the base table
D. A view created with an ORDER BY clause
-----
A subquery appearing in the FROM clause of the SELECT statement is similar to defining and using a view, hence the name inline view. The subquery in the FROM clause is enclosed in parentheses and may be given an alias name. The columns selected in the subquery can be referenced in the parent query, just as you would select from any normal table or view.
Ans: B.


19. Which of the following two statements are true?


A. A view can be created before creating the base table.
B. A view cannot be created before creating the base table.
C. A view will become invalid if the base table's column referred to in the view is altered.
D. A view will become invalid if any column in the base table is altered.
----
The CREATE FORCE VIEW statement can be used to create a view before its base table is created. Any modification to the table will invalidate the view. Use the ALTER VIEW COMPILE statement to recompile the view.
Ans: A, D.


20. Which pseudo-column (with an inline view) can be used to get the top-n rows from a table?


A. ROWID
B. ROW_ID
C. ROWNUM
D. ROW_NUM
----
The ROWNUM pseudo-column gives a record number for each row returned. The row number is assigned as the record is fetched; the number is not stored in the database.

Ans: C.

SQL Chap 7 - Managing Tables and Constraints

Chapter 7 - Managing Tables and Constraints


OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas

Review Questions


1. The STATE table has the following constraints (the constraint status is shown in parentheses):

Primary key           pk_state (enabled)
Foreign key           COUNTRY table-fk_state (enabled)
Check constraint      ck_cnt_code (disabled)
Check constraint      ck_st_code (enabled)
Not null constraint   nn_st_name (enabled)

You execute the following SQL:
CREATE TABLE STATE_NEW AS SELECT * FROM STATE;
How many constraints will there be in the new table?

A. 0
B. 1
C. 3
D. 5
E. 2

----
When you create a table using CTAS (CREATE TABLE AS), only the NOT NULL constraints are copied.
Ans: B.


2. Which line of code has an error?


1 CREATE TABLE FRUITS_VEGETABLES
2 (FRUIT_TYPE VARCHAR2,
3 FRUIT_NAME CHAR (20),
4 QUANTITY NUMBER);

A. 1
B. 2
C. 3
D. 4

---
A VARCHAR2 datatype should always specify the maximum length of the column.
Ans: B.


3. Which statement successfully adds a new column ORDER_DATE to the table ORDERS?


A. ALTER TABLE ORDERS ADD COLUMN ORDER_DATE DATE;
B. ALTER TABLE ORDERS ADD ORDER_DATE (DATE);
C. ALTER TABLE ORDERS ADD ORDER_DATE DATE;
D. ALTER TABLE ORDERS NEW COLUMN ORDER_DATE TYPE DATE;

----
The correct statement is C. When adding only one column, the column definition need not be enclosed in parentheses.
Ans: C


4. What are the special characters allowed in a table name? (Choose two answers.)


A. &
B. #
C. @
D. $

------
Only three special characters ($, _, and #) are allowed in the table names along with letters and numbers.
Ans: B, D.


5. Consider the following statement:

CREATE TABLE MY_TABLE (
1ST_COLUMN NUMBER,
2ND_COLUMN VARCHAR2 (20));

Which of the following best describes this statement?

A. Tables cannot be created without a defining a primary key. The table definition here is missing the primary key.
B. The reserved word COLUMN cannot be part of the column name.
C. The column names are invalid.
D. There is no maximum length specified for the first column definition. You must always specify a length for character and numeric columns.
E. There is no error in the statement.

----
All identifiers (column names, table names, and so on) must begin with an alphabetic character. An identifier can contain alphabetic characters, numbers, and the special characters $, #, and _.
Ans: C


6. Which dictionary view would you query to list only the tables you own?


A. ALL_TABLES
B. DBA_TABLES
C. USER_TABLES
D. USR_TABLES
----
The USER_TABLES view provides information on the tables owned by the user who has logged on that session. DBA_TABLES will have all the tables in the database, and ALL_TABLES will have the tables owned by you as well as the tables to which you have access. USR_TABLES is not a valid dictionary view.
Ans: C.


7. The STATE table has six rows. You issue the following command:


ALTER TABLE STATE ADD UPDATE_DT DATE DEFAULT SYSDATE;

Which of the following is correct?

A. A new column, UPDATE_DT, is added to the STATE table, and its contents for the existing rows are NULL.
B. Since the table is not empty, you cannot add a new column.
C. The DEFAULT value cannot be provided if the table has rows.
D. A new column, UPDATE_DT, is added to STATE and is populated with the current system date and time.
----

When a default value is specified in the new column added, the column values for the existing rows are populated with the default value.
Ans: D.


8. The HIRING table has the following data:


EMPNO HIREDATE
--------- ----------
1021 12-DEC-00
3400 24-JAN-01
2398 30-JUN-01

What will be result of the following query?

SELECT hiredate+1 FROM hiring WHERE empno = 3400;

A. 4-FEB-01
B. 25-JAN-01
C. N-02
D. None of the above
----
In date arithmetic, adding 1 is equivalent to adding 24 hours. To add 6 hours to a date value with time, add 0.25.
Ans: B


9. What is the default length of a CHAR datatype column, if no length is specified in the table definition?


A. 256
B. 1000
C. 64
D. 1
E. You must always specify a length for CHAR columns.
-----
If you do not specify a length for a CHAR datatype column, the default length of 1 is assumed.
Ans: D


10. Which statement will remove the column UPDATE_DT from table STATE?


A. ALTER TABLE STATE DROP COLUMN UPDATE_DT;
B. ALTER TABLE STATE REMOVE COLUMN UPDATE_DT;
C. DROP COLUMN UPDATE_DT FROM STATE;
D. ALTER TABLE STATE SET UNUSED COLUMN UPDATE_DT;
E. You cannot drop a column from the table.
----
You can use the DROP COLUMN clause with the ALTER TABLE statement to drop a column. There is no separate DROP COLUMN statement or a REMOVE clause in the ALTER TABLE statement. The SET UNUSED clause is used to mark the column as unused. This column can be dropped later using the DROP UNUSED COLUMNS clause.
Ans: A


11. Which option is not available in Oracle when modifying tables?


A. Add new columns
B. Rename existing column
C. Drop existing column
D. All of the above
----
You cannot rename an existing column using the ALTER TABLE statement. To rename the column, you must re-create the table with the new name.
Ans: B.


12. Which one of the following statements will create a primary key for the CITY table with columns STATE_CD and CITY_CD?


A. CREATE PRIMARY KEY ON CITY (STATE_CD, CITY_CD);
B. CREATE CONSTRAINT PK_CITY PRIMARY KEY ON CITY (STATE_CD, CITY_CD);
C. ALTER TABLE CITY ADD CONSTRAINT PK_CITY PRIMARY KEY (STATE_CD, CITY_CD);
D. ALTER TABLE CITY ADD PRIMARY KEY (STATE_CD, CITY_CD);
E. ALTER TABLE CITY ADD PRIMARY KEY CONSTRAINT PK_CITY ON (STATE_CD, CITY_CD);
---
The ALTER TABLE statement is used to create and remove constraints. Option D would work if the keyword CONSTRAINT were included between ADD and PRIMARY.
Ans: C


13. Which of the following check constraints will raise an error? (Choose all that apply.)

A. CONSTRAINT ck_gender CHECK (gender IN ('M', 'F'))
B. CONSTRAINT ck_old_order CHECK (order_date > (SYSDATE - 30))
C. CONSTRAINT ck_vendor CHECK (vendor_id IN (SELECT vendor_id FROM vendors))
D. CONSTRAINT ck_profit CHECK (gross_amt > net_amt)
----
Ans: B, C. Check constraints cannot reference the SYSDATE function or other tables.


14. Consider the datatypes DATE, TIMESTAMP (TS), TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ), INTERVAL YEAR TO MONTH (IY2M), INTERVAL DAY TO SECOND (ID2S). Which operations are not allowed by the Oracle9i database? (Choose all that apply.)


A. DATE + DATE
B. TSLTZ - DATE
C. TSLTZ + IY2M
D. TS * 5
E. ID2S / 2
F. IY2M + IY2M
G. ID2S + IY2M
H. DATE - IY2M

----
You cannot add two DATE datatypes, but you can subtract to find the difference in days. Multiplication and division operators are permitted only on INTERVAL datatypes. When adding or subtracting INTERVAL datatypes, both INTERVAL datatypes should be of the same category.
Ans: A, D, G.


15. A constraint is created with the DEFERRABLE INITIALLY IMMEDIATE clause. What does this mean?


A. Constraint checking is done only at commit time.
B. Constraint checking is done after each SQL statement is executed, but you can change this behavior by specifying SET CONSTRAINTS ALL DEFERRED.
C. Existing rows in the table are immediately checked for constraint violation.
D. The constraint is immediately checked in a DML operation, but subsequent constraint verification is done at commit time.
---
DEFERRABLE specifies that the constraint can be deferred using the SET CONSTRAINTS command. INITIALLY IMMEDIATE specifies that the constraint's default behavior is to validate the constraint for each SQL statement executed.
Ans: B.


16. What is the default precision for fractional seconds in a TIMESTAMP datatype column?


A. 0
B. 2
C. 6
D. 9
----
Ans: C. The default precision is 6 digits. The precision can range from 0 to 9.


17. Which datatype stores the time zone information along with the date value?


A. TIMESTAMP
B. TIMESTAMP WITH LOCAL TIME ZONE
C. TIMESTAMP WITH TIME ZONE
D. DATE
E. Both options B and C
-----
Only TIMESTAMP WITH TIME ZONE stores the time zone information as a displacement from UTC. TIMESTAMP WITH LOCAL TIME ZONE adjusts the time to database's time zone before storing it.
Ans: C.


18. You have a large job that will load many thousands of rows into your ORDERS table. To speed up the loading process, you want to temporarily stop enforcing the foreign key constraint FK_ORDERS. Which of the following statements will satisfy your requirement?


A. ALTER CONSTRAINT FK_ORDERS DISABLE;
B. ALTER TABLE ORDERS DISABLE FOREIGN KEY FK_ORDERS;
C. ALTER TABLE ORDERS DISABLE CONSTRAINT FK_ORDERS;
D. ALTER TABLE ORDERS DISABLE ALL CONSTRAINTS;
----
You can disable constraints by specifying its constraint name. You may enable the constraint after the load and avoid the constraint checking while enabling using the ALTER TABLE ORDERS MODIFY CONSTRAINT FK_ORDERS ENABLE NOVALIDATE; command.
Ans: C


19. You are connected to the database as user JOHN. You need to rename a table named NORDERS to NEW_ORDERS, owned by SMITH. Consider the following two statements:


1. RENAME SMITH.NORDERS TO NEW_ORDERS;
2. ALTER TABLE SMITH.NORDERS RENAME TO NEW_ORDERS;

Which of the following is correct?

A. Statement 1 will work; statement 2 will not.
B. Statements 1 and 2 will work.
C. Statement 1 will not work; statement 2 will work.
D. Statements 1 and 2 will not work

----
RENAME can be used to rename objects owned the user. ALTER TABLE should be used to rename tables owned by another user. To do so, you must have the ALTER privilege on the table or the ALTER ANY TABLE privilege.
Ans: C.


20. Which two declarations define the maximum length of a CHAR datatype column in bytes?


A. CHAR (20)
B. CHAR (20) BYTE
C. CHAR (20 BYTE)
D. BYTE (20 CHAR)
E. CHAR BYTE (20)
---
Ans: A, C. The maximum lengths of CHAR and VARCHAR2 columns can be defined in characters or bytes. BYTE is the default.

SQL Chap 6 - Modifying Data

Chapter 6 - Modifying Data

OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas

Review Questions


1. Which of the following statements will succeed?

(1)
merge into product_descriptions p
using (select product_id, language_id
             ,translated_name
from products_for_2003) p2003
where (p.product_id = p2003.product_id)
when matched then update
set p.language=p2003.language_id
   ,p.translated_name = p2003.translated_name
when not matched then insert
   (p.product_id, p.language_id
    ,p.translated_name)
   values (p2003.product_id,p2003.language_id
          ,p2003.translated_name);

(2)
merge into product_descriptions p
using (select product_id, language_id
             ,translated_name
from products_for_2003) p2003
on (p.product_id = p2003.product_id)
when matched then update
set p.language=p2003.language_id
   ,p.translated_name = p2003.translated_name
when not matched then insert
   (p.product_id, p.language_id
    ,p.translated_name)
   values (p2003.product_id,p2003.language_id
          ,p2003.translated_name);
(3)
merge into product_descriptions p
using (select product_id, language_id
             ,translated_name
from products_for_2003) p2003
join on (p.product_id = p2003.product_id)
when matched then update
set p.language=p2003.language_id
   ,p.translated_name = p2003.translated_name
when not matched then insert
   (p.product_id, p.language_id
    ,p.translated_name)
   values (p2003.product_id,p2003.language_id
          ,p2003.translated_name);

A. Statement 1
B. Statement 2
C. Statement 3
D. They all fail.

----
The correct syntax uses an ON clause as in option B. The WHERE in option A and the JOIN ON clause in option C are not valid.
Ans: B.


2. Which of the following statements will not implicitly begin a transaction?


A. INSERT
B. UPDATE
C. DELETE
D. SELECT FOR UPDATE
E. None of the above; they all implicitly begin a transaction.

----
If a transaction is not currently open, any INSERT, UPDATE, MERGE, DELETE, SELECT FOR UPDATE, or LOCK statement will implicitly begin a transaction.
Ans: E.


3. If Julio executes a LOCK TABLE IN SHARE ROW EXCLUSIVE MODE statement, with which of the following statements will Marisa not wait for Julio's commit or rollback?


A. INSERT
B. SELECT FOR UPDATE
C. LOCK TABLE IN SHARE MODE
D. LOCK TABLE IN EXCLUSIVE MODE
E. None of the above; all will wait.

---
The row share exclusive mode will block other share, exclusive, and row exclusive locks, but not row share locks.
Ans: B


4. Which of the following statements does not end a transaction?


A. LOCK TABLE IN EXCLUSIVE MODE
B. COMMIT
C. ALTER USER
D. CREATE INDEX

----
COMMIT, ROLLBACK, and any DDL statement ends a transaction. DDL is automatically committed. LOCK TABLE is DML, like INSERT, UPDATE, DELETE, or MERGE, and requires a commit or rollback.
Ans: A.


5. Choose the maximum number of tables into which rows can be inserted via a single INSERT statement.

A. 1
B. 2
C. No more than 16
D. Unlimited

----
A single INSERT statement can insert data into an unlimited number of tables. This multiple-table insert capability is new in Oracle9i.
Ans: D.


6. Can you execute an ALTER INDEX REBUILD while there are uncommitted updates on a table?


A. No, it will always fail with a resource busy error.
B. Yes, but you must specify the keyword WAIT to wait for the commit or rollback.
C. Yes, the row exclusive locks from the UPDATE statements only block other changes to the same rows.
D. Yes, but only if the updates do not change the indexed columns.

----
The row exclusive locks from the update will block all DDL, including DDL on the indexes—it does not matter which columns the index is on. You cannot specify WAIT on DDL.
Ans: A


7. Which of the following statements will begin a transaction using transaction-level read consistency?

A. ALTER SESSION USE TRANSACTION CONSISTENCY;
B. BEGIN TRANSACTION USING TRANSACTION CONSISTENCY;
C. BEGIN SERIALIZABLE TRANSACTION;
D. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

----
Transaction-level consistency is obtained with a serializable isolation level. An isolation level of read committed identifies statement-level read consistency.
Ans: D.


8. Which of the following statements will improve the performance of a full-table scan on the PROCESS_ORDER_STAGE table?

A. DELETE FROM process_order_stages;
B. TRUNCATE TABLE process_order_stage;
C. CREATE INDEX ord_idx2 ON process_order_stage (customer_id);
D. ALTER SESSION SET hash_area_size 16613376;

-----
A TRUNCATE operation will reset the high-water mark on a table, so when a full-table scan (that scans to the high-water mark) is executed against the table, it will run very fast. Delete operations do not affect the high-water mark or full-scan performance. Indexes and hash_area_size do not affect full-scan performance.

Ans: B.


9. The following table shows two concurrent transactions. What happens at time point 9?

-------------------------------------------------
Session A               Time         Session B 
 

UPDATE customers SET     6
region='H' WHERE
state='43' and
county='046';            
   
                         7          UPDATE customers
                                    SET mgr=4567
                                    WHERE state='47' and
                                    county='072';

UPDATE customers SET     8
region='H' WHERE
state='47' and
county='072';

   
                         9          UPDATE customers
                                    SET mgr=4567
                                    WHERE state='43' and
                                    county='046';

------------------------------------------------------------

A. Session B will wait for session A to commit or roll back.
B. Session A will wait for session B to commit or roll back.
C. A deadlock will occur, and both sessions will hang until the DBA kills one or until one of the users cancels their statement.
D. A deadlock will occur, and Oracle will cancel one of the statements.
E. Both sessions are not updating the same column, so no waiting or deadlocks will occur.

-----
At time point 8, session A will wait for session B. At time point 9, a deadlock will occur; Oracle will recognize it and cancel one of the statements. Oracle locks to the granularity of a row, so even though the columns are different, the locks will still block each other.

Ans: D


10. The following table shows two concurrent transactions. Which statement about the result returned in session A at time point 16 is most true?

-------------------------------------------------------------------
Session A                       Time              Session B 
-------------------------------------------------------------------
SELECT SUM(deposit_amt)         12
FROM transaction_log
WHERE deposit_date >
TRUNC(SYSDATE);           
   
                                13     INSERT INTO transaction_log
                                       (deposit_date, deposit_amt)
                                       VALUES (SYSDATE, 6247.00);

                                14     COMMIT; 
 
Table scan for the active       15
SELECT reaches the data 
block where session B's row 
was inserted.

Table scan complete results     16
returned
---------------------------------------------------------------------

A. The results would include the changes committed by transaction B at time point 14.

B. The results would not include the changes committed by transaction B at time point 14.

C. The results would include the changes committed by transaction B at time point 14 if the two sessions were connected to the database as the same user.

D. Session A would raise a "snapshot too old" exception.
----------
Statement-level read consistency would ensure that the data visible to each statement does not change while the statement is executing. The "snapshot too old" exception might be raised if there were a lot of other transactions committing to the database between time points 12 and 16, but if this exception were raised, the table scan would neither complete nor return results.
Ans: B.


11. The following table shows two concurrent transactions. Which statement about the results returned in session A at time points 16 and 18 is most true?

-----------------------------------------------------------------------
Session A                      Time                     Session B 
------------------------------------------------------------------------
SET TRANSACTION ISOLATION      11
LEVEL READ CONSISTENT;


SELECT SUM(deposit_amt)        12
FROM transaction_log
WHERE deposit_date >
TRUNC(SYSDATE);

   
                               13           INSERT INTO transaction_log
                                            (deposit_date, deposit_amt)
                                            VALUES (SYSDATE, 6247.00);


                               14             COMMIT; 
 
Table scan for the active      15
SELECT reaches the data block 
where session B's row was 
inserted.
   
Table scan complete,           16
results returned.
   

SELECT SUM(deposit_amt)        17
FROM transaction_log
WHERE deposit_date >
TRUNC(SYSDATE);

   
Table scan complete,           18 
results returned.
--------------------------------------------------------------------------

A. The results would be identical.
B. The results would be different.
C. The results would be identical only if the two sessions were connected to the database as the same user.
D. Both statements would include the data committed by transaction B at time point 14.

-------
The read-consistent isolation level is statement-level read consistency, so each statement sees the committed data that existed at the beginning of the statement. The committed data at time point 17 includes session B's commit at time point 14.
Ans: B.


12. The following table shows two concurrent transactions. Which statement about the results returned in session A at time point 16 and 18 is most true?

 
----------------------------------------------------------------------------
Session A                        Time                     Session B 
----------------------------------------------------------------------------
SET TRANSACTION ISOLATION        11
LEVEL SERIALIZABLE;


SELECT SUM(deposit_amt)          12
FROM transaction_log
WHERE deposit_date >
TRUNC(SYSDATE);

                                 13           INSERT INTO transaction_log
                                              (deposit_date, deposit_amt)
                                              VALUES (SYSDATE, 6247.00);

                                 14           COMMIT; 
 
Table scan for the active        15 
SELECT reaches the data block
where session B's row was 
inserted.

   
Table scan complete results      16
returned.
   
SELECT SUM(deposit_amt)          17
FROM transaction_log
WHERE deposit_date >
TRUNC(SYSDATE);

   
Table scan complete results      18
returned.
---------------------------------------------------------------------------

A. The results would be identical.
B. The results would be different.
C. The results would be identical only if the two sessions were connected to the database as the same user.
D. Both statements would include the data committed by transaction B at time point 14.

---
The serializable isolation level is transaction-level read-consistency, so both of session A's SELECT statements see the same data image. Neither would include the changes committed at time point 14.
Ans: A.


13. You have a DELETE statement that will generate a large amount of undo. One rollback segment, named RB_LARGE, is larger than the others. How would you force the use of this rollback segment for the DELETE operation?


A. ALTER SESSION USE ROLLBACK SEGMENT rb_large;
B. SET TRANSACTION USE ROLLBACK SEGMENT rb_large;
C. BEGIN WORK USING ROLLBACK SEGMENT rb_large
D. You cannot force the use of a specific rollback segment.

----
The SET TRANSACTION statement can be used to force the use of a specific rollback segment, provided that the SET TRANSACTION statement begins the transaction.
Ans: B.


14. The following table describes the DEPARTMENTS table.

--------------------------------------------------------------
Column Name    dept_id   dept_name   mgr_id   location_id
--------------------------------------------------------------
Key Type       pk
NULLs/Unique   NN
FK Table 
Datatype       NUMBER    VARCHAR2    NUMBER   NUMBER
Length         4         30          6        4
Default Value  None      None        None     None
------------------------------------------------------------- 

Which of the following INSERT statements will raise an exception?

A.
INSERT INTO departments (dept_id, dept_name, location_
id) VALUES(280,'Security',1700);

B.
INSERT INTO departments
VALUES(280,'Security',1700);

C.
INSERT INTO departments
VALUES(280,'Corporate Giving',266,1700);

D.
None of these statements will raise an exception.

----
Option B will raise an exception because there are not enough column values for the implicit column list (all columns).

Ans: B


15. The SALES table contains the following data:


SELECT channel_id, COUNT(*)
FROM sales
WHERE channel_id IN ('T','I')
GROUP BY channel_id;

C COUNT(*)
- ----------
T 12000
I 24000

How many rows will be inserted into the NEW_CHANNEL_SALES table with the following SQL statement?

INSERT FIRST
 WHEN channel_id ='C' THEN
   INTO catalog_sales (prod_id,time_id,promo_id
                       ,amount_sold)
       VALUES (prod_id,time_id,promo_id,amount_sold)
 WHEN channel_id ='I' THEN
   INTO internet_sales (prod_id,time_id,promo_id
                        ,amount_sold)
       VALUES (prod_id,time_id,promo_id,amount_sold)
 WHEN  channel_id IN ('I','T') THEN
   INTO new_channel_sales (prod_id,time_id,promo_id
                           ,amount_sold)
       VALUES (prod_id,time_id,promo_id,amount_sold)
SELECT channel_id,prod_id,time_id,promo_id,amount_sold
FROM sales;

A. 0
B. 12,000
C. 24,000
D. 36,000

-----
The FIRST clause tells Oracle to execute only the first WHEN clause that evaluates to TRUE. This statement will insert 24,000 rows into the INTERNET_SALES table and 0 rows into the NEW_CHANNEL_ SALES table. If the ALL clause were used, 36,000 rows would be inserted into the NEW_CHANNEL_SALES table.
Ans: A.


16. How many rows will be counted in the last SQL statement that follows?

SELECT COUNT(*) FROM emp;
  120 returned

INSERT INTO emp (emp_id)
   VALUES (140);
SAVEPOINT emp140;

INSERT INTO emp (emp_id)
   VALUES (141);
INSERT INTO emp (emp_id)
   VALUES (142);
INSERT INTO emp (emp_id)
   VALUES (143);
TRUNCATE TABLE emp;
INSERT INTO emp (emp_id)
   VALUES (144);

ROLLBACK;

SELECT COUNT(*) FROM emp;

A. 121
B. 1
C. 0
D. 143
----
The TRUNCATE statement is DDL and performs an implicit commit. After the TRUNCATE statement, there are 0 rows in the table. The one row that was inserted was removed when the ROLLBACK statement was executed.
Ans: C


17. Which of the following statements will raise an exception in a transaction that starts with SET TRANSACTION READ ONLY?


A. ALTER SYSTEM
B. SELECT
C. ALTER USER
D. SET ROLE
---
A read-only transaction will raise an exception if data is changed. Altering a user will change data.
Ans: C


18. Which of the following statements will raise an exception?


A. LOCK TABLE SALES IN EXCLUSIVE MODE;

B. LOCK TABLE SALES IN ROW SHARE EXCLUSIVE MODE;

C. LOCK TABLE SALES IN SHARE ROW EXCLUSIVE MODE;

D. LOCK TABLE SALES IN ROW EXCLUSIVE MODE;
---
There are five types of table locks: row share, row exclusive, share, share row exclusive, and exclusive. Row share exclusive mode does not exist.
Ans: B.


19. Which of the following INSERT statements will raise an exception?


A.
INSERT INTO EMP SELECT * FROM NEW_EMP;

B.
INSERT FIRST WHEN DEPT_NO IN (12,14) THEN INSERT INTO
EMP SELECT * FROM NEW_EMP;

C.
INSERT FIRST WHEN DEPT_NO IN (12,14) THEN INTO EMP
SELECT * FROM NEW_EMP;

D.
INSERT INTO ALL WHEN DEPT_NO IN (12,14) THEN INTO EMP
SELECT * FROM NEW_EMP;
----
The keywords INSERT INTO are required in single-table INSERT statements, but are not valid in multiple-table INSERT statements.
Ans: B.


20. What will the salary of employee Arsinoe be at the completion of the following SQL statements?

UPDATE emp
  SET salary = 1000
  WHERE name = 'Arsinoe';
SAVEPOINT Point_A

UPDATE emp
  SET salary = salary * 1.1
  WHERE name = 'Arsinoe';
SAVEPOINT Point_B;

UPDATE emp
  SET salary = salary * 1.1
  WHERE name = 'Berenike';
SAVEPOINT point_C;

ROLLBACK TO SAVEPOINT point_b;
COMMIT;

UPDATE emp
  SET salary = 1500
  WHERE name = 'Arsinoe';
SAVEPOINT point_d;

ROLLBACK TO point_d;

COMMIT;

A. 1000
B. 1100
C. 1111
D. 1500
----
The final rollback (to point_d) will roll the changes back to just after setting the salary to 1500.
Ans: D.