Free Oracle 1Z0-071 Exam Dumps and 1Z0-071 Exam Questions

Question No: 11

MultipleChoice

Evaluate the following SQL statement:

SQL> select cust_id, cust_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 either one of which can complete the query. (Choose three.)

Options

AORDER BY 'Last name'

BORDER BY 2, cust_id

CORDER BY CUST_NO

DORDER BY 2,1

EORDER BY 'CUST_NO'

Question No: 12

MultipleChoice

Which two statements are true about the WHERE and HAVING clauses in a SELECT statement?

Options

AThe WHERE clause can be used to exclude rows after dividing them into groups

BWHERE and HAVING clauses can be used in the same statement only if applied to different table columns.

CThe HAVING clause can be used with aggregating functions in subqueries.

DAggregating functions and columns used in HAVING clauses must be specified in these SELECT list of a query.

EThe WHERE clause can be used to exclude rows before dividing them into groups.

Question No: 13

MultipleChoice

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

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

Bqty_sold ='05549821 ': requires explicit conversion

CCONCAT(qty_sold, invoice_date): requires explicit conversion

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

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

Question No: 14

MultipleChoice

Examine the description of the PRODUCTS table:

Which three queries use valid expressions?

Options

ASELECT produet_id, unit_pricer, 5 'Discount',unit_price+surcharge-discount FROM products;

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

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

DSPLECT product_id, expiry_date * 2 FROM products;

ESELEGT product_id, unit_price, unit_price + surcharge FROM products;

FSELECT product_id,unit_price || 'Discount', unit_price + surcharge-discount FROM products;

Question No: 15

MultipleChoice

Examine the structure of the two tables.

Which two queries execute successfully? (Choose two.)

Options

AOption A

BOption B

COption C

DOption D

EOption E

Question No: 16

MultipleChoice

Which three actions can you perform on an existing table containing data? (Choose three.)

Options

AAdd a new NOT NULL column with a DEFAULT value

BChange the default value of a column

CChange a DATE column containing data to a NUMBER data type

DAdd a new column as the table's first column

EDefine a default value that is automatically inserted into a column containing nulls

FIncrease the width of a numeric column

Question No: 17

MultipleChoice

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

Options

AUPDATE can be granted only on tables and views.

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

CREFERENCES can be granted only on tables and views.

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

ESELECT can be granted only on tables and views.

FALTER can be granted only on tables and sequences.

Question No: 18

MultipleChoice

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

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

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

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

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

Question No: 19

MultipleChoice

Choose the best answer.

Examine the description of the EMPLOYEES table:

Which query is valid?

Options

ASELECT dept_id, join_date,SUM(salary) FROM employees GROUP BY dept_id, join_date;

BSELECT depe_id,join_date,SUM(salary) FROM employees GROUP BY dept_id:

CSELECT dept_id,MAX(AVG(salary)) FROM employees GROUP BY dept_id;

DSELECT dept_id,AVG(MAX(salary)) FROM employees GROUP BY dapt_id;

Question No: 20

MultipleChoice

Which three are true about the CREATE TABLE command?

Options

AIt can include the CREATE...INDEX statement for creating an index to enforce the primary key constraint.

BThe owner of the table should have space quota available on the tablespace where the table is defined.

CIt implicitly executes a commit.

DIt implicitly rolls back any pending transactions.

EA user must have the CREATE ANY TABLE privilege to create tables.

FThe owner of the table must have the UNLIMITED TABLESPACE system privilege.