Free Oracle 1z0-071 Exam Questions and Answer from Training Expert TestSimulate

Top Oracle 1z0-071 Courses Online

Oracle 1z1-071 exam is an essential certification for professionals who want to work with Oracle databases. Oracle Database SQL certification provides a validation of the candidate's skills and knowledge in SQL and Oracle databases. Oracle is a major player in the database industry, and the Oracle 1z1-071 certification is highly regarded by employers. Oracle Database SQL certification demonstrates that the candidate has a solid understanding of SQL and is capable of working with Oracle databases.

Oracle 1z1-071 (Oracle Database SQL) Exam is a certification exam offered by Oracle Corporation for individuals looking to demonstrate their proficiency in SQL and Oracle Database. 1z0-071 exam is designed to test the candidate's knowledge and skills in SQL language, data modeling, database concepts, and the use of Oracle Database software. Passing 1z0-071 exam validates the candidate's ability to work with Oracle Database and SQL, making them a valuable asset to any organization that uses these technologies.

 

NEW QUESTION # 61
Which two statements are true about * _ TABLES views?

• A. USER_ TABLES displays all tables owned by the current user.
• B. ALL TABLES displays all tables owned by the current user.
• C. You must have SELECT privileges on a table to view it in ALL _TABLES.
• D. You must have SELECT privileges on a table to view it in DBA TABLES.
• E. All users can query DBA TABLES successfully.
• F. You must have SELECT privileges on a table to view it in USER TABLES.

Answer: A,C

NEW QUESTION # 62
Which three statements are true about single-row functions?

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

Answer: C,E

NEW QUESTION # 63
Examine the structure of the MARKS table:

Which two statements would execute successfully? (Choose two.)

• A. SELECT student_name subject1FROM marksWHERE subject1 > AVG(subject1);
• B. SELECT SUM(subject1+subject2+subject3)FROM marksWHERE student_name IS NULL;
• C. SELECT student_name,SUM(subject1)FROM marksWHERE student_name LIKE 'R%';
• D. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)FROM marksWHERE subject1 > subject2;

Answer: B,D

NEW QUESTION # 64
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 (MONTHS_BETWEEN (start_date, end_date), 'Ongoing') FROM programs;
• D. SELECT NVL (ADD_MONTHS (END_DATE,1) SYSDATE) FROM programs;

Answer: A,D

NEW QUESTION # 65
View the exhibit and examine the structure of the EMPLOYEEStable.

You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAMEof the managers and the second column would have LAST_NAMEof the employees.
Which SQL statement would you execute?
SELECT m.last_name "Manager", e.last_name "Employee"

• A. FROM employees m JOIN employees e
  ON m.employee_id = e.manager_id
  WHERE e.manager_id = 100;
  SELECT m.last_name "Manager", e.last_name "Employee"
• B. FROM employees m JOIN employees e
  ON e.employee_id = m.manager_id
  WHERE m.manager_id = 100;
  SELECT m.last_name "Manager", e.last_name "Employee"
• C. FROM employees m JOIN employees e
  ON m.employee_id = e.manager_id
  WHERE m.manager_id = 100;
  SELECT m.last_name "Manager", e.last_name "Employee"
• D. FROM employees m JOIN employees e
  WHERE m.employee_id = e.manager_id AND e.manager_id = 100

Answer: A

NEW QUESTION # 66
You must write a query that prompts users for column names and conditions every time it is executed.
The user must be prompted only once for the table name.
Which statement achieves those objectives?

• A. SELECT &col1, '&col2'
  FROM &table
  WHERE &&condition = '&cond';
• B. SELECT &col1, &col2
  FROM &&table
  WHERE &condition = &cond;
• C. SELECT &col1, &col2
  FROM &&table
  WHERE &condition = &&cond
• D. SELECT &col1, &col2
  FROM "&table"
  WHERE &condition = &cond;

Answer: B

NEW QUESTION # 67
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?

• A. Use the Oracle (q) operator and delimiter to allow the use of a single quotation mark within the literal character string in the SELECT clause
• B. Remove the single quotation marks enclosing the character literal string in the SELECT clause
• C. Enclose the character literal string in the SELECT clause within double quotation marks
• D. Use the escape character to negate the single quotation mark within the literal character string in the SELECT clause

Answer: A

Explanation:
Explanation
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm

NEW QUESTION # 68
Which three statements are true about indexes and their administration in an Oracle database?

• A. A UNIQUE and non-unique index can be created on the same table column
• B. An INVISIBLE index is not maintained when Data Manipulation Language (DML) is performed on its underlying table.
• C. A DROP INDEX statement always prevents updates to the table during the drop operation
• D. A descending index is a type of function-based index
• E. If a query filters on an indexed column then it will always be used during execution of the query
• F. An index can be created as part of a CREATE TABLE statement.

Answer: A,D,F

Explanation:
A). This statement is incorrect. An INVISIBLE index is maintained during DML operations just like a VISIBLE index. The difference is that an INVISIBLE index is not used by the optimizer unless explicitly hinted. B. This statement is correct. When creating a table, you can define indexes on one or more columns as part of the table definition. C. This statement is incorrect. While a DROP INDEX statement will drop the index, it does not always prevent updates to the table. If the index is marked as unusable or is an invisible index, for example, updates can still be performed. D. This statement is correct. It is possible to have both a UNIQUE index and a non-unique index on the same column. The UNIQUE index enforces the uniqueness of column values, while the non-unique index does not. E. This statement is correct to some extent. Descending indexes are not function-based indexes per se, but they are indexes on which the data is sorted in descending order, as opposed to the default ascending order. However, descending indexes are conceptually related to function-based indexes because they alter the way the indexed data is stored. F. This statement is incorrect.
The use of an index in query execution depends on the optimizer's decision, which is based on statistics and the cost associated with using the index. There are situations where the optimizer may choose a full table scan even if there is an index on the filter column.
References can be found in the Oracle Database Concepts Guide and the SQL Language Reference documentation, which detail the behavior of indexes and how they are managed within the Oracle database.

NEW QUESTION # 69
Examine the data in the ORDERS table:

Examine the data in the INVOICES table:

Examine this query:
SELECT order_ id, order_ date FROM orders
INTERSECT
SELECT order_ 1d, order_ date FROM invoices;
Which two rows will it return?

• A. 2 <null>
• B. 1 <null>
• C. 5 01-MAR-2019
• D. 3 <null>
• E. 3 01-JAN-2019
• F. 4 01-FEB-2019

Answer: B,F

Explanation:
The INTERSECT operator in SQL returns the results that are common to both of the SELECT statements. It functions similarly to a set intersection in mathematics. When comparing rows for the INTERSECT operation, Oracle Database uses all the expressions in the SELECT lists to derive the result set. NULL values are considered equal for the INTERSECT operator.
Evaluating the provided data from the ORDERS and INVOICES tables, let's see which rows have the same ORDER_ID and ORDER_DATE in both tables:
* A: Order ID 3 has a NULL order date in the ORDERS table and does not match with any row in the INVOICES table, so it will not be returned.
* B: Order ID 2 has a NULL order date in the ORDERS table but has a non-NULL order date in the INVOICES table, so it will not be returned.
* C: Order ID 1 has a NULL order date in both tables, but INTERSECT considers NULLs as equal, so
* this will be returned.
* D: Order ID 5 has a date of 01-MAR-2019 in the ORDERS table and 01-APR-2019 in the INVOICES table, so it will not be returned since the dates do not match.
* E: Order ID 4 has a date of 01-FEB-2019 in both tables, so this row will be returned as it matches in both.
* F: Order ID 3 has a NULL order date in the ORDERS table but has 01-JAN-2019 in the INVOICES table, so it will not be returned.
Based on this analysis, the query will return:
* Order ID 1 with a NULL order date.
* Order ID 4 with an order date of 01-FEB-2019.
So the correct answer is:

NEW QUESTION # 70
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. MINUS
• B. UNION
• C. UNION ALL
• D. INTERSECT

Answer: A

NEW QUESTION # 71
Examine the description of the PRODUCT_ DETAILS table:

Which two statements are true?

• A. EXPIRY_ DATE contains the SYSDATE by default if no date is assigned to it.
• B. EXPIRY_ DATE cannot be used in arithmetic expressions.
• C. PRODUCT_ PRICE contains the value zero by default if no value is assigned to it.
• D. PRODUCT_ ID can be assigned the PRIMARY KEY constraint.
• E. PRODUCT_ PRICE can be used in an arithmetic expression even if it has no value stored in it.
• F. PRODUCT_ NAME cannot contain duplicate values.

Answer: D,E

NEW QUESTION # 72
Which two statements are true regarding the COUNT function? (Choose two.)

• A. The COUNT function can be used only for CHAR, VARCHAR2 and NUMBER data types
• B. A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE clause
• C. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column
• D. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns
• E. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column

Answer: C,D

Explanation:
Using the COUNT Function
The COUNT function has three formats:
COUNT(*)
COUNT(expr)
COUNT(DISTINCT expr)
COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT
statement, including duplicate rows and rows containing null values in any of the columns.
If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of
rows that satisfy the condition in the WHERE clause.
In contrast,
COUNT(expr) returns the number of non-null values that are in the column identified by
expr.
COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the
column identified by expr.

NEW QUESTION # 73
View the Exhibit and examine the description of the EMPLOYEES table.

You want to calculate the total renumeration for each employee. Total renumeration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission.
Which SQL statement would you execute to get the desired output?

• A. SELECT first_name, salary (salary + NVL (commission_pct, 0)*salary)*12 "Total"FROM EMPLOYEES;
• B. SELECT first_name, salary*12 + NVL(salary,0)*commission_pct, "Total"FROM EMPLOYEES;
• C. SELECT first_name, salary, salary*12+(salary*NVL2 (commission_pct,
  salary,salary+commission_pct))"Total"FROM EMPLOYEES;
• D. SELECT first_name, salary, salary*12+salary*commission_pct "Total"FROM EMPLOYEES;

Answer: C

NEW QUESTION # 74
View the exhibit and examine the structures of the EMPLOYEESand DEPARTMENTStables.

You want to update EMPLOYEEStable as follows:
Update only those employees who work in Boston or Seattle (locations 2900 and 2700).

Set department_idfor these employees to the department_idcorresponding to London

(location_id 2100).
Set the employees' salary in location_id2100 to 1.1 times the average salary of their department.

Set the employees' commission in location_id2100 to 1.5 times the average commission of their

department.
You issue the following command:

What is outcome?

• A. It executes successfully but does not give the desired update
• B. It generates an error because a subquery cannot have a join condition in a UPDATEstatement.
• C. It executes successfully and gives the desired update
• D. It generates an error because multiple columns (SALARY, COMMISSION)cannot be specified together in an UPDATEstatement.

Answer: A

NEW QUESTION # 75
Evaluate the following CRTEATE TABLE commands:
CREATE_TABLE orders
(ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE,
cust_id NUMBER (4) );
CREATE TABLE ord_items
(ord _no NUMBER (2),
item_no NUMBER(3),
qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200),
expiry_date date CHECK (expiry_date> SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no),
CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) );
Why would the ORD_ITEMS table not get created?

• A. The BETWEEN clause cannot be used for the CHECK constraint.
• B. The CHECK constraint cannot be placed on columns having the DATE data type.
• C. SYSDATE cannot be used with the CHECK constraint.
• D. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

Answer: C

NEW QUESTION # 76
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 SALIES2 table.
Which set operator generates the required output?

• A. MINUS
• B. UNION
• C. UNION ALL
• D. SUBTRACT
• E. INTERSECT

Answer: A

NEW QUESTION # 77
You issue this command which succeeds:
SQL> DROP TABLE products;
Which three statements are true?

• A. All existing views and synonyms that refer to the table are invalidated but retained.
• B. Any uncommitted transaction in the session is committed.
• C. Table data is deleted but the table structure is retained.
• D. All the table's indexes if any exist, are invalidated but retained.
• E. Table data and the table structure are deleted.

Answer: A,D,E

NEW QUESTION # 78
View the Exhibit and examine the structure of the PRODUCT_INFORMATION table. (Choose the best answer.)

PRODUCT_ID column is the primary key.
You create an index using this command:
SQL > CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));
No other indexes exist on the PRODUCT_INFORMATION table.
Which query would use the UPPER_NAME_IDX index?

• A. SELECT product_id
  FROM product_information
  WHERE UPPER(product_name) IN ('LASERPRO', 'CABLE');
• B. SELECT product_id, UPPER(product_name)
  FROM product_information
  WHERE UPPER(product_name) = 'LASERPRO' OR list_price > 1000;
• C. SELECT UPPER(product_name)
  FROM product_information
  WHERE product_id = 2254;
• D. SELECT UPPER(product_name)
  FROM product_information;

Answer: A

NEW QUESTION # 79
Examine this SQL statement:
SELECT cust_id, cust_last_name "Last Name
FROM customers
WHERE countryid=10
UNION
SELECT custid CUSTNO, cust_last_name
FROM customers
WHERE countryid=30
Identify three ORDER BY clauses, any one of which can complete the query successfully.

• A. ORDERBY2, 1
• B. ORDER BY CUSTNO
• C. ORDER BY"CUST NO"
• D. ORDER BY 2, cust_id
• E. ORDER BY "Last Name"

Answer: A,D,E

NEW QUESTION # 80
......

New (2024) Oracle 1z0-071 Exam Dumps: https://www.testsimulate.com/1z0-071-study-materials.html