1z1-071 Tested & Approved Oracle PL/SQL Developer Certified Associate Study Materials
Validate your Skills with Updated Oracle PL/SQL Developer Certified Associate Exam Questions & Answers and Test Engine
Oracle Database SQL exam is a multiple-choice exam consisting of 73 questions that must be completed within 100 minutes. 1z1-071 exam is available in English and Japanese languages. 1z1-071 exam covers a wide range of topics, including data manipulation, database design, database objects, and more. 1z1-071 exam is designed to test your knowledge of SQL and your ability to apply it to real-world scenarios.
NEW QUESTION # 20
Examine the structure of the ORDERS table: (Choose the best answer.)
You want to find the total value of all the orders for each year and issue this command:
SQL> SELECT TO_CHAR(order_date,'rr'), SUM(order_total) FROM orders
GROUP BY TO_CHAR(order_date, 'yyyy');
Which statement is true regarding the result?
- A. It return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.
- B. It executes successfully but gives the correct output.
- C. It returns an error because the TO_CHAR function is not valid.
- D. It executes successfully but does not give the correct output.
Answer: A
NEW QUESTION # 21
Which two statements are true about sequences created in a single instance database? (Choose two.)
- A. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted.
- B. The numbers generated by a sequence can be used only for one table.
- C. DELETE <sequencename>would remove a sequence from the database.
- D. When the MAXVALUElimit for the sequence is reached, you can increase the MAXVALUElimit by using the ALTERSEQUENCEstatement.
- E. CURRVALis used to refer to the last sequence number that has been generated.
Answer: D,E
Explanation:
Explanation/Reference:
References:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2012.htm#SQLRF00817
https://docs.oracle.com/cd/A84870_01/doc/server.816/a76989/ch26.htm
NEW QUESTION # 22
In which three situations does a transaction complete? (Choose three.)
- A. when a ROLLBACK command is executed
- B. when a data definition language (DDL) statement is executed
- C. when a DELETE statement is executed
- D. when a PL/SQL anonymous block is executed
- E. when a TRUNCATE statement is executed after the pending transaction
Answer: A,B,E
Explanation:
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm
NEW QUESTION # 23
Examine this statement:
What is returned upon execution?
- A. an error
- B. 0 rows
- C. 1 row
- D. 2 rows
Answer: C
NEW QUESTION # 24
Which statement is true regarding the default behavior of the ORDER BY clause?
- A. Only columns that are specified in the SELECT list can be used in the ORDER BY clause.
- B. In a character sort, the values are case-sensitive.
- C. NULLs are not included in the sort operation.
- D. Numeric values are displayed in descending order if they have decimal positions.
- E. Column aliases can be used in the ORDER BY clause.
Answer: B
Explanation:
Explanation
The ORDER BY clause performs case sensitive sorting with character values.
NEW QUESTION # 25
Which statements are true? (Choose all that apply.)
- A. Views with the same name but different prefixes, such as DBA, ALLand USER, use the same base tables from the data dictionary.
- B. Both USER_OBJECTSand CATviews provide the same information about all the objects that are owned by the user.
- C. The data dictionary is created and maintained by the database administrator.
- D. The data dictionary views consists of joins of dictionary base tables and user-defined tables.
- E. The USER_CONS_COLUMNSview should be queried to find the names of the columns to which a constraint applies.
- F. The usernames of all the users including the database administrators are stored in the data dictionary.
Answer: A,E,F
Explanation:
Explanation/Reference:
References:
https://docs.oracle.com/cd/B10501_01/server.920/a96524/c05dicti.htm
NEW QUESTION # 26
View the Exhibit and examine the structure of the PRODUCT_INFORMATIONand INVENTORIEStables.
You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID,and QUANTITY_ON_HANDfor all the products wherein QUANTITY_ON_HANDis less than five.
Which two SQL statements can accomplish the task? (Choose two.)
SELECT i.product_id, i.quantity_on_hand, pi.supplier_id
- A. FROM product_information pi JOIN inventories i
ON (pi.product_id=i.product_id)
USING (product_id) AND quantity_on_hand < 5; - B. FROM product_information
NATURAL JOIN inventories AND quantity_on_hand < 5;
SELECT i.product_id, i.quantity_on_hand, pi.supplier_id - C. FROM product_information pi JOIN inventories i
ON (pi.product_id=i.product_id)
WHERE quantity_on_hand < 5;
SELECT product_id, quantity_on_hand, supplier_id - D. FROM product_information pi JOIN inventories i
ON (pi.product_id=i.product_id) AND quantity_on_hand < 5;
SELECT i.product_id, i.quantity_on_hand, pi.supplier_id
Answer: C,D
NEW QUESTION # 27
Examine this statement:
What is returned upon execution?
- A. an error
- B. 0 rows
- C. 1 row
- D. 2 rows
Answer: C
NEW QUESTION # 28
Examine this query:
SELECT SUBSTR (SYSDATE,1,5) 'Result' FROM DUAL
Which statement is true?
- A. It fails unless the expression is modified to SUBSTR (TO_ CHAR(TRUNC(SYSDATE)),1,5)
- B. It executes successfully with an implicit data type conversion
- C. It fails unless the expression is modified to SUBSTR (TO_ CHAR(SYSDATE),1,5)
- D. It fails unless the expression is modified to TO-CHAR(SUNBSTR(SYSDATE,1,5)
Answer: B
NEW QUESTION # 29
Which two statements are true regarding constraints? (Choose two.)
- A. A constraint is enforced only for an INSERT operation on a table.
- B. A foreign key cannot contain NULL values.
- C. A column with the UNIQUE constraint can store NULLS.
- D. You can have more than one column in a table as part of a primary key.
Answer: C,D
NEW QUESTION # 30
Examine the description of the TRANSACTIONS table:
Which two SQL statements execute successfully?
- A. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount + 100 DUES FROM transactions;
- B. SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount +100 DUES FROM transactions;
- C. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount + 100 "DUES" FROM transactions;
- D. SELECT customer_id AS 'CUSTOMER-ID', transaction_date AS DATE, amount + 100 'DUES' FROM transactions;
- E. SELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount + 100 "DUES AMOUNT" FROM transactions;
Answer: A,B
NEW QUESTION # 31
View the Exhibit and examine the structures of the employees and departments tables.
You must update the employees table according to these requirements::
-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
-Set department_id for these employees to the department id corresponding to London (locationid 2100).
-Set the employees' salary in iocation_id 2100 to 1.1 times the average salary of their department.
-Set the employees' commission In location_id 2100 to 1.5 times the average commission of their department.
You issue this command:
What is the result?
- A. It executes successfully and produces the desired update.
- B. It generates an error because a subquery cannot have a join condition in an update statement.
- C. It generates an error because multiple columns cannot be specified together in an UPDATE statement.
- D. It executes successfully but does not produce the desired update.
Answer: D
NEW QUESTION # 32
View the exhibit and examine the structure of the EMPLOYEES table.
You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees.
Which SQL statement would you execute?
- A. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eWHERE m.employee_id = e.manager_id and AND e.manager_id = 100
- B. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON m.employee_id = e.manager_idWHERE m.manager_id = 100;
- C. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON m.employee_id = e.manager_idWHERE e.manager_id = 100;
- D. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON e.employee_id = m.manager_idWHERE m.manager_id = 100;
Answer: C
NEW QUESTION # 33
Examine the following query:
SQL> SELECT prod_id, amount_sold
FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS ONLY;
What is the output of this query?
- A. It displays 5 percent of the products with the lowest amount sold.
- B. It displays the first 5 percent of the rows from the SALEStable.
- C. It results in an error because the ORDER BYclause should be the last clause.
- D. It displays 5 percent of the products with the highest amount sold.
Answer: A
Explanation:
Explanation/Reference:
References:
https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1
NEW QUESTION # 34
Examine the structure of the DEPARTMENTS table.
You execute the following command:
Which two statements are true?
- A. Views created in the DEPARTMENTS table that include the COUNTRY column are automatically modified and remain valid.
- B. Synonyms existing of the DEPARTMENTS table would have to be re-created.
- C. Indexes created on the COUNTRY column exist until the DROP UNUSED COLUMNS command is executed.
- D. A new column, COUNTRY, can be added to the DEPARTMENTS table after executing the command.
- E. Unique key constraints defined on the COUNTRY column are removed.
Answer: D,E
NEW QUESTION # 35
View the exhibit and examine the structure in ORDERSand ORDER_ITEMStables.
You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.
Which CREATEVIEWstatement would create the view successfully?
CREATE OR REPLACE VIEW ord_vu
- A. AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date;
CREATE OR REPLACE VIEW ord_vu (order_id, order_date) - B. AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
"NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date;
CREATE OR REPLACE VIEW ord_vu - C. AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) ||
"NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date
WHITH CHECK OPTION; - D. AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
"NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date;
CREATE OR REPLACE VIEW ord_vu
Answer: B
NEW QUESTION # 36
View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables.
Examine this query which must select the employee IDs of all the employees who have held the job SA_MAN at any time during their employment.
SELECT employee_id
FROM employees
WHERE job_id = 'SA_MAN'
-------------------------------------
SELECT employee_id
FROM job_history
WHERE job_id='SA_MAN';
Choose two correct SET operators which would cause the query to return the desired result. (Choose two.)
- A. UNION ALL
- B. INTERSECT
- C. UNION
- D. MINUS
Answer: A,C
NEW QUESTION # 37
Which two statements are true about the rules of precedence for operators? (Choose two.)
- A. The + binary operator has the highest precedence in an expression in a SQL statement
- B. The concatenation operator | | is always evaluated before addition and subtraction in an expression
- C. Multiple parentheses can be used to override the default precedence of operators in an expression
- D. Arithmetic operators with equal precedence area evaluated from left to right within an expression
- E. NULLS influence the precedence of operators in an expression
Answer: C,D
NEW QUESTION # 38
The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE.
You want to display the date of the first Monday after the completion of six month since hiring.
The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day of the week.
Which query can be used?
- A. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;
- B. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 'MONDAY') FROM employees;
- C. SELECT emp_id, NEXT_DAY (MONTHS_BETWEEN (hire_date, SYSDATE), 6) FROM employees;
- D. SELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY('MONDAY') FROM employees;
Answer: B
NEW QUESTION # 39
Examine the structure of the PROMOTIONS table:
Management requires a report of unique promotion costs in each promotion category.
Which query would satisfy this requirement?
- A. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
- B. SELECT DISTINCT promo_cost, promo_category FROM promotions;
- C. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
- D. SELECT promo_category, DISTINCT promo_cost FROM promotions;
Answer: C
NEW QUESTION # 40
......
1z1-071 [Apr-2024] Newly Released] 1z1-071 Exam Questions For You To Pass: https://www.pass4guide.com/1z1-071-exam-guide-torrent.html
For your comfort, Pass4guide provides you the convenience of free Oracle PL/SQL Developer Certified Associate braindumps demo: https://drive.google.com/open?id=1R8kARMCQzMOSUfegUuh6buDSenpCZnE8