1z0-001 Introduction to Oracle: SQL and PL/SQL Part 2

Introduction to Oracle: SQL and PL/SQL   Part 2

Get full version on:http://www.getfreedumps.com/

QUESTION 11

You need to store currency data and you know that data will always have two digits to the right of the decimal
points. However the number of digits to the left of the decimal place will vary greatly. Which data type would be
most appropriate to store the data?
A. NUMBER
B. NUMBER(T)
C. LANG
D. LANGRA
Correct Answer: A
Section: (none)
Explanation
Explanation/Reference:
Explanation: Answer A is correct because by default NUMBER is NUMBER(L, P) type, which is always stored
as variable-length data, where 1 byte is used to store the exponent, 1 byte is used to store for every two
significant digits of the number's mantissa, and 1 byte is used for negative numbers if the number of significant
digits is less than 38 bytes.
Incorrect Answers:
B: NUMBER(T) type will not allow to keep more than T numbers of digits to the left of the decimal place, but
question says that it will vary greatly.
C: There is no type LANG in Oracle 8i.
D: Type LANGRA does not exists too.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 390 Chapter 8: Managing Database objects I

QUESTION 12

Examine the structure of STUDENT table.
NAME NULL TYPE
STUDENT ID NOT NULL NUMBER(3)
NAME NOT NULL VARCHAR2(25)
PHONE NOT NULL VARCHAR2(9)
ADDRESS VARCHAR2(50)
GRADUATION DATE
There are hundred records in the student table. You need to modify the Phone column to hold only numeric
value. Which statement will modify the data type of the Phone column?
A. ALTER TABLE student MODIFY phone NUMBER(9)
B. ALTER STUDENT table MODIFY COLUMN phone NUMBER(9);
C. You can not modify a VARCHAR2 data type to a NUMBER data type for a
D. Column.
E. You cannot modify the data type of a column if there is data in the column.
Correct Answer: E
Section: (none)
Explanation
Explanation/Reference:
Explanation:
Answer E is correct because Phone column in STUDENT table has NOT NULL constraint, which does not
allows to modify data type of a column if there is data in the column.
Incorrect Answers:
A: The statement will fail because it is incorrect way to change data type for column with NOT NULL constraint
on it.
B: There is a wrong syntax 'ALTER STUDENT table' and 'MODIFY COLUMN' in the statement.
C: It is possible to modify VARCHAR2 data type to NUMBER data type (with some restrictions).
D: Does not exists in question
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 141-142 Chapter 4: Creating Other Database
Objects in Oracle

QUESTION 13

You need to update employee salaries if the salary of an employee is less than 1000. The salary needs to be
incremented by 10%. Use SQL*Plus substitution variable to accept the employee number. Which PL/SQL block
successfully updates the salaries?
A. Declare
V_sal emp.sal % TYPE;
Begin
SELECT Sal
INTO V_sal
FROM emp
WHERE empno = and P_empno;
IF (V_Sal<1000) THEN
UPDATE emp
INTO Sal := Sal*1.1
WHERE empno = and P_empno;
END IF;
END;
B. Declare
V_sal emp.sal % TYPE;
Begin
SELECT Sal
INTO V_sal
FROM emp
WHERE empno = and P_empno;
IF (V_Sal<1000) THEN
SAL := SAL * 1.1;
END IF;
END;
C. Declare
V_sal emp.sal % TYPE;
Begin
SELECT Sal
INTO V_sal
FROM emp
WHERE empno = and P_empno;
IF (V_Sal<1000) THEN
UPDATE emp
Sal := Sal*1.1
WHERE empno = and P_empno;
END IF;
END;
D. Declare
V_sal emp.sal % TYPE;
Begin
SELECT Sal
INTO V_sal
FROM empWHERE empno = and P_empno;
IF (V_Sal<1000) THEN
UPDATE emp
Set Sal := Sal*1.1
WHERE empno = and P_empno;
END IF;
END;
Correct Answer: D
Section: (none)
Explanation
Explanation/Reference:
Explanation: Answer D is correct because it's using cursor and IF-THEN structure correctly to increase salary
for all employees with current salary less than 1000.
Incorrect Answers:
A: UPDATE <table_name> INTO is wrong construction for UPDATE command
B: There is no SAL variable defined in PL/SQL block, so SAL:=SAL*1.1 will fail and it's wrong way to change
value of column Sal in table EMP.
C: Sal:=Sal*1.1 can not be inside UPDATE ... WHERE command. Oracle 8, DBA Certification Exam Guide,
Jason S. Couchman, p. 215-217 Chapter 8: Introducing PL/SQL

QUESTION 14

The employee table contains these columns.
LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) DEPT_ID NUMBER(9)
You need to display the names of the employees that are not assigned to the department. Evaluate this SQL
statement.
SELECT last_name, first_nameFROM employee
WHERE dept_id is NULL
Which change should you make to achieve the desired result?
A. Create an outer join.
B. Change the column in the where condition.
C. Change the operator in the where condition
D. Add a second condition to the where condition
Correct Answer: C
Section: (none)
Explanation
Explanation/Reference:
Explanation: Answer C is correct because the NULL operator need to be changed in the WHERE condition to
display correct result.
Incorrect Answers:
A: One table is used for query, so there is no reason to use outer join operation.
B: The column in the WHERE condition is correct and don't need to be changed.
D: Query does not require additional condition to extract correct data. Oracle 8, DBA Certification Exam Guide,
Jason S. Couchman, p. 10-11 Chapter 1: Selecting data from Oracle

QUESTION 15

Which statement about SQL is true?
A. Null values are displayed last in the ascending sequences.
B. Data values are displayed in descending order by default.
C. You cannot specify a column alias in an ORDER BY clause.
D. You cannot sort query results by a column that is not included in the SELECT list.
E. The results are sorted by the first column in the SELECT list, if the ORDER BY clause is not provided.
Correct Answer: A
Section: (none)
Explanation
Explanation/Reference:
Explanation:
Answer A is correct because of null values are displayed last in the ascending sequences.
Incorrect Answers:
B: Data values are displayed in ASCENDING order by default.
C: It is possible to specify a column alias in an ORDER BY clause (but not in a GROUP BY clause).
D: You can sort query results by a column that is not included in the SELECT list.
E: The results will not be sorted at all, if the ORDER BY clause was not used in query. Oracle 8, DBA
Certification Exam Guide, Jason S. Couchman, p. 18-19 Chapter 8: Selecting Data from Oracle

Get full version on:http://www.getfreedumps.com/

QUESTION 16

Written a PL/SQL loop, you need to test if the current FETCH was successful. Which SQL cursor attribute
would you use to accomplish this task?
A. SQL % ISOPEN
B. SQL % ROWCOUNT
C. SQL % FOUND
D. This task cannot be accomplished with a SQL cursor attribute.
E. A SQL cursor attribute cannot be used within a PL/SQL loop.
Correct Answer: C
Section: (none)
Explanation
Explanation/Reference:
Explanation:
Answer C is correct because of SQL%FOUND attribute returns TRUE if current FETCH have been successful.
Incorrect Answers:
A: SQL%ISOPEN attribute returns TRUE if cursor is OPEN and ready for use.
B: SQL%ROWCOUNT returns the number of rows that were processed by the statement.
D: This task can be successfully accomplished with a SQL%FOUND cursor attribute.
E: PL/SQL loop can contain a SQL cursor attribute. Oracle 8, DBA Certification Exam Guide, Jason S.
Couchman, p. 222-224 Chapter 5: Introducing PL/SQL

QUESTION 17

The structure of the DEPT table is as follows:
NAME NULL TYPE
Deptno Not Null NUMBER(2)
Dname VARCHAR2(14)
Loc VARCHAR2(13)
Examine the code
Declare
Type dept_record_type is record
(dno NUMBER, name VARCHAR2(20));
dept_rec dept_record_type;
Begin Select deptno, dname
INTO dept_rec
FROM dept
WHERE deptno=10;
END
Which statement displays the name of selected department?
A. DBMS_OUTPUT.PUT_LINE (name);
B. DBMS_OUTPUT.PUT_LINE (dname);
C. DBMS_OUTPUT.PUT_LINE (dept_rec.name);
D. DBMS_OUTPUT.PUT_LINE (dept_rec.dname);
E. DBMS_OUTPUT.PUT_LINE (dept_rec (name));
Correct Answer: C
Section: (none)
Explanation
Explanation/Reference:
Explanation:
Answer C is correct because it shows field NAME for record DEPT_REC of DEPT_RECORD type.
Incorrect Answers:
A: Statement does not specify that NAME is field of DEPT_REC record.
B: DNAME is name of column in DEPT table and cannot be used this way to show data.
D: There is not field DNAME in record DEPT_REC.
Not E: This statement just has wrong syntax of function PUT_LINE in DBMS_OUTPUT package to represent
data.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 211-213 Chapter 5: Introducing PL/SQL

QUESTION 18

Which privilege concerns with system level security?
A. Drop any table.
B. DELETE
C. ALTER
D. INDEX
E. UPDATE
Correct Answer: A
Section: (none)
Explanation
Explanation/Reference:
Explanation:
Answer A is correct because only DROP ANY TABLE privilege from all choices belongs to SYSTEM level
security, all others - not.
Incorrect Answers:
B: DELETE is object privilege. Permits the grantee of this object privilege to delete data from a table or view.
C: ALTER is object privilege. Permits the grantee of this object privilege to alter the definition of a table or
sequence only. The ALTER privileges on all other database objects are considered system privileges.
D: INDEX is object privilege. Permits the grantee of this object privilege to create an index on a table already
defined.
E: UPDATE is object privilege. Permits the grantee of this object privilege to update data into a table or view.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 485 Chapter 10: Managing Database Use

QUESTION 19

Evaluate the SQL statement.
CREATE ROLE manager;
CREATE ROLE clerk;
CREATE ROLE inventory;
CREATE USER scott IDENTIFIED BY tiger;
GRANT inventory TO clerk;
GRANT clerk TO manager;
GRANT inventory TO scott;
/
How many roles will user scott have access to?
A. 0
B. 1
C. 2
D. 3
Correct Answer: B
Section: (none)
Explanation
Explanation/Reference:
Explanation: Answer B is correct because of only INVENTORY role will be granted to user SCOTT.
Incorrect Answers:
A: One role (INVENTORY) is granted to user SCOTT, not zero.
C: This choice would be correct if role CLERK would be granted to SCOTT because this role includes
INVENTORY role.
D: This choice would be correct if role MANAGER would be granted to SCOTT because this role includes
INVENTORY and CLERK roles.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 490-494 Chapter 10: Managing Database
Use

QUESTION 20

Scott forgot his password while on location. Which command must be executed to set a password for scott?
A. Scott must execute the command. ALTER USER scott PASSOWRD BY lion
B. The DBA must execute the command. ALTER USER scott IDENTIFIED BY lion
C. Scott must execute the command ALTER USER scott IDENTIFIED BY lion
D. The scott must execute the command CHANGE password to lion WHERE "user=scott";
E. The DBA must execute the command CHANGE password to lion WHERE "user=scott";
Correct Answer: B
Section: (none)
Explanation
Explanation/Reference:
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: Clause '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. Oracle 8, DBA Certification Exam Guide, Jason S.
Couchman, p. 182 Chapter 4: Creating Other Database Objects in Oracle

Get full version on:http://www.getfreedumps.com/

Comments

Popular posts from this blog

1z0-020 Oracle8i: New Features for Administrators Part 2

1Z0-023 Oracle8i: Architecture and Administration Part 2

1Z0-024 Oracle8i: Performance and Tuning Part 3