New Year Special Limited Time 70% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: 70special

Examstrust Logo
examstrust mcafee
  1. Home
  2. Oracle
  3. Oracle PL
  4. 1z0-071 - Oracle Database 12c SQL

Oracle 1z0-071 Oracle Database 12c SQL Exam Practice Test

Page: 1 / 33
Total 326 questions
Get 1z0-071 Full Access Download 1z0-071 PDF

Oracle Database 12c SQL Questions and Answers

Testing Engine

  • Product Type: Testing Engine
$37.5  $124.99
Add to Cart

PDF + Testing Engine

  • Product Type: PDF + Testing Engine
$52.5  $174.99
Add to Cart

PDF Study Guide

  • Product Type: PDF Study Guide
$33  $109.99
Add to Cart
Question 1

Examine this data in the EMPLOYERS table：

Which statement will execute successfully?

Options:

A.

SELECT dept_id, MAX (Last_name), SUM (salary) FROM employees GROUP BY dept_id

B.

SELECT dept_id, LENGTH (last_name), SUM (salary) FROM employees GROUP BY dept_id

C.

SELECT dept_id, STDDEV (last_name), SUM (salary) FROM employees GROUP BY dept_id

D.

SELECT dept_id, INSTR (last_name,'A'), SUM (salary) FROM employees GROUP BY dept_id

Question 2

The SYSDATE function displays the current Oracle Server date as:

21 -MAY-19

You wish to display the date as:

MONDAY, 21 MAY, 201 9

Which statement will do this?

Options:

A.

SELECT TO _ CHAR (SYSDATE, ' FMDAY, DD MONTH, YYYY') FROM DUAL;

B.

SELECT TO _ DATE (SYSDATE, ' FMDAY, DD MONTH, YYYY') FROM DUAL;

C.

SELECT TO_ CHAR (SYSDATE, ' FMDD, DAY MONTH, YYYY') FROM DUAL;

D.

SELECT TO_ CHAR (SYSDATE, ' FMDAY, DDTH MONTH, YYYY') FROM DUAL;

Question 3

In the PROMOTIONS table, the PROMO_BEGTN_DATE column is of data type DATE and the default date format is DD-MON-RR.

Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a query?

Options:

A.

TO_NUMBER(PROMO_BEGIN_DATE)-5 will return number

B.

TO_DATE(PROMO_BEGIN_DATE * 5) will return a date

C.

PROMO_BEGIN_DATE-SYSDATE will return a number.

D.

PROMO_BEGIN_DATE-5 will return a date.

E.

PROMO_BEGIN_DATE-SYSDATE will return an error.

Question 4

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 5

Which three statements are true about a self join?

Options:

A.

It must be an inner join.

B.

It can be an outer join.

C.

The ON clause must be used.

D.

It must be an equijoin.

E.

The query must use two different aliases for the table.

F.

The ON clause can be used.

Question 6

Which three actions can you perform by using the ORACLE DATAPUMP access driver?

Options:

A.

Create a directory object for an external table.

B.

Read data from an external table and load it into a table in the database.

C.

Query data from an external table.

D.

Create a directory object for a flat file.

E.

Execute DML statements on an external table.

F.

Read data from a table in the database and insert it into an external table.

Question 7

View the Exhibit and examine the structure of the ORDERS table.

The columns ORDER_MODE and ORDER TOTAL have the default values'direct “and respectively.

Which two INSERT statements are valid? (Choose two.)

Options:

A.

INSERT INTO (SELECT order_id, order date, customer_id FROM orders) VALUES (1, ‘09-mar-2007“，101);

B.

INSERT INTO orders (order_id, order_date, order mode,customer_id, order_total) VALUES (1, TO_DATE (NULL),‘online‘,101, NULL) ;

C.

INSERT INTO orders VALUES (1, ‘09-mar-2007’, ‘online’,’ ’,1000);

D.

INSERT INTO orders (order id, order_date, order mode, order_total)VALUES (1，‘10-mar-2007’,’online’, 1000)

E.

INSERT INTO orders VALUES(‘09-mar-2007’，DEFAULT,101, DEFALLT);

Question 8

You have been asked to create a table for a banking application.

One of the columns must meet three requirements:

1: Be stored in a format supporting date arithmetic without using conversion functions

2: Store a loan period of up to 10 years

3: Be used for calculating interest for the number of days the loan remains unpaid Which data type should you use?

Options:

A.

TIMESTAMP WITH TIMEZONE

B.

TIMESTAMP

C.

TIMESTAMP WITH LOCAL TIMEZONE

D.

INTERVAL YEAR TO MONTH

E.

INTERVAL DAY TO SECOND

Question 9

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 10

Which two statements are true about * _TABLES views?

Options:

A.

You must have ANY TABLE system privileges, or be granted object privilges on the table, to viewa tabl e in DBA TABLES.

B.

USER TABLES displays all tables owned by the current user.

C.

You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in USER_TABLES.

D.

ALL TABLES displays all tables owned by the current user.

E.

You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in ALL_TABLES.

F.

All users can query DBA_TABLES successfully.

Question 11

Examine the description of the EMPLOYEES table:

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 12

Examine the description of the PRODUCTS table:

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 13

Examine the ORDER _ITEms table:

Which two queries return rows where QUANTITY is a multiple of ten?

Options:

A.

SELECT * FROM order_ items WHERE quantity = TRUNC (quantity, -1);

B.

SELECT * FROM order_ items WHERE MOD (quantity, 10) = 0;

C.

SELECT” FROM order_ items WHERE FLOOR (quantity / 10) = TRUNC (quantity / 10);

D.

SELECT FROM order_ items WHERE quantity / 10 = TRUNC (quantity);

E.

SELECT” FROM order_ _items WHERE quantity = ROUND (quantity, 1);

Question 14

You and your colleague Andrew have these privileges on the EMPLOYEE_RECORDS table:

1. SELECT

2. INSERT

3. UPDATE

4. DELETE

You connect to the database instance an perform an update to some of the rows in

EMPLOYEE_RECORDS, but don’t commit yet.

Andrew connects to the database instance and queries the table

No othet user are accessing the table

Which two statements ate true at this point?

Options:

A.

Andrew will be able to modify any rows in the table that have not been modified by your transaction

B.

Andrew will be unable to see the changes you have made

C.

Andrew will be able to see the changes you habe made

D.

Andrew will be unable to perform any INSERT, UPDATE of DELETE on the teble

E.

Andrew will be able to SELECT from the table, but be unable to modify any existing rows.

Question 15

Examine this SQL statement:

Which two are true?

Options:

A.

The subquery is executed before the UPDATE statement is executed.

B.

All existing rows in the ORDERS table are updated.

C.

The subquery is executed for every updated row in the ORDERS table.

D.

The UPDATE statement executes successfully even if the subquery selects multiple rows.

E.

The subquery is not a correlated subquery.

Question 16

Which two statements cause changes to the data dictionary?

Options:

A.

DELETE FROM scott. emp;

B.

GRANT UPDATE ON scott. emp TO fin manager;

C.

AITER SESSION set NLs. _DATE FORMAT = 'DD/MM/YYYY';

D.

TRUNCATE TABLE emp:

E.

SELECT * FROM user_ tab._ privs;

Question 17

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 18

Examine this list of requirements for a sequence:

1. Name:EMP_SEQ

2. First value returned:1

3. Duplicates are never permitted.

4. Provide values to be inserted into the EMPLOYEES.EMPLOYEE_ID COLUMN.

5. Reduce the chances of gaps in the values.

Which two statements will satisfy these requirements?

Options:

A.

CREATE SEQUENCE emp_seq START WITH 1 INCRENENT BY 1 NOCACHE;

B.

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CYCLE;

C.

CREATE SEQUENCE emp_seq NOCACHE;

D.

CREATE SEQUENCE emp_seq START WITH 1 CACHE;

E.

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CACHE;

F.

CREATE SEQUENCE emp_seq;

Question 19

Examine this SQL statement:

DELETE FROM employees e

WHERE EXISTS

(SELECT'dummy'

FROM emp_history

WHERE employee_id = e.employee_id)

Which two are true?

Options:

A.

The subquery is executed for every row in the EMPLOYEES table.

B.

The subquery is not a correlated subquery.

C.

The subquery is executed before the DELETE statement is executed.

D.

All existing rows in the EMPLOYEE table are deleted.

E.

The DELETE statement executes successfully even if the subquery selects multiple rows.

Question 20

Which two statements will do an implicit conversion?

Options:

A.

SELECT * FROM customers WHERE customer_ id = 0001 ;

B.

SELECT * FROM customers WHERE customer id = ‘0001’;

C.

SELECT * FROM customers WHERE insert_ date = DATE ‘2019-01-01’;

D.

SELECT * FROM customers WHERE insert date =’01-JAN-19’

E.

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

Question 21

You create a table named 123.

Which statement runs successfully?

Options:

A.

SELECT * FROM TABLE (123) ;

B.

SELECT * FROM '123';

C.

SELECT * FROM "123";

D.

SELECT * FROM V'123V';

Question 22

Which two tasks require subqueries?

Options:

A.

Display the total number of products supplied by supplier 102 which have a product status of obsolete.

B.

Display suppliers whose PROD_LIST_PRICE is less than 1000.

C.

Display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE.

D.

Display the minimum PROD_LIST_PRICE for each product status.

E.

Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable.

Question 23

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

Options:

A.

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

B.

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

C.

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.

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.

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

F.

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.

Question 24

Which two statements are true about truncate and delete?

Options:

A.

the result of a delete can be undone by issuing a rollback

B.

delete can use a where clause to determine which row(s) should be removed.

C.

TRUNCATE can use a where clause to determine which row(s) should be removed.

D.

truncate leavers any indexes on the table in an UNUSABLE STATE.

E.

the result of a truncate can be undone by issuing a ROLLBACK.

Question 25

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 26

Examine the data in the INVOICES table:

Examine the data in the CURRENCIES table:

CURRENCY_CODE

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

JPY

GPB

CAD

EUR

USD

Which query returns the currencies in CURRENCIES that are not present in INVOICES?

Options:

A.

SELECT currency_ code FROM currencies

MINUS

SELECT currency_ code FROM invoices;

B.

SELECT * FROM currencies

WHERE NOT EXISTS (

SELECT NULL FROM invoices WHERE currency_ code = currency_ code);

C.

SELECT currency_ code FROM currencies

INTERSECT

SELECT currency_ code FROM invoices;

D.

SELECT * FROM currencies

MINUS

SELECT * FROM invoices;

Question 27

Examine this statement which executes successfully:

Which statement will violate the CHECK constraint?

Options:

A.

UPDATE emp80

SET department_id=90

WHERE department_id=80;

B.

DELETE FROM emp80

WHERE department_id=90;

C.

SELECT *

FROM emp80

WHERE department_id=80；

D.

SELECT *

FROM emp80

WHERE department_id=90;

Question 28

Examine the description of the CUSTOMERS table:

Which three statements will do an implicit conversion?

Options:

A.

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

B.

SELECT * FROM customers WHERE customer_id=’0001’;

C.

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

D.

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

E.

SELECT * FROM customers WHERE customer_id=0001;

F.

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

Question 29

Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data?

Options:

A.

Only the primary key can be defined at the column and table level.

B.

The foreign key columns and parent table primary key columns must have the same names.

C.

It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted.

D.

A table can have only one primary key but multiple foreign keys.

E.

Primary key and foreign key constraints can be defined at both the column and table level.

F.

A table can have only one primary key and one foreign key.

G.

It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted

Question 30

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 31

Examine the description of the transactions table:

Which two SQL statements execute successfully?

Options:

A.

SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount+100 "DUES" from transactions;

B.

SELECT customer_id AS 'CUSTOMER-ID',transaction_date AS DATE, amount+100 'DUES' from transactions;

C.

SELECT customer_id CUSTID, transaction_date TRANS_DATE，amount+100 DUES FROM transactions;

D.

SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount+100 DUES FROM transactions;

E.

SELECT customer id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount+100 "DUES AMOUNT" FROM transactions;

Question 32

View the Exhibits and examine the structure of the COSTS and PROMOTIONS tables.

You want to display PROD IDS whose promotion cost is less than the highest cost PROD ID in a pro

motion time interval.

Examine this SQL statement:

SELECT prod id

FROM costs

WHERE promo id IN

(SELECT promo id

FROM promotions

WHERE promo_cost < ALL

(SELECT MAX (promo cost)

FROM promotions

GROUP BY (promo_end date-promo_begin_date)) );

What will be the result?

Options:

A.

It executes successfully but does not give the required result.

B.

It gives an error because the ALL keyword is not valid.

C.

It gives an error because the GROUP BY clause is not valid

D.

It executes successfully and gives the required result.

Question 33

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 34

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 35

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 36

Examine the description of the transactions table:

Which two SQL statements execute successfully?

Options:

A.

SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount+100 "DUES" from transactions;

B.

SELECT customer_id AS 'CUSTOMER-ID',transaction_date AS DATE, amount+100 'DUES' from transactions;

C.

SELECT customer_id CUSTID, transaction_date TRANS_DATE，amount+100 DUES FROM transactions;

D.

SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount+100 DUES FROM transactions;

E.

SELECT customer id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount+100 "DUES AMOUNT" FROM transactions;

Question 37

Which three are true about scalar subquery expressions?

Options:

A.

A scalar subquery expression that returns zero rows evaluates to zoro

B.

They cannot be used in the values clause of an insert statement*

C.

They can be nested.

D.

A scalar subquery expression that returns zero rows evaluates to null.

E.

They cannot be used in group by clauses.

F.

They can be used as default values for columns in a create table statement.

Question 38

Examine this partial command:

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 39

Which two are true about creating tables in an Oracle database?

Options:

A.

A create table statement can specify the maximum number of rows the table will contain.

B.

The same table name can be used for tables in different schemas.

C.

A system privilege is required.

D.

Creating an external table will automatically create a file using the specified directory and file name.

E.

A primary key constraint is manadatory.

Question 40

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 41

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 42

Examine the description of the ORDERS table:

Which three statements execute successfully?

Options:

A.

(SELECT * FROM orders

UNION ALL

SELECT* FROM invoices) ORDER BY order _id;

B.

SELECE order _id, order _ date FRON orders

LNTERSECT

SELECT invoice_ id, invoice_ id, order_ date FROM orders

C.

SELECT order_ id, invoice_ data order_ date FROM orders

MINUS

SELECT invoice_ id, invoice_ data FROM invoices ORDER BY invoice_ id;

D.

SELECT * FROM orders ORDER BY order_ id

INTERSEOT

SELECT * FROM invoices ORDER BY invoice_ id;

E.

SELECT order_ id, order_ data FROM orders

UNION ALL

SELECT invoice_ id, invoice_ data FROM invoices ORDER BY order_ id;

F.

SELECT * FROM orders

MINUS

SELECT * FROM INVOICES ORDER BY 1

G.

SELECT * FROM orders ORDER BY order_ id

UNION

SELECT * FROM invoices;

Question 43

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 44

Which two are true about external tables that use the ORACLE _DATAPUMP access driver?

Options:

A.

Creating an external table creates a directory object.

B.

When creating an external table, data can be selected only from a table whose rows are stored in database blocks.

C.

When creating an external table, data can be selected from another external table or from a table whose rows are stored in database blocks.

D.

Creating an external table creates a dump file that can be used by an external table in the same or a different database.

E.

Creating an external table creates a dump file that can be used only by an external table in the same database.

Question 45

Examine the description of the CUSTOMERS table:

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 46

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 47

Examine thee statements which execute successfully:

CREATE USER finance IDENTIFIED BY pwfin;

CREATE USER fin manager IDENTIETED BY pwmgr;

CREATE USER fin. Clerk IDENTIFIED BY pwclerk;

GRANT CREATE SESSON 20 finance, fin clerk;

GRANT SELECT ON scott. Emp To finance WITH GRANT OPTION;

CONNECT finance/pwfin

GRANT SELECT ON scott. emp To fin_ _clerk;

Which two are true?

Options:

A.

Dropping user FINANCE will automatically revoke SELECT on SCOTT. EMP from user FIN _ CLERK

B.

Revoking SELECT on SCOTT. EMP from user FINANCE will also revoke the privilege from user FIN_ CLERK.

C.

User FINANCE can grant CREATE SESSION to user FIN MANAGER.

D.

User FIN CLERK can grant SELECT on SCORT, ENP to user FIN MANAGER.

E.

User FINANCE is unable to grant ALL on SCOTT.ENP to FIN MANAGER.

Question 48

Which two statements are true about Oracle databases and SQL?

Options:

A.

Updates performed by a database user can be rolled back by another user by using the ROLLBACK command.

B.

The database guarantees read consistency at select level on user-created tablers.

C.

When you execute an UPDATE statement, the database instance locks each updated row.

D.

A query can access only tables within the same schema.

E.

A user can be the owner of multiple schemas In the same database.

Question 49

Which two statements are true about views?

Options:

A.

Views can be indexed.

B.

Theethi CHEcK clause prevents certalin rows from being updated or inserted in the underying table through the view.

C.

Tables in the defining query of a view must always exist in order to create the view.

D.

Views can be updated without the need to re-grant privileges on the view.

E.

The wITH CHECK clause prevents certain rows from being displayed when querying the view.

Question 50

Which two statements are true about the results of using the intersect operator in compound queries?

Options:

A.

intersect ignores nulls.

B.

Reversing the order of the intersected tables can sometimes affect the output.

C.

Column names in each select in the compound query can be different.

D.

intersect returns rows common to both sides of the compound query.

E.

The number of columns in each select in the compound query can be different.

Question 51

Examine the description or the BOOKS_TRANSACTIONS table:

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 52

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 53

You execute this command:

ALTER TABLE employees SET UNUSED (department_id);

Which two are true?

Options:

A.

A query can display data from the DEPARTMENT_ID column.

B.

The storage space occupied by the DEPARTMENT_ID column is released only after a COMMIT is issued.

C.

The DEPARTMENT_ID column is set to null for all tows in the table

D.

A new column with the name DEPARTMENT_ID can be added to the EMPLOYEES table.

E.

No updates can be made to the data in the DEPARTMENT_ID column.

F.

The DEPARTMENT_ID column can be recovered from the recycle bin

Question 54

Which two are true about the precedence of opertors and condtions

Options:

A.

+ (addition) has a higher order of precedence than * (mliplpition)

B.

NOT has a higher order of precedence than AND and OR in a condition.

C.

AND and OR have the same order of precedence in a condition

D.

Operators are evaluated before conditions.

E.

|| has a higher order of precedence than +(addition)

Question 55

Examine these requirements:

1. Display book titles for books purchased before January 17, 2007 costing less than 500 or more than 1000.

2. Sort the titles by date of purchase, starting with the most recently purchased book.

Which two queries can be used?

Options:

A.

SELECT book_title FROM books WHERE (price< 500 OR >1000) AND (purchase date< '17-JAN-2007') ORDER BY purchase date DESC;

B.

SELECT book_title FROM books WHERE (price IN (500, 1000)) AND (purchase date < '17-JAN-2007') ORDER BY purchase_date ASC;

C.

SELECT book_title FROM books WHERE (price NOT BETWEEN 500 AND 1000) AND (purchase_date< '17-JAN-2007') ORDER BY purchase_date DESC;

D.

SELECT book_title FROM books WHERE (price BETWEEN 500 AND 1000) AND (purchase_date<'17-JAN-2007') ORDER BY purchase_date;

Question 56

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 57

Examine the description of the ORDER_ITEMS table:

Examine this incomplete query:

SELECT DISTINCT quantity * unit_price total_paid FROM order_items ORDER BY ;

Which two can replace so the query completes successfully?

Options:

A.

quantity

B.

quantity, unit_price

C.

total_paid

D.

product_id

E.

quantity * unit_price

Question 58

Which two statements are true about single row functions?

Options:

A.

CONCAT: can be used to combine any number of values

B.

FLOOR: returns the smallest integer greater than or equal to a specified number

C.

CEIL: can be used for positive and negative numbers

D.

TRUNC: can be used with NUMBER and DATE values

E.

MOD: returns the quotient of a division operation

Question 59

Choose two

Examine the description of the PRODUCT DETALS table:

Options:

A.

PRODUCT_ID can be assigned the PEIMARY KEY constraint.

B.

EXPIRY_DATE cannot be used in arithmetic expressions.

C.

EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it

D.

PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it

E.

PRODUCT_PRICE contains the value zero by default if no value is assigned to it.

F.

PRODUCT_NAME cannot contain duplicate values.

Question 60

Which statements are true regarding primary and foreign key constraints and the effect they can have on table data?

Options:

A.

A table can have only one primary key but multiple foreign keys.

B.

It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted.

C.

Primary key and foreign key constraints can be defined at both the column and table level.

D.

Only the primary key can be defined the column and table level.

E.

It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted.

F.

The foreign key columns and parent table primary key columns must have the same names.

G.

A table can have only one primary key and one foreign key.

Question 61

Evaluate the following SQL statement

SQL>SELECT promo_id, prom _category FROM promotions

WHERE promo_category=’Internet’ ORDER BY promo_id

UNION

SELECT promo_id, promo_category FROM Pomotions

WHERE promo_category = ‘TV’

UNION

SELECT promoid, promocategory FROM promotions WHERE promo category=’Radio’

Which statement is true regarding the outcome of the above query?

Options:

A.

It executes successfully and displays rows in the descend ignore of PROMO CATEGORY.

B.

It produces an error because positional, notation cannot be used in the ORDER BY clause with SBT operators.

C.

It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.

D.

It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement.

Question 62

In your session NLS_ DATE_ FORMAT is set to DD–MON_RR.

Which two queries display the year as four digits?

Options:

A.

SELECT TO_DATE(TO_CHAR(SYSDATE,'MM/DD/YYYY'),'MM/DD/YYYY') FROM DUAL;

B.

SELECT TO_CHAR (ADD_MONTHS (SYSDATE,6)) FROM DUAL;

C.

SELECT TO_DATE (SYSDATE, 'RRRR-MM-DD') FROM DUAL;

D.

SELECT TO_DATE (ADD_MONTHS(SYSDATE,6), 'dd-mon-yyyy') FROM DUAL;

E.

SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY') FROM DUAL;

F.

SELECT TO_CHAR (ADD_MONTHS (SYSDATE, 6), 'dd-mon-yyyy') FROM DUAL;

Question 63

Which two queries will result in an error?

Options:

A.

SELECT FIRST_NAME LAST_NAME FROM EMPLOYEES;

B.

SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES;

C.

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE ANNUAL_SALARY > 100000

ORDER BY 12 * SALARY ;

D.

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE 12 * SALARY > 100000

ORDER BY ANNUAL_SALARY;

E.

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE 12 * SALARY > 100000

ORDER BY 12 * SALARY;

F.

SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE ANNUAL_SALARY > 100000

ORDER BY ANNUAL_SALARY;

Question 64

Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database?

Options:

A.

The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

B.

A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row

C.

A TIMESTAMP data type column contains information about year, month, and day

D.

The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

E.

The CURRENT_TIMESTAMP function returns data without time zone information

Question 65

Choose the best answer.

Examine the description of the EMPLOYEES table:

Which query is valid?

Options:

A.

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

B.

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

C.

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

D.

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

Question 66

The PRODUCT_INFORMATION table has a UNIT_PRICE column of data type NUMBER(8, 2).

Evaluate this SQL statement:

SELECT TO_CHAR(unit_price,'$9,999') FROM PRODUCT_INFORMATION;

Which two statements are true about the output?

Options:

A.

A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,024.

B.

A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,023.

C.

A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,0236.

D.

A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,023.

E.

A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as #####

Question 67

Which two are true about self joins?

Options:

A.

They are always equijoins.

B.

They require the NOT EXISTS operator in the join condition.

C.

They have no join condition.

D.

They can use INNER JOIN and LEFT JOIN.

E.

They require table aliases.

F.

They require the EXISTS opnrator in the join condition.

Question 68

Which three statements about roles are true?

Options:

A.

Roles are assigned to roles using the ALTER ROLE Statement

B.

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

C.

Roles are assigned to users using the ALTER USER statement

D.

A single role can be assigned to multiple users.

E.

A single user can be assigned multiple roles

F.

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

G.

Privileges are assigned to a role using the GRANT statement.

Question 69

The ORDERS table has a primary key constraint on the ORDER_ID column.

The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the primary key of the ORDERS table.

The constraint is defined with on DELETE CASCADE.

There are rows in the ORDERS table with an ORDER_TOTAL less than 1000.

Which three DELETE statements execute successfully?

Options:

A.

DELETE FROM orders WHERE order_total<1000;

B.

DELETE * FROM orders WHERE order_total<1000;

C.

DELETE orders WHERE order_total<1000;

D.

DELETE FROM orders;

E.

DELETE order_id FROM orders WHERE order_total<1000;

Question 70

SELECT *

FROM bricks,colors;

Which two statements are true?

Options:

A.

You can add an ON clause with a join condition.

B.

You can add a WHERE clause with filtering criteria.

C.

It returns the number of rows in BRICKS plus the number of rows in COLORS.

D.

You can add a USING clause with a join condition.

E.

It returnsthe same rows as SELECT * FROM bricks CROSS JOIN colors.

Question 71

You issued this command: DROP TABLE hr. employees;

Which three statements are true?

Options:

A.

ALL constraints defined on HR, EMPLOYEES are dropped.

B.

The HR. EMPLOYEES table may be moved to the recycle bin.

C.

Synonyms for HR EMPLOYEES are dropped.

D.

Sequences used to populate columns in the HR. EMPLOYEES table are dropped.

E.

All indexes defined on HR, EMPLOYEES are dropped.

F.

Views referencing HR, EMPLOYEES are dropped.

Question 72

Which three queries execute successfully?

Options:

A.

SELECT (SYSDATE-DATE '2019-01-01') / 1 FROM DUAL;

B.

SELECT 1 / SYSDATE - DATE '2019-01-01' FROM DUAL;

C.

SELECT SYSDATE / DATE '2019-01-01' - 1 FROM DUAL

D.

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

E.

SELECT 1 – SYSDATE- DATE '2019-01-01' FROM DUAL;

F.

SELECT SYSDATE - 1 - DATE'2019-01-01' EROM DUAL;

Question 73

Which 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 characters.

B.

The default length for a CHAR column is always one character.

C.

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

D.

A BLOB stores unstructured binary data within the database.

E.

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

F.

A BFILE stores unstructured binary data in operating system files.

Question 74

Which two statements are true about a full outer join?

Options:

A.

It includes rows that are returned by an inner join.

B.

The Oracle join operator (+) must be used on both sides of the join condition in the WHERE clause.

C.

It includes rows that are returned by a Cartesian product.

D.

It returns matched and unmatched rows from both tables being joined.

E.

It returns only unmatched rows from both tables being joined.

Question 75

Examine this business rule:

Each student can work on multiple projects and each project can have multiple students.

You must design an Entity Relationship(ER) model for optimal data storage and allow for generating reports in this format:

Which two statements are true?

Options:

A.

An associative table must be created with a composite key of STUDENT_ID and PROJRCT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.

B.

PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.

C.

The ER must have a 1-to-many relationship between the STUDENTS and PROJECTS entities.

D.

The ER must have a many to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships.

E.

STUDENT ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.

Question 76

Examine the description of the EMPLOYEES table:

Which two statements will run successfully?

Options:

A.

SELECT 'The first_name is '' || first_name || '' FROM employees ;

B.

SELECT 'The first_name is '''||first_name ||'''' FROM employees ;

C.

SELECT 'The first_name is ''' ||first_name||''' FROM employees ;

D.

SELECT 'The first_name is '|| first_name|| '' FROM employees;

E.

SELECT 'The first_name is \'' || first_name || '\'' FROM employees;

Question 77

Which statement is true about TRUNCATE and DELETE?

Options:

A.

For large tables TRUNCATE is faster than DELETE.

B.

For tables with multiple indexes and triggers is faster than TRUNCATE.

C.

You can never TRUNCATE a table if foreign key constraints will be violated.

D.

You can never tows from a table if foreign key constraints will be violated.

Question 78

Which statement falls to execute successfully?

Options:

A.

SELECT *

FROM employees e

JOIN department d

WHERE e.department_id=d.department_id

AND d.department_id=90;

B.

SELECT *

FROM employees e

JOIN departments d

ON e.department_id=d.department_id

WHERE d.department_id=90;

C.

SELECT *

FROM employees e

JOIN departments d

ON e.department_id=d.department_id

AND d.department_id=90;

D.

SELECT *

FROM employees e

JOIN departments d

ON d.departments_id=90

WHERE e.department_id=d.department_id;

Question 79

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 80

Which three actions can you perform on an existing table containing date?

Options:

A.

Add a new column as the table's first column.

B.

Define a default value that is automatically inserted into a column containing nulls.

C.

Add a new NOT NULL Column with a DEFAULT value.

D.

Change a DATE Column containing data to a NUMBER data type.

E.

Increase the width of a numeric column.

F.

Change the default value of a column.

Question 81

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 82

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 83

Examine the description of the EMPLOYEES table:

Which two queries return rows for employees whose manager works in a different department?

Options:

A.

SELECT emp. *

FROM employees emp

WHERE manager_ id NOT IN (

SELECT mgr.employee_ id

FROM employees mgr

WHERE emp. department_ id < > mgr.department_ id

);

B.

SELECT emp.*

FROM employees emp

WHERE NOT EXISTS (

SELECT NULL

FROM employees mgr

WHERE emp.manager id = mgr.employee_ id

AND emp.department_id<>mgr.department_id

);

C.

SELECT emp.*

FROM employees emp

LEFT JOIN employees mgr

ON emp.manager_ id = mgr.employee_ id

AND emp. department id < > mgr. department_ id;

D.

SELECT emp. *

FROM employees emp

RIGHT JOIN employees mgr

ON emp.manager_ id = mgr. employee id

AND emp. department id <> mgr.department_ id

WHERE emp. employee_ id IS NOT NULL;

E.

SELECT emp. *

FROM employees emp

JOIN employees mgr

ON emp. manager_ id = mgr. employee_ id

AND emp. department_ id<> mgr.department_ id;

Question 84

Which two are true about the data dictionary?

Options:

A.

Base tables in the data dictionary have the prefix DBA_.

B.

All user actions are recorded in the data dictionary.

C.

The data dictionary is constantly updated to reflect changes to database objects, permissions, and data.

D.

All users have permissions to access all information in the data dictionary by default

E.

The SYS user owns all base tables and user-accessible views in the data dictionary.

Question 85

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 86

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 87

Which is the default column or columns for sorting output from compound queries using SET operators such as INTERSECT in a SQL statement?

Options:

A.

The first column in the last SELECT of the compound query

B.

The first NUMBER column in the first SELECT of the compound query

C.

The first VARCHAR2 column in the first SELECT of the compound query

D.

The first column in the first SELECT of the compound query

E.

The first NUMBER or VARCHAR2 column in the last SELECTof the compound query

Question 88

Examine these two queries and their output:

SELECT deptno, dname FROM dept;

SELECT ename, job, deptno FROM emp ORDER BY deptno;

Now examine this query:

SELECT ename, dname

FROM emp CROSS JOIN dept WHERE job = 'MANAGER'

AND dept.deptno IN (10, 20) ;

Options:

A.

64

B.

6

C.

3

D.

12

Question 89

Examine this query:

SELECT TRUNC (ROUND(156.00,-2),-1) FROM DUAL; What is the result?

Options:

A.

16

B.

160

C.

150

D.

200

E.

100

Question 90

Which two statements are true about the COUNT function?

Options:

A.

It can only be used for NUMBER data types.

B.

COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column

C.

COUNT(*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column.

D.

A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.

E.

COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column.

Question 91

Examine the command to create the BOOKS table.

SQL> create table books(book id CHAR(6) PRIMARY KEY,

title VARCHAR2(100) NOT NULL,

publisher_id VARCHAR2(4)，

author_id VARCHAR2 (50));

The BOOK ID value 101 does not exist in the table.

Examine the SQL statement.

insert into books (book id title, author_id values

(‘101’，’LEARNING SQL’,’Tim Jones’)

Options:

A.

It executes successfully and the row is inserted with a null PLBLISHER_ID.

B.

It executes successfully only if NULL is explicitly specified in the INSERT statement.

C.

It executes successfully only NULL PUBLISHER_ID column name is added to the columns list in the INSERT statement.

D.

It executes successfully onlyif NULL PUBLISHER ID column name is added to the columns list and NULL is explicitly specified In the INSERT statement.

Question 92

Which three are true about privileges and roles?

Options:

A.

A role is owned by the user who created it.

B.

System privileges always set privileges for an entire database.

C.

All roles are owned by the SYS schema.

D.

A role can contain a combination of several privileges and roles.

E.

A user has all object privileges for every object in their schema by default.

F.

PUBLIC can be revoked from a user.

G.

PUBLIC acts as a default role granted to every user in a database

Question 93

MANAGER is an existing role with no privileges or roles.

EMP is an existing role containing the CREATE TABLE privilege.

EMPLOYEES is an existing table in the HR schema.

Which two commands execute successfully?

Options:

A.

GRANT CREATE SEQUENCE TO manager, emp;

B.

GRANT SELECT, INSERT ON hr.employees TO manager WITH GRANT OPTION:

C.

GRANT CREATE TABLE, emp TO manager;

D.

GRANT CREATE TABLE, SELECT ON hr. employees TO manager;

E.

GRANT CREATE ANY SESSION, CREATE ANY TABLE TO manager;

Question 94

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 clause.

C.

In a character sort, the values are case-sensitive.

D.

Column aliases can be used in the ORDER BY clause.

E.

NULLS are not included in the sort operation.

Question 95

Which two actions can you perform with object privileges?

Options:

A.

Create roles.

B.

Delete rows from tables in any schema except sys.

C.

Set default and temporary tablespaces for a user.

D.

Create FOREIGN KEY constraints that reference tables in other schemas.

E.

Execute a procedure or function in another schema.

Question 96

What is true about non-equijoin statement performance?

Options:

A.

The between condition always performs less well than using the >= and <= conditions.

B.

The Oracle join syntax performs better than the SQL: 1999 compliant ANSI join syntax.

C.

The join syntax used makes no difference to performance.

D.

The between condition always performs better than using the >= and <= conditions.

E.

Table aliases can improve performance.

Question 97

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

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