Free Testking Oracle Certification 1z0-047 Exam Questions
Exam Number: 1Z0-047
Associated Certifications: Oracle 11g DBA OCA
Oracle Database: SQL Certified Expert
Oracle9i DBA OCA
Oracle PL/SQL Developer Certified Associate
Exam Price: $125 US
maintain a consistent standard across versions. For example, to maintain a consistent standard for success, a new version of an exam which contains more hard questions than the prior version will be assigned a somewhat lower required passing score. For this reason, passing scores may vary between different exams in your certification track, and between later and earlier versions of the same exam.
The passing scores provided on the Oracle Certification Program website are for informational purposes only. Oracle does not recommend an exam preparation strategy targeting the passing score, because passing scores are subject to change without notice.
1.You need to load information about new customers from the NEW_CUST table into the tables
CUST and CUST_SPECIAL. If a new customer has a credit limit greater than 10,000, then the
details have to be inserted into CUST_SPECIAL. All new customer details have to be inserted into
the CUST table. Which technique should be used to load the data most efficiently?
A.external table
B.the MERGE command
C.the multitable INSERT command
D.INSERT using WITH CHECK OPTION
Correct:C
Which three tasks can be performed using regular expression support in Oracle Database 10g?
(Choose three.)
A.It can be used to concatenate two strings.
B.It can be used to find out the total length of the string.
C.It can be used for string manipulation and searching operations.
D.It can be used to format the output for a column or expression having string data.
E.It can be used to find and replace operations for a column or expression having string data.
Correct:C D E
5.Evaluate the CREATE TABLE statement: CREATE TABLE products (product_id NUMBER(6)
CONSTRAINT prod_id_pk PRIMARY KEY, product_name VARCHAR2(15)); Which statement is true
regarding the PROD_ID_PK constraint?
A.It would be created only if a unique index is manually created first.
B.It would be created and would use an automatically created unique index.
C.It would be created and would use an automatically created nonunique index.
D.It would be created and remains in a disabled state because no index is specified in the command.
Correct:B
6.Which two statements are true? (Choose two.)
A.The USER_SYNONYMS view can provide information about private synonyms.
B.The user SYSTEM owns all the base tables and useraccessible
views of the data dictionary.
C.All the dynamic performance views prefixed with V$ are accessible to all the database users.
D.The USER_OBJECTS view can provide information about the tables and views created by the user
only.
E.DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.
Correct:A E
9.Evaluate the following CREATE SEQUENCE statement: CREATE SEQUENCE seq1 START WITH
100 INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE; The sequence SEQ1 has generated
numbers up to the maximum limit of 200. You issue the following SQL statement: SELECT
seq1.nextval FROM dual; What is displayed by the SELECT statement?
A.1
B.10
C.100
D.an error
Correct:A
13.Which two statements are true regarding the execution of the correlated subqueries? (Choose
two.)
A.The nested query executes after the outer query returns the row.
B.The nested query executes first and then the outer query executes.
C.The outer query executes only once for the result returned by the inner query.
D.Each row returned by the outer query is evaluated for the results returned by the inner query.
Correct:A D
14.OE and SCOTT are the users in the database. The ORDERS table is owned by OE. Evaluate the
statements issued by the DBA in the following sequence: CREATE ROLE r1; GRANT SELECT,
INSERT ON oe.orders TO r1; GRANT r1 TO scott; GRANT SELECT ON oe.orders TO scott;
REVOKE SELECT ON oe.orders FROM scott; What would be the outcome after executing the
statements?
A.SCOTT would be able to query the OE.ORDERS table.
B.SCOTT would not be able to query the OE.ORDERS table.
C.The REVOKE statement would remove the SELECT privilege from SCOTT as well as from the role R1.
D.The REVOKE statement would give an error because the SELECT privilege has been granted to the
role R1.
Correct:A
Evaluate the following SQL statement: ALTER TABLE hr.emp SET UNUSED (mgr_id); Which
statement is true regarding the effect of the above SQL statement?
A.Any synonym existing on the EMP table would have to be recreated.
B.Any constraints defined on the MGR_ID column would be removed by the above command.
C.Any views created on the EMP table that include the MGR_ID column would have to be dropped and recreated.
D.Any index created on the MGR_ID column would continue to exist until the DROP UNUSED COLUMNS
command is executed.
Correct:B
EMPDET is an external table containing the columns EMPNO and ENAME. Which command
would work in relation to the EMPDET table?
A.UPDATE empdet SET ename = ‘Amit’ WHERE empno = 1234;
B.DELETE FROM empdet WHERE ename LIKE ‘J%’;
C.CREATE VIEW empvu AS SELECT * FROM empdept;
D.CREATE INDEX empdet_idx ON empdet(empno);
Correct:C
20.In which scenario would you use the ROLLUP operator for expression or columns within a
GROUP BY clause?
A.to find the groups forming the subtotal in a row
B.to create groupwise
grand totals for the groups specified within a GROUP BY clause
C.to create a grouping for expressions or columns specified within a GROUP BY clause in one direction,
from right to left for calculating the subtotals
D.to create a grouping for expressions or columns specified within a GROUP BY clause in all possible
directions, which is crosstabular
report for calculating the subtotals
Correct:C
21.View the Exhibit and examine the details of the EMPLOYEES table. Evaluate the following SQL
statement: SELECT phone_number,
REGEXP_REPLACE(phone_number,’([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})’, ‘(\1) \2\
3′)
“PHONE NUMBER” FROM employees; The query was written to format the PHONE_NUMBER for
the employees. Which option would be the correct format in the output?
22.Which statement correctly grants a system privilege?
A.GRANT EXECUTE ON proc1 TO PUBLIC;
B.GRANT CREATE VIEW ON table1 TO user1;
C.GRANT CREATE TABLE TO user1,user2;
D.GRANT CREATE SESSION TO ALL;
Correct:C
26.Which statement is true regarding external tables?
A.The default REJECT LIMIT for external tables is UNLIMITED.
B.The data and metadata for an external table are stored outside the database.
C.ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an
external table.
D.The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the
database from an external table.
Correct:D
28.Which two statements are true regarding the EXISTS operator used in the correlated
subqueries? (Choose two.)
A.The outer query stops evaluating the result set of the inner query when the first value is found.
B.It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
C.It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
D.The outer query continues evaluating the result set of the inner query until all the values in the result set
are processed.
Correct:A C
29.A noncorrelated
subquery can be defined as ____.
A.a set of sequential queries, all of which must always return a single value
B.a set of sequential queries, all of which must return values from the same table
C.a SELECT statement that can be embedded in a clause of another SELECT statement only
D.a set of one or more sequential queries in which generally the result of the inner query is used as the
search value in the outer query
Correct:D
You need to create a table for a banking application with the following considerations: 1) You
want a column in the table to store the duration of the credit period. 2) The data in the column should be stored in a format such that it can be easily added and subtracted with 3) date type data
without using the conversion functions. 4) The maximum period of the credit provision in the
application is 30 days. 5) The interest has to be calculated for the number of days an individual
has taken a credit for. Which data type would you use for such a column in the table?
A.INTERVAL YEAR TO MONTH
B.INTERVAL DAY TO SECOND
C.TIMESTAMP WITH TIME ZONE
D.TIMESTAMP WITH LOCAL TIME ZONE
Correct:B
Posted in Oracle | No Comments »

