Tested Material Used To 1z1-071 Test Engine Exam Questions in here [Nov-2021]
Penetration testers simulate 1z1-071 exam PDF
NEW QUESTION 119
You want to display 5 percent of the rows from the SALES table for products with the lowest AMOUNT_SOLD and also want to include the rows that have the same AMOUNT_SOLD even if this causes the output to exceed 5 percent of the rows.
Which query will provide the required result?
- A. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS WITH TIES;
- B. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS ONLY WITH TIES;
- C. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS WITH TIES ONLY;
- D. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS ONLY;
Answer: A
NEW QUESTION 120
View the exhibit and examine the description of the EMPLOYEES table. (Choose two.)
You executed this SQL statement:
SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, first_name, salary desc;
Which two statements are true regarding the result? (Choose two.)
- A. The values in the FIRST_NAME column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
- B. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.
- C. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
- D. The values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column.
- E. The values in all columns would be returned in descending order.
Answer: B,D
NEW QUESTION 121
View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose two.)
In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You must list each product ID and the number of times it has been sold.
Examine this query which is missing a JOIN operator:
Which two JOIN operations can be used to obtain the required output? (Choose two.)
- A. RIGHT OUTER JOIN
- B. FULL OUTER JOIN
- C. LEFT OUTER JOIN
- D. JOIN
Answer: B,C
NEW QUESTION 122
Which two statements are true about substitution variables?
- A. A substitution variable used to prompt for a column name must be endorsed in single quotation marks.
- B. A substitution variable can be used only in a SELECT statement.
- C. A substitution variable can be used with any clause in a SELECT statement.
- D. A substitution variable prefixed with & always prompts only once for a value in a session.
- E. A substitution variable used to prompt for a column name must be endorsed in double quotation marks.
- F. A substitution variable prefixed with 6 prompts only once for a value in a session unless is set to undefined in the session.
Answer: C,F
NEW QUESTION 123
Which statement is true regarding external tables?
- A. The default REJECT LIMIT for external tables is UNLIMITED.
- B. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.
- C. The data and metadata for an external table are stored outside the database.
- D. The CREATE TABLE AS SELECT statement can be used to upload data into a normal table in the database from an external table.
Answer: D
Explanation:
References:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables013.htm
NEW QUESTION 124
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. SELECT prod_id ||q'(' s not available)' 'CURRENT AVAILABILITY' FROM
product_ status WHERE status = 'OUT OF STOCK' - B. SELECT prod_id q's not available" from product_ status WHERE status = 'OUT OF STOCK';
- C. SELECT PROD_ID||q'('s not available)' FROM
product_ status WHERE status = 'OUT OF STOCK'; - D. SELECT PROD_ID||q'('s not available)' "CURRENT AVAILABILITY"
FROM product_ status WHERE status = 'OUT OF STOCK'; - E. SELECT prod_id "CURRENT AVAILABILITY"||q' ('s not available)' from product_ status WHERE status
= 'OUT OF STOCK'; - F. SELECT prod_id ||q"' s not available" FROM
product_ status WHERE status = 'OUT OF STOCK';
Answer: C,D
NEW QUESTION 125
Which statement adds a column called SALARYto the EMPLOYEEStable having 100 rows, which cannot contain null?
- A. ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL; - B. ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) DEFAULT NOT NULL; - C. ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) DEFAULT 0 NOT NULL; - D. ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) NOT NULL;
Answer: C
NEW QUESTION 126
View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.)
Examine this SQL statement:
SELECT department_id "DEPT_ID", department_name, 'b' FROM
departments
WHERE departments_id=90
UNION
SELECT department_id, department_name DEPT_NAME, 'a' FROM
departments
WHERE department_id=10
Which two ORDER BY clauses can be used to sort output?
- A. ORDER BY 3;
- B. ORDER BY DEPT_ID;
- C. ORDER BY DEPT_NAME;
- D. ORDER BY 'b';
Answer: A,B
NEW QUESTION 127
View the exhibit and examine the structure of the CUSTOMERStable.
Which two tasks would require subqueries or joins to be executed in a single statement?
- A. finding the average credit limit of male customers residing in 'Tokyo'or 'Sydney'
- B. finding the number of customers, in each city, whose marital status is 'married'.
- C. listing of customers who do not have a credit limit and were born before 1980
- D. listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.
- E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
Answer: D,E
NEW QUESTION 128
Which three statements are true regarding the data types? (Choose three.)
- A. The BLOBdata type column is used to store binary data in an operating system file.
- B. Only one LONGcolumn can be used per table.
- C. A TIMESTAMPdata type column stores only time values with fractional seconds.
- D. The value for a CHARdata type column is blank-padded to the maximum defined column width.
- E. The minimum column width that can be specified for a VARCHAR2data type column is one.
Answer: B,D,E
NEW QUESTION 129
Examine the following query:
What is the output of this query?
- A. It displays 5 percent of the products with the lowest amount sold.
- B. It displays 5 percent of the products with the highest amount sold.
- C. It displays the first 5 percent of the rows from the SALES table.
- D. It results in an error because the ORDER BY clause should be the last clause.
Answer: A
NEW QUESTION 130
Examine the structure of the EMPLOYEES table. (Choose two.)
You must display the maximum and minimum salaries of employees hired 1 year ago.
Which two statements would provide the correct output?
- A. SELECT MIN(Salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary);
- B. SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary);
- C. SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employeesWHERE hire_date < SYSDATE-365)GROUP BY maxsal, minsal;
- D. SELECT MIN(Salary), MAX(salary)FROM (SELECT salary FROM employeesWHERE hire_date < SYSDATE-365);
Answer: C,D
NEW QUESTION 131
Which two statements are true about a self join?
- A. It must be a full outer join.
- B. It can be an inner join.
- C. The join key column must have an index.
- D. It must be an equijoin.
- E. It can be a left outer join.
Answer: B,E
NEW QUESTION 132
View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose two.)
In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You must list each product ID and the number of times it has been sold.
Examine this query which is missing a JOIN operator:
Which two JOIN operations can be used to obtain the required output?
- A. RIGHT OUTER JOIN
- B. FULL OUTER JOIN
- C. LEFT OUTER JOIN
- D. JOIN
Answer: B,C
NEW QUESTION 133
Examine the structure of the MEMBERS table:
Examine the SQL statement:
SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC;
What would be the result execution? (Choose the best answer.)
- A. It fails because a column alias cannot be used in the ORDER BY clause.
- B. It displays all cities in ascending order, within which the last names are further sorted in descending order.
- C. It fails because a column number and a column alias cannot be used together in the ORDER BY clause.
- D. It displays all cities in descending order, within which the last names are further sorted in descending order.
Answer: B
NEW QUESTION 134
Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement?
(Choose all that apply.)
- A. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
- B. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
- C. The WHERE clause can be used to exclude rows before dividing them into groups.
- D. The WHERE clause can be used to exclude rows after dividing them into groups.
- E. The HAVING clause can be used with aggregate functions in subqueries.
Answer: C,E
NEW QUESTION 135
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
There is only one customer with the cust_last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?
- A. INSERT INTO orders (order_id, order_data, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), order_total)VALUES(1, '10-mar-2007', 'direct', &&customer_id, 1000);
- B. INSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_idAND c.cust_last_name='Roberts' AND c.credit_limit=600 )VALUES (1, '10-mar-2007', 'direct', (SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);
- C. INSERT INTO ordersVALUES(1, '10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);
- D. INSERT INTO orders (order_id, order_data, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), order_total)VALUES (1, '10-mar-2007', 'direct', &customer_id, 1000).
Answer: C
NEW QUESTION 136
Examine the structure of the BOOKS_TRANSACTIONS table:
You want to display the member IDs, due date, and late fee as $2 for all transactions.
Which SQL statement must you execute?
- A. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE
FROM BOOKS_TRANSACTIONS; - B. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS;
- C. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;
- D. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS;
Answer: B
NEW QUESTION 137
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 true, then the row would be evaluated by the subsequent WHEN clauses.
- B. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.
- C. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.
- D. The insert statement would give an error because the ELSE clause is not present for support in case none of WHEN clauses are true.
Answer: C
Explanation:
Explanation
References:
http://psoug.org/definition/WHEN.htm
NEW QUESTION 138
Which three statements are true regarding subqueries?
- A. Only one column or expression can be compared between the main query and subquery.
- B. Subqueries can contain ORDER BYbut not the GROUP BYclause.
- C. Main query and subquery must get data from the same tables.
- D. Multiple columns or expressions can be compared between the main query and subquery.
- E. Subqueries can contain GROUP BY and ORDER BYclauses.
- F. Main query and subquery can get data from different tables.
Answer: D,E,F
Explanation:
Explanation/Reference:
References:
http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html
NEW QUESTION 139
Examine the description of the CUSTOMERStable:
Which two SELECTstatements will return these results (Choose two.):
SELECT customer_name FROM customers WHERE customer_name = '*Ma*';
- A. SELECT customer_name FROM customers WHERE customer_name LIKE '%a%';
- B.
- C. SELECT customer_name FROM customers WHERE customer_name LIKE '*Ma*';
- D. SELECT customer_name FROM customers WHERE UPPER (customer_name) LIKE 'MA%';
- E. SELECT customer_name FROM customers WHERE UPPER (customer_name) LIKE 'MA*';
- F. SELECT customer_name FROM customers WHERE customer_name LIKE 'Ma%';
- G. SELECT customer_name FROM customers WHERE customer_name LIKE 'Ma*';
Answer: D,F
Explanation:
You can use the UPPER function to perform a case-insensitive match, as in this condition:
UPPER(last_name) LIKE 'SM%'
Reference: https://docs.oracle.com/cd/B12037_01/server.101/b10759/conditions016.htm
NEW QUESTION 140
Which task can be performed by using a single Data Manipulation Language (DML) statement?
- A. Removing all data from a single column on which a unique constraint is defined.
- B. Adding a column with a default value while inserting a row into a table.
- C. Adding a column constraint while inserting a row into a table.
- D. Removing all data only from a single column on which a primary key constraint is defined.
Answer: D
NEW QUESTION 141
The following are the steps for a correlated subquery, listed in random order:
* The WHERE clause of the outer query is evaluated.
* The candidate row is fetched from the table specified in the outer query.
* This is repeated for the subsequent rows of the table, till all the rows are processed.
* Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query.
Which is the correct sequence in which the Oracle server evaluates a correlated subquery?
- A. 2, 4, 1, 3
- B. 4, 2, 1, 3
- C. 2, 1, 4, 3
- D. 4, 1, 2, 3
Answer: A
Explanation:
Explanation
References:
http://rajanimohanty.blogspot.co.uk/2014/01/correlated-subquery.html
NEW QUESTION 142
Which two statements are true regarding the WHERE and HAVING clauses in a SELECT statement?
(Choose two.)
- A. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
- B. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
- C. The WHERE clause can be used to exclude rows before dividing them into groups.
- D. The WHERE clause can be used to exclude rows after dividing them into groups.
- E. The HAVING clause can be used with aggregate functions in subqueries.
Answer: D,E
NEW QUESTION 143
......
Authentic Best resources for 1z1-071 Online Practice Exam: https://www.passsureexam.com/1z1-071-pass4sure-exam-dumps.html
Get the superior quality 1z1-071 Dumps with explanations waiting just for you, get it now: https://drive.google.com/open?id=1KxVep3nR0WBaNpqe-nAJZZ4FL8VAOazL