Get Ready to Pass the 1z1-071 exam with Oracle Latest Practice Exam
Get Prepared for Your 1z1-071 Exam With Actual Oracle Study Guide!
NEW QUESTION # 138
You must create a table for a banking application. (Choose the best answer.) One of the columns in the table has these requirements:
1: A column to store the duration of a short team loan
2: The data should be stored in a format supporting DATE arithmetic with DATE datatypes without using conversion functions.
3: The maximum loan period is 30 days.
4: Interest must be calculated based on the number of days for which the loan remains unpaid.
Which data type would you use?
- A. Interval year to month
- B. Timestamp
- C. Interval day to second
- D. Date
- E. Number
Answer: C
NEW QUESTION # 139
View the Exhibit and examine the details of the PRODUCT_INFORMATION table.
You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:
SELECT product_name, list_price
FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; Which statement is true regarding the execution of the query?
- A. It would not execute because the same column has been used twice with the AND logical operator.
- B. It would execute but would return no rows.
- C. It would execute and return the desired.
- D. It would not execute because the entire WHERE clause is not enclosed within parentheses.
Answer: B
NEW QUESTION # 140
View the exhibit and examine the structure of the STOREStable.
STORES table
Name Null? Type
----------------- ----- -------------
STORE_ID NUMBER
NAME VARCHAR2(100)
ADDRESS VARCHAR2(200)
CITY VARCHAR2(100)
COUNTRY VARCHAR2(100)
START_DATE DATE
END_DATE DATE
PROPERTY_PRICE NUMBER
You want to display the NAME of the store 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?
- A. SELECT name, concat (address| | ','| |city| |', ', country) AS full_address, start_date,
property_price, property_price*115/100
FROM stores
WHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36; - B. 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; - C. SELECT name, concat (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; - D. SELECT name, concat (address| | ','| |city| |', ', country) AS full_address, start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, '01-JAN-2000') <=36;
Answer: C
NEW QUESTION # 141
View the Exhibit and examine the structure of ORDER_ITEMS and ORDERS tables.
You need to remove from the ORDER_ITEMS table those rows that have an order status of 0 or 1 in the ORDERS table.
Which two DELETE statements are valid (Choose two.)
- A. DELETE FROM order_items iWHERE order_id = (SELECT order_id FROM orders oWHERE i.order_id = o.order_id AND order_status IN (0,1));
- B. DELETEFROM order_itemsWHERE order_id IN (SELECT order_idFROM ordersWHERE orders_status in (0,1));
- C. DELETEFROM (SELECT * FROM order_items I,orders oWHERE i.order_id = o.order_id AND order_status IN (0,1));
- D. DELETE *FROM order_itemsWHERE order_id IN (SELECT order_id)FROM ordersWHERE order_status IN (0,1));
Answer: B,C
NEW QUESTION # 142
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 with any clause in a SELECT statement.
- C. A substitution variable prefixed with & always prompts only once for a value in a session.
- D. A substitution variable can be used only in a SELECT statement.
- E. A substitution variable prefixed with 6 prompts only once for a value in a session unless is set to undefined in the session.
- F. A substitution variable used to prompt for a column name must be endorsed in double quotation marks.
Answer: B,C
Explanation:
Substitution variables in Oracle are used to replace a value dynamically during the execution of SQL statements. The behavior of these variables is well-documented:
* C. A substitution variable prefixed with & always prompts only once for a value in a session: This is true. In a session, when you use a single ampersand (&), SQL*Plus or SQL Developer will prompt for the value the first time the variable is encountered. The value for this variable will then be reused for the remainder of the session unless it is redefined.
* D. A substitution variable can be used with any clause in a SELECT statement: Substitution variables can be placed in any part of a SQL statement, including the SELECT, WHERE, GROUP BY, ORDER BY, etc. They are not limited to any specific clause.
References:
* Oracle SQL*Plus User's Guide and Reference, which discusses substitution variables.
NEW QUESTION # 143
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 IS NOT NULL;
- B. SELECT first_ name, DISTINCT last_ name FROM employees WHERE first_ name < > NULL;
- C. SELECT first_ name, DISTNCT last_ name FROM employees WHERE first_ name IS NOT NULL;
- D. SELECT Distinct * FROM employees WHERE first_ name < > NULL;
Answer: A
NEW QUESTION # 144
Examine the description of the SALES1 table:
SALES2 is a table with the same description as SALES1.
Some sales data is duplicated in both tables.
You want to display the rows from the SALES1 table which are not present in the SALES2 table.
Which set operator generates the required output?
- A. UNION ALL
- B. SUBTRACT
- C. UNION
- D. INTERSECT
- E. MINUS
Answer: E
NEW QUESTION # 145
Which three are true?
- A. CEIL returns the largest integer less than or equal to a specified number.
- B. CEIL requires an argument which is a numeric data type.
- C. LAST_DAY returns the date of the last day of the month for the date argument passed to the function.
- D. ADD_MONTHS adds a number of calendar months to a date.
- E. LAST_DAY returns the date of the last day of the current ,month onlyu.
- F. LAST_DAY return the date of the last day the previous month only.
- G. ADD_MONTHS works with a character string that can be implicitlyt converted to a DATE data type.
Answer: C,D,G
Explanation:
* A: LAST_DAY does not only return the last day of the current month; it returns the last day of the month based on the date argument passed to it, which may not necessarily be the current month. Thus, statement A is incorrect.
* B: CEIL requires a numeric argument and returns the smallest integer greater than or equal to that number. Thus, statement B is incorrect.
* C: ADD_MONTHS function adds a specified number of calendar months to a date. This statement is correct as per the Oracle documentation.
* D: ADD_MONTHS can work with a character string if the string can be implicitly converted to a DATE, according to Oracle SQL data type conversion rules. Therefore, statement D is correct.
* E: LAST_DAY does not specifically return the last day of the previous month; it returns the last day of the month for any given date. Thus, statement E is incorrect.
* F: CEIL returns the smallest integer greater than or equal to the specified number, not the largest integer
* less than or equal to it. Hence, statement F is incorrect.
* G: LAST_DAY returns the last day of the month for the date argument passed to the function, which aligns with the definition in Oracle's SQL reference. Therefore, statement G is correct.
NEW QUESTION # 146
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 # 147
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 the output?
- A. ORDER BY DEPT_NAME;
- B. ORDER BY DEPT_ID;
- C. ORDER BY 3;
- D. ORDER BY 'b';
Answer: B,C
Explanation:
Explanation/Reference:
NEW QUESTION # 148
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 indexes on the table remain but they are invalidated.
- B. All data along with the table structure is deleted.
- C. All data in the table is deleted but the table structure remains.
- D. All views and synonyms on the table remain but they are invalidated.
- E. A pending transaction in the session is committed.
Answer: B,D,E
NEW QUESTION # 149
Examine the description or the CUSTOMERS 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_AMONT FROM customers WHERE cust_income_level IS NOT NULL AND due_amount IS NOT NULL;
- B. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level != NULL AND cust_credit_level != NULL;
- C. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;
- D. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers WHERE cust_income_level != NULL AND due_amount != NULL;
- E. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers WHERE cust_income_level <> NULL AND due_amount <> NULL;
Answer: C
NEW QUESTION # 150
Which statement is true regarding the INTERSECT operator?
- A. Reversing the order of the intersected tables alters the result.
- B. The number of columns and data types must be identical for all SELECT statements in the query.
- C. It ignores NULL values.
- D. The names of columns in all SELECT statements must be identical.
Answer: B
Explanation:
Explanation
INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates.
The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.
References:
http://oraclexpert.com/using-the-set-operators/
NEW QUESTION # 151
Examine the structure of the SHIPMENTS table:
You want to generate a report that displays the PO_ID and the penalty amount to be paid if the SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.
Evaluate the following two queries:
Which statement is true regarding the above commands?
- A. Only the first query executes successfully and gives the correct result.
- B. Only the second query executes successfully and gives the correct result.
- C. Only the first query executes successfully but gives a wrong result.
- D. Only the second query executes successfully but gives a wrong result.
- E. Both execute successfully and give correct results.
Answer: A
NEW QUESTION # 152
Examine the description of the CUSTOMERS 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. Option E
- B. Option B
- C. Option D
- D. Option C
- E. Option A
Answer: E
NEW QUESTION # 153
View the Exhibit and examine the data in the PRODUCTS table.
You must display product names from the PRODUCTS table that belong to the 'Software/other' category with minimum prices as either $2000 or $4000 and with no unit of measure.
You issue this query:
Which statement is true?
- A. It executes successfully but returns no result.
- B. It executes successfully and returns the required result.
- C. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
- D. It generates an error because the condition specified for the PROD_CATEGORY column is not valid.
Answer: A
NEW QUESTION # 154
View the exhibit and examine the description for the SALESand CHANNELStables.
You issued this SQL statement:
Which statement is true regarding the result?
- A. The statement will fail because the subquery in the VALUESclause is not enclosed within single quotation marks.
- B. The statement will execute and a new row will be inserted in the SALEStable.
- C. The statement will fail because a subquery cannot be used in a VALUESclause.
- D. The statement will fail because the VALUESclause is not required with a subquery.
Answer: B
NEW QUESTION # 155
View the Exhibit and examine the data in the employees table.
You want to generate a report showing the total compensation paid to each employee to date.
You issue the following query:
What is the outcome?
- A. It executes successfully and gives the correct output.
- B. It generates an error because the concatenation operator can be used to combine only two items.
- C. It generates an error because the alias is not valid.
- D. It executes successfully but does not give the correct output.
- E. It generates an error because the usage of the round function in the expression is not valid
Answer: D
NEW QUESTION # 156
Examine the description of EMPLOYEES table:
Which three queries return all rows for which SALARY+COMMISSION is greate than 20000?
- A. SELECT * FROM employees WHERE salary+NULLF(commission,0)>=20000;
- B. SELECT * FROM employees WHERE NVL2(salary)+commission,salary+commission,
- C. SELECT * FROM employees WHERE NVL(salary+commission,0)>==20000;
- D. SELECT * FROM employees WHERE NVL(salary+commission,0)>=20000;
- E. SELECT * FROM employees WHERE salary+NVL(commission,0)>=20000;
- F. SELECT * FROM employees WHERE salary+NVL2(commission,commission,0)>=20000;
Answer: A,E,F
Explanation:
In Oracle Database 12c, when dealing with NULL values in arithmetic expressions, the functions NVL, NVL2
, and equivalent techniques using NULLIF or arithmetic operations with NULL can be crucial.
* Option A: SELECT * FROM employees WHERE salary + NULLIF(commission, 0) >= 20000;
* NULLIF(value1, value2) returns NULL if value1 is equal to value2, otherwise it returns value1.
In this case, if commission is NULL, it treats it as 0.
* This query correctly adds salary and commission (assuming 0 when commission is NULL) and checks if the total is at least 20000.
* Option B: SELECT * FROM employees WHERE salary + NVL2(commission, commission, 0) >=
20000;
* NVL2(expr1, expr2, expr3) returns expr2 if expr1 is not NULL; otherwise, it returns expr3.
Here, if commission is not NULL, it uses commission, otherwise 0.
* This condition correctly calculates salary + commission (assuming commission as 0 if it is NULL) and checks if the total is at least 20000.
* Option D: SELECT * FROM employees WHERE salary + NVL(commission, 0) >= 20000;
* NVL(expr1, expr2) returns expr2 if expr1 is NULL; otherwise, it returns expr1. This query treats commission as 0 if it is NULL.
* It correctly sums salary and commission and compares the result against 20000.
Options C, E, and F contain either syntax errors or logical errors in handling NULLs and comparisons.
NEW QUESTION # 157
Examine the description of the EMP_DETAILS table given below:
Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL TABLE?
- A. You cannot add a new column to the table with LONG as the data type.
- B. An EMP_IMAGE column cannot be included in the ORDER BY clause.
- C. An EMP_IMAGE column can be included in the GROUP BY clause.
- D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column.
Answer: A,B
NEW QUESTION # 158
Which two are SQL features?
- A. providing database transaction control
- B. providing variable definition capabilities.
- C. providing update capabilities for data in external files
- D. processing sets of data
- E. providing graphical capabilities
Answer: A,D
Explanation:
SQL (Structured Query Language) is a domain-specific language used in programming and designed for managing data held in a relational database management system.
A). False. SQL does not have graphical capabilities; it is a textual language for database interaction.
B). False. SQL supports variable definition, but it is not a core feature of the language. Variables are more commonly defined in procedural extensions to SQL, such as PL/SQL in Oracle.
C). True. SQL provides database transaction control through statements like COMMIT, ROLLBACK, and SAVEPOINT.
D). True. SQL is designed for processing sets of data, allowing for operations such as selection, projection, and joins on sets of rows.
E). False. SQL does not provide capabilities to update data in external files. It operates on data within the database.
NEW QUESTION # 159
Examine the description of the CUSTOMERS table:
Which two statements will do an implicit conversion?
- A. SELECT FROM + customers WHERE insert_date = '01-JAN-19';
- B. SELECT FROM + customers WHERE (customer_id) = 0001;
- C. SELECT FROM + customers WHERE insert_date = DATE '2019-01-01';
- D. SELECT FROM + customers WHERE (customer_id) = '0001';
- E. SELECT FROM + customers WHERE TO-CHAR (customer_id) = '0001';
Answer: A,B
NEW QUESTION # 160
Which three statements are true about the DESCRIBE command? (Choose three.)
- A. It can be used to display the structure of an existing view
- B. It can be used from SQL Developer
- C. It displays the PRIMARY KEY constraint for any column or columns that have that constraint
- D. It displays all constraints that are defined for each column
- E. It can be used only from SQL*Plus
- F. It displays the NOT NULL constraint for any columns that have that constraint
Answer: A,E,F
NEW QUESTION # 161
Examine the description products table:
Examine the description of the new_projects table;
Which two queries execute successfully?
- A.
- B.
- C.
- D.
- E.
Answer: B,C
Explanation:
To determine which queries will execute successfully, we need to consider the compatibility of the column definitions and the structure of the SELECT statements:
* Option A uses the MINUS set operator, which subtracts rows returned by the second SELECT statement from the rows returned by the first. For MINUS to work, the number and the order of columns and their data types must be the same in both queries. This query will not execute successfully because the second SELECT statement does not include all columns from the first SELECT statement, and the data types and sizes of PROD_ID do not match (CHAR(2) vs CHAR(4)).
* Option B uses the UNION ALL set operator, which appends the results of the second SELECT statement to the results of the first. Unlike UNION, UNION ALL does not eliminate duplicate rows.
This query will execute successfully because UNION ALL does not require the same data types or sizes, and the result will contain all columns from the first SELECT statement filled with NULL for non-matching columns from the second SELECT statement.
* Option C uses the UNION set operator, which requires the same number of columns and compatible data types. This query will not execute successfully because PROD_NAME has different data types (CHAR(4) vs VARCHAR2(10)), and the result of a UNION must have the same number of columns with compatible data types in the two SELECT statements.
* Option D uses the UNION set operator as well, but unlike Option C, it does not require a specific data type match because both SELECT statements include all columns and UNION is used (which will automatically handle type conversion where necessary). This query will execute successfully.
* Option E uses the INTERSECT set operator, which requires the same number and order of columns and their data types to be identical or compatible. This query will not execute successfully because the data types and sizes of PROD_ID do not match (CHAR(2) vs CHAR(4)).
References:
* Oracle Documentation on Set Operators: SQL Language Reference - Set Operators
* Oracle Documentation on Data Type Precedence: SQL Language Reference - Data Type Precedence In conclusion, only Option B and Option D will execute successfully because they adhere to the rules of the UNION ALL and UNION operators respectively, regarding column count and data type compatibility.
NEW QUESTION # 162
View the Exhibit and examine the structure of the ORDER_ITEMS and ORDERS tables.
You are asked to retrieve the ORDER_ID, product_ID, and total price (UNIT_PRICE multiplied by QUANTITY), where the total price is greater than 50,000.
You executed the following SQL statement:
SELECT prder_id, product_id, unit_price*quantity "Total Price"
FROM order_items
WHERE unit_price*quantity > 50000
NATURAL JOIN orders;
Which statement is true regarding the execution of the statement?
- A. The statement would not execute because the WHERE clause is before the NATURAL JOIN clause.
- B. The statement would execute and provide the desired result.
- C. The statement would not execute because the ON keyword is missing in the NATURAL JOIN clause.
- D. The statement would not execute because the USING keyword is missing in the NATURAL JOIN clause.
Answer: A
NEW QUESTION # 163
......
Oracle 1z0-071, also known as the Oracle Database SQL Exam, is designed to assess an individual's skills and knowledge in SQL programming language. 1z1-071 exam is intended for database developers, database administrators, and professionals who wish to validate their SQL skills and expertise in Oracle database management. Passing 1z1-071 exam is a prerequisite for obtaining the Oracle Database SQL Certified Associate certification.
Oracle 1z1-071 exam is a computer-based exam that consists of 73 multiple-choice questions. 1z1-071 exam has a time limit of 100 minutes, and candidates must score at least 63% to pass. 1z1-071 exam is available in several languages, including English, Japanese, and Portuguese.
Pass Your Next 1z1-071 Certification Exam Easily & Hassle Free: https://www.fast2test.com/1z1-071-premium-file.html
Free Oracle 1z1-071 Exam Question Practice Exams: https://drive.google.com/open?id=1BiZC1QljN21JWT_84R_VhAEObtcilkLw