Oracle 1z0-071 Exam Questions Free Practice Test

Viewing page 5 out of 10 pages

Viewing questions 41-50 out of questions

Questions # 41:

Which two statements are true about selecting related rows from two tables based on entity relationship diagram (ERD)?

Options:

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

An inner join relates rows within the same table.

Rows from unrelated tables cannot be joined.

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

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

Expert Solution

Questions # 42:

Which two statements are true about the rules of precedence for operators?

Options:

Arithmetic operators with equal precedence are evaluated from left to right within an expression.

Multiple parentheses can be used to override the default precedence of operators in an expression.

The + binary operator has the highest precedence in an expression in a SQL statements.

NULLS influence the precedence of operators in an expression.

The concatenation operator || is always evaluated before addition and subtraction in an expression.

Expert Solution

Questions # 43:

Examine the description of the CUSTOMERS table:

Which three statements will do an implicit conversion?

Options:

SELECT * FROM customers WHERE insert_date=DATE’2019-01-01’;

SELECT * FROM customers WHERE customer_id=’0001’;

SELECT * FROM customers WHERE TO_DATE(insert_date)=DATE’2019-01-01’;

SELECT * FROM customers WHERE insert_date’01-JAN-19’;

SELECT * FROM customers WHERE customer_id=0001;

SELECT * FROM customers WHERE TO_CHAR(customer_id)=’0001’;

Expert Solution

Questions # 44:

Which three statements are true about GLOBAL TEMPORARY TABLES?

Options:

A GLOBAL TEMPORARY TABLE cannot have PUBLIC SYNONYM.

A GLOBAL TEMPORARY TABLE can have multiple indexes

A GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view.

Data Manipulation Language (DML) on GLOBAL TEMPORARY TABLES generates no REDO.

A GLOBAL TEMPORARY TABLE can have only one index.

A trigger can be created on a GLOBAL TEMPORARY TABLE

Expert Solution

Questions # 45:

Examine the description of the CUSTONERS table:

Question # 45

CUSTNO is the PRIMARY KEY.

You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names.

Which two methods can you use to get the required result?

Options:

LEFT OUTER JOIN with self join

PULL OUTER JOIN with self join

subquery

RIGHT OUTER JOIN with self join

self Join

Expert Solution

Questions # 46:

Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?

Options:

SELECT deptno,LISTAGG(ename, ' , ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno;

SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno ORDER BY ename;

SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (GROUP BY deptno) AS employee_list FROM emp ORDER BY ename;

SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM emp GROUP BY deptno;

Expert Solution

Questions # 47:

Examine the description of the EMPLOYEES table:

Question # 47

NLS_DATE_FORMAT is set to DD-MON-YY.

Which query requires explicit data type conversion?

Options:

SELECT salary + 120.50 FROM employees;

SELECT SUBSTR(join date, 1, 2)- 10 FROM employees;

SELECT join date 11.’11 salary FROM employees;

SELECT join date FROM employees where join date > *10-02-2018*;

SELECT join date + 20 FROM employees;

Expert Solution

Questions # 48:

The CUSTOMERS table has a CUST_CREDT_LIMIT column of data type number.

Which two queries execute successfully?

Options:

SELECT TO_CHAR(NVL(cust_credit_limit * .15,'Not Available')) FROM customers;

SELECT NVL2(cust_credit_limit * .15,'Not Available') FROM customers;

SELECT NVL(cust_credit_limit * .15,'Not Available') FROM customers;

SLECT NVL(TO_CHAR(cust_credit_limit * .15),'Not available') from customers;

SELECT NVL2(cust_credit_limit,TO_CHAR(cust_credit_limit * .15),'NOT Available') FROM customers;

Expert Solution

Questions # 49:

Examine this statement:

Question # 49

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

Options:

The word CONSTRAINT in the foreign key constraint on DEPT_ID must be changed to FOREIGN KEY.

The foreign key constraint on DEPT_ID must be defined at the table level instead of the column level.

One of the LONG columns must be changed to a VARCHAR2 or CLOB.

The NOT NULL constraint on ENAME must be defined at the column level instead of the table level.

The primary key constraint on BMP_ID must have a name.

Expert Solution

Questions # 50:

Examine these statements and results：

SQL> SELECT COUNT(*) FROM emp

COUNT(*)

---------------------

sQL> CREATE GLOBAL TEMPORARY TABLE t emp As SELECT * FROM emp;

Table created

SQL> INSERT INTo temp SELECT * FROM emp;

14 rows created

SQL> COMMIT:

Commit complete*

SQL> INSERT INTo temp SELECT * EROM emp;

14. rows created

SQL> SELECT COUNT(*) FROM t emp

How many rows are retrieved by the last query?

Options:

Expert Solution

Viewing page 5 out of 10 pages

Viewing questions 41-50 out of questions

Pass the Oracle Oracle PL 1z0-071 Questions and answers with CertsForce