[Sep-2021] Oracle PL/SQL Developer Certified Associate 1z0-071 Exam Practice Dumps
2021 1z0-071 Premium Files Test pdf - Free Dumps Collection
NEW QUESTION 145
Examine the command to create the BOOKS table.
SQL> create table books(book id CHAR(6) PRIMARY KEY,
title VARCHAR2(100) NOT NULL,
publisher_id VARCHAR2(4),
author_id VARCHAR2 (50));
The BOOK ID value 101 does not exist in the table.
Examine the SQL statement.
insert into books (book id title, author_id values
('101','LEARNING SQL','Tim Jones')
- A. It executes successfully only NULL PUBLISHER_ID column name is added to the columns list in the INSERT statement.
- B. It executes successfully only if NULL is explicitly specified in the INSERT statement.
- C. It executes successfully and the row is inserted with a null PLBLISHER_ID.
- D. It executes successfully onlyif NULL PUBLISHER ID column name is added to the columns list and NULL is explicitly specified In the INSERT statement.
Answer: C
NEW QUESTION 146
Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
-------------------
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
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 customersWHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
- B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';
- C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';
- D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');
Answer: B
NEW QUESTION 147
Examine this SQL statement:
Which two are true?
- A. The UPDATE statement executes successfully even if the subquery selects multiple rows
- B. The subquery is not a correlated subquery
- C. All existing rows in the ORDERS table are updated
- D. The subquery is executed for every updated row in the ORDERS table
- E. The subquery is executed before the UPDATE statement is executed
Answer: B
NEW QUESTION 148
Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY.
Only the EMPLOYEE_ID column is indexed.
Rows exist for employees 100 and 200.
Examine this statement:
Which two statements are true? (Choose two.)
- A. Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100
- B. Employee 200 will have SALARY set to the same value as the SALARY of employee 100
- C. Employee 100 will have SALARY set to the same value as the SALARY of employee 200
- D. Employees 100 will have JOB_ID set to the same value as the JOB_ID of employee 200
- E. Employees 100 and 200 will have the same JOB_ID as before the update command
- F. Employees 100 and 200 will have the same SALARY as before the update command
Answer: C,D
NEW QUESTION 149
Examine the description of the PRODUCT_STATUS table:
The STATUS column contains the values 'IN STOCK' or 'OUT OF STOCK' for each row.
Which two queries will execute successfully?
- A. Option F
- B. Option D
- C. Option B
- D. Option A
- E. Option C
- F. Option E
Answer: C,F
NEW QUESTION 150
Examine the structure of the EMPLOYEES table. (Choose the best answer.)
You must display the details of employees who have manager with MANAGER_ID 100, who were hired in the past 6 months and who have salaries greater than 10000.
- A. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id
(SELECT employee_id FROM employees WHERE employee_id = '100')UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -
1 80INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary >
1 0000; - B. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id
(SELECT employee_id FROM employees WHERE employee_id = 100)UNION
ALL(SELECT last_name, hire_date, salaryFROM employeesWHERE hire_date >
SYSDATE -180INTERSECTSELECT last_name, hire_date, salaryFROM
employeesWHERE salary > 10000); - C. SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALL SELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id
100)INETRSECTSELECT last_name, hire_date, salaryFROM employees WHERE
hire_date > SYSDATE- 180; - D. (SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100))UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180;
Answer: A
NEW QUESTION 151
Examine this statement:
SELECT cust_id, cust^last_ndma "Last Name"
FROM customers
WHERE country_id - 10 UNION
SELECT cust_ID_CUST_NO, cust_last_name
PROM customers
WHERE country__id = 30
Identify three order by clauses, any one of which will complete the query successfully.
- A. ORDER BY 2. cust _id
- B. ORDER BY "CUST^NO
- C. ORDER BY 2,
- D. ORDER BY "Last Name
- E. ORDER BY CUST_NO
Answer: A,C,D
NEW QUESTION 152
Which two statements are true about the COUNT function?
- A. COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column
- B. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.
- C. It can only be used for NUMBER data types.
- D. COUNT(*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column.
- E. COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column.
Answer: A,D
NEW QUESTION 153
Examine these SQL statements that are executed in the given order:
What will be the status of the foreign key EMP_MGR_FK?
- A. It will remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.
- B. It will remain disabled and can be re-enabled manually.
- C. It will be enabled and deferred.
- D. It will be enabled and immediate.
Answer: B
NEW QUESTION 154
Examine the data in the COLORS table:
Examine the data in the BRICKS table:
Which two queries return all the rows from COLORS?
- A. SELECT
EROM colors C
LEET JOIN bricks b
ON b. color_ rgb_ hex value = c. rgb. hex.
value
WHERE b. brick_ id > 0; - B. SELECT
EROM colors C
LEFT JOIN bricks b
USING (rgb _ hex_ value) ; - C. SELECT *
EROM bricks | b
JOIN colors C
ON b. color_ rgb_ hex_ value =c. rgb _hex value; - D. SELECT.
FROM bricks b
RIGHT JOIN colors c
ON b. color _rgb_ hex_ value = c. rgb hex_ value; - E. SELECT
FROM bricks b
FULL JOIN colors C
ON b. color rgb _ hex_ value = c. rgb _hex_ value;
Answer: D,E
NEW QUESTION 155
Which three statements indicate the end of a transaction? (Choose three.)
- A. after a SELECTstatement is issued
- B. after a CREATEstatement is issued
- C. after a COMMITis issued
- D. after a ROLLBACKis issued
- E. after a SAVEPOINTis issued
Answer: B,C,D
NEW QUESTION 156
View the Exhibit and examine the structure in the EMPLOYEES tables.
Evaluate the following SQL statement:
SELECT employee_id, department_id
FROM employees
WHERE department_id= 50 ORDER BY department_id
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id=90
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id=10;
What would be the outcome of the above SQL statement?
- A. The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.
- B. The statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.
- C. The statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.
- D. The statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.
Answer: A
NEW QUESTION 157
Examine this query:
SELECT SUBSTR (SYSDATE,1,5) 'Result' FROM DUAL
Which statement is true?
- A. It executes successfully with an implicit data type conversion
- B. It fails unless the expression is modified to SUBSTR (TO_ CHAR(SYSDATE),1,5)
- C. It fails unless the expression is modified to SUBSTR (TO_ CHAR(TRUNC(SYSDATE)),1,5)
- D. It fails unless the expression is modified to TO-CHAR(SUNBSTR(SYSDATE,1,5)
Answer: A
NEW QUESTION 158
Which two statements are true about the rules of precedence for operators? (Choose two.)
- A. Multiple parentheses can be used to override the default precedence of operators in an expression
- B. NULLS influence the precedence of operators in an expression
- C. The concatenation operator | | is always evaluated before addition and subtraction in an expression
- D. The + binary operator has the highest precedence in an expression in a SQL statement
- E. Arithmetic operators with equal precedence area evaluated from left to right within an expression
Answer: A,D
Explanation:
Explanation/Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14200/operators001.htm
NEW QUESTION 159
Which statements are true regarding primary and foreign key constraints and the effect they can have on table data?
- A. A table can have only one primary key but multiple foreign keys.
- B. Primary key and foreign key constraints can be defined at both the column and table level.
- C. 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.
- D. Only the primary key can be defined the column and table level.
- E. 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.
- F. A table can have only one primary key and one foreign key.
- G. The foreign key columns and parent table primary key columns must have the same names.
Answer: A,B,C,E
NEW QUESTION 160
View the exhibit and examine the structure of the PROMOTIONS table.
You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category.
Which query would give you the required output?
- A. SELECT promo_name, promo_begin_date FROM promotionsWHERE
promo_begin_date IN (SELECT promo_begin_dateFROM promotionsWHERE
promo_category= 'INTERNET'); - B. SELECT promo_name, promo_begin_date FROM promotionsWHERE
promo_begin_date> ALL (SELECT MAX (promo_begin_date)FROM promotions)
ANDpromo_category= 'INTERNET'; - C. SELECT promo_name, promo_begin_date FROM promotionsWHERE
promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE
promo_category = 'INTERNET'); - D. SELECT promo_name, promo_begin_date FROM promotionsWHERE
promo_begin_date> ANY (SELECT promo_begin_dateFROM promotionsWHERE
promo_category= 'INTERNET');
Answer: C
NEW QUESTION 161
Which statement is true about TRUNCATEand DELETE?
- A. For tables with multiple indexes and triggers DELETEis faster than TRUNCATE.
- B. You can never DELETE rows from a table if foreign key constraints will be violated.
- C. For large tables TRUNCATEis faster than DELETE.
- D. You can never TRUNCATEa table if foreign key constraints will be violated.
Answer: C
Explanation:
Explanation/Reference: https://www.sqlservercentral.com/articles/difference-between-truncate-and-delete
NEW QUESTION 162
View the exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.
Evaluate the following MERGE statement:
MERGE_INTO orders_master o
USING monthly_orders m
ON (o.order_id = m.order_id)
WHEN MATCHED THEN
UPDATE SET o.order_total = m.order_total
DELETE WHERE (m.order_total IS NULL)
WHEN NOT MATCHED THEN
INSERT VALUES (m.order_id, m.order_total);
What would be the outcome of the above statement?
- A. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.
- B. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.
- C. The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.
- D. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and 4.
Answer: B
Explanation:
Explanation
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm
NEW QUESTION 163
Examine the description of the EMPLOYEEStable:
Which query is valid?
SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;
- A. join_date;
SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id; - B.
- C. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id,
- D. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;
Answer: A
NEW QUESTION 164
......
Get ready to pass the 1z0-071 Exam right now using our Oracle PL/SQL Developer Certified Associate Exam Package: https://www.actualpdf.com/1z0-071_exam-dumps.html
A fully updated 2021 1z0-071 Exam Dumps exam guide from training expert ActualPDF: https://drive.google.com/open?id=1rqgImwrFut2oS7y0KmB6Fr8jv2bZuy6i
