Braindumps for "1Z0-001" Exam

Introduction to Oracle: SQL and PL/SQL

 Question 1.
Andrew forgot his password while on location. Which command must be executed to set a password for andrew?

A. Andrew must execute the command. ALTER USER andrew PASSOWRD BY lion
B. The DBA must execute the command. ALTER USER andrew IDENTIFIED BY lion
C. Andrew must execute the command ALTER USER andrew IDENTIFIED BY lion
D. The andrew must execute the command CHANGE password to lion WHERE “user=andrew”;
E. The DBA must execute the command CHANGE password to lion WHERE “user=andrew”;

Answer:   B

Explanation:
Answer B is correct because of only DBA (not user himself) can change password for account if user forgot his password.

Incorrect Answers:
A: Andrew ‘PASSWORD BY’ is wrong for ‘ALTER USER’ command.
C: User needs to be connected to change his password, but he cannot connect if he forgot password.
D: Command CHANGE is incorrect to change password and user cannot connect himself to change password because he forgot it.
E: Command CHANGE is incorrect to change password.

Reference:
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 182
Chapter 4: Creating Other Database Objects in Oracle

Question 2.
You are updating the employee table. Sharon has been granted the same privileges as you on the employee table. You ask Sharon to logon to the database to check your world before you issue the commit command. What can Sharon do to the employee table?

A. 	Sharon can access the table and verify your changes.
B. 	Sharon cannot access the table.
C. 	Sharon can access the table but she cannot see your changes, she can make the changes for you.
D.	Sharon can access the table but she cannot see your changes and cannot make the changes to the roles that you are changing.

Answer:   D

Explanation:
Answer D is correct because before you committed changes in the employee table nobody
can see changed data.

Incorrect Answers:
A: Sharon can access table but she cannot verify changes because she does not see them before you commit data changes.
B: Sharon can access table because she has been granted the same privileges as you on the employee table.
C: Sharon cannot commit herself changes you did to the data.

Reference:
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 484-485
Chapter 10: Managing Database Use

Question 3
Examine the structure of STUDENT table:

Name	Null?	Type
STUD-ID	NOT NULL	NUMBER(3)
NAME	NOT NULL	VARCHER2(25)
ADDRESS		VARCHER2(50)
GRADUATION		DATE

Which statement inserts a new row into the STUDENT table?

A.	INSERT INTO student.
VALUES(101, ‘Smith’);
B.	INSERT INTO student.
VALUES(101, ‘100 Main Street’, ‘17-JUN-99’, ‘Smith’);
C.	INSERT INTO test.
VALUES(101, ‘Smith’, ‘100 Main Street’, ‘17-JUN-99’);
D.	INSERT INTO student.(stud-id,address,gradulation)
VALUES(101, ‘Smith’, ‘100 Main Street’, ‘17-JUN-99’);
E.	INSERT INTO student.(stud-id,address,name,gradulation)
VALUES(101, ‘100 Main Street’,‘Smith’, ‘17-JUN-99’);

Answer:   E

Explanation:
Answer E is correct because you need to show ALL columns and in correct order for values which you try to insert into table STUDENT.

Incorrect Answers:
A: You need to show ALL columns for values, which you try to insert into table STUDENT.
B: You need to show columns in correct order for values, which you try to insert into table STUDENT. In this statement Name is located in wrong place according table definition.
C: This statement contains wrong table name TEST, not STUDENT.
D: This statement shows list of only 3 columns when there are 4 values need to be inserted into table row.

Reference:
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 120-122
Chapter 4: Creating Oracle Database Objects

Question 4.
Examine the structure of the STUDENT table:

NAME	NULL?	TYPE
STUDENT_ID	NOT NULL	NUMBER(3)
NAME	NOT NULL	VARCHAR2(25)
ADDRESS		VARCHAR2(50)
GRADUATION		DATE

Graduation column is a foreign key column to the graduate table. Examine the data
in the GRADE DATE table:

Graduation 20-jan-1999
12-may-1999
19-jan-2000
25-may-2000
13-jan-2001
29-may-2001

Which update statement produces the following error: 

ORA-02291 integrity constraint(sys_c23) violated parent key not found?

A.	UPDATE student
SET stud-id=999,
graduation= ’29-MAY-2001’
WHERE stud-id=101;’
B.	UPDATE student
SET name= ‘Smith’,
graduation= ’29-MAY-2001’
WHERE stud-id=101;
C.	UPDATE student
SET name= ‘Smith’,
graduation= ‘15-AUG-2000’
WHERE stud-id=101
D.	UPDATE student
SET stud-id=NULL,
address= ‘100 Main Street’
WHERE graduation= ‘20-JAN-1999’

Answer:   C

Explanation:
Answer C is correct because there is no ’15-AUG-2000’ value in the graduate table.
Foreign constraint for this statement will be violated because parent key not found.

Incorrect Answers:
A: There is ’29-MAY-2001’ in the graduate table and foreign constraint will not be violated.
B: There is ’29-MAY-2001’ in the graduate table and foreign constraint will not be violated.
D: There is ’20-JUN-1999’ in the graduate table and foreign constraint will not be violated.

Reference:
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 428-433
Chapter 9: Managing Database Objects II

Question 5.
The view EMP-VIEW is created based on the EMP table as follows.

CREATE OF REPLACE VIEW emp-view
AS
SELECT deptno,SUM(sal)TOT_SAL,COUNT(*)NOT-EMP
FROM emp
GROUP BY deptno;

What happens when the following command is used?

UPDATE emp-view
SET tot-sal=20000
WHERE deptno=10;

A. The base table cannot be updated through this view.
B. The TOT_SAL column in the EMP table is updated to 20,000 for department
C. The TOT_SAL column in the EMP view is updated to 20,000 for department10.
D. The SAL column in the EMP table is updated to 20,000 for employees in department 10.

Answer:   A

Explanation:
Answer A is correct because the user may not INSERT, DELETE, or UPDATE data on the table underlying the sample view if the SELECT statement creating the view contains GROUP BY, or a single-row operation.

Incorrect Answers:
B: TOT_SAL column in the EMP table will not be updated.
C: TOT_SAL column in the EMP table will not be updated for any rows.
D: UPDATE command does not contain SAL column for update.

Reference:
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 164
Chapter 4: Creating Other Database Objects in Oracle

Question 6.
You have a view card ANN_SAL that is based on the employee table. The structure
of the ANN_SAL view is:

NAME	NULL?	TYPE
EMPNO	NOT NULL	NUMBER(4)
YEARLY_SAL		NUMBER(9,2)
MONTHLY_SAL		NUMBER(9,2)

Which statement retrieves the data from the ANN_SAL view?

A. SELECT * FROM ANN_SAL
B. SELECT * FROM EMPLOYEE
C. SELECT * FROM VIEW ANN_SAL
D. SELECT * FROM VIEW ANN_SAL IS DON EMPLOYEE

Answer:   A

Explanation:
Answer A is correct because correct syntax for SELECT command for view is SELECT * FROM 

Incorrect Answers:
B: This statement will show data from table EMPLOYEE, not view ANN_SAL.
C: It’s an incorrect statement because of usage word ‘VIEW’.
D: It’s an incorrect statement because of usage words ‘VIEW’ and ‘IS DON EMPLOYEE’.

Reference:
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 160-169
Chapter 4: Creating Other Database Objects in Oracle

Question 7.
Evaluate this IF statement:

IF v_value>100 THEN
v_new-value:=2*v-value;
ELSIF v-value>200 THEN
v-new-value:=3*v-value;
ELSIF v-value>300 THEN
v-new-value:=4*v-value;
ELSE
v-new-value:=5*v-value;
END IF

What would be assigned to v_new_value if v_value=250?

A. 250
B. 500
C. 750
D. 1000

Answer:   B

Explanation:
Answer B is correct because first IF condition v_value > 100 will be TRUE if v_value have been assigned with new value equal 250. Result of 2*v_value is 500.

Incorrect Answers:
A: All IF conditions multiple 250 on 2, 3, 4 or 5, so result cannot be 250.
C: First IF condition will work not second one, so result will be 500, not 750.
D: First IF condition will work not second or third one, so result will be 500, not 750 or 1000.

Reference:
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 215-217
Chapter 5: Introducing PL/SQL

Question 8.
The PLAYER table contains these columns

ID 			NUMBER(9)
NAME 			VARCHER(2)
MANAGERID 		NUMBER(9)

In this instance, managers are players with you need to display a list of players.
Evaluate these TWO SQL statements:

SELECT p.name,m.name
FROM player p,player m
WHERE m.id= m.manager_id;

SELECT p.name,m.name
FROM player p,player m
WHERE m.manager_id=p.id;

How would the results differ?

A. Statement1 will not execute, statement2 will.
B. Statement1 will execute, statement2 will not.
C. Statement1 is self join, statement2 is not.
D. The results will be same but the display will be different.

Answer:   D

Explanation:
Answer D is correct because the results of these queries will be same, just will look different. In first statement driving column is ID, in second – MANAGER_ID.

Incorrect Answers:
A: Both statements will be executed successfully.
B: Both statements will be executed successfully.
C: Both statements are self join, not only Statement1.

Reference:
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 55-56
Chapter 4: Advanced Data Selection in Oracle

Question 9.
How would you declare a PL/SQL table of records to hold the rows selected from the EMP table?

A.	DECLARE
emp-table is TABLE of emp%ROWTYPE.
B.	BEGIN
TYPE emp-table is TABLE of emp%ROWTYPE
emp-table emp-table-type;
C.	DECLARE
TYPE emp-table is TABLE of emp%ROWTYPE
INDEX BY WHOLE NUMBER:
emp-table emp-table-type;
D.	DECLARE
TYPE emp-table is TABLE of emp%ROWTYPE
INDEX BY BINARY INTEGRATDE.
emp-table emp-table-type;

Answer:   D

Explanation:
Answer D is correct because INDEX BY BINARY INTEGRATED clause need to be set for TABLE type.

Incorrect Answers:
A: INDEX BY clause is not used in definition statement of PL/SQL block.
B: INDEX BY clause is not used in definition statement of PL/SQL block.
C: INDEX BY clause uses WHOLE NUMBER option, which is incorrect for TABLE type definition.

Reference:
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 392
Chapter 8: Managing Database Objects I

Question 10.
You want to create a cursor that can be used several times in a block. Selecting a different active set each time that it is opened. Which type of cursor do you create?

A. A cursor for loop.
B. A multiple selection cursor.
C. A cursor for each active set.
D. A cursor that uses parameters.

Answer:   D

Explanation:
Answer D is correct because a cursor with parameters can be used several times in a block, selecting active set each time that it was opened depending on parameters’ values.

Incorrect Answers:
A: A cursor for loop used for different purpose.
B: A multiple selection cursor does not exist.
C: A cursor for each active set does not allow to use the same cursor several times in a block.

Reference:
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 231-233
Chapter 5: Introducing PL/SQL

Question 11. 
You need to create a report to display the ship date and order totals of your ordid table. If the order has not been shipped your report must display not shipped. If the total is not available your report must say not available. In the ordid table the ship date column has a data type of date the total column has a data type of number. 

Which statement do you use to create this report?

A.	Select ordid, shipdate “Not shipped”,
total “Not available”
FROM order;
B.	Select ordid, NVL (shipdate ‘Not shipped’),
NVL (total, “Not available”)
FROM order;
C.	Select ordid, NVL (TO_CHAR (shipdate), ‘Not shipped’),
NVL (TO_CHAR (total), ‘Not available’)
FROM order;
D.	Select ordid, TO_CHAR (shipdate, ‘Not shipped’)
TO_CHAR (total, ‘Not available’)
FROM order;

Answer:   C

Explanation:
Answer C shows correct syntax of command NVL

Incorrect Answers:
A: This command will show ALL data with name substitution of columns shipdate and total.
B: Incorrect usage for NVL command, because shipdate and total are needed to be converted into VARCHAR2 type with TO_CHAR function. Both parameters of NVL command have to have the same data type.
D: Incorrect syntax. TO_CHAR command is used just to convert data type into VARCHAR2 data type, it have nothing to do with NULL values in columns.

Reference:
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 10-11
Chapter 1: Selecting Data from Oracle

Question 12 
You want to display the details of all employees whose last names are Smith. But you are not sure in which case last names are stored. Which statement will list all the employees whose last name is Smith?

A.	Select last name, first name.
FROM emp
WHERE last name= ‘smith’;
B.	Select last name, first name.
FROM emp
WHERE UPPER (last name)= ‘smith’;
C.	Select last name, first name.
FROM emp
WHERE last name=UPPER (‘smith’);
D.	Select last name, first name.
FROM emp
WHERE LOWER (last name)= ‘smith’;

Answer:   D

Explanation:
Select last name, first name.
FROM emp
WHERE LOWER (last name)= ‘smith’

Answer D shows all records with last name Smith because function LOWER returns the column value passed as x into all lowercase

Incorrect Answers:
A: This command will show only records with last name ‘smith’.
B: Command UPPER converts all data in last_name column into uppercase.
C: This command will show only records with last name ‘SMITH’.

Reference:
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 22
Chapter 1: Selecting Data from Oracle

Question 13.
You need to analyze how long it takes your orders to be shipped from the date that the order is placed. To do this you must create a report that displays the customer number, date order, date shipped and the number of months in whole numbers from the time the order is placed to the time the order is shipped. 

Which statement produces the required results?

A.	SELECT custid, orderate, shipdate,
ROUND(MONTHS_BETWEEN(shipdate,orderate))
“Time Taken”
FROM ord;
B.	SELECT custid, orderate, shipdate,
ROUND(DAYS_BETWEEN(shipdate,orderate))/30.
FROM ord;
C.	SELECT custid, orderate, shipdate,
ROUND OFF (shipdate-orderate) “Time Taken”
FROM ord;
D.	SELECT custid, orderate, shipdate,
MONTHS_BETWEEN (shipdate,orderate) “Time Taken”.
FROM ord;

Answer:   A

Explanation:
Answer A shows the number of months (rounded to integer) between the date of order and the date of shipment.

Incorrect Answers:
B: Function, function DAYS_BETWEEN shows number of days between shipping date and order date.
C: Incorrect function ROUND OFF.
D: This command will show not rounded to integer value, like 8.6451613.

Reference:
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 30
Chapter 1: Selecting Data from Oracle

Question 14 
The employee table contains these columns:
Last_name 		Varchar2 (25)
First_name 		Varchar2 (25)
Salary 			Number7, 2

You need to display the names of employees on more than an average salary of all employees. Evaluate the SQL statement:

SELECT, LAST_NAME, FIRST_NAME from employee where salary< avg(salary); 

Which change should you make to achieve the desired results?

A. Change the function in the Where clause.
B. Move the function to the select clause and add a group clause.
C. Use a sub query in the where clause to compare the average salary value.
D. Move the function to the select clause and add a group by clause and a having clause.

Answer:   C

Explanation:
Answer C shows the correct way to change query, because function AVG can not be used in WHERE clause.

Incorrect Answers:
A: Usage of function AVG is correct
B: This query does not require grouping to extract correct information from the table.
D: This query does not require to use GROUP BY and HAVING clauses to extract correct information from table

Reference:
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 57
Chapter 2: Advanced Data Selection in Oracle

Question 15.
The employee table contains these columns:

FIRST-NAME 		VARCHER2(25)
COMISSION 		NUMBER(3,2)

Evaluate this SQL statement
SELECT first-name,commission
FROM employee
WHERE commission=
(SELECTcomission
FROM employee
WHERE UPPER(first-name)= ‘scott’)

Which statement will cause this statement to fail?

A. Scott has a null commission resolution.
B. Scott has a zero commission resolution.
C. There is no employee with the first name Scott.
D. The first name values in the database are in the lower case.

Answer:   A

Explanation:
Answer A is correct because if Scott has a null commission expression in WHERE clause will cause error.

Incorrect Answers:
B: Query will work correctly.
C: Query will work even without employee with the first name Scott.
D: Name values will be converted to upper case by function UPPER, query will work, but for correct result you need to change UPPER to LOWER function.

Reference:
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 64
Chapter 2: Advanced Data Selection in Oracle

	Web www.certsbraindumps.com

Advertise

Submit Braindumps

Forum

Tell A Friend

Contact Us

Privacy Policy                   Disclaimer                    Feedback                    Term & Conditions

ITCertKeys.com

Copyright © 2004 CertsBraindumps.com Inc. All rights reserved.