Oracle 1z1-071 Certification All-in-One Exam Guide Aug-2024 [Q149-Q172]

Oracle 1z1-071 Certification All-in-One Exam Guide Aug-2024

Get Real 1z1-071 Exam Dumps [Aug-2024] Practice Tests

Oracle 1z0-071 is an exam that tests the knowledge of candidates in SQL and its applications in the Oracle Database. 1z1-071 exam is designed by Oracle, which is one of the leading technology companies in the world. The Oracle 1z0-071 exam is intended for individuals who want to become proficient in SQL programming, particularly in the context of the Oracle Database environment.

NEW QUESTION # 149
Which three statements are true about single-row functions? (Choose three.)

A. They can be used only in the WHERE clause of a SELECT statement
B. They can be nested to any level
C. The data type returned can be different from the data type of the argument
D. They can accept only one argument
E. The argument can be a column name, variable, literal or an expression
F. They return a single result row per table

Answer: C,E

NEW QUESTION # 150
Examine the data in the ORD_ITEMS table:

Evaluate this query:

Which statement is true regarding the result?

A. It returns an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT list.
B. It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table.
C. It returns an error because the HAVING clause should be specified after the GROUP BY clause.
D. It displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the table.

Answer: B

NEW QUESTION # 151
Examine this statement:

Which two things must be changed for it to execute successfully?

A. The word CONSTRAINT in the foreign key constraint on DEPT_ID must be changed to FOREIGN KEY.
B. The foreign key constraint on DEPT_ID must be defined at the table level instead of the column level.
C. One of the LONG columns must be changed to a VARCHAR2 or CLOB.
D. The NOT NULL constraint on ENAME must be defined at the column level instead of the table level.
E. The primary key constraint on BMP_ID must have a name.

Answer: C,D

NEW QUESTION # 152
Examine the description of the PRODUCT_INFORMATION table:

A. SELECT count(nvl( list_price,0)) FROM product_information WHERE list_price is null;
B. SELECT (COUNT(list_price) FROM Product_intormation WHERE list_price=NULL;
C. BELECT COUNT(list_price) FROM product_information where list_price is NULL;
D. SELECT COUNT(DISTINCT list_price) FROM product_information WHERE list_price is null.

Answer: A

NEW QUESTION # 153
View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.

The CUSTOMERS table contains the current location of all currently active customers.
The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.
You need to find those customers who have never changed their address.
Which SET operator would you use to get the required output?

A. INTERSECT
B. MINUS
C. UNION ALL
D. UNION

Answer: B

NEW QUESTION # 154
Which two statements are true about a full outer join?

A. The Oracle join operator (+) must be used on both sides of the join condition in the WHERE clause.
B. It includes rows that are returned by a Cartesian product.
C. It returns matched and unmatched rows from both tables being joined.
D. It includes rows that are returned by an inner join.
E. It returns only unmatched rows from both tables being joined.

Answer: C,D

NEW QUESTION # 155
The ORDERS table has a primary key constraint on the ORDER_ID column.
The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the primary key of the ORDERS table.
The constraint is defined with on DELETE CASCADE.
There are rows in the ORDERS table with an ORDER_TOTAL less than 1000.
Which three DELETE statements execute successfully?

A. DELETE FROM orders WHERE order_total<1000;
B. DELETE * FROM orders WHERE order_total<1000;
C. DELETE orders WHERE order_total<1000;
D. DELETE FROM orders;
E. DELETE order_id FROM orders WHERE order_total<1000;

Answer: A,C,D

Explanation:
In Oracle 12c SQL, the DELETE statement is used to remove rows from a table based on a condition. Given the constraints and the information provided, let's evaluate the options:
* A. DELETE FROM orders WHERE order_total<1000;: This statement is correctly formatted and will delete rows from the ORDERS table where ORDER_TOTAL is less than 1000. If there is a DELETE CASCADE constraint, corresponding rows in the ORDER_ITEMS table will also be deleted.
* B. DELETE * FROM orders WHERE order_total<1000;: This syntax is incorrect. The asterisk (*) is not used in the DELETE statement.
* C. DELETE orders WHERE order_total<1000;: This statement is also correctly formatted and is a shorthand version of the DELETE statement without the FROM clause.
* D. DELETE FROM orders;: This statement will delete all rows from the ORDERS table, and with DELETE CASCADE, it will also delete all related rows in the ORDER_ITEMS table.
* E. DELETE order_id FROM orders WHERE order_total<1000;: This syntax is incorrect because you cannot specify a column after the DELETE keyword.
References:
* Oracle Database SQL Language Reference 12c Release 1 (12.1), DELETE Statement

NEW QUESTION # 156
Examine the structure of the BOOKS_TRANSACTIONS table:
NameNull?Type
------------------------------------------------------------
TRANSACTION_IDNOT NULLVARCHAR2 (6)
BORROWED_DATEVARCHAR2 (50)
DUE_DATEDATE
BOOK_IDDATE
MEMBER_IDVARCHAR2 (6)
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 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;
C. SELECT member_id AS "MEMBER ID", due_date AS "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: C

NEW QUESTION # 157
Which two are true about self joins?

A. They require table aliases.
B. They require the EXISTS opnrator in the join condition.
C. They require the NOT EXISTS operator in the join condition.
D. They can use INNER JOIN and LEFT JOIN.
E. They are always equijoins.
F. They have no join condition.

Answer: A,D

NEW QUESTION # 158
You are designing the structure of a table in which two columns have the specifications:
COMPONENT_ID- must be able to contain a maximum of 12 alphanumeric characters and uniquely identify the row
EXECUTION_DATETIME- contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and is used for calculations and comparisons between components.
Which two options define the data types that satisfy these requirements most efficiently?

A. The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type.
B. The EXECUTION_DATETIME must be of DATE data type.
C. The COMPONENT_IDcolumn must be of CHAR data type.
D. The COMPONENT_IDmust be of VARCHAR2 data type.
E. The COMPONENT_IDmust be of ROWID data type.
F. The EXECUTION_DATETIME must be of TIMESTAMP data type.

Answer: B,C

NEW QUESTION # 159
View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables.

You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.
Which SQL statement would you execute to accomplish the task?

A. UPDATE dept dSET city = (SELECT cityFROM locations l)WHERE d.location_id = l.location_id;
B. UPDATE dept dSET city = ALL (SELECT cityFROM locations lWHERE d.location_id = l.location_id);
C. UPDATE dept dSET city = ANY (SELECT cityFROM locations l)
D. UPDATE dept dSET city = (SELECT cityFROM locations lWHERE d.location_id = l.location_id);

Answer: D

NEW QUESTION # 160
View the exhibit and examine the structure of the STOREStable.

You must display the NAME of stores along with the ADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, which is 115% of property price.
The stores displayed must have START_DATEin the range of 36 months starting from 01-Jan-2000 and above.
Which SQL statement would get the desired output?
SELECT name, concat (address| | ','| |city| |', ', country) AS full_address,

A. SELECT name, address||', '||city||', '||country AS full_address, start_date, property_price, property_price*115/100 FROM stores WHERE MONTHS_BETWEEN(start_date,TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36; SELECT name, concat (address||','| |city| |', ', country) AS full_address,
B. start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
C. start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, '01-JAN-2000') <=36;
SELECT name, concat (address| | ','| |city| |', ', country) AS full_address,
D. start_date,
property_price, property_price*115/100
FROM stores
WHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;

Answer: B

NEW QUESTION # 161
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 (sysdate) , 1, 5S).
B. It executes successfully with an implicit data type conversion.
C. It fails unless the expression is modified to to_char (Substr (sysdate, 1, 5) ) .
D. It falls unless the expression is modified to substr (tociiar (Trunc (svsdate) ) , 1, 5).

Answer: B

NEW QUESTION # 162
Examine the description of the EMPLOYEES table:

Which statement will execute successfully, returning distinct employees with non-null first names?

A. SELECT Distinct * FROM employees WHERE first_ name < > NULL;
B. SELECT DISTINCT * FROM employees WHERE first_ name IS NOT NULL;
C. SELECT first_ name, DISTNCT last_ name FROM employees WHERE first_ name IS NOT NULL;
D. SELECT first_ name, DISTINCT last_ name FROM employees WHERE first_ name < > NULL;

Answer: B

NEW QUESTION # 163
View the exhibit and examine the description of the PRODUCT_INFORMATION table.

Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL?

A. SELECT COUNT (DISTINCT list_price)FROM product_informationWHERE list_price is NULL
B. SELECT COUNT (NVL(list_price, 0))FROM product_informationWHERE list_price is NULL
C. SELECT COUNT (list_price)FROM product_informationWHERE list_price i= NULL
D. SELECT COUNT (list_price)FROM product_informationWHERE list_price is NULL

Answer: B

NEW QUESTION # 164
Which two are true about virtual columns?

A. They can be indexed.
B. They cannot have a data type explicitly specified.
C. They can be referenced In the where clause of an update or debete statement.
D. They can be referenced in the column expression of another virtxial column.
E. They can be referenced in the set clause of an update statement as the name of the column To be updated.

Answer: A,C

NEW QUESTION # 165
View the Exhibit and examine PRODUCTSand ORDER_ITEMStables.

You executed the following query to display PRODUCT_NAMEand the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt
FROM order_items
GROUP BY product_id) i RIGHT OUTER JOIN products p
ON i.product_id = p.product_id;
What would happen when the above statement is executed?

A. The statement would not execute because the GROUP BYclause cannot be used in the inline.
B. The statement would not execute because the ITEM_CNTalias cannot be displayed in the outer query.
C. The statement would not execute because inline views and outer joins cannot be used together.
D. The statement would execute successfully to produce the required output.

Answer: D

NEW QUESTION # 166
Which two are true about multiple table INSERT statements?

A. The conditional INSERT FIRST statement always inserts a row into a single table.
B. They always use subqueries.
C. The unconditional INSERT ALL statement must have the same number of columns in both the source and target tables.
D. They can transform a row from a source table into multiple rows in a target table.
E. The conditional INSERT ALL statement inserts rows into a single table by aggregating source rows.

Answer: C,D

Explanation:
B: True. Multiple table insert statements, specifically the conditional INSERT ALL, can transform a single row from the source table into multiple rows in one or more target tables depending on the conditions specified in the WHEN clauses.
E: True. The unconditional INSERT ALL statement will insert rows into multiple target tables without any conditions. However, it does not require the same number of columns in both the source and target tables. The INSERT ALL syntax allows you to specify different columns for each target table into which rows will be inserted.
Multiple table insert operations allow for complex insert scenarios, where based on the data, one can insert into different tables or multiple times into the same table based on different conditions.
References:Oracle SQL reference details the use of INSERT ALL and INSERT FIRST clauses for inserting into multiple tables based on conditions specified.

NEW QUESTION # 167
You want to write a query that prompts for two column names and the WHEREcondition each time it is executed in a session but only prompts for the table name the first time it is executed.
The variables used in your query are never undefined in your session.
Which query can be used?

Answer: E

NEW QUESTION # 168
Which two statements are true about Data Manipulation Language (DML) statements? (Choose two.)

A. An UPDATE....SET.... statement can modify multiple rows based on only a single condition on a table.
B. An INSERT INTO... VALUES..... statement can add a single row based on multiple conditions on a table.
C. A DELETE FROM..... statement can remove rows based on only a single condition on a table.
D. A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.
E. An UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table.
F. An INSERT INTO...VALUES.. statement can add multiple rows per execution to a table.

Answer: D,E

Explanation:
http://www.techonthenet.com/sql/and_or.php

NEW QUESTION # 169
Which two are true about a SQL statement using SET operators such as UNION?

A. The data type group of each column returned by the second query must match the data type group of the corresponding column returned by the first query.
B. The names and number of columns must be identical for all SELECT statements in the query.
C. The data type of each column returned by the second query must exactly match the data type of the corresponding column returned by the first query.
D. The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query.
E. The number, but not names, of columns must be identical for all SELECT statements in the query.

Answer: C,E

NEW QUESTION # 170
View the Exhibit and examine the description of the EMPLOYEEStable.

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 AMERICAin the session.
Which statement is true regarding this query?

A. The query would not execute because date functions cannot be nested.
B. The query would execute to give the desired output.
C. The query would execute but the output would give review dates that are Sundays.
D. The query would not execute because the NEXT_DAYfunction accepts a string as argument.

Answer: C

NEW QUESTION # 171
Which CREATE TABLE statement is valid?

A. Option D
B. Option C
C. Option A
D. Option B

Answer: A

NEW QUESTION # 172
......

Last 1z1-071 practice test reviews: Practice Test Oracle dumps: https://www.actualcollection.com/1z1-071-exam-questions.html

Try 1z1-071 Free Now! Real Exam Question Answers: https://drive.google.com/open?id=1F6FzmftLsh_RAE3tFF-e-ysWB6SQXPzP

Oracle 1z1-071 Certification All-in-One Exam Guide Aug-2024 [Q149-Q172]

Related Articles