(2024) PASS 1z1-071 Exam Free Practice Test with 100% Accurate Answers
1z1-071 dumps Free Test Engine Verified By It Certified Experts
NEW QUESTION # 176
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. Option A
- B. Option C
- C. Option B
- D. Option D
- E. Option F
- F. Option E
Answer: C,F
NEW QUESTION # 177
Examine the description of the BOOKS_TRANSACTIONS table:
Examine this partial SQL statement:
SELECT * FROM books_transactions
Which two WHERE conditions give the same result?
- A. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN('A101','A102');
- B. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' AND (member_id = 'A101' OR member_id = 'A102'));
- C. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' AND member_id = 'A101' OR member_id = 'A102');
- D. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN('A101','A102');
- E. WHERE (borrowed_date = SYSDATE AND transaction_type = 'RM') OR member_id IN ('A101','A102');
Answer: D,E
NEW QUESTION # 178
The CUSTOMERStable has a CUST_CREDIT_LIMITcolumn of data type NUMBER.
Which two queries execute successfully? (Choose two.)
- A. Available') FROM customers;
SELECT NVL2(cust_credit_limit * .15, 'Not Available') FROM customers; - B. SLECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
- C. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not
- D. SELECT TO_CHAR(NVL(cust_credit_limit * .15, 'Not Available')) FROM customers;
- E. SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers;
Answer: A,B
NEW QUESTION # 179
View the Exhibit and examine the structure of the ORDERS table.
You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order placed by CUSTOMER_ID 101.
Which query would give you the desired result?
- A. SELECT order_id, order_date FROM ordersWHERE order_date > IN(SELECT order_date FROM orders WHERE customer_id = 101);
- B. SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
- C. SELECT order_id, order_date FROM ordersWHERE order_date >ANY(SELECT order_date FROM orders WHERE customer_id = 101);
- D. SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT order_date FROM orders WHERE customer_id = 101);
Answer: D
NEW QUESTION # 180
Evaluate the following SELECTstatement 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_CONDITI R_CONSTRAINT_NAME DELETE_RULE STATUS ON
ORDER_DATE_NN C "ORDER_DATE" IS ENABLED
NOT NULL
ORDER_CUSTOMER_ID_ C "CUSTOMER_ID" IS ENABLED
NN NOT NULL
ORDER_MODE_LOV C order _mode in ENABLED
( 'direct',
' online')
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 STATUScolumn indicates whether the table is currently in use.
- B. The R_CONSTRAINT_NAMEcolumn gives the alternative name for the constraint.
- C. In the second column, 'c'indicates a check constraint.
- D. The column DELETE_RULEdecides the state of the related rows in the child table when the corresponding row is deleted from the parent table.
Answer: C,D
NEW QUESTION # 181
You issue the following command to drop the PRODUCTS table: (Choose all that apply.) SQL > DROP TABLE products; Which three statements are true about the implication of this command?
- A. All data in the table is deleted but the table structure remains.
- B. A pending transaction in the session is committed.
- C. All data along with the table structure is deleted.
- D. All indexes on the table remain but they are invalidated.
- E. All views and synonyms on the table remain but they are invalidated.
Answer: B,C,E
NEW QUESTION # 182
An Oracle database server session has an uncommitted transaction in progress which updated 5000 rows
in a table.
In which three situations does the transact ion complete thereby committing the updates?
- A. When a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user, then issues a COMMIT
- B. When a CREATE INDEX statement is executed successfully in same session
- C. When a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session
- D. When the session logs out is successfully
- E. When a COMMIT statement is issued by the same user from another session in the same database instance
- F. When a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT
Answer: A,B,D
NEW QUESTION # 183
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 the first 5 percent of the rows from the SALEStable.
- D. It displays 5 percent of the products with the lowest amount sold.
Answer: D
Explanation:
Explanation/Reference:
References:
https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1
NEW QUESTION # 184
The ORDERS table has a column ORDER_DATE of date type DATE The default display format for a date is DD-MON-RR Which two WHERE conditions demonstrate the correct usage of conversion functions?
- A. WHERE TO_CHAR(order_date,'MON DD YYYY') ='JAN 20 2019';
- B. WHERE order_date> TO_DATE('JUL 10 2018','MON DD YYYY');
- C. WHERE order_date> TO_DATE(ADD_MONTHS(SYSDATE,6),'MON DD YYYY');
- D. WHERE order_date IN (TO_DATE ('Oct 21 2018','MON DD YYYY'), TO_CHAR('Nov 21 2018','MON DD YYYY'));
- E. WHERE ordet_date> TO_CHAR(ADD_MONTHS(SYSDATE, 6),'MON DD YYYY')
Answer: A,B
NEW QUESTION # 185
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. Full outer-join with self-join
- B. Left outer-join with self-join
- C. Self-join
- D. Right outer-join with self-join
- E. Subquery
Answer: C,E
NEW QUESTION # 186
Examine the structure of the PROGRAMStable:
Which two SQL statements would execute successfully?
SELECT NVL(ADD_MONTHS(END_DATE,1)SYSDATE)
- A. FROM programs;
SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE)) - B. FROM programs;
SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start-date,end_date)),'Ongoing') - C. FROM programs;
- D. FROM programs;
SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing')
Answer: A,C
NEW QUESTION # 187
Examine the data in the EMPLOYEES table:
Which statement will compute the total annual compensation for each employee?
- A. SELECT last name,
(monthly salary*12) + (monthly_commission_pct * 12) AS
annual comp
FROM employees
; - B. SELECT last_ name (monthly_ salary+ monthly_ commission _ pct) *12 AS annual_ FROM employees ;
- C. SELECT last_ name, monthly_ salary*12) + (monthly_ salary * 12 * Monthly commission _Pct) AS annual_ comp FROM employees;
- D. SELECT last name, (monthly_ salary *12) + (monthly_ salary * 12 * NVL (monthly commission pct,0) ) As annual _ comp FROM employees;
Answer: D
NEW QUESTION # 188
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 generates an error because multiple columns cannot be specified together in an UPDATE statement.
- B. It generates an error because a subquery cannot have a join condition in an update statement.
- C. It executes successfully and produces the desired update.
- D. It executes successfully but does not produce the desired update.
Answer: D
NEW QUESTION # 189
View the Exhibits and examine PRODUCTSand SALES tables.
Exhibit 1
Exhibit 2
You issue the following query to display product name the number of times the product has been sold:
What happens when the above statement is executed?
- A. The statement produces an error because the GROUP BYclause cannot be used in a subquery in the FROM clause.
- B. The statement produces an error because ITEM_CNTcannot be displayed in the outer query.
- C. The statement executes successfully and produces the required output.
- D. The statement produces an error because a subquery in the FROMclause and outer-joins cannot be used together.
Answer: C
NEW QUESTION # 190
Which three statements are true regarding subqueries? (Choose three.)
- A. A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.
- B. A subquery can be used in the FROM clause of a SELECT statement.
- C. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.
- D. The ORDER BY Clause can be used in a subquery.
- E. If a subquery returns NULL, the main query may still return rows.
Answer: B,D,E
Explanation:
Explanation/Reference:
NEW QUESTION # 191
Examine this partial statement:
SELECT ename, sal,comm FROM emp
Now examine this output:
WHICH ORDER BY clause will generate the displayed output?
- A. ORDER BY comm DESC NULLS LAST, ename
- B. ORDER BY NVL(comm,0) ASC NULLS LAST, ename
- C. ORDER BY NVL(comm,0) ASC NULLS FIRST, ename
- D. ORDER BY NVL(enam,0) DESC, ename
Answer: A,D
NEW QUESTION # 192
View the Exhibit and examine the description of the ORDERS table. (Choose two.)
Which two WHERE clause conditions demonstrate the correct usage of conversion functions?
- A. WHERE Order_date > ( TO_DATE('JUL 10 2006', 'MON DD YYYY')
- B. WHERE Order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY')
- C. WHERE TO_CHAR(Order_date, 'MON DD YYYY') = 'JAN 20 2003'
- D. WHERE Order_date_IN ( TO_DATE('OCT 21 2003', 'MON DD YYYY'), TO_CHAR('NOV 21 2003',
'MON DD YYYY') )
Answer: A,C
NEW QUESTION # 193
View the Exhibit and examine the description of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECT first_name, employee_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) "Review" FROM employees; The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees. The review date is the firsts Monday after the completion of six months of the hiring. The NLS_TERRITORY parameter is set to AMERICA in the session.
Which statement is true regarding this query?
- A. The query would not execute because the NEXT_DAY function accepts a string as argument.
- B. The query would execute to give the desired output.
- C. The query would not execute because date functions cannot be nested.
- D. The query would execute but the output would give review dates that are Sundays.
Answer: D
NEW QUESTION # 194
What is true about non-equijoin statement performance?
- A. Table aliases can improve performance.
- B. The BETWEEN condition always performs less well than using the >= and <= conditions.
- C. The BETWEEN condition always performs better than using the >= and <= conditions.
- D. The join syntax used makes no difference to performance.
- E. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
Answer: A,D
NEW QUESTION # 195
Examine the description of the EMPLOYEES table:
NLS_DATE FORMAT is DD-MON-RR.
Which two queries will execute successfully?
- A. SELECT dept_ id, AVG (MAX(salary)) FROM employees GROUP By dept_id HAVING hire_date> ' O1-JAN-19';
- B. SELECT dept_ iD, sum(salary) FROM employees WHERE hire_date > '01-JAN-9' GROUP BY dept_id;
- C. SELECT dept_ id, AVG(MAX(salary)) FROM employees GROUP BY dept_id, salary;
- D. SELECT AVG(MAX(salary)) FROM employees GROUP BY salary;
- E. SELECT dept id, MAX (SUM(salary)) FROM employees GROUP BY dept_id;
Answer: B
Explanation:
In Oracle SQL, aggregation functions such as AVG and MAX cannot be nested directly inside each other and must be used in conjunction with GROUP BY on the column(s) that are not included in the aggregate function.
Also, the HAVING clause filters groups after aggregation is applied.
A). This query will not execute successfully because it improperly nests MAX inside AVG. Oracle SQL does not allow this type of nested aggregation without a subquery.
B). This query will not execute successfully for the same reason as A, it improperly nests MAX inside AVG.
C). Similar to A and B, this query improperly nests SUM inside MAX, which is not allowed without a subquery.
D). This query will execute successfully. It filters rows based on the HIRE_DATE using a correct date format (assuming '9' refers to '09' or '1999' due to the NLS_DATE_FORMAT being 'DD-MON-RR'), then groups the remaining rows by DEPT_ID and calculates the sum of SALARY for each department.
E). This query will not execute successfully because it improperly nests MAX inside AVG without a subquery, and it incorrectly attempts to GROUP BY SALARY, which is already being aggregated.
References:
* Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Aggregate Functions"
* Oracle Database SQL Language Reference, 12c Release 1 (12.1): "GROUP BY Clause"
* Oracle Database SQL Language Reference, 12c Release 1 (12.1): "HAVING Clause"
NEW QUESTION # 196
Which two statements are true regarding constraints? (Choose two.)
- A. All constraints can be defined at the table or column level.
- B. Constraints are enforced only during INSERToperations.
- C. A column with a FOREIGN KEYconstraint can never contain a NULL value.
- D. A constraint can be disabled even if the constrained column contains data.
- E. A column with a UNIQUEconstraint can contain a NULL value.
Answer: D,E
NEW QUESTION # 197
Examine the description of the EMPLOYEES table:
NLS_DATE FORMAT is DD-MON-RR.
Which two queries will execute successfully?
- A. SELECT dept_ id, AVG (MAX(salary)) FROM employees GROUP By dept_id HAVING hire_date> ' O1-JAN-19';
- B. SELECT dept_ iD, sum(salary) FROM employees WHERE hire_date > '01-JAN-9' GROUP BY dept_id;
- C. SELECT AVG(MAX(salary)) FROM employees GROUP BY salary;
- D. SELECT dept_ id, AVG(MAX(salary)) FROM employees GROUP BY dept_id, salary;
- E. SELECT dept id, MAX (SUM(salary)) FROM employees GROUP BY dept_id;
Answer: B,C
NEW QUESTION # 198
Examine the structure of the PROGRAMS table:
Which two SQL statements would execute successfully?
- A. SELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), 'Ongoing') FROM programs
- B. SELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;
- C. SELECT NVL (ADD_MONTHS (END_DATE,1) SYSDATE) FROM programs;
- D. SELECT NVL (MONTHS_BETWEEN (start_date, end_date), 'Ongoing') FROM programs;
Answer: A,B
NEW QUESTION # 199
Examine the structure of the Books_TRANSACTIONS table:
Examine the SQl statement:
SQL> SELECT * FROM books_transactions WHERE brrrowed_data<SYSDATE AND
transaction_type='RM' OR MEMBER_ID IN
('A101; 'A102');
Which statement is true about the outcome?
- A. It displays details for members who have borrowed today with RM as TRANSACTION_TYPE and details for member A101 or A102.
- B. It displays details for members who have borrowed before today's with either RM as TRANSACTION_TYPE or MEMBER_ID as A101 and A102.
- C. It displays details only for the members who have borrowed before today with RM as TRANSACTION_TYPE.
- D. It displays details for only member A101 and A102 have borrowed before today with RM as TRANSACTION-TYPE.
Answer: C
NEW QUESTION # 200
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 ordersVALUES(1, '10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);
- C. 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);
- 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: B
NEW QUESTION # 201
......
Latest Oracle 1z1-071 Practice Test Questions: https://freetorrent.dumpsmaterials.com/1z1-071-real-torrent.html
