Month End Sale Limited Time 70% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: buysanta

Exact2Pass Menu

Question # 4

Which three statements are true about an ORDER BY clause?

A.

An ORDER BY clause always sorts NULL values last.

B.

An ORDER BY clause can perform a binary sort

C.

An ORDER BY clause can perform a linguistic sort

D.

By default an ORDERBY clause sorts rows in ascending order

E.

An ORDR BY clause will always precede a HAVI NG clause if both are used in the same top-level

Full Access
Question # 5

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

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.

Full Access
Question # 6

Examine the description of the CUSTOMERS table:

Which two SELECT statements will return these results:

CUSTOMER_ NAME

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

Mandy

Mary

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*';

Full Access
Question # 7

You execute this command:

TRUNCATE TABIE depts;

Which two are true?

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.

Full Access
Question # 8

Which three statements are true about dropping and unused columns in an Oracle database?

A.

A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.

B.

A DROP COLUMN command can be rolled back.

C.

An UNUSED column's space is remained automatically when the block containing that column is next queried.

D.

An UNUSED column's space is remained automatically when the row containing that column is next queried.

E.

Partition key columns cannot be dropped.

F.

A column that is set to NNUSED still counts towards the limit of 1000 columns per table.

Full Access
Question # 9

Which two are SQL features?

A.

providing graphical capabilities

B.

providing variable definition capabilities.

C.

providing database transaction control

D.

processing sets of data

E.

providing update capabilities for data in external files

Full Access
Question # 10

Which two are true about granting privilege on objects?

A.

The owner of an object acquires all object privilege on that object by default.

B.

The WITH GRANT OPTION clause can be used only by DBA users.

C.

A table owner must grant the references privilege to allow other users to create FOREIGN KEY constraints using that table.

D.

An object privilege can be granted to a role only by the owner of that object.

E.

An object privilege can be granted to other users only by the owner of object.

Full Access
Question # 11

Whith three statements are true about built in data types?

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

Full Access
Question # 12

Examine the description of EMPLOYEES table:

Which three queries return all rows for which SALARY+COMMISSION is greate than 20000?

A.

SELECT * FROM employees WHERE salary+NULLF(commission,0)>=20000;

B.

SELECT * FROM employees WHERE salary+NVL2(commission,commission,0)>=20000;

C.

SELECT * FROM employees WHERE NVL2(salary)+commission,salary+commission,

D.

SELECT * FROM employees WHERE salary+NVL(commission,0)>=20000;

E.

SELECT * FROM employees WHERE NVL(salary+commission,0)>=20000;

F.

SELECT * FROM employees WHERE NVL(salary+commission,0)>==20000;

Full Access
Question # 13

Which two statements are true about views?

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.

Full Access
Question # 14

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

A.

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

B.

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

C.

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

D.

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

E.

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

Full Access
Question # 15

Which three statements are true about the DESCRIBE command?

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.

Full Access
Question # 16

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?

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;

Full Access
Question # 17

Which three statements about roles are true?

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.

Full Access
Question # 18

Which two queries execute successfully?

A.

SELECT NULLIF(100, 100) FROM DUAL;

B.

SELECT COALESCE(100, NULL, 200) FROM DUAL;

C.

SELECT NULLIF(100, 'A') FROM DUAL;

D.

SELECT NULLIF(NULL, 100) FROM DUAL;

E.

SELECT COALESCE(100, 'A' ) FROM DUAL;

Full Access
Question # 19

Which three are key components of an Entity Relationship Model?

A.

a table

B.

an attribute

C.

a unique identifier

D.

an activity

E.

a relationship

F.

an entity

Full Access
Question # 20

The STORES table has a column START_DATE of data type DATE, containing the datethe row was inserted.

You only want to display details of rows where START_DATEis within the last 25 months.which WHERE clause can be used?

A.

WHERE TO_NUMBER(start_date - SYSDATE)<=25

B.

WHERE ADD_MONTHS (start date , 25)<= SYSDATE

C.

WHERE MONTHS_BETWEEN(SYSDATE, start_date)<=25

D.

WHERE MONTHS_BETWEEN (start_date, SYSDATE)<=25

Full Access
Question # 21

Which two are true about transactions in the Oracle Database?

A.

DDL statements automatically commit only data dictionary updates caused by executing the DDL.

B.

A DDL statement issued by a session with an uncommitted transation automaticall commits that transaction.

C.

An uncommitted transaction is automatically committed when the user exits SQL*PLUS

D.

DML statements always start new transactions.

E.

A session can see uncommitted updates made by the same user in a different session

Full Access
Question # 22

Which two statements are true about transactions in the Oracle Database server?

A.

An uncommitted transaction commits automatically if the user exits SQL*Plus

B.

Data Manipulation Language (DML) statements always start a new transaction.

C.

A user can always see uncommitted updates made by the same user in a different session.

D.

A Data Definition Language (DDL) statement does a commit automatically only for the data dictionary updates caused by the DDL

E.

A session can always see uncommitted updates made by itself.

F.

If a session has an uncommitted transaction, then a DDL statement issue a COMMIT before starting a new transaction.

Full Access
Question # 23

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

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.

Full Access
Question # 24

Which two statements are true about substitution variables?

A.

A substitution variable used to prompt for a column name must be endorsed in single quotation marks.

B.

A substitution variable used to prompt for a column name must be endorsed in double quotation marks.

C.

A substitution variable prefixed with & always prompts only once for a value in a session.

D.

A substitution variable can be used with any clause in a SELECT statement.

E.

A substitution variable can be used only in a SELECT statement.

F.

A substitution variable prefixed with 6 prompts only once for a value in a session unless is set to undefined in the session.

Full Access
Question # 25

Which three statements are true about Data Manipulation Language (DML)?

A.

delete statements can remove multiple rows based on multiple conditions.

B.

insert statements can insert nulls explicitly into a column.

C.

insert into. . .select. . .from statements automatically commit.

D.

DML statements require a primary key be defined on a table.

E.

update statements can have different subqueries to specify the values for each updated column.

Full Access
Question # 26

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?

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');

Full Access
Question # 27

Which two statements are true about INTERVAL data types

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.

Full Access
Question # 28

Examine this statement:

Which two statements are true?

A.

All remaining employee names will appear in an ascending order

B.

The names of employees remaining the maximum salary will appear first in an ascending order

C.

All remaining employee names will appear in ascending order

D.

All remaining employee names will appear in descending order

E.

The names of employees maximum salary will appear fist to descending order

F.

The names of employees maximum salary will appear fist to ascending order

Full Access
Question # 29

Examine this description of the PRODUCTS table:

You successfully execute this command:

CREATE TALE new_prices(prod_id NUBER(2),price NUMBER(8,2));

Which two statements execute without errors?

A.

MERGE INTO new_prices n

USING(SELECT*FROM products)p

WHEN MATECHED 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 product WHERE cost>150) p

ON (n.prod_id=p.prod_id)

WHEN NATCHED THEN

DELETE WHERE(p.cost<200)

WHEN NOT MATCHED THEN

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

C.

MERGE INTO new_prices n

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

ON (n.prod_id=p.prod_id)

WHEN NATCHED THEN

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

DELETE WHERE (p.cost<200);

D.

MERGE INTO new_prices n

USING products p

WHEN NOT NATCHED THEN

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

WHERE (p.cost <200);

Full Access
Question # 30

Which three statements are true about Structured Query Language (SQL)?

A.

It guarantees atomicity, consistency, isolation, and durability (ACID) features

B.

It best supports relational databases

C.

It is used to define encapsulation and polymorphism for a relational table

D.

It requires that data be contained in hierarchical data storage

E.

It is the only language that can be used for both relational and object-oriented databases

F.

It provides independence for logical data structures being manipulated from the underlying physical data storage

Full Access
Question # 31

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?

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:

Full Access
Question # 32

Which two statements are true about truncate and delete?

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.

Full Access
Question # 33

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

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.

Full Access
Question # 34

Examine these SQL statements which execute successfully:

Which two statements are true after execution?

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.

Full Access
Question # 35

Examine the description of the SALES table:

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?

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.

Full Access
Question # 36

Examine this partial statement:

SELECT ename, sal,comm FROM emp

Now examine this output:

WHICH ORDER BY clause will generate the displayed output?

A.

ORDER BY NVL(enam,0) DESC, ename

B.

ORDER BY NVL(comm,0) ASC NULLS FIRST, ename

C.

ORDER BY NVL(comm,0) ASC NULLS LAST, ename

D.

ORDER BY comm DESC NULLS LAST, ename

Full Access
Question # 37

Which two are true about scalar subquery expressions?

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.

Full Access
Question # 38

Which three statements are true about the Oracle join and ANSI Join syntax?

A.

The Oracle join syntax only supports right outer joins,

B.

The Oracle join syntax supports creation of a Cartesian product of two tables.

C.

The SQL:1999 compliant ANSI join syntax supports natural joins.

D.

The Oracle join syntax supports natural joins.

E.

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

F.

The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.

G.

The Oracle join syntax performs less well than the SQL:1999 compliant ANSI Join Answer.

Full Access
Question # 39

Examine the description of the EMPLOYEES table:

Which statement will fail?

A.

SELECT department_id, COUNT (*)

FROM employees

HAVING department_ id <> 90 AND COUNT(*) >= 3

GROUP BY department_id;

B.

SELECT department_id, COUNT (*)

FROM employees

WHERE department_ id <> 90 AND COUNT(*) >= 3

GROUP BY department_id;

C.

SELECT department_id, COUNT(*)

FROM employees

WHERE department_id <> 90 HAVING COUNT(*) >= 3

GROUP BY department_id;

D.

SELECT department_id, COUNT(*)

FROM employees

WHERE department_id <> 90 GROUP BY department_id

HAVING COUNT(*) >= 3;

Full Access
Question # 40

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?

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.

Full Access
Question # 41

Choose two

Examine the description of the PRODUCT DETALS table:

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.

Full Access
Question # 42

You want to return the current date and time from the user session, with a data type of TIMESTAMP WITH TIME ZONE.

Which function will do this?

A.

CURRENT DATE

B.

CURRENT_ TIMESTAMP

C.

SYSDATE

D.

LOCALTIMESTAMP

Full Access
Question # 43

Which two will execute successfully?

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) ;

Full Access
Question # 44

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.

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"

Full Access
Question # 45

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

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.

Full Access
Question # 46

Which two statements are true about * _TABLES views?

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.

Full Access
Question # 47

Which two statements are true about the ORDER BY clause?

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.

Full Access