Re: DBA Prep -- Sample question

From: Michael E Corum <mecoru_at_ccmail.monsanto.com>
Date: 1996/03/15
Message-ID: <4icm2s$id1_at_tin.monsanto.com>


In article <4ic817$hem_at_alpine.valleynet.com> jared_at_valleynet.com (Jared Still) wrote:

> jwingram_at_whale.st.usm.edu (Jonathan Wayne Ingram) wrote:
> 

>>: QUESTION:
>>: A BEFORE INSERT trigger is created to do validation on a column created
>>: as not NULL. The purpose of the trigger is to check the contains (sic) of
>>: the column and display a message "Column cannot be NULL" if the column is
>>: NULL. What will happen? (May have multiple answers)
 

>>: A. The trigger will be fired and it will check value of the colmn and
>>: return a message when the column is NULL.
>>: B. The trigger will never be fired even when the column is NULL.
>>: C. The message "Column cannot be NULL" will never be displayed.
>>: D. The trigger is always fired whenever data is inserted into thie table.
 

>>Steve,
>>I am going to go out on a limb here and say that A is *incorrect*, because
>>Oracle will parse the insert statement before the table is actually updated.
>>B is obviously incorrect because if the statement parse is successful,
>>the trigger will fire.
 

>>I think that both C and D are correct. This is my best shot, since
>>I am unaware of any constraints on which columns can be checked
>>in a trigger.
 

>>Jonathan

>
>
snip...

I posted this a few days ago. Burried in it is my answer to the question above.

--
As stated in my last two communications,

"I will be taking tests over the next few weeks at my leisure.  I will publish here any wrong answers given by
dbaPrep.  I am collecting documentation on the wrong answers in the package so that I can send them to 
DOC Software.  I guess somebody needs to do this since they didn't do it themselves."
---
I took the Random Topic Exams/MGMT of Resources test.  This one only had one bug.  Here is the
question and suggested "correct" answer.

7) What are the requirements for a column when creating a foreign key?
(Single Answers)

A) The column must be a primary key or unique key.
B) The column must be a primary key, unique key or index.
C) The column must be an indexed column.
D) The column must be a primary key.
E) The column must be a unique key.

Correct Answer: B,  Your Answer: A

	SORRY.  THE CORRECT ANSWER IS "A" AS I PROVIDED.  JUST TO BE SURE, I TRIED IT
	ON MY ORACLE V7.2 SYSTEM AGAINST AN INDEX THAT WAS NOT A PRIMARY OR UNIQUE
	KEY AND IT GAVE A SYNTAX ERROR.

		ORA-02270: no matching unique or primary key for this column-list
-----
I took the Random Topic Exams/Installation and Setup test.  This test had two questionable answers.
I could be misunderstanding these questions.

2) The system rollback segment spans multiple datafiles?
(Single Answers)

A) True
B) False

Correct Answer: A,  Your Answer: B

	I UNDERSTAND THIS.  HOWEVER, THE WORDING OF THE QUESTION IMPLIES THAT THE
	SYSTEM ROLLBACK SEGMENT MUST SPAN MULTIPLE DATAFILES.  EITHER TRUE OR FALSE
	IS CORRECT.  POORLY WORDED QUESTION.  THIS MAY BE OK IF WE CAN EXPECT THIS
	ON THE ACTUAL EXAM.

6) Prior to installing a new version of Oracle, which of the following items should be checked off your check list?
(Multiple Answers)

A) The SHARED_MEMORY_POOL increased
B) No users should be attached to the server
C) Review the readme files included with the upgrade
D) Defragment the hard drives if possible
E) Create a user id called Oracle which will be used for the installation

Correct Answer: ABCD,  Your Answer: BCD

	DOES THE SHARED_MEMORY_POOL REFER TO THE SHARED_POOL_SIZE PARAMETER?  IF SO,
	WHY NOT SAY "THE SHARED_POOL_SIZE INCREASED"?  THIS IMPLIES SOME OTHER 
	NON-EXISTANT INIT.ORA PARAMETER.  ALSO, IT MAY NOT ALWAYS BE NECESSARY TO
	INCREASE SHARED_POOL_SIZE WHEN INSTALLING A NEW VERSION OF ORACLE.  
------
I took the Random Topic Exams/Environment test.  This one had two bugs.  Here are the
questions and suggested "correct" answers.

1) A BEFORE INSERT trigger is created to do validation on a column created as not NULL. 
The purpose of the trigger is to check the contains of the column and display a message 
"Column cannot be NULL" if the column is NULL. What will happen?
(Multiple Answers)

A) The trigger will be fired and it will check value of the column and return a message when 
   the column is NULL.
B) The trigger will never be fired even when the column is NULL.
C) The message "Column cannot be NULL" will never be displayed.
D) The trigger is always fired whenever data is inserted into this table.

Correct Answer: BC,  Your Answer: C

	SORRY.  I TRIED THIS ONE.  THE TRIGGER IS FIRED ON AN INSERT IF THE COLUMN
	IS NOT NULL.  THEREFORE, ANSWER "B" IS INCORRECT.  ANSWER "D" WOULD ALSO BE
	INCORRECT SINCE THE TRIGGER ONLY FIRES WHEN THE COLUMN IS NOT NULL WHICH IS ALSO
	THE REASON THAT "C" IS THE ONLY CORRECT ANSWER.

19) How would you find a % sign in a database column?
(Multiple Answers)

A) select * from table where col like'\%';
B) select * from table where col like'%%%';
C) select * from table where instr(col, '%') > 0;
D) select * from table where col = '%';

Correct Answer: AC,  Your Answer: C

	SORRY, YOUR "correct" ANSWER "A" DOES NOT WORK ON MY ORACLE  (7.1 and 7.2).  
	YOU WOULD HAVE TO ADD THE "ESCAPE '\'" CLAUSE AT THE END TO MAKE "A" WORK.
-------
I took the Random Topic Exams/Disaster Recovery/Prevention test.  This one had two bugs.  Here are the
questions and suggested "correct" answers.

1) For recovery purposes, it is best to have a single redo log.
(Single Answers)

A) True
B) False

Correct Answer: B ,  Your Answer: B

	THIS IS INTERESTING.  I ANSWERED "B".  THE CORRECT ANSWER IS "B" BUT MINE IS 
	MARKED AS WRONG.  BUG!

3) Which of the following views are related to backups and recovery?
(Multiple Answers)

A) V$LOCK
B) V$BACKUP
C) V$LOGS
D) V$RECOVERY_LOG
E) V$ARCHIVE

Correct Answer: AC,  Your Answer: BE

	SORRY. "A" - V$LOCK HAS NOTHING TO DO WITH BACKUP/RECOVERY.  ALSO "C" - V$LOGS
	DOESN'T EXIST ON MY SYSTEM. I HAVE V$LOG.  ALSO, YOU MISSED "B" - V$BACKUP (INFO ON
	BACKUP STATUS OF ONLINE DATAFILES) AND "E" - V$ARCHIVE (INFO ON ARCHIVE LOGS BY
	THREAD).
-------
I took the Random Topic Exams/Data Management test.  This one had two bugs.  Here are the
questions and suggested "correct" answers.

7) What are the ADVANTAGES of using truncate over delete for removing all records from a table?
(Multiple Answers)

A) Truncate does not retain rollback information.
B) Truncate is much faster than delete.
C) Truncate uses fewer resources.
D) Truncate does not add data to the undo log.

Correct Answer: BC,  Your Answer: ABC

	MY ANSWER INCLUDED "A" WHICH IS CORRECT ACCORDING TO THE SQL REFERENCE FOR ORACLE.

25) The block size is set to 2048 and a table is created with "maxextents 999", 
how many extents will be allocated?
(Single Answers)

A) 121
B) 128
C) 240
D) 512
E) 999

Correct Answer: A,  Your Answer: E

	I HAVE TO ADMIT I DIDN'T UNDERSTAND THIS QUESTION AT ALL SO I PICKED A RANDOM
	ANSWER.  DO YOU MEAN "HOW MANY EXTENTS COULD BE ALLOCATED OVER THE LIFE OF THE
	TABLE?" OR "HOW MANY EXTENTS WILL BE ALLOCATED WHEN THE TABLE IS FIRST CREATED?"
	IF IT IS THE FIRST OPTION, THAT ANSWER IS PLATFORM-SPECIFIC.  ON MY WINDOWS/NT
	SERVER VERSION OF ORACLE7.2, THE MAXIMUM IS 249, NOT 121.  IF YOU MEAN THE 
	SECOND QUESTION, THERE IS NOT ENOUGH INFORMATION TO ANSWER IT (WHATEVER THE
	DEFAULT IS).

More to come later.

Mike Corum
Technical Consulting Team
COTE
Monsanto Co
(All Opinions My Own)
Received on Fri Mar 15 1996 - 00:00:00 CET

Original text of this message