1Z0-071 practice questions 101–110

1. Question 101 · 238 in bank

   Select 2

   Which two statements are true about the results of using the INTERSECT operator in compound queries? (Choose two.)

   • AThe number of columns in each SELECT in the compound query can be different.
   • BINTERSECT returns rows common to both sides of the compound query.
   • CINTERSECT ignores NULLs.
   • DColumns named in each SELECT in the compound query can be different.
   • EReversing the order of the intersected tables can sometimes affect the output.

   Show answer

   Answer: BD

   Next question

2. Question 102 · 238 in bank

   Select 3

   Examine the description of the PRODUCTS table:

   

   Which three queries use valid expressions? (Choose three.)

   • ASELECT product_id, unit_price, S "Discount", unit_price + surcharge - discount FROM products;
   • BSELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
   • CSELECT product_id, (expiry_date - delivery_date) * 2 FROM products;
   • DSELECT product_id, unit_price || 5 "Discount", unit_price + surcharge - discount FROM products;
   • ESELECT product_id, expiry_date * 2 FROM products;
   • FSELECT product_id, unit_price, unit_price + surcharge FROM products;

   Show answer

   Answer: BCF

   Explanation

   BCF The three queries that use valid expressions are: B. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products; C. SELECT product_id, (expiry_date - delivery_date) * 2 FROM products; F. SELECT product_id, unit_price, unit_price + surcharge FROM products; Explanation: A. This query is invalid because the alias "S" is not defined anywhere in the query. B. This query is valid. It calculates a value based on the unit price of each product using a mathematical expression. C. This query is valid. It calculates the difference between the expiry date and delivery date for each product and multiplies it by 2. D. This query is invalid because the alias "discount" is not defined anywhere in the query. E. This query is invalid because you cannot multiply a date value by a number. F. This query is valid. It selects the product ID, unit price, and the sum of the unit price and surcharge for each product.

   Source: Adapted from community discussion — fantastic_side_eye

   Next question

3. Question 103 · 238 in bank

   Select 2

   Examine this partial command:

   

   Which two clauses are required for this command to execute successfully? (Choose two.)

   • Athe access driver TYPE clause
   • Bthe DEFAULT DIRECTORY clause
   • Cthe REJECT LIMIT clause
   • Dthe LOCATION clause
   • Ethe ACCESS PARAMETERS clause

   Show answer

   Answer: BD

   Explanation

   TYPE: "Oracle Database provides two access drivers: ORACLE_LOADER and ORACLE_DATAPUMP. If you omit the TYPE specification, ORACLE_LOADER is the default access driver." https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables013.htm#ADMIN11705 ACCESS PARAMETERS: "To modify the default behavior of the access driver for external tables, specify access parameters." https://docs.oracle.com/en/database/oracle/oracle-database/23/sutil/oracle-external-tables-concepts.html#GUID-ACF1D3AA-1D61-4682-AEC5-42C944756E12 REJECT LIMIT : there are several examples that don't use this clause LOCATION: "For ORACLE_HIVE, the LOCATION clause is not used." https://docs.oracle.com/en/database/oracle/oracle-database/23/sutil/oracle-external-tables-concepts.html#GUID-ACF1D3AA-1D61-4682-AEC5-42C944756E12 That about LOCATION confuse me, but I tend to believe it's a very particular case, so the answer should be (BD) DEFAULT DIRECTORY and LOCATION

   Source: Adapted from community discussion — J4vi

   References

   • Oracle documentation
   • Oracle documentation

   Next question

4. Question 104 · 238 in bank

   Examine these statements:

   

   Which is true about modifying the columns in ALTER_TEST?

   • Ac1 can be changed to NUMBER(10) and c2 can be changed to VARCHAR2(10).
   • Bc1 can be changed to NUMBER(10) and c2 cannot be changed to VARCHAR2(10).
   • Cc2 can be changed to NUMBER(5) but c1 cannot be changed to VARCHAR2(5).
   • Dc1 can be changed to VARCHAR2(10) but c1 cannot be changed to NUMBER(10).
   • Ec1 can be changed to VARCHAR2(5) but c2 can be changed to NUMBER(12,2).

   Show answer

   Answer: E

   Explanation

   E Correct. create table a_test (c1 VARCHAR2(10), c2 NUMBER(10)); insert into a_test values ('123', 123); select * from a_test; commit; alter table a_test modify c1 VARCHAR2(5); alter table a_test modify c2 NUMBER(12,2); desc a_test; if alter table a_test modify c1 VARCHAR2(2); ORA-01441: cannot decrease column length because some value is too big

   Source: Adapted from community discussion — WingL

   Next question

5. Question 105 · 238 in bank

   Select 2

   You and your colleague Andrew have these privileges on the EMPLOYEE_RECORDS table: 1. SELECT 2. INSERT 3. UPDATE 4. DELETE You connect to the database instance and perform an update to some of the rows in EMPLOYEE_RECORDS, but do not commit yet. Andrew connects to the database instance and queries the table. No other users are accessing the table. Which two statements are true at this point? (Choose two.)

   • AAndrew will be unable to see the changes you have made.
   • BAndrew will be unable to perform any INSERTs, UPDATEs, or DELETEs on the table.
   • CAndrew will be able to SELECT from the table, but be unable to modify any existing rows.
   • DAndrew will be able to see the changes you have made.
   • EAndrew will be able to modify any rows in the table that have not been modified by your transaction.

   Show answer

   Answer: AE

   Next question

6. Question 106 · 238 in bank

   Select 2

   Which two statements cause changes to the data dictionary? (Choose two.)

   • ADELETE FROM scott.emp;
   • BALTER SESSION SET NLS_DATE_FORMAT = ‘DD/MM/YYYY’;
   • CGRANT UPDATE ON scott.emp TO fin manager;
   • DSELECT * FROM user_tab_prive;
   • ETRUNCATE TABLE emp;

   Show answer

   Answer: CE

   References

   • Oracle documentation

   Next question

7. Question 107 · 238 in bank

   Select 3

   Examine the description of the ORDERS table:

   

   Examine the description of the INVOICES table:

   

   Which three statements execute successfully? (Choose three.)

   • ASELECT * FROM orders ORDER BY order_id INTERSECT - SELECT * FROM invoices ORDER BY invoice_id;
   • B(SELECT * FROM orders UNION ALL - SELECT * FROM invoices) ORDER BY order_id;
   • CSELECT order_id, order_date FROM orders UNION ALL - SELECT invoice_id, invoice_date FROM invoices ORDER BY order_id;
   • DSELECT * FROM orders - MINUS - SELECT * FROM invoices ORDER BY 1;
   • ESELECT order_id invoice_id, order_date FROM orders MINUS - SELECT invoice_id, invoice_date FROM invoices ORDER BY invoice_id;
   • FSELECT * FROM orders ORDER BY order_id UNION - SELECT * FROM invoices;
   • GSELECT order_id, order_date FROM orders INTERSECT - SELECT invoice_id, invoice_date FROM invoices ORDER BY invoice_id;

   Show answer

   Answer: CDE

   Explanation

   CDE CORRECT. PLEASE HAVE A TRY: CREATE TABLE ORDERS_01 ( ORDER_ID NUMBER(38), ORDER_DATE DATE ); CREATE TABLE INVOICES_01 ( INVOICE_ID NUMBER(38), INVOICE_DATE DATE ); SELECT * FROM ORDERS_01 ORDER BY order_id INTERSECT SELECT * FROM INVOICES_01 ORDER BY invoice_id; SELECT * FROM ORDERS_01 UNION ALL SELECT * FROM INVOICES_01 ORDER BY ORDER_ID; SELECT order_id, order_date FROM ORDERS_01 UNION ALL SELECT invoice_id, invoice_date FROM INVOICES_01 ORDER BY order_id; SELECT * FROM ORDERS_01 MINUS SELECT * FROM INVOICES_01 ORDER BY 1; SELECT order_id invoice_id, order_date FROM ORDERS_01 MINUS SELECT invoice_id, invoice_date FROM INVOICES_01 ORDER BY invoice_id; SELECT * FROM ORDERS_01 ORDER BY order_id UNION SELECT * FROM INVOICES_01; SELECT order_id, order_date FROM ORDERS_01 INTERSECT SELECT invoice_id, invoice_date FROM INVOICES_01 ORDER BY invoice_id;

   Source: Adapted from community discussion — WingL

   Next question

8. Question 108 · 238 in bank

   Examine the description of the PRODUCT_INFORMATION table:

   

   Which query retrieves the number of products with a null list price?

   • ASELECT COUNT(NVL(list_price, 0)) FROM product_information WHERE list_price is NULL;
   • BSELECT COUNT(list_price) FROM product_information WHERE list_price = NULL;
   • CSELECT COUNT(list_price) FROM product_information WHERE list_price IS NULL;
   • DSELECT COUNT(DISTINCT list_price) FROM product_information WHERE list_price IS NULL;

   Show answer

   Answer: A

   Next question

9. Question 109 · 238 in bank

   Examine this partial statement:

   

   Which is true?

   • ABoth the query and the subquery can select only zero rows or one row.
   • BBoth the query and the subquery can select any number of rows.
   • CThe query can select only zero rows or one row, but the subquery can select any number of rows.
   • DThe query can select any number of rows, but the subquery can select only zero rows or one row.

   Show answer

   Answer: D

   Next question

10. Question 110 · 238 in bank

    Select 3

    The ORDERS table has a primary key constraint on the ORDER_ID column. The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the primary key of the ORDERS table. The constraint is defined with ON DELETE CASCADE. There are rows in the ORDERS table with an ORDER_TOTAL of less than 1000. Which three DELETE statements execute successfully? (Choose three.)

    • ADELETE order_id FROM orders WHERE order_total < 1000;
    • BDELETE orders WHERE order_total < 1000
    • CDELETE * FROM orders WHERE order_total < 1000;
    • DDELETE FROM orders;
    • EDELETE FROM orders WHERE order_total < 1000;

    Show answer

    Answer: BDE

    Explanation

    The question asks which ones: "execute successfully" it executes and successfully, there is nothing saying the condition except in other alternativas.

    Source: Adapted from community discussion — mrcsp

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

1Z0-071 practice, question 101. 238 questions available.