1z0-071 Premium Exam Engine - Download Free PDF Questions
Instant Download 1z0-071 Free Updated Test Dumps
NEW QUESTION # 58
View the Exhibit and examine the data in EMP and DEPT tables.
In the DEPT table, DEPTNO is the PRIMARY KEY.
In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing the DEPTNO column in the DEPT table.
What would be the outcome of the following statements executed in the given sequence?
DROP TABLE emp;
FLASHBACK TABLE emp TO BEFORE DROP;
INSERT INTO emp VALUES (2, 'SCOTT', 10);
INSERT INTO emp VALUES (3, 'KING', 55);
- A. Both the INSERT statements would succeed because none of the constraints on the table are automatically retrieved when the table is flashed back.
- B. Only the SECOND INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back.
- C. Both the INSERT statements would fail because the constraints are automatically retrieved when the table is flashed back.
- D. Only the first INSERT statement would succeed because all constraints except the primary key constraint are automatically retrieved after a table is flashed back.
Answer: B
NEW QUESTION # 59
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 results in an error because the ORDER BYclause should be the last clause.
- B. It displays 5 percent of the products with the highest amount sold.
- C. It displays 5 percent of the products with the lowest amount sold.
- D. It displays the first 5 percent of the rows from the SALEStable.
Answer: C
Explanation:
Explanation
Explanation/Reference:
References:
https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1
NEW QUESTION # 60
Which three statements are true regarding the SQL WHERE and HAVING clauses?
- A. The HAVING clause conditions can have aggregating functions.
- B. The HAVING clause is used to exclude one or more aggregated results after grouping data.
- C. The HAVING clause conditions can use aliases for the columns.
- D. The WHERE and HAVING clauses cannot be used together in a SQL statement.
- E. The WHERE clause is used to exclude rows before grouping data.
Answer: A,B,E
NEW QUESTION # 61
Examine the description of the EMPLOYEES table:
NLS_DATE FORMAT is DD-MON-RR.
Which two queries will execute successfully?
- A. SELECT AVG(MAX(salary)) FROM employees GROUP BY salary;
- B. SELECT dept id, MAX (SUM(salary)) FROM employees GROUP BY dept_id;
- C. SELECT dept_ id, AVG(MAX(salary)) FROM employees GROUP BY dept_id, salary;
- D. SELECT dept_ iD, sum(salary) FROM employees WHERE hire_date > '01-JAN-9' GROUP BY dept_id;
- E. SELECT dept_ id, AVG (MAX(salary)) FROM employees GROUP By dept_id HAVING hire_date> ' O1-JAN-19';
Answer: A,D
NEW QUESTION # 62
View the Exhibit and examine the structure of the stores table.
You want to display the name of the store along with the address, START_DATE, PROPERTV_PRICE, and the projected property price, which is 115% of the property price. The stores displayed must have START_DATE in the range of 36 months starting from 01Jan-2000 and above.
Which SQL statement would get the desired output?
A)
B)
C)
D)
- A. Option A
- B. Option D
- C. Option C
- D. Option B
Answer: C
NEW QUESTION # 63
Examine the description of the EMPLOYEES table:
Which two queries return the highest salary in the table?
- A. SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id; - B. SELECT MAX (salary)
FROM employees
GROUP BY department_id
HAVING MAX (salary) = MAX (MAX (salary)); - C. SELECT MAX (salary)
FROM employees; - D. SELECT MAX (MAX (salary))
FROM employees
GROUP BY department_id; - E. SELECT MAX (salary)
FROM employees
GROUP BY department_id;
Answer: C,D
NEW QUESTION # 64
Which three statements are true regarding the usage of the WITH clause in complex correlated subqueries: (Choose three.)
- A. The WITH clause can hold more than one query.
- B. It can be used only with the SELECT clause.
- C. The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block
- D. If the query block name and the table name are the same, then the table name takes precedence.
Answer: A,B,C
NEW QUESTION # 65
Which two tasks can be performed by using Oracle SQL statements? (Choose two.)
- A. querying data from tables in different databases
- B. connecting to a database instance
- C. changing the password for an existing database user
- D. executing operating system (OS) commands in a session
- E. starting up a database instance
Answer: A,C
Explanation:
References:
http://www.techonthenet.com/oracle/password.php
https://docs.oracle.com/cd/B28359_01/server.111/b28324/tdpii_distdbs.htm
NEW QUESTION # 66
You need to list the employees in DEPARTMENT_ID 20 days in a single row, ordered by HIRE_DATE.
Examine the sample output:
Which query will provide the required output?
- A. SELECT LISTAGG(last_name, '; ')WITHIN GROUP ORDER BY (hire_date) "Emp_list", MIN(hire_date) "Earliest"FROM employeesWHERE department_id = 30;
- B. SELECT LISTAGG(last_name)WITHIN GROUP ORDER BY (hire_date) "Emp_list", MIN(hire_date) "Earliest"FROM employeesWHERE department_id = 30;
- C. SELECT LISTAGG(last_name, '; ') "Emp_list", MIN(hire_date) "Earliest"FROM employeesWHERE department_id = 30;ORDER BY (hire_date);
- D. SELECT LISTAGG(last_name, '; ') "Emp_list", MIN(hire_date) "Earliest"FROM employeesWHERE department_id = 30;WITHIN GROUP ORDER BY (hire_date);
Answer: A
NEW QUESTION # 67
Evaluate the following SQL statement:
SQL> select cust_id, cust_last_name "Last name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30
Identify three ORDER BY clauses either one of which can complete the query.
- A. ORDER BY "CUST_NO"
- B. ORDER BY CUST_NO
- C. ORDER BY 2, cust_id
- D. ORDER BY "Last name"
- E. ORDER BY 2, 1
Answer: C,D,E
Explanation:
Explanation
Using the ORDER BY Clause in Set Operations
-The ORDER BY clause can appear only once at the end of the compound query.
-Component queries cannot have individual ORDER BY clauses.
-The ORDER BY clause recognizes only the columns of the first SELECT query.
-By default, the first column of the first SELECT query is used to sort the output in an ascending order.
NEW QUESTION # 68
Which three statements are true about GLOBAL TEMPORARY TABLES? (Choose three.)
- A. A TRUNCATEcommand issued in a session causes all rows in a GLOBAL TEMPORARY TABLEfor the issuing session to be deleted.
- B. GLOBAL TEMPORARY TABLEspace allocation occurs at session start.
- C. A GLOBAL TEMPORARY TABLE'sdefinition is available to multiple sessions.
- D. Any GLOBAL TEMPORARY TABLErows existing at session termination will be deleted.
- E. A DELETE command on a GLOBAL TEMPORARY TABLEcannot be rolled back.
- F. GLOBAL TEMPORARY TABLErows inserted by a session are available to any other session whose user has been granted select on the table.
Answer: B,E,F
NEW QUESTION # 69
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 100 will have SALARY set to the same value as the SALARY of employee 200
- B. Employee 200 will have SALARY set to the same value as the SALARY of employee 100
- C. Employees 100 and 200 will have the same SALARY as before the update command
- D. Employees 100 and 200 will have the same JOB_ID as before the update command
- E. Employees 100 will have JOB_ID set to the same value as the JOB_ID of employee 200
- F. Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100
Answer: A,E
NEW QUESTION # 70
View the Exhibit and examine the structure of the CUSTOMERS table.
Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?
- A. It executes successfully.
- B. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.
- C. It returns an error because the BETWEEN operator cannot be used in the HAVING clause.
- D. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.
Answer: A
NEW QUESTION # 71
View the Exhibit and examine the structure of the PROMOTIONS table.
Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?
- A. It shows COST_REMARKfor all the promos in the promo category 'TV'.
- B. It produces an error because subqueries cannot be used with the CASEexpression.
- C. It shows COST_REMARKfor all the promos in the table.
- D. It produces an error because the subquery gives an error.
Answer: C
NEW QUESTION # 72
The PRODUCTS table has the following structure.
Evaluate the following two SQL statements:
SQL>SELECT prod_id, NVL2 (prod_expiry_date, prod_expiry_date + 15, ' ') FROM products;
SQL>SELECT prod_id, NVL (prod_expiry_date, prod_expiry_date + 15) FROM products;
Which statement is true regarding the outcome?
- A. Only the second SQL statement executes successfully
- B. Both the statements execute and give the same result
- C. Only the first SQL statement executes successfully
Using the NVL2 Function
The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.
Syntax
NVL2(expr1, expr2, expr3)
In the syntax:
Expr1 is the source value or expression that may contain a null
Expr2 is the value that is returned if expr1 is not null
Expr3 is the value that is returned if expr1 is null - D. Both the statements execute and give different results
Answer: D
NEW QUESTION # 73
Whith three statements are true about built in data types?
- A. The default length for a CHAR column is always one character
- B. A BFILE stores unstructured binary data in operating systerm files
- C. A CHAR column definition does not require the length to be specified
- D. A VARCHAR2 column definition does not require the length to be specified
- E. A BLOB stores unstructured binary data within the database
- F. A VARCHAR2 blank pads column values only if the data stored is non numeric and contains no special characlers
Answer: A,B,E
NEW QUESTION # 74
Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database?
- A. A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row.
- B. The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC).
- C. The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC).
- D. A TIMESTAMP data type column contains information about year, month, and day.
- E. The CURRENT_TIMESTAMP function returns data without time zone information.
Answer: B,C,E
NEW QUESTION # 75
The following are the steps for a correlated subquery, listed in random order:
1. The WHEREclause of the outer query is evaluated.
2. The candidate row is fetched from the table specified in the outer query.
3. The procedure is repeated for the subsequent rows of the table, till all the rows are processed.
4. Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query.
Identify the option that contains the steps in the correct sequence in which the Oracle server evaluates a correlated subquery.
- A. 2, 4, 1, 3
- B. 2, 1, 4, 3
- C. 4, 2, 1, 3
- D. 4, 1, 2, 3
Answer: A
Explanation:
Explanation/Reference:
References:
http://rajanimohanty.blogspot.co.uk/2014/01/correlated-subquery.html
NEW QUESTION # 76
You execute this command:
Which two are true?
- A. It always retains the space used by the removed rows.
- B. It retains the integrity constraints defined on the table.
- C. A FLASHBACK TABLE statement can be used to retrieve the deleted data.
- D. It retains the indexes defined on the table.
- E. A ROLLBACK statement can be used to retrieve the deleted data.
- F. It drops any triggers defined on the table.
Answer: B,D
NEW QUESTION # 77
View the Exhibit and examine the structure of the PRODUCT_INFORMATION table. (Choose the best answer.)
PRODUCT_ID column is the primary key.
You create an index using this command:
SQL > CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));
No other indexes exist on the PRODUCT_INFORMATION table.
Which query would use the UPPER_NAME_IDX index?
- A. SELECT product_id, UPPER(product_name)
FROM product_information
WHERE UPPER(product_name) = 'LASERPRO' OR list_price > 1000; - B. SELECT UPPER(product_name)
FROM product_information; - C. SELECT product_id
FROM product_information
WHERE UPPER(product_name) IN ('LASERPRO', 'CABLE'); - D. SELECT UPPER(product_name)
FROM product_information
WHERE product_id = 2254;
Answer: C
NEW QUESTION # 78
View the Exhibit and examine the structure of ORDERSand ORDER_ITEMStables.
ORDER_IDis the primary key in the ORDERStable. It is also the foreign key in the ORDER_ITEMStable wherein it is created with the ON DELETE CASCADEoption.
Which DELETEstatement would execute successfully?
- A. DELETE orders o, order_items I
WHERE o.order_id = i.order_id; - B. DELETE
FROM orders
WHERE (SELECT order_id
FROM order_items); - C. DELETE orders
WHERE order_total < 1000; - D. DELETE order_id
FROM orders
WHERE order_total < 1000;
Answer: B
NEW QUESTION # 79
......
Free 1z0-071 Exam Braindumps Oracle Pratice Exam: https://www.practicetorrent.com/1z0-071-practice-exam-torrent.html
Valid 1z0-071 FREE EXAM DUMPS QUESTIONS & ANSWERS: https://drive.google.com/open?id=1FC55098a2avcbZJLjMICaeRGlyQdA-tH