2024 Best 1z1-071 Exam Preparation Material with New Dumps Questions
Free 1z1-071 Exam Files Verified & Correct Answers Downloaded Instantly
NEW QUESTION # 145
Evaluate the following SQL statement:
SELECT product_name || 'it's not available for order'
FROM product_information
WHERE product_status = 'obsolete';
You received the following error while executing the above query:
ERROR
ORA-01756: quoted string not properly terminated
What would you do to execute the query successfully?
- A. Remove the single quotation marks enclosing the character literal string in the SELECTclause
- B. Use the escape character to negate the single quotation mark within the literal character string in the SELECTclause
- C. Use the Oracle (q) operator and delimiter to allow the use of a single quotation mark within the literal character string in the SELECTclause
- D. Enclose the character literal string in the SELECT clause within double quotation marks
Answer: C
Explanation:
Explanation/Reference:
References:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm
NEW QUESTION # 146
Which three statements are true about single-row functions? (Choose three.)
- A. They return a single result row per table
- B. They can be used only in the WHERE clause of a SELECT statement
- C. They can be nested to any level
- D. They can accept only one argument
- E. The argument can be a column name, variable, literal or an expression
- F. The data type returned can be different from the data type of the argument
Answer: E,F
Explanation:
Explanation/Reference: https://www.folkstalk.com/2012/01/oracle-single-row-functions-examples.html
NEW QUESTION # 147 
For each employee in department 90 you want to display:
1. their last name
2. the number of complete weeks they have been employed
The output must be sorted by the number of weeks, starting with the longest serving employee first.Which statement will accomplish this?
- A. SELECT last_name, ROUND( (SYSDATE - hire_ date) 1 7) AS tenure
FROM employees
WHERE department_ id = 90
ORDER BY tenure ; - B. SELECT last_name, TRUNC ( (SYSDATE - - hire_ date) 1 7) AS tenure
FROM employees
WHERE department_id = 90
ORDER BY tenure DESC; - C. SELECT last_name, ROUND( (SYSDATE - hire_ date) 17) AS tenure
FROM employees
WHERE department_ id = 90
ORDER BY tenure DESC; - D. SELECT last_name, TRUNC( (SYSDATE - hire_ date) 1 7) AS tenure
FROM employees
WHERE department_ id = 90
ORDER BY tenure ;
Answer: B
Explanation:
D). True. The TRUNC function, when applied to the difference between SYSDATE and hire_date divided by
7, will return the number of complete weeks of employment. The ORDER BY tenure DESC will sort the result in descending order, starting with the longest-serving employee.
A is incorrect because it does not include the DESC keyword necessary to start with the longest-serving employee. B is incorrect because ROUND could result in rounding up to the next week, which does not give the number of complete weeks. C is incorrect because the division is done by 17 instead of 7, which is likely a typo, and ROUND is used instead of TRUNC.
NEW QUESTION # 148
Examine this SELECT statement and view the Exhibit to see its output:
SELECT constraints_name, constraints_type, search_condition, r_constraints_name, delete_rule, status, FROM user_constraints WHERE table_name = 'ORDERS'; Which two statements are true about the output? (Choose two.)
- A. The R_CONSTRAINT_NAME column contains an alternative name for the constraint.
- B. The DELETE_RULE column indicates the desired state of related rows in the child table when the corresponding row is deleted from the parent table.
- C. The STATUS column indicates whether the table is currently in use.
- D. In the second column, 'c' indicates a check constraint.
Answer: B,D
NEW QUESTION # 149
View the Exhibit and examine the description for the SALES and CHANNELS tables. (Choose the best answer.)
You issued this SQL statement:
Which statement is true regarding the result?
- A. The statement will execute and a new row will be inserted in the SALES table.
- B. The statement will fail because a subquery cannot be used in a VALUES clause.
- C. The statement will fail because the VALUES clause is not required with the subquery.
- D. The statement will fail because the subquery in the VALUES clause is not enclosed within single quotation marks.
Answer: A
NEW QUESTION # 150
View the exhibit and examine the description of SALES and PROMOTIONS tables.
You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS table has either blowout sale or everyday low price as values.
Which three DELETE statements are valid? (Choose three.)
- A. DELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'blowout sale')OR promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'everyday low price')
- B. DELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'blowout sale')OR promo_name = 'everyday low price');
- C. DELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promo_name = 'blowout sale')AND promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'everyday low price')FROM promotionsWHERE promo_name = 'everyday low price');
- D. DELETEFROM salesWHERE promo_id IN (SELECT promo_idFROM promotionsWHERE promo_name IN = 'blowout sale','everyday low price'));
Answer: A,B,D
NEW QUESTION # 151
Examine the structure of the EMPLOYEEStable.
There is a parent/child relationship between EMPLOYEE_IDand MANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_IDis 123.
Which query provides the correct output?
- A. SELECT e.last_name, e.manager_id
FROM employees e RIGHT OUTER JOIN employees m
on (e.employee_id = m.employee_id)
WHERE e.employee_id = 123; - B. SELECT e.last_name, m.manager_id
FROM employees e RIGHT OUTER JOIN employees m
on (e.manager_id = m.employee_id)
AND e.employee_id = 123; - C. SELECT m.last_name, e.manager_id
FROM employees e LEFT OUTER JOIN employees m
on (e.manager_id = m.manager_id)
WHERE e.employee_id = 123; - D. SELECT e.last_name, m.manager_id
FROM employees e LEFT OUTER JOIN employees m
on (e.employee_id = m.manager_id)
WHERE e.employee_id = 123;
Answer: C
NEW QUESTION # 152
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 fails because a column number and a column alias cannot be used together in the ORDER BY clause.
- C. It displays all cities in descending order, within which the last names are further sorted in descending order.
- D. It displays all cities in ascending order, within which the last names are further sorted in descending order.
Answer: D
NEW QUESTION # 153
In which three situations does a new transaction always start?
- A. when issuing a TRUNCATE statement after a SELECT statement was issued in the same session
- B. when issuing a CREATE TABLE statement 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 CREATE INDEX statement after a CREATE TABLE statement completed successfully in the same session
- E. when issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued 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: C,E,F
NEW QUESTION # 154
Which two statements are true regarding a SAVEPOINT? (Choose two.)
- A. A SAVEPOINTdoes not issue a COMMIT
- B. Rolling back to a SAVEPOINTcan undo a DELETEstatement
- C. Rolling back to a SAVEPOINTcan undo a CREATE INDEX statement
- D. Only one SAVEPOINTmay be issued in a transaction
- E. Rolling back to a SAVEPOINT can undo a TRUNCATEstatement
Answer: A,B
NEW QUESTION # 155
Examine the structure of the CUSTOMERS table: (Choose two.)
CUSTNO is 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?
- A. Right outer-join with self-join
- B. Full outer-join with self-join
- C. Subquery
- D. Left outer-join with self-join
- E. Self-join
Answer: C,E
NEW QUESTION # 156
View the exhibit and examine the structures of the EMPLOYEESand DEPARTMENTStables.
EMPLOYEES
Name Null? Type
- ---------------- ----- -------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(10,2)
COMMISSION NUMBER(6,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
DEPARTMENTS
Name Null? Type
----------------- ----- -------------
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
You want to update EMPLOYEEStable as follows:
Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
Set department_idfor these employees to the department_idcorresponding to London
(location_id 2100).
Set the employees' salary in location_id2100 to 1.1 times the average salary of their department.
Set the employees' commission in location_id2100 to 1.5 times the average commission of their
department.
You issue the following command:
SQL> UPDATE employees
SET department_id
( SELECT department_id
FROM departments
WHERE location_id = 2100),
( salary, commission)
( SELECT 1.1*AVG(salary), 1.5*AVG(commission)
FROM employees, departments
WHERE departments.location_id IN(2900, 2700, 2100))
WHERE department_id IN
( SELECT department_id
FROM departments
WHERE location_id = 2900
OR location_id = 2700;
What is outcome?
- A. It generates an error because a subquery cannot have a join condition in a UPDATEstatement.
- B. It executes successfully and gives the correct result.
- C. It executes successfully but does not give the correct result.
- D. It generates an error because multiple columns (SALARY, COMMISSION)cannot be specified together in an UPDATEstatement.
Answer: C
NEW QUESTION # 157
Examine the description of the ORDERS table:
Which three statements execute successfully?
- A. SELECT * FROM orders
MINUS
SELECT * FROM INVOICES ORDER BY 1 - B. (SELECT * FROM orders
UNION ALL
SELECT* FROM invoices) ORDER BY order _id; - C. SELECT * FROM orders ORDER BY order_ id
INTERSEOT
SELECT * FROM invoices ORDER BY invoice_ id; - D. SELECE order _id, order _ date FRON orders
LNTERSECT
SELECT invoice_ id, invoice_ id, order_ date FROM orders - E. SELECT order_ id, order_ data FROM orders
UNION ALL
SELECT invoice_ id, invoice_ data FROM invoices ORDER BY order_ id; - F. SELECT * FROM orders ORDER BY order_ id
UNION
SELECT * FROM invoices; - G. SELECT order_ id, invoice_ data order_ date FROM orders
MINUS
SELECT invoice_ id, invoice_ data FROM invoices ORDER BY invoice_ id;
Answer: A,B,E
NEW QUESTION # 158
View the exhibit and examine the description of SALESand PROMOTIONStables.
You want to delete rows from the SALES table, where the PROMO_NAMEcolumn in the PROMOTIONStable has either blowout saleor everyday low priceas values.
Which three DELETEstatements are valid? (Choose three.)
DELETE
- A. FROM sales
WHERE promo_id = (SELECT promo_id
FROM promo_name = 'blowout sale')
AND promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = 'everyday low price')
FROM promotions
WHERE promo_name = 'everyday low price');
DELETE - B. FROM sales
WHERE promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = 'blowout sale')
OR promo_name = 'everyday low price');
DELETE - C. FROM sales
WHERE promo_id IN (SELECT promo_id
FROM promotions
WHERE promo_name IN = 'blowout sale','everyday low price')); - D. FROM sales
WHERE promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = 'blowout sale')
OR promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = 'everyday low price')
DELETE
Answer: B,C,D
NEW QUESTION # 159
Which three actions can you perform by using the ORACLE_DATAPUMP access driver?
- A. Execute DML statements on an external table.
- B. Read data from an external table and load it into a table in the database.
- C. Query data from an external table.
- D. Create a directory object for a flat file.
- E. Create a directory object for an external table.
- F. Read data from a table in the database and insert it into an external table.
Answer: B,F
NEW QUESTION # 160
......
Instant Download 1z1-071 Dumps Q&As Provide PDF&Test Engine: https://endexam.2pass4sure.com/Oracle-PL-SQL-Developer-Certified-Associate/1z1-071-actual-exam-braindumps.html