Braindumps: Dumps for 1Z0-001 Exam Brain Dump

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.

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.

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.

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.

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.

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 

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.

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.

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.

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.