Weekend Sale Special Limited Time 65% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: dumps65

DumpsWrap Logo

Oracle 1z0-071 Dumps

Page: 1 / 32
Total 324 questions
Get 1z0-071 Full Access Download 1z0-071 PDF

Oracle Database 12c SQL Questions and Answers

Question 1

Table ORDER_ITEMS contains columns ORDER_ID, UNIT_PRICE and QUANTITY, of data type NUMBER

Statement 1:

SELECT MAX (unit price*quantity) "Maximum Order FROM order items;

Statement 2:

SELECT MAX (unit price*quantity "Maximum order" FROM order items GROUP BY order id;

Which two statements are true?

Options:

A.

Statement 2 returns only one row of output.

B.

Both the statement given the same output.

C.

Both statements will return NULL if either UNIT PRICE or QUANTITY contains NULL,

D.

Statement 2 may return multiple rows of output.

E.

Statement 1 returns only one row of output.

Question 2

Examine the description of the CUSTOMERS table:

as

Which two SELECT statements will return these results:

CUSTOMER_ NAME

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

Mandy

Mary

Options:

A.

SELECT customer_ name FROM customers WHERE customer_ name LIKE ' % a % ’ ;

B.

SELECT customer_ name FROM customers WHERE customer name LIKE 'Ma%' ;

C.

SELECT customer_ name FROM customers WHERE customer_ name='*Ma*';

D.

SELECT customer_ name FROM customers WHERE UPPER (customer_ name ) LIKE 'MA*. ;

E.

SELECT customer_ name FROM customers WHERE customer name LIKE 'Ma*';

F.

SELECT customer_ name FROM customers WHERE UPPER (customer name) LIKE 'MA&';

G.

SELECT customer_ name FROM customers WHERE customer_ name KIKE .*Ma*';

Question 3

Which two are true about scalar subquery expressions?

Options:

A.

You cannot correlate them with a table in the parent statement

B.

You can use them as a default value for a column.

C.

.You must enclose them in parentheses.

D.

They can return at most one row.

E.

They can return two columns.

Question 4

Examine these statements which execute successfully:

ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24 MI: SS’

ALTER SESSION SET TIME_ ZONE = ‘-5:00’;

SELECT DBTIMEZONE, SYSDATE FROM DUAL

Examine the result:

as

If LOCALTIMESTAMP was selected at the same time what would it return?

Options:

A.

11-JUL-2019 6,00,00,00000000 AM – 05:00

B.

11-JUL-2019 11,00,00,00000000 AM

C.

11-JUL-2019 6,00,00,000000 AM

D.

11-JUL-2019 11,00,00,000000AM -05:00

Question 5

Examine this statement:

CREATE TABTE orders

(sarial_no NUMBER UNIQUE,

order_id NUMBER PRIMARY KEY ,

order_date DATE NOT NULL,

status VARCHAR2 (10) CHECK (status IN ('CREDIT', 'CASH')),

product_id NUMBER REFERENCES products (product_id),

order_ total NUMBER);

On which two columns of the table will an index be created automatically?

Options:

A.

SERIAL_NO

B.

ORDER_DATE

C.

PRODUCT_ ID

D.

ORDER TOTAL

E.

ORDER_ ID

F.

STATUS

Question 6

Which two are true about queries using set operators such as UNION?

Options:

A.

An expression in the first SELECT list must have a column alias for the expression

B.

CHAR columns of different lengths used with a set operator retum a vAacsua mhtoe e equals the longest CHAR value.

C.

Queries using set operators do not perform implicit conversion across data type groups (e.g. character, numeric)

D.

In a query containing multiple set operators INTERSECT always takes precedence over UNION and UNION ALL

E.

All set operators are valid on columns all data types.

Question 7

Examine the description of the PRODUCTS table:

as

Which three queries use valid expressions?

Options:

A.

SELECT produet_id, unit_pricer, 5 "Discount",unit_price+surcharge-discount FROM products;

B.

SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;

C.

SELECT ptoduct_id, (expiry_date-delivery_date) * 2 FROM products;

D.

SPLECT product_id, expiry_date * 2 FROM products;

E.

SELEGT product_id, unit_price, unit_price + surcharge FROM products;

F.

SELECT product_id,unit_price || "Discount", unit_price + surcharge-discount FROM products;

Question 8

Which two join conditions in a from clause are non-equijoins?

Options:

A.

tablet join table2 ON (table1.column = table2.column) where table2.column LIKE 'A'

B.

table1 join table2 on (table1.column between table2.column] and table2.column2)

C.

table1 natural JOIN table2

D.

table1 join table2 using (column1, column2)

E.

table1 join table2 ON (table1.column >= table2.column)

Question 9

Which three items does a direction of a relationship contain?

Options:

A.

an attribute

B.

a cardinality

C.

label

D.

an optionality

E.

a unique identifier

F.

an entity

Question 10

Examine this Statement which returns the name of each employee and their manager,

SELECT e.last name AS emp,,m.last_name AS mgr

FROM employees e JOIN managers m

ON e.manager_ id = m. employee_ id ORDER BY emp;

You want to extend the query to include employees with no manager. What must you add before JOIN to do this?

Options:

A.

CROSS

B.

FULL OUTER

C.

LEFT OUTER

D.

RIGHT OUTER

Question 11

Which statements is true about using functions in WHERE and HAVING?

Options:

A.

using single-row functions in the WHERE clause requires a subquery

B.

using single-row functions in the HAVING clause requires a subquery

C.

using aggregate functions in the WHERE clause requires a subquery

D.

using aggregate functions in the HAVING clause requires a subquery

Question 12

Which two statements are true about Oracle synonyms?

Options:

A.

A synonym can have a synonym.

B.

All private synonym names must be unique in the database.

C.

Any user can create a PUBLIC synonym.

D.

A synonym can be created on an object in a package.

E.

A synonym has an object number.

Question 13

Examine the description of the SALES table:

as

The SALES table has 5,000 rows.

Examine this statement:

CREATE TABLE sales1 (prod id, cust_id, quantity_sold, price)

AS

SELECT product_id, customer_id, quantity_sold, price

FROM sales

WHERE 1=1

Which two statements are true?

Options:

A.

SALES1 is created with 1 row.

B.

SALES1 has PRIMARY KEY and UNIQUE constraints on any selected columns which had those constraints in the SALES table.

C.

SALES1 Is created with 5,000 rows.

D.

SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table.

Question 14

Examine this partial command:

CREATE TABLE cust(

cust_id NUMBER(2),

credit_limit NUMBER(10)

ORGANIZATION EXTERNAL

Which two clauses are required for this command to execute successfully?

Options:

A.

the ACCESS PARAMETERS clause

B.

the DEFAULT DIRECTORY clause

C.

the access driver TYPE clause

D.

the LOCATION clause

E.

the REJECT LIMIT clause

Question 15

Which two queries execute successfully?

Options:

A.

SELECT prod_id, exp_date FROM products

UNION ALL

SELECT prod_id, NULL FROM new_products;

B.

SELECT prod_id, prod_name FROM products

INTERSECT

SELECT 100, prod_name FROM newproducts;

C.

SELECT * FROM products

UNION

SELECT * FROM new_products;

D.

SELECT k FROM products

MINUS

SELECT prod_id FROM new_products;

E.

SELECT prod_id FROM products

UNION ALL

SELECT prod_id, prod_name FROM new_products;

Question 16

Examine this statement which executes successfully:

Which three are true?

Options:

A.

Regardless of salary,only if the employee id is less than 125,insert EMPLOYEE_ID,NANAGER_ID,SALARY into the MGR_HISTORY table.

B.

If the salary is more than 20000 and the employee is less than 125,insert EMPLOYEE_ID and SALARY into the SPECIAL_SAL table.

C.

Only if the salary is 20000 or less and the employee id is less than 125,insert EMPLOYEE_ID,MANAGER_ID,and SALARY into the MGR_HISTORY table.

D.

Regardless of salary and employee id,insert EMPLOYEE_ID,MANAGER_ID,and SALARY into the MGR_HISTORY table.

E.

If the salary is 20000 or less and the employee id is less than 125,insert EMPLOYEE_ID,HIRE_DATE,and SALARY into the SAL_HISTORY table.

F.

Only if the salary is 20000 or less and the employee id is 125 or higher,insert EMPLOYEE_ID,MANAGER_ID,and SALARY into the MDR_HISTORY table.

Question 17

Which two statements are true about the SET VERIFY ON command?

Options:

A.

It displays values for variables created by the DEFINE command.

B.

It can be used in SQL Developer and SQL*Plus.

C.

It can be used only in SQL*plus.

D.

It displays values for variables prefixed with &&.

E.

It displays values for variables used only in the WHERE clause of a query.

Question 18

Which three statements are true?

Options:

A.

A customer can exist in many countries.

B.

The statement will fail if a row already exists in the SALES table for product 23.

C.

The statement will fail because subquery may not be I contained in a values clause.

D.

The SALES table has five foreign keys.

E.

The statement will execute successfully and a new row will be inserted into the SALES table.

F.

A product can have a different unit price at different times.

Question 19

You execute this query:

SELECT TO CHAR (NEXT_DAY(LAST_DAY(SYSDATE),’MON’ ),’ dd“Monday for” fmMonth rrr’) FROM DUAL;

What is the result?

Options:

A.

It executes successfully but does not return any result.

B.

It returns the date for the first Monday of the next month.

C.

It generates an error.

D.

It returns the date for the last Monday of the current month.

Question 20

Examine this statement which executes successfully:

CREATE view emp80 AS

SELECT

FROM employees

WHERE department_ id = 80

WITH CHECK OPTION;

Which statement will violate the CHECK constraint?

Options:

A.

DELETE FROM emp80

WHERE department_ id = 90;

B.

SELECT

FROM emp80

WHERE department_ id = 90;

C.

SELECT

FROM emp80

WHERE department. id = 80;

D.

UPDATE emp80

SET department. 1d =80;

WHERE department_ id =90;

Question 21

The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE

You want to display the date of the first Monday after the completion of six months since hiring.

The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day of the week Which query can be used?

Options:

A.

SELECT emp_id,NEXT_DAY(ADD_MONTHS(hite_date,6),'MONDAY') FROM employees;

B.

SELECT emp_id,ADD_MONTHS(hire_date,6), NEXT_DAY('MONDAY') FROM employees;

C.

SELECT emp_id,NEXT_DAY(MONTHS_BETWEEN(hire_date,SYSDATE),6) FROM employees;

D.

SELECT emp_id,NEXT_DAY(ADD_MONTHS(hire_date,6),1) FROM employees;

Question 22

Examine these statements and results：

SQL> SELECT COUNT(*) FROM emp

COUNT(*)

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

14

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:

A.

28

B.

0

C.

14

D.

42

Question 23

Examine the description or the BOOKS_TRANSACTIONS table:

as

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?

Options:

A.

SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT

FROM customers

WHERE cust income_level !=NULL

AND cust credit_level !=NULL;

B.

SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT

FROM customers

WHERE cust income_level IS NOT NULL

AND due_amount IS NOT NULL;

C.

SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT

FROM customers

WHERE cust income_level <> NULL

AND due_amount <> NULL;

D.

SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT

FROM customers

WHERE cust_income_level IS NOT NULL

AND cust_credit_limit IS NOT NULL;

E.

SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT

FROM customers

WHERE cust income_level !=NULL

AND due_amount !=NULL;

Question 24

Examine these SQL statements which execute successfully:

as

Which two statements are true after execution?

Options:

A.

The primary key constraint will be enabled and DEFERRED.

B.

The primary key constraint will be enabled and IMMEDIATE.

C.

The foreign key constraint will be disabled.

D.

The foreign key constraint will be enabled and DEFERRED.

E.

The foreign key constraint will be enabled and IMMEDIATE.

Question 25

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

Options:

A.

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

B.

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

C.

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

D.

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

E.

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

F.

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.

Question 26

Which three statements about roles are true?

Options:

A.

Roles are assigned to roles using the ALTER ROLE statement.

B.

A single user can be assigned multiple roles.

C.

Roles are assigned to users using the ALTER USER statement.

D.

A single role can be assigned to multiple users.

E.

Privileges are assigned to a role using the ALTER ROLE statement.

F.

A role is a named group of related privileges that can only be assigned to a user.

G.

Privileges are assigned to a role using the GRANT statement.

Question 27

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

Options:

A.

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

B.

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

C.

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

D.

NULLS influence the precedence of operators in an expression.

E.

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

Question 28

Which statement fails to execute successfully?

A)

as

B)

as

C)

as

D)

as

Options:

A.

Option A

B.

Option B

C.

Option C

D.

Option D

Question 29

Which three statements are true about GLOBAL TEMPORARY TABLES?

Options:

A.

GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table.

B.

A TRUNCATE command issued in a session causes all rows In a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.

C.

A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.

D.

A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions.

E.

Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.

F.

GLOBAL TEMPORARY TABLE space allocation occurs at session start.

Question 30

A session's NLS_DATE_FORMAT is set to DD Mon YYYY .

Which two queries return the value 1 Jan 2019?

Options:

A.

SELECT to_date(' 2019-01-01 ', 'YYYY -MM-DD' ) FROM DUAL;

B.

SELECT DATE '2019-01-01' FROM DUAL ;

C.

SELECT TO_CHAR('2019-01-01') FROM DUAL; 2019-01-01

D.

SELECT '2019-01-01' FROM DUAL ; 2019-01-01

E.

SELECT TO_ DATE('2019-01-01') FROM DUAL;

Question 31

Examine this statement:

SELECT1 AS id,‘ John’ AS first_name, NULL AS commission FROM dual

INTERSECT

SELECT 1,’John’ null FROM dual ORDER BY 3;

What is returned upon execution?[

Options:

A.

2 rows

B.

0 rows

C.

An error

D.

1 ROW

Question 32

Which three statements are true about the DESCRIBE command?

Options:

A.

It can be used from SQL Developer.

B.

It can be used to display the structure of an existing view.

C.

It can be used only from SQL*Plus.

D.

It displays the NOT NULL constraint for any columns that have that constraint.

E.

It displays all constraints that are defined for each column.

F.

It displays the PRIMARY KEY constraint for any column or columns that have that constraint.

Question 33

Which two statements are true about the ORDER BY clause?

Options:

A.

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

B.

Only columns that are specified in the SELECT list can be used in the ORDER BY cause.

C.

NULLS are not included in the sort operation.

D.

Column aliases can be used In the ORDER BY cause.

E.

Ina character sort, the values are case-sensitive.

Question 34

Viev the Exhibit and examine the structure of the PRODUCT INFORMATION and INVENTORIEStables.

You have a requirement from the supplies department to give a list containing PRODUCT _ID,SUPPLIER ID, and QUANTITY_ON HAND for all the products where in QUANTITY ON HAND is lessthan five.

Which two SQL statements can accomplish the task? (Choose two)

Options:

A.

SELECT product id, quantity on hand, supplier id

FROM product information

NATURAL JOIN inventories AND quantity .on hand < 5;

B.

SELECT i. product id, i. quantity .on hand, pi. supplier_id

FROM product_information pi JOIN inventories i

ON (pi. product. id=i. product id) AND quantity on hand < 5;

C.

SELECT i. product_id, i. quantity_on hand, pi. supplier id

FROM product information pi JOIN inventories i USING (product id) AND quantity .on hand < 5;

D.

SELECT i.product id, i. quantity on hand, pi. supplier id

FROM product information pi JOIN inventories i

ON (pi.product id=i. product id)WHERE quantity on hand < 5;

Question 35

Examine the description of the MEMBERS table;

SELECT city,last_name LNAME FROM members …

You want to display all cities that contain the string AN. The cities must be returned in ascending order, with the last names further sorted in descending order.

Which two clauses must you add to the query?

Options:

A.

ORDER BY 1,2.

B.

ORDER BY last_name DESC,city ASC

C.

CORADER BY 1, LNAME DESC

D.

WHERE city=’%AN%;

E.

WHERE city LIKE ’%AN%;

F.

WHERE city IN (’%AN%’)

Question 36

The PROD_ID column is the foreign key in the SALES table.Which references the PRODUCTS table.

Similarly,the CUST_ID and TIME_ID columns are Also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.

Evaluate the following CREATE TABLE command:

CREATE TABLE new_sales(prod_id, I cust_id, order_date DEFAULT SYSDATE)

AS SELECT I prod_id,cust_id,time_id FROM sales.

Which statement is true regarding the above command?

Options:

A.

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

B.

The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.

C.

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

D.

The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table

Question 37

Examine the description of the PRODUCT_INFORMATION table:

as

Options:

A.

SELECT (COUNT(list_price) FROM Product_intormation WHERE list_price=NULL;

B.

SELECT count(nvl( list_price,0)) FROM product_information WHERE list_price is null;

C.

SELECT COUNT(DISTINCT list_price) FROM product_information WHERE list_price is null.

D.

BELECT COUNT(list_price) FROM product_information where list_price is NULL;

Question 38

Which three statements are true about Oracle synonyms?

Options:

A.

A synonym cannot be created for a PL /SQL package.

B.

A SEQUENCE can have a synonym.

C.

A synonym can be available to all users .

D.

A synonym created by one user can refer to an object belonging to another user.

E.

Any user can drop a PUBLIC synonym.

Question 39

Whith three statements are true about built in data types?

Options:

A.

A VARCHAR2 blank pads column values only if the data stored is non numeric and contains no special characlers

B.

A BFILE stores unstructured binary data in operating systerm files

C.

A CHAR column definition does not require the length to be specified

D.

The default length for a CHAR column is always one character

E.

A VARCHAR2 column definition does not require the length to be specified

F.

A BLOB stores unstructured binary data within the database

Question 40

In your session, the NLS._DAE_FORMAT is DD- MM- YYYY.There are 86400 seconds in a day.Examine

this result:

DATE

02-JAN-2020

Which statement returns this?

Options:

A.

SELECT TO_ CHAR(TO_ DATE(‘29-10-2019’) +INTERVAL ‘2’; MONTH + INTERVAL ‘5’; DAY -

INTERVAL ‘86410’ SECOND, ‘ DD-MON-YYYY’) AS "date"

FROM DUAL;

B.

SELECT TO_ CHAR(TO_ DATE(‘29-10-2019’) + INTERVAL ‘3’ MONTH + INTERVAL ‘7’ DAY -

INTERVAL ‘360’ SECOND, ‘ DD-MON-YYYY’) AS "date"

FROM DUAL;

C.

SELECT To CHAR(TO _DATE(‘29-10-2019’) + INTERVAL ‘2’ NONTH + INTERVAL ‘5’ DAY

INEERVAL ‘120’ SECOND, ‘ DD-MON-YYY) AS "date"

FROM DUAL;

D.

SELECT-TO_CHAR(TO _DATE(‘29-10-2019’+ INTERVAL ‘2’ MONTH+INTERVAL ‘6’ DAYINTERVAL

‘120’ SECOND, ‘DD-MON-YY’) AS "daTe"

FROM DUAL;

E.

SELECT-TO_CHAR(TO _DATE(‘29-10-2019’+ INTERVAL ‘2’ MONTH+INTERVAL ‘4’ DAYINTERVAL

‘120’ SECOND, ‘DD-MON-YY’) AS "daTe"

FROM DUAL;

Question 41

Which two queries execute successfully?

Options:

A.

SELECT INTERVAL '1' DAY - SYSDATE FROM DUAL;

B.

SELECT SYSTIMESTAMP + INTERVAL '1' DAY FROM DUAL;

C.

SELECT INTERVAL '1' DAY - INTERVAL '1' MINUTE FROM DUAL;

D.

select INTERVAL '1' DAY +INTERVAL '1' MONTH FROM DUAL;

E.

SELECT SYSDATE “INTERRVAL '1' DAY FROM DUAL;

Question 42

Which two statements are true about INTERVAL data types

Options:

A.

INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years.

B.

The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column.

C.

INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.

D.

The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value.

E.

INTERVAL DAY TO SECOND columns support fractions of seconds.

F.

INTERVAL YEAR TO MONTH columns support yearly intervals.

Question 43

Which three statements are true about single row functions?

Options:

A.

They can be used only in the where clause of a select statement.

B.

They can accept only one argument.

C.

They return a single result row per table.

D.

The argument can be a column name, variable, literal or an expression.

E.

They can be nested to any level.

F.

The date type returned can be different from the data type of the argument.

Question 44

Which two statements are true regarding the UNION ALL operators?

Options:

A.

NULLS are not ignored during duplicate checking.

B.

Duplicates are eliminated automatically by the UNION ALL operator

C.

The names of columns selected in each SELECT statement must be identical.

D.

The number of columns selected in each SELECT statement must be identical

E.

The output is sorted by the UNION ALL operator.

Question 45

Which two statements are true about the DUAL table?

Options:

A.

It can display multiple rows and columns.

B.

It can be accessed only by the SYS user.

C.

It can be accessed by any user who has the SELECT privilege in any schema

D.

It can display multiple rows but only a single column.

E.

It consists of a single row and single column of VARCHAR2 data type.

F.

It can be used to display only constants or pseudo columns.

Question 46

You need to calculate the number of days from 1st January 2019 until today.

Dates are stored in the default format of DD-MON-RR.

Which two queries give the required output?

Options:

A.

SELECT SYSDATE-TO_DATE ('01-JANUARY-2019') FROM DUAL;

B.

SELECT TO_DATE (SYSDATE, 'DD/MONTH/YYYY')-'01/JANUARY/2019' FROM DUAL;

C.

SELECT ROUND (SYSDATE-TO_DATE ('01/JANUARY/2019')) FROM DUAL;

D.

SELECT TO_CHAR (SYSDATE, 'DD-MON-YYYY')-'01-JAN-2019' FROM DUAL;

E.

SELECT ROUND (SYSDATE- '01-JAN-2019') FROM DUAL:

Question 47

You must find the number of employees whose salary is lower than employee 110.

Which statement fails to do this?

Options:

A.

SELECT COUNT (*)

FROM employees

JOIN employees a

ON e. salary< a. salary

WHERE a. employee_ id= 110;

B.

SELECT COUNT (* )

FROM employees

WHERE salary < (SELECT salary FROM employees WHERE employee 业id =

110) ;

C.

SELECT COUNT (*)

FROM employees e

JOIN (SELECT salary FROM employees WHERE employee_ id= 110) a

ON e. salary< a. salary;

D.

SELECT COUNT (* )

FROM employees e

WHERE e. salary < (SELECT a. salary FROM employees a WHERE e. employee_ id = 110);

Question 48

Which statement will execute successfully?

Options:

A.

SELECT 1, 2 FROM DUAL

UNION

SELECT 3, 4 FROM DUAL

ORDER BY 1, 2;

B.

SELECT 3 FROM DUAL

UNION

SELECT 4 FROM DUAL

ORDER BY 3 ;

C.

SELECT 1, 2 FROM DUAL

UNION

SELECT 3, 4 FROM DUAL

ORDER BY 3, 4;

D.

SELECT 1 FROM DUAL

UNION

SELECT 2 FROM DUAL

ORDER BY 1, 2;

Question 49

Examine this partial command:

as

Which two clauses are required for this command to execute successfully?

Options:

A.

the DEFAULT DIRECTORY clause

B.

the REJECT LIMIT clause

C.

the LOCATION clause

D.

the ACCESS PARAMETERS clause

E.

the access driver TYPE clause

Question 50

Which two are true?

Options:

A.

CONCAT joins two or more character strings together.

B.

FLOOR returns the largest integer less than or equal to a specified number.

C.

CONCAT joins two character strings together.

D.

INSTR finds the offset within a string of a single character only.

E.

INSTR finds the offset within a character string, starting from position 0.

F.

FLOOR returns the largest positive integer less than or equal to a specified number.

Question 51

Examine this statement,which executes successfully:

In which order are the rows displayed?

Options:

A.

sorted by DEPARTMENT_NAME

B.

sorted by DEPARTMENT_NAME and AVGSAL

C.

sorted by DEPARTMENT_NAME and MAXSAL

D.

sorted by AVGSAL

E.

Sorted by MAXSAL

Question 52

Examine the BRICKS table:

as

You write this query:

SELECT

FROM bricks b1 CROSS JOIN bricks b2

WHERE b1. Weight < b2. Weight:

How many rows will the query return?

Options:

A.

1

B.

16

C.

10

D.

6

E.

4

F.

0

Question 53

Examine the description of the EMPLOYEES table:

as

NLS_DATE_FORMAT is set to DD-MON-YY.

Which query requires explicit data type conversion?

Options:

A.

SELECT salary + 120.50 FROM employees;

B.

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

C.

SELECT join date 11.’11 salary FROM employees;

D.

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

E.

SELECT join date + 20 FROM employees;

Question 54

Examine this SQL statement:

SELECT cust_id, cus_last_name "Last Name"

FROM customers

WHERE country_id = 10

UNION

SELECT cust_id CUST_NO, cust_last_name

FROM customers

WHERE country_id = 30

Identify three ORDER BY clauses, any one of which can complete the query successfully.

Options:

A.

ORDERBY 2, 1

B.

ORDER BY "CUST_NO"

C.

ORDER BY 2,cust_id

D.

ORDER BY CUST_NO

E.

ORDER BY "Last Name"

Question 55

Which is true about the & and && prefixes with substitution variables?

Options:

A.

& can prefix a substitution variable name only in queries. DML

B.

An & prefix to an undefined substitution variable, which is referenced twice in the same query, will prompt for a value twice .

C.

The && prefix will not prompt for a value even if the substitution variable is not previously defined in the session.

D.

An && prefix to an undefined substitution variable, which is referenced multiple times in multiple queries, will prompt for a value once per query.

E.

Both & and && can prefix a substitution variable name in queries and DML statements.

Question 56

Which two are true about rollbacks?

Options:

A.

The ROLLBACK statement does not release locks resulting from table updates.

B.

Data Control L anguage (DCL) statements, such as GRANT and REVOKE, can be rolled back.

C.

A transaction interrupted by a system failure is automatically rolled back.

D.

If the ROLLBACK statement is used without TO SAVEPOINT, then all savepoints in the transaction are deleted .

E.

Data consistency is not guaranteed after a rollback.

Question 57

Examine this query:

SELECT SUBSTR (SYSDATE,1,5) ‘Result’ FROM DUAL

Which statement is true?

Options:

A.

It fails unless the expression is modified to TO-CHAR(SUNBSTR(SYSDATE,1,5)

B.

It fails unless the expression is modified to SUBSTR (TO_ CHAR(SYSDATE),1,5)

C.

It fails unless the expression is modified to SUBSTR (TO_ CHAR(TRUNC(SYSDATE)),1,5)

D.

It executes successfully with an implicit data type conversion

Question 58

Evaluate these commands which execute successfully CREATE SEQUENCE ord_seq

INCREMENT BY 1

START WITH 1

MAXVALUE 100000

CYCLE

CACHE 5000;

Create table ord_items(

ord_no number(4) default ord_seq.nextval not null,

Item_no number(3),

Qty number(3),

Expiry_date date,

Constraint it_pk primary key(ord_no,item_no),

Constraint ord_fk foreign key (ord_no) references orders(ord_no));

Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?

Options:

A.

Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ.

B.

Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO.

C.

Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times

D.

IF sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS.

E.

Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.

Question 59

Examine this incomplete query:

SELECT DATA’2019-01-01’+

FROM DUAL;

Which three clauses can replaceti add 22 hours to the date?

Options:

A.

INTERVAL ‘12:00’

B.

INTERVAL’0,5’DAY

C.

INTERVAL’12’ HOUR

D.

INTERVAL’720’MINUTE

E.

INTERVAL’0 12’DAY TO HOUR

F.

INTERVAL’11:60’HOUR TO MINUTE

Question 60

You create a table by using this command:

CREATE TABLE rate_list (rate NUMBER(6,2));

Which two are true about executing statements?

Options:

A.

INSERT INTO rate_list VALUES (-.9) inserts the value as -.9.

B.

INSERT INTO rate_list VALUES (0.999) produces an error.

C.

INSERT INTO rate_list VALUES (-10) produces an error.

D.

INSERT INTO rate_list VALUES (87654. 556) inserts the value as 87654.6.

E.

INSERT INTO rate_list VALUES (0.551) inserts the value as .55.

F.

INSERT INTO rate_list VALUES (-99.99) inserts the value as 99.99.

Question 61

You execute this command:

TRUNCATE TABLE depts;

Which two are true?

Options:

A.

It retains the indexes defined on the table.

B.

It drops any triggers defined on the table.

C.

A Flashback TABLE statement can be used to retrieve the deleted data.

D.

It retains the integrity constraints defined on the table.

E.

A ROLLBACK statement can be used to retrieve the deleted data.

F.

It always retains the space used by the removed rows

Question 62

Which two will execute successfully?

Options:

A.

SELECT COALESCR('DATE', SYSDATE) FROM DUAL;

B.

SELECT NVL('DATE',SYSDATE) FROM DUAL;

C.

SELECT COALESCE(O,SYSDATE) TRCH DUAL;

D.

SELECT NVL('DATE',200) FROM (SELECT NULL AS “DATE” FROM DUAL);

E.

SELECT COALESCE('DATE',SYSDATE) FROM (SELECT NULL AS “DATE” FROM DUAL) ;

Question 63

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 want to display the CUST_NAME values where the last name starts with Mc or MC.

Which two WHERE clauses give the required result?

Options:

A.

WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name,’ ’) + 1)) LIKE UPPER('MC%')

B.

WHERE SUBSTR(cust_name, INSTR(cust_name,’ ’) + 1) LIKE 'Mc%’ OR 'MC%’

C.

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ’) + 1)) IN (‘MC%’,’Mc%’)

D.

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ') + 1)) LIKE ‘Mc%’

E.

WHERE SUBSTR(cust_name, INSTR(cust_name,’ ‘) + 1) LIKE ‘Mc%’

Question 64

Examine the data in the ENPLOYEES table:

as

Which statement will compute the total annual compensation tor each employee？

Options:

A.

SECECT last_namo, (menthy_salary + monthly_commission_pct) * 12 AS annual_comp

FROM employees;

B.

SELCECT last_namo, (monthly_salary * 12) + (monthly_commission_pct * 12) AS annual_comp

FROM employees

C.

SELCECT last_namo, (monthly_salary * 12) + (menthy_salary * 12 * NVL

(monthly_commission_pct, 0)) AS annual_comp FROM employees

D.

SELCECT last_namo, (monthly_salary * 12) + (menthy_salary * 12 * monthly_commission_pct)

AS annual_comp FROM employees

Question 65

Examine the description of the PRODUCTS table:

as

Which two statements execute without errors?

Options:

A.

MERGE INTO new_prices n

USING (SELECT * FROM products) p

WHEN MATCHED THEN

UPDATE SET n.price= p.cost* 01

WHEN NOT MATCHED THEN

INSERT(n.prod_id, n.price) VALUES(p.prod_id, cost*.01)

WHERE(p.cost<200);

B.

MERGE INTO new_prices n

USING (SELECT * FROM products WHERE cost>150) p

ON (n.prod_id= p.prod_id)

WHEN MATCHED THEN

UPDATE SET n.price= p.cost*.01

DELETE WHERE (p.cost<200);

C.

MERGE INTO new_prices n

USING products p

ON (p.prod_id =n.prod_id)

WHEN NOT MATCHED THEN

INSERT (n.prod _id, n.price) VALUES (p.prod_id, cost*.01)

WHERE (p.cost<200);

D.

MERGE INTO new_prices n

USING (SELECT * FROM products WHERE cost>150) p

ON (n.prod_id= p.prod_id)

WHEN MATCHED THEN

DELETE WHERE (p.cost<200)

Question 66

Which three are key components of an Entity Relationship Model?

Options:

A.

a table

B.

an attribute

C.

a unique identifier

D.

an activity

E.

a relationship

F.

an entity

Question 67

Which two are true about constraints?

Options:

A.

Constraints are enforced only during INSERT operations.

B.

A column with a foreign key constraint can never contain a null value.

C.

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

D.

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

E.

A column with a UNIQUE constraint can contain a NULL value.

Question 68

Which two are true about multiple table INSERT statements?

Options:

A.

They always use subqueries.

B.

They can transform a row from a source table into multiple rows in a target table.

C.

The conditional INSERT FIRST statement always inserts a row into a single table.

D.

The conditional INSERT ALL statement inserts rows into a single table by aggregating source rows.

E.

The unconditional INSERT ALL statement must have the same number of columns in both the source and target tables.

Question 69

Which three privileges can be restricted to a subset of columns in a table?

Options:

A.

ALTER

B.

REFERENCES

C.

UPDATE

D.

SELECT

E.

INDEX

F.

INSERT

G.

DELETE

Question 70

Which two are true about granting object privileges on tables, views, and sequences?

Options:

A.

DELETE can be granted on tables, views, and sequences.

B.

REFERENCES can be grantrd only on tables.

C.

INSERT can be granted only on tables and sequences.

D.

SELECT can be granted on tables, views, and sequences.

E.

ALTER can be granted only on tables and sequences.

Question 71

Examine the description of the PROMOTIONS TABLE:

You want to display the unique is promotion costs in each promotion category.

Which two queries can be used?

Options:

A.

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

B.

SELECT promo_cost, promo_category FROM promotions ORDER BY 1

C.

SELECT promo_category, DISTINCT promo_cost FROM promotiong ORDER BY 2:

D.

select DISTINCT promo_categoryIl ‘has’||promol_cost as COSTS FROM promotions ORDER BY 1:

E.

SELECT DISTINCT promo_cost ||’in’IIDISTINCT promo_category promotions ORDER BY1:

Question 72

Which three are true about granting object privileges on tables, views, and sequences?

Options:

A.

UPDATE can be granted only on tables and views.

B.

DELETE can be granted on tables, views, and sequences.

C.

REFERENCES can be granted only on tables and views.

D.

INSERT can be granted on tables, views, and sequences.

E.

SELECT can be granted only on tables and views.

F.

ALTER can be granted only on tables and sequences.

Question 73

Which two statements about INVISIBLE indexes are true?

Options:

A.

an INVISIBLE Index consumes no storage

B.

You can only create one INVISIBLE index on the same column list

C.

The query optimizer never considers INVISIBLE Indexes when determining execution plans

D.

You use AlTER INDEX to make an INVISIBLE Index VISIBLE

E.

All INSERT, UPDATE, and DELETE statements maintain entries in the index

Question 74

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 want to display the CUST_NAME values where the last name starts with Mc or MC. Which two WHERE clauses give the required result?

Options:

A.

WHERE INITCAP (SUBSTR(cust_name, INSTR(cust_name,'') +1)) IN ('MC%','Mc%)

B.

WHERE UPPER (SUBSTR(cust_name, INSTR(cust_name, '') +1)) LIKE UPPER('MC%')

C.

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,'') +1)) LIKE'Mc%'

D.

WHERE SUBSTR(cust_name,INSTR(cust_name,'') +1) LIKE'Mc%' OR'MC%'

E.

WHERE SUBSTR(cust_name, INSTR(cust_name,'') +1) LIKE'Mc%'

Question 75

Examine these statements executed in a single Oracle session:

CREATE TABLE product (pcode NUMBER(2),pname VARCHAR2(20));

INSERT INTO product VALUES(1,'pen');

INSERT INTO product VALUES (2,'pencil');

INSERT INTO product VALUES(3,'fountain pen');

SAVEPOINT a;

UPDATE product SET pcode=10 WHERE pcode =1;

COMMIT;

DELETE FROM product WHERE pcode =2;

SAVEPOINT b;

UPDATE product SET pcode=30 WHERE pcode =3;

SAVEPOINT c;

DELETE FROM product WHERE pcode =10;

ROLLBACK TO SAVEPOINT b;

COMMIT;

Which three statements are true？

Options:

A.

The code for pen is 10.

B.

There is no row containing fountain pen.

C.

There is no row containing pencil.

D.

The code for pen is 1.

E.

The code for fountain pen is 3

F.

There is no row containing pen

Question 76

Which two statements are true about conditional INSERT ALL?

Options:

A.

Each row returned by the subquery can be inserted into only a single target table.

B.

It cannot have an ELSE clause.

C.

The total number of rows inserted is always equal to the number of rows returned by the subquery

D.

A single WHEN condition can be used for multiple INTO clauses.

E.

Each WHEN condition is tested for each row returned by the subquery.

Question 77

The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOICE_DATE column of data type DATE NLS_DATE_FORMAT is set to DD-MON-RR.

Which two are true about data type conversions involving these columns in query expressions?

Options:

A.

invoice_date> '01-02-2019': uses implicit conversion

B.

qty_sold ='05549821 ': requires explicit conversion

C.

CONCAT(qty_sold, invoice_date): requires explicit conversion

D.

qty_sold BETWEEN '101' AND '110': uses implicit conversion

E.

invoice_date = '15-march-2019': uses implicit conversion

Question 78

Examine the description of the PRODCTS table which contains data:

as

Which two are true?

Options:

A.

The PROD ID column can be renamed.

B.

The PROD_ ID column data type can be changed to VARCHAR2 (2).

C.

The EXPIRY DATE column data type can be changed to TIME STAMP.

D.

The EXPIRY DATE column cannot be dropped.

E.

The PROD NAME column cannot have a DEFAULT clause added to it.

Question 79

You execute this command:

TRUNCATE TABIE depts;

Which two are true?

Options:

A.

A ROLLBACK statement can be used to retrieve the deleted data.

B.

It drops any triggers defined on the table.

C.

It retains the indexes defined on the table.

D.

It retains the integrity constraints defined on the table,

E.

It always retains the space used by the removed rows.

F.

A FLASHBACK TABLE statement can be used to retrieve the deleted data.

Question 80

which three statements are true about indexes and their administration in an Oracle database?

Options:

A.

The same table column can be part of a unique and non-unique index

B.

A DESCENDING INDEX IS A type of function-based index

C.

A DROP INDEX statement always prevents updates to the table during the drop operation

D.

AN INVISIBLE INDEX is not maintained when DML is performed on its underlying table.

E.

AN INDEX CAN BE CREATED AS part of a CREATE TABLE statement

F.

IF a query filters on an indexed column then it will always be used during execution of query

Question 81

Which three statements are true regarding single row subqueries?

Options:

A.

They must be placed on the left side of the comparison operator or condition.

B.

They must return a row to prevent errors in the SQL statement.

C.

A SQL statement may have multiple single row subquery blocks.

D.

They can be used in the HAVING clause.

E.

They must be placed on the right side of the comparison operator or condition.

F.

They can be used in the clause.

Question 82

The ORDERS table has a column ORDER_DATE of date type DATE The default display format for a date is DD-MON-RR

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

Options:

A.

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

B.

WHERE TO_CHAR(order_date,'MON DD YYYY') ='JAN 20 2019';

C.

WHERE order_date> TO_DATE('JUL 10 2018','MON DD YYYY');

D.

WHERE order_date IN (TO_DATE ('Oct 21 2018','MON DD YYYY'), TO_CHAR('Nov 21 2018','MON DD YYYY'));

E.

WHERE order_date> TO_DATE(ADD_MONTHS(SYSDATE,6),'MON DD YYYY');

Question 83

Which two are true about using constraints?

Options:

A.

A FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the parenttable must have the same names.

B.

A table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints.

C.

A table can have only one PRIMARY KEY and one FOREIGN KEY constraint.

D.

PRIMARY KEY and FOREIGNY constraints can be specified at the column and at the table level

E.

A table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints.

F.

NOT NULL can be specified at the column and at the table level.

Question 84

Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and MINUS)?

Options:

A.

There must be an equal number of columns in each SELECT list.

B.

The name of each column in the first SELECT list must match the name of the corresponding column in each subsequent SELECT list.

C.

Each SELECT statement in the query can have an ORDER BY clause.

D.

None of the set operators can be used when selecting CLOB columns.

E.

The FOR UPDATE clause cannot be specified.

Question 85

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

Options:

A.

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

B.

An inner join relates rows within the same table.

C.

Rows from unrelated tables cannot be joined.

D.

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

E.

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

Question 86

You execute this command:

TRUNCATE TABLE dept;

Which two are true?

Options:

A.

It drops any triggers defined on the table.

B.

It retains the indexes defined on the table.

C.

It retains the integrity constraints defined on the table.

D.

A ROLLBACK statement can be used to retrieve the deleted data.

E.

It always retains the space used by the removed rows.

F.

A FLASHBACK TABLE statement can be used to retrieve the deleted data.

Question 87

Which three statements are true about views in an Oracle database?

Options:

A.

A SELECT statement cannot contain a where clause when querying a view containing a WHERE clause in its defining query

B.

Rows inserted into a table using a view are retained in the table if the view is dropped

C.

Views can join tables only if they belong to the same schema.

D.

Views have no segment.

E.

Views have no object number.

F.

A view can be created that refers to a non-existent table in its defining query.

Question 88

Which three statements are true about performing DML operations on a view with no INSTEAD OF triggers defined?

Options:

A.

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

B.

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

C.

Delete statements can always be done on a table through a view.

D.

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.

E.

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

F.

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

Question 89

Which three are true?

Options:

A.

LAST_DAY returns the date of the last day of the current ,month onlyu.

B.

CEIL requires an argument which is a numeric data type.

C.

ADD_MONTHS adds a number of calendar months to a date.

D.

ADD_MONTHS works with a character string that can be implicitlyt converted to a DATE data type.

E.

LAST_DAY return the date of the last day the previous month only.

F.

CEIL returns the largest integer less than or equal to a specified number.

G.

LAST_DAY returns the date of the last day of the month for the date argument passed to the function.

Question 90

Examine the description of the CUSTOMERS table:

as

Which three statements will do an implicit conversion?

Options:

A.

SELECT * FROM customers WHERE TO_ CHAR (customer_ id) = '0001';

B.

SELECT * FROM customers WHERE customer id = '0001';

C.

SELECT * FROM customers WHERE customer_ id = 0001;

D.

SELECT FROM customers WHERE insert date = '01-JAN-19';

E.

SELECT. FROM customers WHERE insert_ date = DATE *2019-01-01';

F.

SELECT. FRON customers WE TO DATE (Insert _ date) = DATE ‘2019-01-01’;

Question 91

Which four statements are true about constraints on Oracle tables?

Options:

A.

A Column can have only one CHECK Constraint.

B.

A NOT NULL Constraint can be defined at the table level.

C.

A UNIQUE constraint permits NULLS.

D.

A PRIMARY KEY Constraint can be added after a table has been created and populated.

E.

A CHECK Constraint can refer to values in other rows.

F.

A UNIQUE Constraint can use a pre-existing index on the constrained column or columns.

G.

A FOREIGN KEY Column can contain NULLS.

Question 92

Which three are true about subqueries?

Options:

A.

A subquery can be used in a WHERE clause.

B.

A subquery can be used in a HAVING clause.

C.

=ANY can only evaluate the argument against a subcjuery if it returns two or more values.

D.

E.

A subquery cannot be used in a FROM clause.

F.

< any returns true if the argument is less than the lowest value returned by the subquery.

G.

A subquery cannot be used in the select list.

Question 93

Which statement is true regarding the SESSION_PRIVS dictionary view?

Options:

A.

It contains the object privileges granted to other users by the current user session.

B.

It contains the system privileges granted to other users by the current User session.

C.

It contains the current system privileges available in the user session.

D.

It contains the current object privileges available in the user session.

Question 94

Which two statements are true about outer Joins?

Options:

A.

The outer join operator (+) can be used on both sides of the join condition in an outer join.

B.

An outer join is used to retrieve only the rows that do not meet the join condition.

C.

The IN operator cannot be used in a condition that Involves an outer join.

D.

A condition representing an outer join cannot be linked to another condition using the or logical operator.

E.

The outer join operator (+) is used next to the column of the table without the matching rows.

Question 95

Which two statements are true about CURRENT_TIMEITAMP?

Options:

A.

The date is in the time zone of DBTIMEZONE.

B.

The value varies depending on the setting of SESSIONTIMEZONE.

C.

It returns the same date as CURRENT_DATE.

D.

The time is in the time zone of DBTIMEZONE.

E.

It returns a value of data type TIMESTAMP

F.

It always returns the same value as SYSTIMESTAMP

Question 96

Examine this query which executes successfully:

SELECT job, deptno FROM emp

UNION ALL

SELECT job, deptno FROM jobs_ history;

What will be the result?

Options:

A.

It will return rows common to both SELECT statements.

B.

It will return rows from both SELECT statements after eliminating duplicate rows.

C.

It will return rows that are not common to both SELECT statements.

D.

It will return rows from both SELECT statements including duplicate rows.

Question 97

Which two are true about the NVL, NVL2, and COALESCE functions?

Options:

A.

The first expression in NVL2 is never returned.

B.

NVL2 can have any number of expressions in the list.

C.

COALESCE stops evaluating the list of expressions when it finds the first null value.

D.

COALESCE stops evaluating the list of expressions when it finds the first non-null value.

E.

NVL must have expressions of the same data type.

F.

NVL can have any number of expressions in the list.

Load More 1z0-071 Questions
Page: 1 / 32
Total 324 questions
Get 1z0-071 Full Access Download 1z0-071 PDF