Best Preparations of 1z0-071 Exam 2021 Oracle PL/SQL Developer Certified Associate Unlimited 305 Questions
Focus on 1z0-071 All-in-One Exam Guide For Quick Preparation.
NEW QUESTION 42
In which three situations does a new transaction always start? (Choose three.)
- A. when issuing a CREATE TABLEstatement after a SELECTstatement was issued in the same session
- B. when issuing a TRUNCATEstatement after a SELECT statement was issued in the same session
- C. when issuing a DML statement after a DML statement failed in the same session
- D. when issuing a SELECT FOR UPDATEstatement after a CREATE TABLE AS SELECTstatement was issued in the same session
- E. when issuing a CREATE INDEXstatement after a CREATE TABLEstatement completed successfully in the same session
- F. when issuing the first Data Manipulation Language (DML) statement after a COMMIT or ROLLBACK statement was issued in the same session
Answer: D,E,F
NEW QUESTION 43
Examine the structure of the invoice table.
Which two SQL statements would execute successfully? A)
B)
C)
D)
- A. Option D
- B. Option C
- C. Option A
- D. Option B
Answer: B
NEW QUESTION 44
Examine the structure of the SHIPMENTS table:
You want to generate a report that displays the PO_IDand the penalty amount to be paid if the SHIPMENT_DATEis later than one month from the PO_DATE. The penalty is $20 per day.
Evaluate the following two queries:
Which statement is true regarding the above commands?
- A. Only the second query executes successfully and gives the correct result.
- B. Only the first query executes successfully and gives the correct result.
- C. Only the first query executes successfully but gives a wrong result.
- D. Only the second query executes successfully but gives a wrong result.
- E. Both execute successfully and give correct results.
Answer: B
NEW QUESTION 45
Examine the description of the EMPLOYYES table:
Which two statements will run successfully?
- A. SELECT 'The first-name is ' ' ' | | first_name | | ' ' ' FROM employees;
- B. SELECT 'The first-name is ' | | first_name | | ' ' FROM employees;
- C. SELECT 'The first-name is \ ' ' | | first_name | | ' \ ' ' FROM employees;
- D. SELECT 'The first-name is ' ' ' | | first_name | | ' ' ' ' FROM employees;
- E. SELECT 'The first-name is \ ' ' | | first_name | | ' ' FROM employees;
Answer: B,D
NEW QUESTION 46
You issued this command:
Which three statements are true?
- A. All constraints defined on HR.EMPLOYEES are dropped.
- B. Synonyms for HR.EMPLOYEES are dropped.
- C. The HR.EMPLOYEES table may be moved to the recycle bin.
- D. All indexes defined on HR.EMPLOYEES are dropped.
- E. Sequences used to populate columns in the HR.EMPLOYEES table are dropped.
- F. Views referencing HR.EMPLOYEES are dropped.
Answer: A,D,F
NEW QUESTION 47
Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data? (Choose four.)
- A. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted
- B. Primary key and foreign key constraints can be defined at both the column and table level
- C. A table can have only one primary key but multiple foreign keys
- D. The foreign key columns and parent table primary key columns must have the same names
- E. Only the primary key can be defined at the column and table level
- F. It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted
- G. A table can have only one primary key and one foreign key
Answer: A,B,C,D
NEW QUESTION 48
View and Exhibit and examine the structure and data in the INVOICE table. (Choose two.)
Which two statements are true regarding data type conversion in query expressions?
- A. inv_amt = '0255982' : requires explicit conversion
- B. CONCAT(inv_amt, inv_date) : requires explicit conversion
- C. inv_no BETWEEN '101' AND '110' : uses implicit conversion
- D. inv_date = '15-february-2008' :uses implicit conversion
- E. inv_date > '01-02-2008' : uses implicit conversion
Answer: C,D
NEW QUESTION 49
Examine the structure of the MEMBERS table: (Choose the best answer.)
Examine the SQL statement:
SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC; What would be the result execution?
- A. It fails because a column alias cannot be used in the ORDER BY clause.
- B. It displays all cities in ascending order, within which the last names are further sorted in descending order.
- C. It fails because a column number and a column alias cannot be used together in the ORDER BY clause.
- D. It displays all cities in descending order, within which the last names are further sorted in descending order.
Answer: B
NEW QUESTION 50
Examine the structure of the SALES table.
Examine this statement:
Which two statements are true about the SALES1 table? (Choose two.)
- A. It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
- B. It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
- C. It will not be created because of the invalid WHERE clause.
- D. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.
- E. It is created with no rows.
Answer: A,E
NEW QUESTION 51
View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose two.)
In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You must list each product ID and the number of times it has been sold.
Examine this query which is missing a JOIN operator:
SQL > SELECT p.prod_id, count(s.prod_id)
FROM products p ______________ sales s
ON p.prod_id = s.prod_id
GROUP BY p.prod_id;
Which two JOIN operations can be used to obtain the required output?
- A. RIGHT OUTER JOIN
- B. JOIN
- C. LEFT OUETR JOIN
- D. FULL OUTER JOIN
Answer: C,D
NEW QUESTION 52
You want to display the date for the first Monday of the next month and issue the following command:
What is the outcome?
- A. It executes successfully and returns the correct result.
- B. It executes successfully but does not return the correct result.
- C. In generates an error because rrrrshould be replaced by rrin the format string.
- D. In generates an error because fmand double quotation marks should not be used in the format string.
- E. In generates an error because TO_CHARshould be replaced with TO_DATE.
Answer: A
NEW QUESTION 53
Evaluate the following query
What is the correct output of the above query?
- A. +00-300, +54-02, +00 11:12:10.123457
- B. +00-300, +00-650, +00 11:12:10.123457
- C. +25-00, +54-02, +00 11:12:10.123457
- D. +25-00, +00-650, +00 11:12:10.123457
Answer: C
NEW QUESTION 54
Examine the structure of the BOOKS_TRANSACTIONS table:
Examine the SQL statement:
Which statement is true about the outcome?
- A. It displays details for members who have borrowed before today with RM as TRANSACTION_TYPE and the details for members A101 or A102.
- B. It displays details for only members A101 and A102 who have borrowed before today with RM TRANSACTION_TYPE.
- C. It displays details for members who have borrowed before today's date with either RM as TRANSACTION_TYPE or MEMBER_ID as A101 and A102.
- D. It displays details only for members who have borrowed before today with RM as TRANSACTION_TYPE.
Answer: A
NEW QUESTION 55
Examine the description of the PRODUCTS table:
Which three queries use valid expressions? (Choose three.)
- A. SELECT product_id, unit_price, 5 "Discount", unit_price + surcharge - discount FROM products;
- B. SELECT product_id, (expiry_date - delivery_date) * 2 FROM products;
- C. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
- D. SELECT product_id, unit_price | | 5 "Discount", unit_price + surcharge - discount FROM products;
- E. SELECT product_id, unit_price, unit_price + surcharge FROM products;
- F. SELECT product_id, expiry_date * 2 FROM products;
Answer: A,E,F
NEW QUESTION 56
View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tables.
You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.
Which CREATE VIEW statement would create the view successfully?
- A. CREATE OR REPLACE VIEW ord_vu (order_id, order_date)AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;
- B. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)||'NO OF ITEMS'FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id,o.order_dateWHITH CHECK OPTION;
- C. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id,o.order_date;
- D. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id,o.order_date;
Answer: D
NEW QUESTION 57
The SALES table has columns PROD_IDand QUANTITY_SOLDof data type NUMBER.
Which two queries execute successfully? (Choose two.)
SELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id HAVING
- A. BY COUNT(*) > 10;
SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold > 55000; - B.
- C. SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP
- D. BY prod_id HAVING COUNT(*) > 10;
SELECT COUNT(prod_id) FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id; - E. COUNT(*) > 10;
SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP
Answer: C,E
NEW QUESTION 58
Examine the description of the CUSTOMERStable:
CUSTNOis the PRIMARY KEY.
You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result? (Choose two.)
- A. FULL OUTER JOINwith self join
- B. subquery
- C. LEFT OUTER JOINwith self join
- D. RIGHT OUTER JOINwith self join
- E. self join
Answer: B,E
NEW QUESTION 59
Which two statements are true regarding operators used with subqueries (Choose two.)
- A. The <ANYoperator means less than the maximum.
- B. =ANYand =ALLoperators have the same functionality.
- C. The NOT INoperator is equivalent to IS NULL.
- D. The INoperator cannot be used in single-row subqueries.
- E. The NOToperator can be used with IN, ANYand ALLoperators.
Answer: A,E
NEW QUESTION 60
Which three statements are correct regarding indexes? (Choose three.)
- A. When a table is dropped, the corresponding indexes are automatically dropped.
- B. Indexes should be created on columns that are frequently referenced as part of any expression.
- C. For each DML operation performed, the corresponding indexes are automatically updated.
- D. A non-deferrable PRIMARY KEYor UNIQUE KEYconstraint in a table automatically attempts to create a unique index.
Answer: A,C,D
Explanation:
Explanation/Reference:
References:
http://viralpatel.net/blogs/understanding-primary-keypk-constraint-in-oracle/
NEW QUESTION 61
View the exhibit and examine the structure of ORDERSand CUSTOMERStables.
Which INSERT statement should be used to add a row into the ORDERStable for the customer whose CUST_LAST_NAMEis Robertsand CREDIT_LIMITis 600? Assume there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.
INSERT INTO (SELECT o.order_id, o.order_date, o.order_mode, c.customer_id,
- A. (SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), order_total)
VALUES (1,'10-mar-2007', 'direct', &&customer_id, 1000); - B. (SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), order_total)
VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);
INSERT INTO orders - C. o.order_total
FROM orders o, customers c
WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' AND
c.credit_limit=600)
VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
INSERT INTO orders (order_id, order_date, order_mode, - D. VALUES (1,'10-mar-2007', 'direct',
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
INSERT INTO orders (order_id, order_date, order_mode,
Answer: D
NEW QUESTION 62
Which three statements are true regarding the data types? (Choose three.)
- A. Only one LONGcolumn can be used per table.
- B. The value for a CHARdata type column is blank-padded to the maximum defined column width.
- C. A TIMESTAMPdata type column stores only time values with fractional seconds.
- D. The BLOBdata type column is used to store binary data in an operating system file.
- E. The minimum column width that can be specified for a VARCHAR2data type column is one.
Answer: A,B,E
NEW QUESTION 63
View the exhibit and examine the structure of the STOREStable.
You want to display the NAME of the store along with the ADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, which is 115% of property price.
The stores displayed must have START_DATEin the range of 36 months starting from 01-Jan-2000 and above.
Which SQL statement would get the desired output?
- A. SELECT name, concat (address||','| |city| |', ', country) AS full_address, start_date, property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR'))
<=36; - B. SELECT name, concat (address| | ','| |city| |', ', country) AS full_address, start_date, property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, '01-JAN-2000') <=36; - C. SELECT name, address||','||city||','||country AS full_address,
start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR'))
<=36; - D. SELECT name, concat (address| | ','| |city| |', ', country) AS full_address, start_date, property_price, property_price*115/100
FROM stores
WHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
Answer: A
NEW QUESTION 64
View the Exhibit and examine the structure of ORDER_ITEMSand ORDERStables.
You need to remove from the ORDER_ITEMStable those rows that have an order status of 0 or 1 in the ORDERStable.
Which two DELETEstatements are valid (Choose two.)
DELETE *
- A. FROM order_items
WHERE order_id IN (SELECT order_id
FROM orders
WHERE orders_status in (0,1)); - B. FROM order_items
WHERE order_id IN (SELECT order_id)
FROM orders
WHERE order_status IN (0,1));
DELETE - C. WHERE order_id = (SELECT order_id FROM orders o
WHERE i.order_id = o.order_id AND order_status IN (0,1));
DELETE - D. FROM (SELECT * FROM order_items I,orders o
WHERE i.order_id = o.order_id AND order_status IN (0,1));
DELETE FROM order_items i
Answer: A,D
NEW QUESTION 65
Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS tables:
You want to generate a list of all department IDs that do not exist in the COURSE_DETAILS table.
You execute the SQL statement:
What is the outcome?
- A. It executes successfully and displays the required list.
- B. It fails because the join type used is incorrect.
- C. It executes successfully but displays an incorrect list.
- D. It fails because the ON clause condition is not valid.
Answer: A
NEW QUESTION 66
View the Exhibit and examine the description of the ORDERS table. (Choose two.)
Which two WHERE clause conditions demonstrate the correct usage of conversion functions?
- A. WHERE Order_date > ( TO_DATE('JUL 10 2006', 'MON DD YYYY')
- B. WHERE Order_date_IN ( TO_DATE('OCT 21 2003', 'MON DD YYYY'), TO_CHAR('NOV
21 2003', 'MON DD YYYY') ) - C. WHERE TO_CHAR(Order_date, 'MON DD YYYY') = 'JAN 20 2003'
- D. WHERE Order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY')
Answer: A,C
NEW QUESTION 67
......
Guaranteed Success with 1z0-071 Dumps: https://www.verifieddumps.com/1z0-071-valid-exam-braindumps.html
Pass Oracle 1z0-071 Exam – Experts Are Here To Help You: https://drive.google.com/open?id=1Md-gXJi9gpo1n89kj_KoN7LERo0QAdKL
