Free Oracle 1z0-071 Practice Test & Real Exam Questions, Page 4

Question #43

Examine the structure proposed for the TRANSACTIONS table:

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

A. The TRANS_VALIDITY column would give an error.

B. The CUST_STATUS column would give an error.

C. The TRANS_VALIDITY column would have a maximum size of one character.

D. The CUST_STATUS column would store exactly one character.

E. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.

F. The CUST_CREDIT_LIMIT column would be able to store decimal values.

Discussion 0

Correct Answer: A,D Vote an answer

Question #44

View the Exhibit and examine the structure of the EMPLOYEES table.
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_NAME of the managers and the second column would have LAST_NAME of the employees.
Which SQL statement would you execute?

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

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

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

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

Discussion 0

Correct Answer: C Vote an answer

Question #45

Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)

A. During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution.

B. During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.

C. During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.

D. During executing, the oracle server may read data from storage if the required data is not already in memory.

Discussion 0

Correct Answer: D Vote an answer

Question #46

Which two statements are true about selecting related rows from two tables based on an Entity Relationship Diagram (ERD)? (Choose two.)

A. Implementing a relationship between two tables might require joining additional tables

B. An inner join relates rows within the same table

C. Every relationship between the two tables must be implemented in a join condition

D. Rows from unrelated tables cannot be joined

E. Relating data from a table with data from the same table is implemented with a self join

Discussion 0

Correct Answer: A,E Vote an answer

Question #47

Which two statements are true about an Oracle database? (Choose two.)

A. A table can have multiple foreign keys.

B. A column definition can specify multiple data types.

C. A NUMBER column without data has a zero value.

D. A VARCHAR2 column without data has a NULL value.

E. A table can have multiple primary keys.

Discussion 0

Correct Answer: A,D Vote an answer

Question #48

In your session, the NLS_DATE_FORMATis DD-MM-YYYY.
There are 86400 seconds in a day.
Examine this result:
DATE
-----------
02-JAN-2020
Which statement returns this?

A. SELECT TO_CHAR(TO_DATE('29-10-2019') + INTERVAL '3' MONTH + INTERVAL '7' DAY - INTERVAL '360' SECOND, 'DD-MON-YYYY') AS "date" FROM DUAL;

B. SELECT TO_CHAR(TO_DATE('29-10-2019') + INTERVAL '2' MONTH + INTERVAL '6' DAY - INTERVAL '120' SECOND, 'DD-MON-YYYY') AS "date" FROM DUAL;

C. SELECT TO_CHAR(TO_DATE('29-10-2019') + INTERVAL '2' MONTH + INTERVAL '4' DAY - INTERVAL '120' SECOND, 'DD-MON-YYYY') AS "date" FROM DUAL;

D. SELECT TO_CHAR(TO_DATE('29-10-2019') + INTERVAL '2' MONTH + INTERVAL '5' DAY - INTERVAL '120' SECOND, 'DD-MON-YYYY') AS "date" FROM DUAL;

E. SELECT TO_CHAR(TO_DATE('29-10-2019') + INTERVAL '2' MONTH + INTERVAL '5' DAY - INTERVAL '86410' SECOND, 'DD-MON-YYYY') AS "date" FROM DUAL;

Discussion 0

Correct Answer: C Vote an answer

Question #49

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

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

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

C. They can be used on columns or expressions.

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

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

Discussion 0

Correct Answer: A,C,E Vote an answer

Question #50

Examine the commands used to create DEPARTMENT_DETAILSand COURSE_DETAILS:

You want to generate a report that shows all course IDs irrespective of whether they have corresponding department IDs or not but no department IDs if they do not have any courses.
Which SQL statement must you use?

A. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id=d. department_id)

B. SELECT c.course_id, d.department_id FROM course_details c RIGHT OUTER JOIN .department_details d ON (c.depatrment_id=d.department_id)

C. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id<>d. department_id)

D. SELECT course_id, department_id, FROM department_details d RIGHT OUTER JOIN course_details c USING (department_id)

Discussion 0

Correct Answer: A Vote an answer

Question #51

Examine the description of the EMPLOYEES table:

Which two queries return all the rows for employees whose salary is greater than the average salary in their department? (Choose two.)

A.

B.

C.

D.

E.

Discussion 0

Correct Answer: A,B Vote an answer

Question #52

Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
------------------------
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC." Which query gives the required output?

A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers
WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');

B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';

C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');

D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';

Discussion 0

Correct Answer: D Vote an answer

Question #53

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 generates an error because the condition specified for the PROD_CATEGORY column is not valid.

B. It executes successfully and returns the required result.

C. It executes successfully but returns no result.

D. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.

Discussion 0

Correct Answer: C Vote an answer

Question #54

You want to display the date for the first Monday of the next month and issue the following command:

What is the outcome?

A. It generates an error because rrrr should be replaced by rr in the format string.

B. It generates an error because fm and double quotation marks should not be used in the format string.

C. It executes successfully but does not return the correct result.

D. It generates an error because TO_CHAR should be replaced with TO_DATE.

E. It executes successfully and returns the correct result.

Discussion 0

Correct Answer: E Vote an answer

Question #55

Which three statements are true about performing Data Manipulation Language (DML) operations on a view in an Oracle Database? (Choose three.)

A. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains aggregating functions.

B. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.

C. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.

D. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.

E. Insert statements can always be done on a table through a view.

F. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the columns are not referenced in the defining query of the view.
PRIMARY KEY

Discussion 0

Correct Answer: A,B,F Vote an answer

Question #56

Which statement is true regarding the UNION operator?

A. By default, the output is not sorted.

B. Null values are not ignored during duplicate checking.

C. The number of columns selected in all select statements need not be the same.

D. Names of all columns must be identical across all select statements.

Discussion 0

Correct Answer: B Vote an answer

Free Oracle 1z0-071 Practice Test & Real Exam Questions

Download Free Oracle 1z0-071 Demo