1Z1-047 by Oracle Valid Free Exam Practice Test

Question 1

View the Exhibit and examine the structure of the ORDERS table.
NEWJDRDERS is a new table with the columns ORD_ID, ORD_DATE, CUST_ID, and
ORD_TOTAL that have the same data types and size as the corresponding columns in the
ORDERS table.
Evaluate the following INSERT statement:
INSERT INTO new_orders (ord_id, ord_date, cust_id, ord_total) VALUES(SELECT
order_id.order_date.customer_id.order_total FROM orders WHERE order_date > '31-dec-1999');
Why would the INSERT statement fail?

A. because the WHERE clause cannot be used in a subquery embedded in an INSERT statement B. because column names in NEW_ODRDERS and ORDERS tables do not match C. because the VALUES clause cannot be used in an INSERT with a subquery D. because the total number of columns in the NEW ORDERS table does not match the total number of columns in the ORDERS table

Correct Answer: C

Question 2

View the Exhibit and examine the ORDERS table.
The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?

A. ALTER TABLE orders
ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id); B. ALTER TABLE orders
MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id); C. ALTER TABLE orders
ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL; D. ALTER TABLE orders
MODIFY customer_id CONSTRAINT orders_cust_id_nn NOT NULL;

Correct Answer: D

Question 3

View the Exhibit and examine the structure of the EMP table which is not partitioned and not an
index-organized table.
Evaluate the following SQL statement:
ALTER TABLE emp DROP COLUMN first_name;
Which two statements are true regarding the above command? (Choose two.)

A. The FIRST_NAME column would be dropped provided it does not contain any data. B. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is used. C. The FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the above SQL statement. D. The FIRST_NAME column would be dropped provided at least one or more columns remain in the table.

Correct Answer: B,D

Question 4

Evaluate the following SELECT statement and view the Exhibit to examine its output: SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status FROM user_constraints WHERE table_name = ORDERS
Which two statements are true about the output? (Choose two.)

A. The STATUS column indicates whether the table is currently in use. B. The R_CONSTRAINT_NAME column gives the alternative name for the constraint. C. In the second column, indicates a check constraint. D. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.

Correct Answer: C,D

Question 5

Evaluate the following SQL statement:
CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));
Which query would use the UPPER_NAME_IDX index?

A. SELECT UPPER(product_name)
FROM product_information
WHERE product_jd = 2254; B. SELECT product_id
FROM product_jnformation
WHERE UPPER(product_name) IN ('LASERPRO', 'Cable); C. SELECT UPPER(product_name)
FROM product_jnformation; D. SELECT product_jd, UPPER(product_name)
FROM product_information
WHERE UPPER(product_name)='LASERPRO' OR list_price > 1000;

Correct Answer: B

Question 6

Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)

A. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups. B. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause. C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause. D. You can use column alias in the GROUP BY clause. E. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.

Correct Answer: B,E

Question 7

Which statement is true regarding the CUBE operator in the GROUP BY clause of a SQL statement?

A. It produces 2 n possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause. B. It produces n+1 possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause. C. It finds all the NULL values in the superaggregates for the groups specified in the GROUP BY clause. D. It produces only aggregates for the groups specified in the GROUP BY clause.

Correct Answer: A

Question 8

Which statements are true regarding the hierarchical query in Oracle Database 10g? (Choose all
that apply.)

A. You cannot specify conditions when you retrieve data by using a hierarchical query. B. It is possible to retrieve data in top-down or bottom-up hierarchy. C. It is possible to retrieve data only in top-down hierarchy. D. It is possible to remove an entire branch from the output of the hierarchical query.

Correct Answer: B,D

Question 9

View the Exhibit and examine the structure of the EMPLOYEES table.
You want to retrieve hierarchical data of the employees using the top-down hierarchy. Which SQL clause would let you choose the direction to walk through the hierarchy tree?

A. CONNECT BY PRIOR B. START WITH C. HAVING D. GROUP BY E. WHERE

Correct Answer: A

Question 10

Evaluate the following ALTER TABLE statement:
ALTER TABLE orders SET UNUSED order_date;
Which statement is true?

A. The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully. B. The DESCRIBE command would still display the ORDER_DATE column. C. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table. D. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.

Correct Answer: C

Question 11

View the Exhibit and examine the descriptions for ORDERS and ORDER_ITEMS tables.
Evaluate the following SQL statement:
SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Order Amount"
FROM orde_items oi JOIN orders o
ON oi.order_id = o.order_id
GROUP BY CUBE (o.customer_id, oi.product_id);
Which three statements are true regarding the output of this SQL statement? (Choose three.)

A. It would return the subtotals for the Order Amount of every PRODUCT_ID and CUSTOMER_ID as one group. B. It would return only the grand total for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group. C. It would return the subtotals for the Order Amount of every CUSTOMER_ID. D. It would return the subtotals for the Order Amount for every PRODUCT_ID. E. It would return the subtotals for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group.

Correct Answer: C,D,E

Question 12

View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.
You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id;
What would happen when the above statement is executed?

A. The statement would not execute because the GROUP BY clause cannot be used in the inline view. B. The statement would not execute because inline views and outer joins cannot be used together. C. The statement would execute successfully to produce the required output. D. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.

Correct Answer: C

Question 13

View the Exhibit and examine the details for the CATEGORIES_TAB table. Evaluate the following incomplete SQL statement:
SELECT category_name ,category_description FROM categories_tab You want to display only the rows that have 'harddisks' as part of the string in the CATEGORY_DESCRIPTION column.
Which two WHERE clause options can give you the desired result? (Choose two.)

A. WHERE REGEXPJJKE (category_description, '[^Hlhard+.s]'); B. WHERE REGEXPJJKE (category_description, '^H|hard+.s'); C. WHERE REGEXPJJKE (category_description, 'hard+.s'); D. WHERE REGEXPJJKE (category_description, '^H|hard+.s$');

Correct Answer: B,C

Question 14

View the Exhibit and examine the data in EMP and DEPT tables.
In the DEPT table, DEPTNO is the PRIMARY KEY.
In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing
the DEPTNO column in the DEPT table.
What would be the outcome of the following statements executed in the given sequence?
DROP TABLE emp;
FLASHBACK TABLE emp TO BEFORE DROP;
INSERT INTO emp VALUES (2.COTT 10); INSERT INTO emp VALUES (3,ING 55);

A. Both the INSERT statements would fail because all constraints are automatically retrieved when the table is flashed back. B. Only the first INSERT statement would succeed because all the constraints except the primary key constraint are automatically retrieved after a table is flashed back. C. Both the INSERT statements would succeed because none of the constraints on the table are automatically retrieved when the table is flashed back. D. Only the second INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back.

Correct Answer: D

Oracle Database SQL Expert - 1Z1-047 Exam Practice Test