Free Access Oracle.1z0-071.v2022-09-21.q298 Practice Test (Page 45)

Question 216

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.

There is only one customer with the cust_last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?

A.INSERT INTO ordersVALUES(1, '10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);

B.INSERT INTO orders (order_id, order_data, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), order_total)VALUES(1, '10-mar-2007', 'direct', &&customer_id, 1000);

C.INSERT INTO orders (order_id, order_data, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), order_total)VALUES (1, '10-mar-2007', 'direct', &customer_id, 1000).

D.INSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_idAND c.cust_last_name='Roberts' AND c.credit_limit=600 )VALUES (1, '10-mar-2007', 'direct', (SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);

Question 217

Examine the structure of the INVOICEtable.

Which two SQL statements would execute successfully?
SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete')

A.FROM invoice;
SELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available')

B.FROM invoice;
SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available')

C.FROM invoice;
SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)

D.FROM invoice;

Question 218

Examine the data in the CUST_NAMEcolumn of the CUSTOMERStable.
CUST_NAME
-------------------
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikilineni
Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC".
Which query gives the required output?

A.SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)
FROM customers
WHERE SUBSTR (cust_name, INSTR (cust_name, ' ')+1)
LIKE INITCAP ('MC%');

B.SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)
FROM customers
WHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1))
LIKE 'Mc%';

C.SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)
FROM customers
WHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1))
INITCAP 'MC%';

D.SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)
FROM customers
WHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1))
'Mc';

Question 219

Which three statements are true regarding subqueries? (Choose three.)

A.Multiple columns or expressions can be compared between the main query and subquery.

B.Subqueries can contain ORDER BY but not the GROUP BY clause.

C.Main query and subquery can get data from different tables.

D.Subqueries can contain GROUP BY and ORDER BY clauses.

E.Main query and subquery must get data from the same tables.

F.Only one column or expression can be compared between the main query and subquery.

Question 220

Which two statements best describe the benefits of using the WITH clause? (Choose two.)

A.It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace.

B.It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.

C.It enables sessions to store the results of a query permanently.

D.It enables sessions to store a query block permanently in memory and use it to create complex queries.

Other Version: 3265Oracle.1z0-071.v2025-03-22.q314; 6793Oracle.1z0-071.v2024-10-24.q196; 3201Oracle.1z0-071.v2023-10-02.q276; 2691Oracle.1z0-071.v2023-05-08.q154; 5642Oracle.1z0-071.v2022-08-30.q239; 25382Oracle.1z0-071.v2022-02-20.q161; 85Oracle.Prepawaypdf.1z0-071.v2022-01-02.by.miles.211q.pdf

Latest Upload: 286PaloAltoNetworks.NGFW-Engineer.v2026-05-01.q43; 414Nokia.4A0-113.v2026-05-01.q69; 447EC-COUNCIL.312-49v11.v2026-04-30.q214; 416Microsoft.MB-820.v2026-04-30.q101; 284Salesforce.MC-202.v2026-04-30.q57; 327BICSI.INSTC_V8.v2026-04-29.q53; 462NMLS.MLO.v2026-04-28.q82; 303NCARB.Project-Management.v2026-04-28.q27; 556EMC.D-AV-DY-23.v2026-04-27.q184; 1403ServiceNow.CSA.v2026-04-27.q483

Question 216

Question 217

Question 218

Question 219

Question 220

Download PDF File