11.11 Sale - Special 70% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: 70dumps

1z0-071 Questions and Answers

Question # 6

Examine the description of the EMPLOYEES table:

Examine this query:

Which line produces an error?

A.

Line 7

B.

Line 8

C.

Line 3

D.

Line 5

Full Access
Question # 7

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 # 8

Examine the description of the ORDERS table:

Which three statements execute successfully?

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;

Full Access
Question # 9

Examine this description of the EMP table:

You execute this query:

SELECT deptno AS "departments", SUM (sal) AS "salary"

FROM emp

GROUP | BY 1

HAVING SUM (sal)> 3 000;

What is the result?

A.

only departments where the total salary is greater than 3000, returned in no particular order

B.

all departments and a sum of the salaries of employees with a salary greater than 3000

C.

an error

D.

only departments where the total salary is greater than 3000, ordered by department

Full Access
Question # 10

Examine this query which executes successfully;

Select job,deptno from emp

Union all

Select job,deptno from jobs_history;

What will be the result?

A.

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

B.

It will return rows common to both select statements.

C.

It will return rows both select statements including duplicate rows.

D.

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

Full Access
Question # 11

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

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

Full Access
Question # 12

Examine the data in the ENPLOYEES table:

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

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

Full Access
Question # 13

What is true about non-equijoin statement performance?

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.

Full Access
Question # 14

Choose the best answer.

Examine the description of the EMPLOYEES table:

Which query is valid?

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;

Full Access
Question # 15

Which two are true about granting privilege on objects?

A.

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

B.

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

C.

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

D.

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

E.

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

Full Access
Question # 16

Which two statements will return the names of the three employees with the lowest salaries?

A.

SELECT last_name, salary

FROM employees

WHERE ROWNUM<=3

B.

SELECT last_name,salary

FROM employees

ORDER BY salary

FETCH FIRST 3 ROWS ONLY;

C.

SELECT last_name,salary

FROM employees

WHERE ROWNUM<=3

ORDER BY (SELECT salary FROM employees);

D.

SELECT last_name,salary

FROM (SELECT * FROM employees ORDER BY salary)

E.

SELECT last_name,salary

FROM employees

FETCH FIRST 3 ROWS ONLY

ORDER BY salary;

Full Access
Question # 17

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 # 18

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 # 19

Which two statements are true about a full outer join?

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.

Full Access
Question # 20

Examine this SQL statement:

SELECT cust_id, cust_last_name "Last Name

FROM customers

WHERE countryid=10

UNION

SELECT custid CUSTNO, cust_last_name

FROM customers

WHERE countryid=30

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

A.

ORDER BY“CUST NO"

B.

ORDER BY 2, cust_id

C.

ORDERBY2, 1

D.

ORDER BY "Last Name"

E.

ORDER BY CUSTNO

Full Access
Question # 21

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

Which two queries return the value 1 Jan 2019?

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;

Full Access
Question # 22

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

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.

Full Access
Question # 23

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?

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;

Full Access
Question # 24

Which two are true about constraints?

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.

Full Access
Question # 25

Examine the description of the transactions table:

Which two SQL statements execute successfully?

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;

Full Access
Question # 26

Which two statements are true about an Oracle database?

A.

A table can have multiple primary keys.

B.

A table can have multiple foreign keys.

C.

A NUMBER column without data has a zero value.

D.

A column definition can specify multiple data types.

E.

A VARCHAR2 column without data has a NULL value.

Full Access
Question # 27

Which three statements are true?

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.

Full Access
Question # 28

Which three statements are true about GLOBAL TEMPORARY TABLES?

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.

Full Access
Question # 29

Examine the description products table:

Examine the description of the new_projects table;

Which two queries execute successfully?

A)

B)

C)

D)

E)

A.

Option A

B.

Option B

C.

Option C

D.

Option D

E.

Option E

Full Access
Question # 30

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

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.

Full Access
Question # 31

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 # 32

Examine the data in the EMPLOYEES table:

Which statement will compute the total annual compensation for each employee?

A.

SELECT last _ NAME (monthly_ salary + monthly _commission _ pct) * 12 AS annual_ comp FROM employees;

B.

select last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 *monthly_ commission_ pct) AS annual_ camp FROM employees

C.

SELECT last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 * NVL (monthly_ commission _pct, 0)) AS annual _comp

D.

SELECT last _ name, (monthly _ salary * 12) + (monthly_ commission _ pct * 12) AS FROM employees:

Full Access
Question # 33

You execute these commands:

SQL> DEFINE hiredate = ’01-APR -2011’;

SQL> SELECT employee_id, first_name, salary FROM employees WHERE hire date > &hiredate AND manager_id >&mgr_id;

For which substitution variables will you be prompted?

A.

none

B.

&hiredate and &mgr_id

C.

only &hiredate

D.

only &mgr_id

Full Access
Question # 34

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

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

Full Access
Question # 35

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 # 36

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

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.

Full Access
Question # 37

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?

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.

Full Access
Question # 38

Examine this partial query:

SELECT ch.channel_type, t.month, co.country_code, SUM(s.amount_sold) SALES

FROM sales s, times t, channels ch, countries co

WHERE s.time_ id = t.time id

AND s.country_ id = co. country id

AND s. channel id = ch.channel id

AND ch.channel type IN ('Direct Sales', 'Internet')

AND t.month IN ('2000-09', '2000-10')

AND co.country code IN ('GB', 'US')

Examine this output:

Which GROUP BY clause must be added so the query returns the results shown?

A.

GROUP BY ch.channel_type, t.month, co.country code;

B.

GROUP BY ch.channel_type,ROLLUP (t month, co. country_ code) ;

C.

GROUP BY CUBE (ch. channel_ type, t .month, co. country code);

D.

GROUP BYch. channel_ type, t.month,ROLIUP (co. country_ code) ;

Full Access
Question # 39

Which three statements are true about inner and outer joins?

A.

A full outer join returns matched and unmatched rows.

B.

A full outer join must use Oracle syntax.

C.

Outer joins can be used when there are multiple join conditions on two tables.

D.

Outer joins can only be used between two tables per query.

E.

An inner join returns matched rows.

F.

A left or right outer join returns only unmatched rows.

Full Access
Question # 40

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 # 41

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

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.

Full Access
Question # 42

Which two tasks require subqueries?

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.

Full Access
Question # 43

Which two statements are true about a self join?

A.

The join key column must have an index.

B.

It can be a left outer join.

C.

It must be a full outer join.

D.

It can be an inner join.

E.

It must be an equijoin.

Full Access
Question # 44

Examine the description of the ENPLYEES table:

Which two queries return all rows for employees whose salary is greater than the average salary in their department?

A.

SELECT ”

FROM employees

WHERE salary > ANY

SELECT AVG (salary)

EROM employees

GROUP BY department_ id);

B.

SELECT

FROM employees

WHERE salary > AVG (salary) OVER (PARTITION BY department _ id);

C.

SELECT”

FROM employees e1

WHERE salary >!

SELECT AVG (salary)

FROM employees e2

WHERE e1. Department _id = e2, department_ id

D.

SELECT.

FROM

SELECT e.", AVG (salary) OVER (PARTITION BY department id) avg_ sal

FROM employees e

WHERE salary > avg_ sal;

E.

SELECT”

FROM employees

WHERE salary >

( SELECT AVG

(salary) FROM

employees

GROUP BY department _ id

Full Access
Question # 45

Examine this list of queries:

Which two statements are true?

A.

1 and 4 give the same result.

B.

2 returns the value 20.

C.

2 and 3 give the same result.

D.

3 returns an error.

E.

1 and 4 give different results.

Full Access
Question # 46

Examine the description of the EMPLOYEES table:

Which statement will execute successfully, returning distinct employees with non-null first names?

A.

SELECT DISTINCT * FROM employees WHERE first_ name IS NOT NULL;

B.

SELECT first_ name, DISTNCT last_ name FROM employees WHERE first_ name IS NOT NULL;

C.

SELECT Distinct * FROM employees WHERE first_ name < > NULL;

D.

SELECT first_ name, DISTINCT last_ name FROM employees WHERE first_ name < > NULL;

Full Access
Question # 47

Which four statements are true about constraints on Oracle tables?

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.

Full Access
Question # 48

Which three items does a direction of a relationship contain?

A.

an attribute

B.

a cardinality

C.

label

D.

an optionality

E.

a unique identifier

F.

an entity

Full Access
Question # 49

Examine this partial command:

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

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

Full Access
Question # 50

Which two statements will do an implicit conversion?

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

Full Access
Question # 51

Which two statements are true about outer Joins?

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.

Full Access
Question # 52

What is true about non-equijoin statement performance?

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.

Full Access
Question # 53

Examine the description of the BRICKS table;

Examine the description of the BRICKS_STAGE table;

Which two queries execute successfully?

A.

SELECT shape,color,weight from bricks

MINUS

SELECT * FROM bricks_stage;

B.

SELECT shape,color FROM bricks

MINUS

SELECT WEIGHT,color FROM bricks_stage;

C.

select * from bricks

MINUS

select * from bricks_stage;

D.

SELECT shape,color FROM bricks

MINUS

SELECT color,shape FROM bricks_stage;

E.

SELECT brick_id,shape FROM bricks

MINUS

SELECT WEIGHT,COLOR from bricks_stage;

Full Access
Question # 54

Examine this query which executes successfully:

SELECT job, deptno FROM emp

UNION ALL

SELECT job, deptno FROM jobs_ history;

What will be the result?

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.

Full Access
Question # 55

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?

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.

Full Access
Question # 56

Which two actions can you perform with object privileges?

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.

Full Access
Question # 57

Which two are true about self joins?

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.

Full Access
Question # 58

Which two statements are true about * TABLES views?

A.

You must have SELECT privileges on a table to view it in ALL TABLES.

B.

You must have SELECT privileges on a table to view it in DBA TABLES.

C.

USER TABLES displays all tables owned by the current user.

D.

All TABLES displays all tables owned by the current user.

E.

You must have SELECT privileges on a table to view it in USER TABLES.

F.

All users can query DBA TABLES successfully.

Full Access
Question # 59

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 # 60

Examine the description of the PRODUCT_ DETAILS table:

Which two statements are true?

A.

PRODUCT_ PRICE can be used in an arithmetic expression even if it has no value stored in it.

B.

PRODUCT_ ID can be assigned the PRIMARY KEY constraint.

C.

EXPIRY_ DATE cannot be used in arithmetic expressions.

D.

EXPIRY_ DATE contains the SYSDATE by default if no date is assigned to 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 # 61

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

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

Full Access
Question # 62

Examine the description of the EMPLOYEES table:

Which two queries return the highest salary in the table?

A.

SELECT department_id, MAX(salary)

FROM employees

GROUP BY department_id;

B.

SELECT MAX (salary)

FROM employees;

C.

SELECT MAX (salary)

FROM employees

GROUP BY department_id;

D.

SELECT MAX (salary)

FROM employees

GROUP BY department_id

HAVING MAX (salary) = MAX (MAX (salary));

E.

SELECT MAX (MAX (salary))

FROM employees

GROUP BY department_id;

Full Access
Question # 63

Which three are true about subqueries?

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.

Full Access
Question # 64

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?

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.

Full Access
Question # 65

Examine this query:

SELECT employee_id, first_name, salary

FROM employees

WHERE hiredate > 61*

Which two methods should yours to prevent prompting for hire date value when this queries executed?

A.

Execute the SET VERIFY ON command before executing the query.

B.

Execute the SET VERIFY OFF command before executing the query.

C.

Store the query in a script and pass the substitution value to the script when executing it.

D.

Replace 's1' with &1'in the query:

E.

Use the UNDEFINE command before executing the query.

F.

Use the DEFINE command before executing the query

Full Access
Question # 66

Which two statements cause changes to the data dictionary?

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;

Full Access
Question # 67

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?

A.

CROSS

B.

FULL OUTER

C.

LEFT OUTER

D.

RIGHT OUTER

Full Access
Question # 68

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 # 69

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
Question # 70

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

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.

Full Access
Question # 71

Examine the description of the CUSTONERS table

CUSTON is the PRIMARY KEY.

You must derermine if any customers’derails have entered more than once using a different

costno,by listing duplicate name

Which two methode can you use to get the requlred resuit?

A.

RIGHT OUTER JOIN with seif join

B.

FULL OUTER JOIN with seif join

C.

SUBQUERY

D.

seif join

E.

LEFT OUTER JOIN with seif join

Full Access
Question # 72

Examine the description or the CUSTOMERS 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?

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_AMONT FROM customers

WHERE cust_income_level <> NULL AND due_amount <> NULL;

C.

SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers

WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;

D.

SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers

WHERE cust_income_level IS NOT NULL AND due_amount IS NOT NULL;

E.

SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers

WHERE cust_income_level != NULL AND due_amount != NULL;

Full Access
Question # 73

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 # 74

Examine this statement which executes successfully:

Which statement will violate the CHECK constraint?

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;

Full Access
Question # 75

Which two are true about the MERGE statement?

A.

The WHEN NOT MATCHED clause can be used to specify the deletions to be performed.

B.

The WHEN NOT MATCHED clause can be used to specify the inserts to be performed.

C.

The WHEN MATCHED clause can be used to specify the inserts to be performed.

D.

The WHEN NOT MATCHED clause can be used to specify the updates to be performed.

E.

The WHEN MATCHED clause can be used to specify the updates to be performed.

Full Access
Question # 76

An Oracle database server session has an uncommitted transaction in progress which updated 5000 rows

in a table.

In which three situations does the transact ion complete thereby committing the updates?

A.

When the session logs out is successfully

B.

When a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT

C.

When a CREATE INDEX statement is executed successfully in same session

D.

When a COMMIT statement is issued by the same user from another session in the same database instance

E.

When a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session

F.

When a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user, then issues a COMMIT

Full Access
Question # 77

Which two statements are true about Oracle synonyms?

A.

A synonym can have a synonym.

B.

A synonym has an object number.

C.

Any user can create a public synonym.

D.

All private synonym names must be unique in the database.

E.

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

Full Access
Question # 78

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 # 79

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:

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

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

Full Access
Question # 80

Which two are true about the precedence of opertors and condtions

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)

Full Access
Question # 81

Which two are true about the WITH GRANT OPTION clause?

A.

The grantee can grant the object privilege to any user in the database, with of without including this option.

B.

The grantee must have the GRANT ANY OBJECT PRIVILEGE system prvilege to use this option.

C.

It can be used when granting privileges to roles.

D.

It can be used for system and object privileges.

E.

It cannot be used to pass on privileges to PUBLIC by the grantee.

F.

It can be used to pass on privileges to other users by the grantee.

Full Access
Question # 82

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?

A.

quantity

B.

quantity, unit_price

C.

total_paid

D.

product_id

E.

quantity * unit_price

Full Access
Question # 83

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 # 84

Which two statements are true about conditional INSERT ALL?

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.

Full Access
Question # 85

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?

A.

TIMESTAMP WITH TIMEZONE

B.

TIMESTAMP

C.

TIMESTAMP WITH LOCAL TIMEZONE

D.

INTERVAL YEAR TO MONTH

E.

INTERVAL DAY TO SECOND

Full Access
Question # 86

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 # 87

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 # 88

Which three statements are true regarding single row subqueries?

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.

Full Access
Question # 89

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?[

A.

2 rows

B.

0 rows

C.

An error

D.

1 ROW

Full Access
Question # 90

Examine the data in the EMPLOYEES table:

Which statement will compute the total annual compensation for each employee?

A.

SELECT last name,

(monthly salary*12) + (monthly_commission_pct * 12) AS

annual comp

FROM employees

;

B.

SELECT last_ name (monthly_ salary+ monthly_ commission _ pct) *12 AS annual_

FROM employees ;

C.

SELECT last name, (monthly_ salary *12) + (monthly_ salary * 12 * NVL

(monthly commission pct,0) ) As annual _ comp

FROM employees;

D.

SELECT last_ name, monthly_ salary*12) + (monthly_ salary * 12 * Monthly commission _Pct) AS

annual_ comp

FROM employees;

Full Access
Question # 91

Examine this query:

SELECT employee_id,first_name,salary

FROM employees

WHERE hire_date>'&1';

Which two methods should you use to prevent prompting for a hire date value when this query is executed?

A.

Use the DEFINE command before executing the query.

B.

Store the query in a script and pass the substitution value to the script when executing it.

C.

Replace'&1' with'&&1' in the query.

D.

Execute the SET VERIFY OFF command before executing the query.

E.

Use the UNDEFINE command before executing the query.

F.

Execute the SET VERIFY ON command before executing the query.

Full Access
Question # 92

Which two queries will result in an error?

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;

Full Access
Question # 93

Which two queries only return CUBE?

A.

SELECT shape FROM bricks JOIN boxes ON weight >= min_weight AND weight < max_weight;

B.

SELECT shape FROM bricks JOIN boxes ON weight > min_weight;

C.

SELECT shape FROM bricks JOIN boxes ON weight BETWEEN min_weight AND max_weight;

D.

SELECT shape FROM bricks JOIN boxes ON weight < max_weight;

E.

SELECT shape FROM bricks JOIN boxes ON NOT (weight > max_weight);

Full Access
Question # 94

Which two statements are true about external tables?

A.

Indexes can be created on them.

B.

You can populate them from existing data in the database by using the CREATE TABLE AS SELECT command.

C.

DML statements cannot be used on them.

D.

Their data can be retrieved by using only SQL or PL/SQL.

E.

Their metadata and actual data are both stored outside the database.

Full Access
Question # 95

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 # 96

Examine the description of the PROMTIONS table:

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

Which two queries can be used?

A.

SELECT promo_cost, | pxomo_category FROM promotions ORDER BY 1;

B.

SELECT promo_category, DISTINCT promo_cost PROM promotions ORDER BY 2:

C.

SELECT DISTINCT promo_category ||'has’|| promo_cost AS COSTS FROM promotions ORDER BY 1;

D.

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

E.

SELECT DISTINCT promo_cost ||' in' II DISTINCT promo_category FROM promotions ORDER BY 1;

Full Access
Question # 97

Which three are true about privileges and roles?

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

Full Access