Online Access Free 1z1-071 Exam Questions

View the Exhibit and examine the structure of the PRODUCT_INFORMATIONand INVENTORIES tables.

You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID,and QUANTITY_ON_HANDfor all the products wherein QUANTITY_ON_HANDis less than five.
Which two SQL statements can accomplish the task? (Choose two.)

• A.SELECT i.product_id, i.quantity_on_hand, pi.supplier_id FROM product_information pi JOIN inventories i ON (pi.product_id=i.product_id) USING (product_id) AND quantity_on_hand < 5;
• B.SELECT product_id, quantity_on_hand, supplier_id
  FROM product_information
  NATURAL JOIN inventories AND quantity_on_hand < 5;
• C.SELECT i.product_id, i.quantity_on_hand, pi.supplier_id FROM product_information pi JOIN inventories i ON (pi.product_id=i.product_id) AND quantity_on_hand < 5;
• D.SELECT i.product_id, i.quantity_on_hand, pi.supplier_id FROM product_information pi JOIN inventories i ON (pi.product_id=i.product_id) WHERE quantity_on_hand < 5;

Which task can be performed by using a single Data Manipulation Language (OML) statement?

• A.removing all data only from one single column on which a unique constraint is defined
• B.removing all data only from one single column on which a primary key constraint is defined
• C.adding a column constraint when inserting a row into a table
• D.adding a column with a default value when inserting a row into a table

Which two partitioned table maintenance operations support asynchronous Global Index Maintenance in Oracle database 12c?

• A.ALTER TABLE MOVE PARTITION
• B.ALTER TABLE MERGE PARTITION
• C.ALTER TABLE TRUNCATE PARTITION
• D.ALTER TABLE SPLIT PARTITION
• E.ALTER TABLE DROP PARTITION
• F.ALTER TABLE ADD PARTITION

Which two statements are true about TRUNCATE and DELETE? (Choose two.)

• A.The result of a TRUNCATE can be undone by issuing a ROLLBACK.
• B.TRUNCATE leaves any indexes on the table in an UNUSABLE state.
• C.The result of a DELETE can be undone by issuing a ROLLBACK.
• D.TRUNCATE can use a WHERE clause to determine which row(s) should be removed.
• E.DELETE can use a WHERE clause to determine which row(s) should be removed.

The CUSTOMERS table has the following structure:
You need to write a query that does the following tasks:
1. Display the first name and tax amount of the customers. Tax is 5% of their credit limit.
2. Only those customers whose income level has a value should be
considered.
3. Customers whose tax amount is null should not be considered.
Which statement accomplishes all the required tasks?

• A.SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level <> NULL AND tax_amount <> NULL;
• B.SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;
• C.SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE (cust_income_level,tax_amount) IS NOT NULL;
• D.Which statement accomplishes all the required tasks? SELECT cust_first_name, cust_credit_limit
  * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND tax_amount IS NOT NULL;