1z0-071 by Oracle Actual Free Exam Q&As

Question 1

Which two statements are true regarding views? (Choose two.)

A. Rows added through a view are deleted from the table automatically when the view is dropped. B. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view. C. A simple view in which column aliases have been used cannot be updated. D. A subquery used in a complex view definition cannot contain group functions or joins. E. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword. F. The OR REPLACE option is used to change the definition of an existing view without dropping and re- creating it.

Discussion 0

Correct Answer: E,F Vote an answer

Question 2

Which two statements are true about a full outer join? (Choose two.)

A. It includes rows that are returned by a Cartesian product. B. The Oracle join operator (+) must be used on both sides of the join condition in the WHERE clause. C. It returns matched and unmatched rows from both tables being joined. D. It returns only unmatched rows from both tables being joined. E. It includes rows that are returned by an inner join.

Discussion 0

Correct Answer: C,E Vote an answer

Explanation: Only visible for PassTestking members. You can sign-up / login (it's free).

Question 3

View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER__ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.

Which DELETE statement would execute successfully?

A. DELETE
FROM orders
WHERE (SELECT order_id
FROM order_items); B. DELETE order_id
FROM orders
WHERE order_total < 1000; C. DELETE orders
WHERE order_total < 1000; D. DELETE orders o, order_items i
WHERE o.order id = i.order id;

Discussion 0

Correct Answer: C Vote an answer

Question 4

You own table DEPARTMENTS, referenced by views, indexes, and synonyms.
Examine this command which executes successfully:
DROP TABLE departments PURGE;
Which three statements are true? (Choose three.)

A. It will remove all synonyms for the DEPARTMENTS table. B. It will remove the DEPARTMENTS table from the database. C. Neither can it be rolled back nor can the DEPARTMENTS table be recovered. D. It will remove all views that are based on the DEPARTMENTS table. E. It will drop all indexes on the DEPARTMENTS table. F. It will delete all rows from the DEPARTMENTS table, but retain the empty table.

Discussion 0

Correct Answer: B,C,E Vote an answer

Explanation: Only visible for PassTestking members. You can sign-up / login (it's free).

Question 5

The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER.
Which two queries execute successfully? (Choose two.)

A. SELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id HAVING COUNT(*) >10; B. SELECT COUNT (prod_id) FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id; C. SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold > 55000; D. SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY COUNT(*) >10; E. SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY prod_id HAVING COUNT(*) >10;

Discussion 0

Correct Answer: A,B Vote an answer

Question 6

You need to produce a report where each customer's credit limit has been incremented by $1000.
In the output, the customer's last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase.
Which statement would accomplish this requirement?

A. SELECT INITCAP (cust_last_name) "Name", cust_credit_limit + 1000 INITCAP ("NEW CREDIT LIMIT") FROM customers; B. SELECT cust_last_name AS Name, cust_credit_limit + 1000 AS New Credit Limit FROM customers; C. SELECT cust_last_name AS "Name", cust_credit_limit + 1000 AS "New Credit Limit" FROM customers; D. SELECT cust_last_name AS Name, cust_credit_limit + 1000 "New Credit Limit" FROM customers;

Discussion 0

Correct Answer: C Vote an answer

Question 7

Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?

A. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement. B. It executes successfully and displays rows in the descending order of PROMO_CATEGORY. C. It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement. D. It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators.

Discussion 0

Correct Answer: C Vote an answer

Question 8

View the Exhibit and examine the structure of the PRODUCT_INFORMATION table.

You want to see the product names and the date of expiration of warranty for all the products, if the product is purchased today. The products that have no warranty should be displayed at the top and the products with maximum warranty period should be displayed at the bottom.
Which SQL statement would you execute to fulfill this requirement?

A. SELECT product_name, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER BY SYSDATE B. SELECT product_name, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information WHERE warranty_period > SYSDATE C. SELECT product_name, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER BY SYSDATE-warranty_period D. SELECT product_name, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER BY SYSDATE+warranty_period

Discussion 0

Correct Answer: D Vote an answer

Question 9

Which two are true about the NVL, NVL2, and COALESCE functions? (Choose two.)

A. The first expression in NVL2 is never returned. B. COALESCE stops evaluating the list of expressions when it finds the first non-null value. C. NVL2 can have any number of expressions in the list. D. NVL can have any number of expressions in the list. E. NVL must have expressions of the same data type. F. COALESCE stops evaluating the list of expressions when it finds the first null value.

Discussion 0

Correct Answer: A,B Vote an answer

Explanation: Only visible for PassTestking members. You can sign-up / login (it's free).

Question 10

Which two are true about granting privileges on objects? (Choose two.)

A. A table owner must grant the REFERENCES privilege to allow other users to create FOREIGN KEY constraints using that table. B. An object privilege can be granted to a role only by the owner of that object. C. The WITH GRANT OPTION clause can be used only by DBA users. D. The owner of an object acquires all object privileges on that object by default. E. An object privilege can be granted to other users only by the owner of that object.

Discussion 0

Correct Answer: A,D Vote an answer

Explanation: Only visible for PassTestking members. You can sign-up / login (it's free).

Question 11

Examine this description of the PRODUCTS table:

Rows exist in this table with data in all the columns. You put the PRODUCTS table in read-only mode.
Which three commands execute successfully on PRODUCTS? (Choose three.)

A. CREATE INDEX price_idx ON products (price); B. ALTER TABLE products DROP COLUMN expiry_date; C. TRUNCATE TABLE products; D. ALTER TABLE products DROP UNUSED COLUMNS; E. ALTER TABLE products SET UNUSED (expiry_date); F. DROP TABLE products;

Discussion 0

Correct Answer: A,C,F Vote an answer

Question 12

When does a transaction complete? (Choose all that apply.)

A. When a data definition language statement is executed B. When a PL/SQL anonymous block is executed C. When a ROLLBACK command is executed D. When a DELETE statement is executed E. When a TRUNCATE statement is executed after the pending transaction

Discussion 0

Correct Answer: A,C,E Vote an answer

Question 13

Which statement is true about transactions?

A. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction. B. Each Data Definition Language (DDL) statement executed forms a single transaction. C. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction. D. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for PassTestking members. You can sign-up / login (it's free).

Question 14

Examine this list of requirements for a sequence:
1. Name: EMP_SEQ
2. First value returned: 1
3. Duplicates are never permitted.
4. Provide values to be inserted into the EMPLOYEES.EMPLOYEE_ID column.
5. Reduce the chances of gaps in the values.
Which two statements will satisfy these requirements? (Choose two.)

A. CREATE SEQUENCE emp_seq NOCACHE; B. CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CACHE; C. CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 NOCACHE; D. CREATE SEQUENCE emp_seq START WITH 1 CACHE; E. CREATE SEQUENCE emp_seq; F. CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CYCLE;

Discussion 0

Correct Answer: B,C Vote an answer

Question 15

Evaluate the following SQL statements that are issued in the given order:
CREATE TABLE emp
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
enameVARCHAR2(15),
salary NUMBER(8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);
ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What would be the status of the foreign key EMP_MGR_PK?

A. It would be automatically enabled and deferred. B. It would be automatically enabled and immediate. C. It would remain disabled and can be enabled only by dropping the foreign key constraint and re- creating it. D. It would remain disabled and has to be enabled manually using the ALTER TABLE command.

Discussion 0

Correct Answer: D Vote an answer

Oracle Database SQL - 1z0-071 Exam Practice Test