Latest [Oct 29, 2024] Realistic Verified 1z0-071 Dumps
Pass Oracle 1z0-071 Exam Updated 323 Questions
NEW QUESTION # 69
Examine the description of the PROMOTIONS TABLE:
You want to display the unique is promotion costs in each promotion category.
Which two queries can be used?
- A. SELECT DISTINCT promo_cost ||'in'IIDISTINCT promo_category promotions ORDER BY1;
- B. SELECT promo_cost, promo_category FROM promotions ORDER BY 1
- C. SELECT promo_category, DISTINCT promo_cost FROM promotiong ORDER BY 2:
- D. select DISTINCT promo_categoryIl 'has'||promol_cost as COSTS FROM promotions ORDER BY 1:
- E. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
Answer: D,E
NEW QUESTION # 70
Evaluate the following statement.
INSERT ALL
WHEN order_total < 10000 THEN
INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN
INTO medium_orders
WHEN order_total > 200000 AND order_total < 20000 THEN
INTO large_orders
SELECT order_id, order_total, customer_id
FROM orders;
Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?
- A. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.
- B. The insert statement would give an error because the ELSE clause is not present for support in case none of WHEN clauses are true.
- C. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.
- D. They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
Answer: C
Explanation:
References:
http://psoug.org/definition/WHEN.htm
NEW QUESTION # 71
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 # 72
SCOTT is a user in the database.
Evaluate the commands issued by the DBA:
Which statement is true regarding the execution of the above commands?
- A. Statement 1 would not execute because the IDENTIFIED BY <password> clause is missing.
- B. Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement.
- C. Statement 1 would not execute because the WITH GRANT option is missing.
- D. Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command.
Answer: D
NEW QUESTION # 73
Evaluate the following SELECT statement and view the exhibit to examine its output:
SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status, FROM user_constraints WHERE table_name = 'ORDERS'; CONSTRAINT_NAME CON SEARCH_CONDITION R_CONSTRAINT_NAME DELETE_RULE STATUS
ORDER_DATE_NN
C
"ORDER_DATE" IS NOT NULL
ENABLED
ORDER_CUSTOMER_ID_NN
C
"CUSTOMER_ID" IS NOT NULL
ENABLED
ORDER_MODE_LOV
C
order _mode in ('direct', 'online')
ENABLED
ORDER TOTAL MIN
C
order total >= 0
ENABLED
ORDER PK
P
ENABLED
ORDERS CUSTOMER ID
R
CUSTOMERS ID
SET NULL
ENABLED
ORDERS SALES REP
R
EMP EMP ID
SET NULL
ENABLED
Which two statements are true about the output? (Choose two.)
- A. The STATUS column indicates whether the table is currently in use.
- B. In the second column, 'c' indicates a check constraint.
- C. The R_CONSTRAINT_NAME column gives the alternative name for the constraint.
- D. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.
Answer: B,D
NEW QUESTION # 74
Use HR has CREATE SESSION, CREATE ANY TABLE and UNLIMITED TABLESPACE privileges.
User SCOTT has CREAT SESSION, CREATE TABLE and UNLIMITED TABLESPACE Privileges HR successfully executes this statement:
Which will execute successfully?
- A. 2 and 3 only
- B. 1 only
- C. 2, 3 and 4
- D. 1,2 and 3
Answer: A
NEW QUESTION # 75
Which two are true?
- A. CONCAT joins two or more character strings together.
- B. INSTR finds the offset within a string of a single character only.
- C. CONCAT joins two character strings together.
- D. FLOOR returns the largest positive integer less than or equal to a specified number.
- E. INSTR finds the offset within a character string, starting from position 0.
- F. FLOOR returns the largest integer less than or equal to a specified number.
Answer: C,F
NEW QUESTION # 76
Examine the commands used to create DEPARTMENT_DETAILSand COURSE_DETAILS:
SQL>CREATE TABLE DEPARTMENT_DETAILS
( DEPARTMENT_ID NUMBER PRIMARY KEY,
DEPARTMENT_NAME VARCHAR2(50),
HOD VARCHAR2(50));
SQL>CREATE TABLE COURSE_DETAILS
(COURSE_ID NUMBER PRIMARY KEY,
COURSE_NAME VARCHAR2(50),
DEPARTMENT_ID VARCHAR2(50));
You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.
Which SQL statement must you use?
- A. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c. department_id);
- B. SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d. department_id);
- C. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c. department_id);
- D. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=d. department_id);
Answer: C
NEW QUESTION # 77
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_no BETWEEN '101' AND '110' : uses implicit conversion
- B. inv_date = '15-february-2008' :uses implicit conversion
- C. inv_date > '01-02-2008' : uses implicit conversion
- D. CONCAT(inv_amt, inv_date) : requires explicit conversion
- E. inv_amt = '0255982' : requires explicit conversion
Answer: A,B
NEW QUESTION # 78 
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, TRUNC( (SYSDATE - hire_ date) 1 7) AS tenure
FROM employees
WHERE department_ id = 90
ORDER BY tenure ; - B. SELECT last_name, ROUND( (SYSDATE - hire_ date) 17) AS tenure
FROM employees
WHERE department_ id = 90
ORDER BY tenure DESC; - C. SELECT last_name, TRUNC ( (SYSDATE - - hire_ date) 1 7) AS tenure
FROM employees
WHERE department_id = 90
ORDER BY tenure DESC; - D. SELECT last_name, ROUND( (SYSDATE - hire_ date) 1 7) AS tenure
FROM employees
WHERE department_ id = 90
ORDER BY tenure ;
Answer: C
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 # 79
View the Exhibit and examine, the description for the SALES and CHANNELS tables. (Choose the best answer.)
You issued this SQL statement:
INSERT INTO SALES VALUES (23, 2300, SYSDATE,
(SELECT CAHNNEL_ID
FROM CHANNELS
WHERE CHANNEL_DESC='DIRECT SALES'), 12, 1, 500);
Which statement is true regarding the result?
- A. The statement will fail because a subquery cannot be used in a VALUES clause.
- B. The statement will fail because the VALUES clause is not required with the subquery.
- C. The statement will fail because the sub-query in the VALUES clause is not enclosed within single quotation marks.
- D. The statement will execute and a new row will be inserted in the SALES table.
Answer: D
NEW QUESTION # 80
Examine these statements which execute successfully:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYY HH24 :MT:SS;'
ALTER SESSION SET TIME-ZONE = '-5:00;:
Examine the result:
If LOCALTIMESTAMP was selected at the same time, what would it return?
- A. 11-JUL-2019 6.00.00.00000000 AM
- B. 11-JUL-2019 11.00.00.00000000 AM
- C. 11-JUL_2019 6.00.00.00000000 AM -05:00
- D. 11-JUL_2019 11.00.00.00000000 AM -05:00
Answer: A
NEW QUESTION # 81
Examine the description or the BOOKS_TRANSACTIONS table:
FOR customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed.
Which query should be used?
- A. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level !=NULL
AND cust credit_level !=NULL; - B. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level IS NOT NULL
AND due_amount IS NOT NULL; - C. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level <> NULL
AND due_amount <> NULL; - D. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level !=NULL
AND due_amount !=NULL; - E. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust_income_level IS NOT NULL
AND cust_credit_limit IS NOT NULL;
Answer: E
NEW QUESTION # 82
Which three statements are true about defining relations between tables in a relational database?
- A. Every foreign key value must refer to a matching primary or unique key value.
- B. Primary key columns allow null values.
- C. Unique key columns allow null values
- D. Every primary or unique key value must refer to a matching foreign key value.
- E. Foreign key columns allow null values.
Answer: A,C,E
NEW QUESTION # 83
Examine the description of the SALES table:
The SALES table has 55,000 rows.
Examine this statement:
Which two statements are true?
- A. SALES1 is created with 55,000 rows.
- B. SALES1 has PRIMARY KEY and UNIQUE constraints on any selected columns which had those constraints in the SALES table.
- C. SALES1 is created with no rows.
- D. SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table.
- E. SALES1 is created with 1 row.
Answer: C,D
NEW QUESTION # 84
Which two statements are true about the WHERE and HAVING clauses in a SELECT statement?
- A. Aggregating functions and columns used in HAVING clauses must be specified in these SELECT list of a query.
- B. The HAVING clause can be used with aggregating functions in subqueries.
- C. WHERE and HAVING clauses can be used in the same statement only if applied to different table columns.
- D. The WHERE clause can be used to exclude rows before dividing them into groups.
- E. The WHERE clause can be used to exclude rows after dividing them into groups
Answer: B,D
NEW QUESTION # 85
Which three statements are true? (Choose three.)
- A. The data dictionary views consist of joins of dictionary base tables and user-defined tables.
- B. The USER_CONS_COLUMNSview should be queried to find the names of columns to which constraints apply.
- C. Views with the same name but different prefixes, such as DBA, ALLand USER, reference the same base tables from the data dictionary.
- D. The data dictionary is created and maintained by the database administrator.
- E. Both USER_OBJECTSand CATviews provide the same information about all objects that are owned by the user.
- F. The usernames of all the users including database administrators are stored in the data dictionary.
Answer: B,C,F
Explanation:
Explanation
Explanation/Reference:
References:
https://docs.oracle.com/cd/B10501_01/server.920/a96524/c05dicti.htm
NEW QUESTION # 86
Examine the ORDER _ITEms table:
Which two queries return rows where QUANTITY is a multiple of ten?
- A. SELECT * FROM order_ items WHERE quantity = TRUNC (quantity, -1);
- B. SELECT FROM order_ items WHERE quantity / 10 = TRUNC (quantity);
- C. SELECT * FROM order_ items WHERE MOD (quantity, 10) = 0;
- D. SELECT" FROM order_ items WHERE FLOOR (quantity / 10) = TRUNC (quantity / 10);
- E. SELECT" FROM order_ _items WHERE quantity = ROUND (quantity, 1);
Answer: C,D
Explanation:
* A: This statement will not work because TRUNC(quantity, -1) will truncate the quantity to zero decimal places when the number is a negative power of ten, which does not guarantee that the quantity is a multiple of ten.
* B: This statement is correct. The MOD function returns the remainder of a division. If MOD(quantity,
10) equals zero, it means that quantity is a multiple of ten.
* C: This statement is correct. FLOOR(quantity / 10) equals TRUNC(quantity / 10) when quantity is an exact multiple of ten, as FLOOR returns the largest integer less than or equal to a number, and TRUNC will truncate the decimal part.
* D: This statement is incorrect because TRUNC(quantity) would remove any decimal places from quantity, and comparing it to quantity / 10 will not guarantee that the quantity is a multiple of ten.
* E: This statement is incorrect. ROUND(quantity, 1) will round the quantity to one decimal place, not check if it's a multiple of ten.
NEW QUESTION # 87
Examine the description of the BOOKS table:
The table has 100 rows.
Examine this sequence of statements issued in a new session:
Which two statements are true?
- A. The second ROLLBACK command replays the delete.
- B. The second ROLLBACK command does nothing.
- C. The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row.
- D. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed.
- E. The second ROLLBACK command undoes the insert.
Answer: E
NEW QUESTION # 88
......
Get 2024 Updated Free Oracle 1z0-071 Exam Questions and Answer: https://www.pass4leader.com/Oracle/1z0-071-exam.html
1z0-071 Dumps PDF and Test Engine Exam Questions: https://drive.google.com/open?id=1dPx0pMcMoygS5OB4esNaY5P8DgA9jRxy