HERE ARE SOME DUMPS THAT I COLLECTED FROM THE NET
Rizvi@mail.idm.edu
Hey I got trough the iz0_001
48/57

QUESTION 1: Output from a table called PLAYS with two columns, PLAY_NAME and AUTHOR, is shown next. Which of the following SQL statements
produced it? PLAY_TABLE ------------------------------------- "Midsummer Night's Dream", SHAKESPEARE "Waiting For Godot", BECKETT "The Glass
Menagerie", WILLIAMS Top of Form 1
A. select PLAY_NAME|| AUTHOR from PLAYS;
B. select PLAY_NAME, AUTHOR from PLAYS;
C. select PLAY_NAME||',
' || AUTHOR from PLAYS;
D. select PLAY_NAME||', ' || AUTHOR play_table from PLAYS;
ANSWER:D

QUESTION 2: A user is setting up a join
operation between tables EMP and DEPT. There are some employees in the EMP table that the user wants returned by the query, but the employees are
not assigned to department heads yet. Which SELECT statement is most appropriate for this user? Top of Form 1
A. select e.empid, d.head from emp
e, dept d;
B. select e.empid, d.head from emp e, dept d where e.dept# = d.dept#;
C. select e.empid, d.head from emp e, dept d where e.dept# =
d.dept# (+);
D. select e.empid, d.head from emp e, dept d where e.dept# (+) = d.dept#; ANSWER: C

QUESTION 3: In order to perform an inner join,
which criteria must be true? Top of Form 1
A. The common columns in the join do not need to have shared values.
B. The tables in the join need
to have common columns.
C. The common columns in the join may or may not have shared values.
D. The common columns in the join must have shared
values.
ANSWER: B

QUESTION 4: Which of the following integrity constraints automatically create an index when defined? (Choose two) Top of Form
1
A. Foreign keys
B. Unique constraints
C. NOT NULL constraints
D. Primary keys
ANSWER: B, D

QUESTION 5: Omitting the WHERE clause from
a DELETE statement has which of the following effects? Top of Form 1
A. The delete statement will fail because there are no records to delete.

B. The delete statement will prompt the user to enter criteria for the deletion.
C. The delete statement will fail because of syntax error.
D.
The delete statement will remove all records from the table.
ANSWER: D

QUESTION 6: User SUJITH has granted the CREATE ANY VIEW WITH ADMIN
OPTION privilege to user SAIBU. User SAIBU granted the same privilege WITH ADMIN OPTION to user SHA. User SUJITH revokes the privilege from user
SAIBU. Which statement is true about privileges granted to users SHA, SUJITH, and SAIBU? Top of Form 1
A. SHA and SUJITH have the privilege, but
SAIBU does not.
B. SHA and SAIBU have the privilege, but SUJITH does not.
C. SUJITH and SAIBU have the privilege, but SHA does not.
D. SUJITH
has the privilege, but SAIBU and SHA do not.
ANSWER: A

QUESTION 7: Which system privilege allows the user to connect to a database in
restricted session mode? Top of Form 1
A. create table
B. create user
C. restricted session
D. create session
ANSWER: C

QUESTION 8: The
following statement is issued on the database: COMMENT ON TABLE empl IS 'Do not use this table.' How can this data be viewed? Top of Form 1
A.
Using the describe command
B. Issuing a select * from empl statement
C. Selecting from ALL_COMMENTS
D. Selecting from ALL_TAB_COMMENTS

ANSWER: D

QUESTION 9: To add the number of columns selected by a view: Top of Form 1
A. Add more columns to the underlying table
B. Issue
the alter view statement
C. Use a correlated subquery in conjunction with the view
D. Drop and re-create the view with references to select more
columns
ANSWER: D

QUESTION 10: If left out, which of the following would cause an infinite loop to occur in a simple loop? Top of Form 1
A.
loop
B. end loop
C. if-then
D. exit
ANSWER: D

QUESTION 11: Which of the following statements are true about WHILE loops? Top of Form 1

A. Explicit exit statements are required in while loops.
B. Counter variables are required in while loops.
C. An if-then statement is needed
to signal when a while loop should end.
D. All exit conditions for while loops are handled in the exit when clause.
ANSWER: D

QUESTION 12:You
must permanently remove all data from the INVOICE table, but will need the table structure in the future. What single command should be
issued?

a. DROP TABLE invoice
b. TRUNCATE TABLE invoice
c. DELETE
FROM invoice
d. TRNCATE TABLE invoice
KEEP STRUCTURE;

ANSWER:
C

QUESTION 13: . Examine the following command:

CREATE TABLE pay
(employee-id Number(9)
CONSTRAINT pay-pk PRIMARY KEY,
2000-pay
NUMBER(8,2)
manager-name VARCHAR2(25)
CONSTRAINT mgr-name-nn. NOT NULL,
9pay NUMBER(8,2));

Which two lines of this command will return an
error?

a. First
b. Second
c. Third
d. Fourth
e. Fifth
f. Sixth
g. Seventh

ANSWER:D:G

QUESTION 14: Examine this executable section of
a PL/SQL block:

BEGIN
FOR worker_record IN pay_cursor LOOP
Worker_id_table(worker_id):=
Worker_record.last_name;
END LOOP;
CLOSE
salary_cursor;
END;

Why does this section cause an error?

a. The cursor needs to be open.
b. No fetch statements were used.
c. Terminating
conditions are missing.
d. The cursor does not need to be explicitly closed.

ANSWER:D

QUESTION 15: You have been assigned the task of
updating worker salaries. If a salary is less than 1000, it must be incremented by 10%. The SQL Plus substitution variable will be used to accept a
worker number. Which PL/SQL block successfully updates salaries?

a. DECLARE
V_sal worker.sal%type;
BEGIN
SELECT sal
INTO v_sal
FROM
worker
WHERE workerno=&&p_workerno;
IF В‘v_sal60
GROUP BY by section_no
ORDER BY AVG(MONTHS_BETWEEN(SYSDATE,hire_date));

Why does the command
cause an error?

a. A SELECT clause cannot contain a group function.
b. A WHERE clause cannot be used to restrict groups.
c. An ORDER BY clause
cannot contain a group function.
d. A group function cannot contain a single row function.

ANSWER:B

QUESTION 26: WHICH IS NOT AN SQL*PLUS
COMMAND?
a) LIST
b) ACCEPT
c) CHANGE
d) UPDATE
e) DESCRIBE

ANSWER:D

QUESTION 27: WHICH STATEMENT IS VALID ABOUT PROJECTION?
a)
PROJECTION ALLOWS YOU TO CHOOSE ROWS
b) PROJECTION ALLOWS YOU TO CHOOSE COLUMNS
c) PROJECTION ALLOWS YOU TO JOIN TABLES
d) PROJECTION ALLOWS YOU
TO ADD COLUMS TO A TABLE

ANSWER:B

QUESTION 28: UNDER WHICH SITUATION IS IT NECESSARY TO USE ON EXPLICIT CURSOR?
a) WHEN ANY DML OR SELECT
STATEMENT IS USED IN A PL/SQL BLOCK
b) WHEN A DELETE STATEMENT IN A PL/SQL BLOCK DELETES MORE THAN ONE ROW
c) WHEN A SELECT STATEMENT IN PL/SQL
RETRIEVES MORE THAN ONE ROW
d) WHEN AN UPDATE STATEMENT IN A PL/SQL BLOCK HAS TO MODIFY MORE THAN ONE ROW

ANSWER :C

QUESTION 30: WHICH SELECT
STATEMENT DISPLAYS ALL THE EMPLOYEES WHO DO NOT HAVE ANY SUBORDINATES?
a) SELECT E.ENAME
FROM EMP E
WHERE E.MGR IS NOT NULL;

b) SELECT
E.ENAME
FROM EMP E
WHERE E.EMPNO IN
(SELECT M.MGR
FROM EMP M);

c) SELECT E.ENAME
FROM EMP E
WHERE E.EMPNO NOT IN
(SELECT M.MGR
FROM EMP
M);

d) SELECT E.ENAME
FROM EMP E
WHERE E.EMPNO NOT IN
(SELECT M.MGR
FROM EMP M
WHERE M.MGR IS NOT NULL);

ANSWER:D

QUESTION 31: which
statement is true about nesting block?
A. Variable name must unique between blocks
B. Variable define in the outblock is visible in the inner
block
C. Variable define in the inner block is visible in the outer block.

ANSWER:B

QUESTION 32: Which SELECT statement dispaly the
ORDERID, PRODID and Quantity of items in the
ITEM table that match both the product ID and Quantilty of item in order 605?
(don't display the
details for 605)
A. SELECT ordid, prodid, quantity
FROM item
WHERE (prodid, quantity) = (SELECT prodid, quantity
FROM item
WHERE
ordid=605)
AND ordid605;

B. SELECT ordid, prodid, quantity
FROM item
WHERE (prodid, quantity)IN (SELECT prodid, quantity
FROM item
WHERE
ordid=605)
AND ordid605;

c. SELECT ordid, prodid, quantity
FROM item
WHERE (prodid, quantity)=(SELECT prodid, quantity
FROM item
WHERE
ordid=605)
AND ordid=605;

ANSWER:B


QUESTION 33: Which operator can not be used in nonequel join.
a. IN.
b. Like.
C. Equal operator

d. between...and..

ANSWER:C

QUESTION 34: DBA grants "Create any TAble" and "Create procedure" to you. What
privileges would you
have?
a) create any table in any schema but can drop table from and create
procedure only in my schema. *
b) create table, drop table and create
procedure only in my schema.
c) create table and drop table in any schema but create procedure only in
my own schema.

ANSWER:B

QUESTION 35:
Inside PL/SQL, insert data into emp table, get employee name and
deptno from user prompt.
A. Declare
v_hiredate Date := Sysdate;
Begin
Insert
into emp(empno, ename, hiredate, depno)
values(empno_seques.nextval,'&Name',v_hiredate, &deptno);
End;

B. Declare
v_hiredate Date :=
Sysdate;
Begin
Insert into emp(empno, ename, hiredate, depno)
values(empno_seques.nextval,'&Name',v_hiredate, &deptno)
where
job='CLERK';
End;

ANSWER:A

---