24 October, 2020

The Secret Of Oracle 1Z0-071 Test Question

Our pass rate is high to 98.9% and the similarity percentage between our 1Z0-071 study guide and real exam is 90% based on our seven-year educating experience. Do you want achievements in the Oracle 1Z0-071 exam in just one try? I am currently studying for the Oracle 1Z0-071 exam. Latest Oracle 1Z0-071 Test exam practice questions and answers, Try Oracle 1Z0-071 Brain Dumps First.

Check 1Z0-071 free dumps before getting the full version:

Page: 1 / 15

Total 187 questions Full Exam Access

Question 1

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. (Choose the best answer.)
1Z0-071 dumps exhibit

You executed this UPDATE statement: UPDATE
( SELECT order_date, order_total, customer_id FROM orders) Set order_date = '22-mar-2007'
WHERE customer_id IN
(SELECT customer_id FROM customers
WHERE cust_last_name = 'Roberts' AND credit_limit = 600); Which statement is true regarding the execution?

It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.

It would not execute because two tables cannot be referenced in a single UPDATE statement.

It would execute and restrict modifications to the columns specified in the SELECT statement.

It would not execute because a SELECT statement cannot be used in place of a table name.

Question 2

Which three statements are true regarding group functions? (Choose three.)

They can be used on columns or expressions.

They can be passed as an argument to another group function.

They can be used only with a SQL statement that has the GROUP BY clause.

They can be used on only one column in the SELECT clause of a SQL statement.

They can be used along with the single-row function in the SELECT clause of a SQL statement.

Question 3

Which two are the minimal requirements for a self-join? (Choose two.)

Only equijoin conditions may be used in the query.

Outer joins must not be used in the query.

There must be a condition on which the self-join is performed.

No other condition except the self-join may be specified.

The table used for the self-join must have two different alias names in the query.

Question 4

View the exhibit and examine the structure of the STORES table. STORES table
NameNull?Type
---------------------- ------------- STORE_IDNUMBER NAMEVARCHAR2(100)
ADDRESSVARCHAR2(200) CITYVARCHAR2(100) COUNTRYVARCHAR2(100) START_DATEDATE END_DATEDATE PROPERTY_PRICENUMBER
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_DATE in 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,start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN (start_date, \'01-JAN-2000\')<=36;

SELECT name, concat (address| | \',\'| |city| |\', \', country) AS full_address,start_date,property_price, property_price*115/100FROM storesWHERETO_NUMBER(start_date-TO_DATE(\'01-JAN-2000\',\'DD-MON-RRRR\')) <=36;

SELECT name, address||\',\'||city||\',\'||country AS full_address,start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN (start_date, TO_DATE(\'01-JAN-2000\',\'DD-MON-RRRR\')) <=36;

SELECT name, concat (address||\',\'| |city| |\', \', country) AS full_address,start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN (start_date, TO_DATE(\'01-JAN-2000\',\'DD-MON-RRRR\')) <=36;

Question 5

Examine the structure of the PROGRAMS table:
1Z0-071 dumps exhibit

Which two SQL statements would execute successfully?

SELECT NVL (ADD_MONTHS (END_DATE,1) SYSDATE) FROM programs;

SELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;

SELECT NVL (MONTHS_BETWEEN (start_date, end_date), ‘Ongoing’) FROM programs;

SELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), ‘Ongoing’) FROMprograms

Question 6

Which two statements are true regarding constraints?

A foreign key column cannot contain null values.

A column with the UNIQUE constraint can contain null values.

A constraint is enforced only for INSERT operation on the table.

A constraint can be disabled even if the constraint column contains data.

All constraints can be defined at the column level and at the table level.

Question 7

View the Exhibit and examine the data in the employees table.
1Z0-071 dumps exhibit

You want to generate a report showing the total compensation paid to each employee to date. You issue the following query:
1Z0-071 dumps exhibit

What is the outcome?

It executes successfully but does not give the correct output.

It generates an error because the concatenation operator can be used to combine only two items.

It generates an error because the usage of the round function in the expression is not valid

It generates an error because the alias is not valid.

It executes successfully and gives the correct output.

Question 8

Which task can be performed by using a single Data Manipulation Language (DML) statement?

adding a column constraint when inserting a row into a table

adding a column with a default value when inserting a row into a table

removing all data only from one single column on which a unique constraint is defined

removing all data only from one single column on which a primary key constraint is defined

Question 9

View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the PRIMARY KEY in the ORDERS table.
1Z0-071 dumps exhibit

Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id) AS
SELECT order_id.order_date,customer_id FROM orders;
Which statement is true regarding the above command?

The NEW_ODRDERS table would not get created because the DEFAULT value cannot be specified in the column definition.

The NEW_ODRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.

The NEW_ODRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.

The NEW_ODRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.

Question 10

Examine the structure proposed for the TRANSACTIONS table:
1Z0-071 dumps exhibit

Which two statements are true regarding the storage of data in the above table structure? (Choose two.)

The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.

The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.

The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.

The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.

Question 11

Which statements are correct regarding indexes? (Choose all that apply.)

A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically attempts to creates a unique index.

Indexes should be created on columns that are frequently referenced as part of any expression.

When a table is dropped, the corresponding indexes are automatically dropped.

For each DML operation performed, the corresponding indexes are automatically updated.

Question 12

Which statement is true regarding the default behaviour of the ORDER by clause?

Numeric values are displayed in descending order if they have decimal positions.

Only columns that are specified in the SELECT list can be used in the ORDER by clause.

In a character sort, the values are case-sensitive.

NULLs are not including in the sort operation

Question 13

You execute the following commands: SQL > DEFINE hiredate = '01-APR-2011'
SQL >SELECT employee_id, first_name, salary FROM employees
WHERE hire_date > '&hiredate' AND manager_id >&mgr_id;
For which substitution variables are you prompted for the input?

none, because no input required

both the substitution variables \'\'hiredate\' and \'mgr_id\'.

only hiredate\'

only \'mgr_id\'

Question 14

Which two statements are true regarding subqueries? (Choose two.)

A subquery can appear on either side of a comparison operator.

Only two subqueries can be placed at one level.

A subquery can retrieve zero or more rows.

A subquery can be used only in SQL query statements.

There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.

Question 15

View the Exhibit and examine the description of the ORDERS table. (Choose two.)
1Z0-071 dumps exhibit

Which two WHERE clause conditions demonstrate the correct usage of conversion functions?

WHERE Order_date_IN ( TO_DATE(\'OCT 21 2003\', \'MON DD YYYY\'), TO_CHAR(\'NOV 21 2003\', \'MON DD YYYY\') )

WHERE Order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), \'MON DD YYYY\')

WHERE TO_CHAR(Order_date, \'MON DD YYYY\') = \'JAN 20 2003\'

WHERE Order_date > ( TO_DATE(\'JUL 10 2006\', \'MON DD YYYY\')

Question 16

Which statement is true about Enterprise Manager (EM) express in Oracle Database 12c?

By default, EM express is available for a database after database creation.

You can use EM express to manage multiple databases running on the same server.

You can perform basic administrative tasks for pluggable databases by using the EM express interface.

You cannot start up or shut down a database Instance by using EM express.

You can create and configure pluggable databases by using EM express.

Question 17

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?

It displays 5 percent of the products with the highest amount sold.

It displays the first 5 percent of the rows from the SALES table.

It displays 5 percent of the products with the lowest amount sold.

It results in an error because the ORDER BY clause should be the last clause.

Question 18

You need to display the date 11-oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’. Which SQL statement would give the required result?

SELECT TO_CHAR (TO_DATE (’11-oct-2007’), ‘fmDdthsp “of” Month, Year’)FROM DUAL

SELECT TO_CHAR (‘11-oct-2007’, ‘fmDdspth “of” Month, Year’)FROM DUAL

SELECT TO_CHAR (TO_DATE (‘11-oct-2007’), ‘fmDdspth of month, year’)FROM DUAL

SELECT TO_DATE (TO_CHAR (’11-oct-2007’), ‘fmDdspth “of” Month, Year’))FROM DUAL

Question 19

Which statement is true about an inner join specified in the WHERE clause of a query?

It must have primary-key and foreign-key constraints defined on the columns used in the join condition.

It requires the column names to be the same in all tables used for the join conditions.

It is applicable for equijoin and nonequijoin conditions.

It is applicable for only equijoin conditions.

Question 20

Which two statements are true regarding savepoints? (Choose two.)

Savepoints may be used to ROLLBACK.

Savepoints can be used for only DML statements.

Savepoints are effective only for COMMIT.

Savepoints are effective for both COMMIT and ROLLBACK.

Savepoints can be used for both DML and DDL statements.

Question 21

View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.
1Z0-071 dumps exhibit

The retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, the following SQL statement was written:
SELECT employee_id, first_name, department_name FROM employees
NATURAL JOIN departments;
The desired output is not obtained after executing the above SQL statement. What could be the reason for this?

The table prefix is missing for the column names in the SELECT clause.

The NATURAL JOIN clause is missing the USING clause.

The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.

The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.

Question 22

Evaluate the following SQL statement:
SELECT product_name || 'it's not available for order' FROM product_information
WHERE product_status = 'obsolete';
You received the following error while executing the above query: ERROR
ORA-01756: quoted string not properly terminated What would you do to execute the query successfully?

Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string.

Enclose the literal character string in the SELECT clause within the double quotation marks.

Do not enclose the character literal string in the SELECT clause within the single quotation marks.

Use escape character to negate the single quotation mark inside the literal character string in the SELECT clause.

Question 23

Examine the structure of the MEMBERS table: (Choose the best answer.)
1Z0-071 dumps exhibit

Examine the SQL statement:
SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC;
What would be the result execution?

It displays all cities in descending order, within which the last names are further sorted in descending order.

It fails because a column alias cannot be used in the ORDER BY clause.

It fails because a column number and a column alias cannot be used together in the ORDER BY clause.

It displays all cities in ascending order, within which the last names are further sorted in descending order.

Question 24

View the Exhibit and examine the details of the PRODUCT_INFORMATION table.
1Z0-071 dumps exhibit

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?

It would not execute because the entire WHERE clause is not enclosed within parentheses.

It would execute but would return no rows.

It would not execute because the same column has been used twice with the AND logical operator.

It would execute and return the desired.

Question 25

Evaluate the following SELECT statement 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_CONDITION R_CONSTRAINT_NAME DELETE_RULE
STATUS ORDER_DATE_NN C
"ORDER_DATE" IS NOT NULL ENABLED ORDER_CUSTOMER_ID_NN C
"CUSTOMER_ID" IS NOT NULL ENABLED ORDER_MODE_LOV C
order _mode in ('direct', 'online') ENABLED
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.)

The R_CONSTRAINT_NAME column gives the alternative name for the constraint.

In the second column, \'c\' indicates a check constraint.

The STATUS column indicates whether the table is currently in use.

The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.

Page: 1 / 15

Total 187 questions Full Exam Access