1z0-071 Oracle Database 12c SQL exact Exam Questions

Question # 4

Examine this data in the EMPLOYERS table：

Which statement will execute successfully?

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

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

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

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

Full Access

Question # 5

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?

CREATE SEQUENCE emp_seq START WITH 1 INCRENENT BY 1 NOCACHE;

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CYCLE;

CREATE SEQUENCE emp_seq NOCACHE;

CREATE SEQUENCE emp_seq START WITH 1 CACHE;

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CACHE;

CREATE SEQUENCE emp_seq;

Full Access

Question # 6

Examine the description of the countries table:

Examine the description of the departments table:

Examine the description of the locations table:

Which two queries will return a list of countries with no departments?

Option A

Option B

Option C

Option D

Full Access

Question # 7

Examine this query which executes successfully:

SELECT job, deptno FROM emp

UNION ALL

SELECT job, deptno FROM jobs_ history;

What will be the result?

It will return rows common to both SELECT statements.

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

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

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

Full Access

Question # 8

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

Which statement fails to do this?

SELECT COUNT (*)

FROM employees

JOIN employees a

ON e. salary< a. salary

WHERE a. employee_ id= 110;

SELECT COUNT (* )

FROM employees

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

110) ;

SELECT COUNT (*)

FROM employees e

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

ON e. salary< a. salary;

SELECT COUNT (* )

FROM employees e

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

Full Access

Question # 9

Which two statements are true about the order by clause when used with a sql statement containing a set operator such as union?

column positions must be used in the order by clause.

The first column in the first select of the compound query with the union operator is used by default to sort output in the absence of an order by clause.

Each select statement in the compound query must have its own order by clause.

only column names from the first select statement in the compound query are recognized.

Each select statement in the compound query can have its own order by clause.

Full Access

Question # 10

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

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

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

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

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

The FOR UPDATE clause cannot be specified.

Full Access

Question # 11

Examine the description of the BRICKS table;

Examine the description of the BRICKS_STAGE table;

Which two queries execute successfully?

SELECT shape,color,weight from bricks

MINUS

SELECT * FROM bricks_stage;

SELECT shape,color FROM bricks

MINUS

SELECT WEIGHT,color FROM bricks_stage;

select * from bricks

MINUS

select * from bricks_stage;

SELECT shape,color FROM bricks

MINUS

SELECT color,shape FROM bricks_stage;

SELECT brick_id,shape FROM bricks

MINUS

SELECT WEIGHT,COLOR from bricks_stage;

Full Access

Question # 12

You create a table named 123.

Which statement runs successfully?

SELECT * FROM TABLE (123) ;

SELECT * FROM '123';

SELECT * FROM "123";

SELECT * FROM V'123V';

Full Access

Question # 13

You execute this command:

ALTER TABLE employees SET UNUSED (department_id);

Which two are true?

A query can display data from the DEPARTMENT_ID column.

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

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

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

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

The DEPARTMENT_ID column can be recovered from the recycle bin

Full Access

Question # 14

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?

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

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

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

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

Full Access

Question # 15

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

delete statements can remove multiple rows based on multiple conditions.

insert statements can insert nulls explicitly into a column.

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

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

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

Full Access

Question # 16

Examine the description of the BOOKS table:

The table has 100 rows.

Examine this sequence of statements issued in a new session;

INSERT INTO BOOKS VALUES (‘ADV112’ , ‘Adventures of Tom Sawyer’, NULL, NULL);

SAVEPOINT a;

DELETE from books;

ROLLBACK TO SAVEPOINT a;

ROLLBACK;

Which two statements are true?

The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed.

The second ROLLBACK command does nothing.

The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row.

The second ROLLBACK command replays the delete.

The second ROLLBACK command undoes the insert.

Full Access

Question # 17

Which three statements about roles are true?

Roles are assigned to roles using the ALTER ROLE Statement

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

Roles are assigned to users using the ALTER USER statement

A single role can be assigned to multiple users.

A single user can be assigned multiple roles

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

Privileges are assigned to a role using the GRANT statement.

Full Access

Question # 18

Which three are true about system and object privileges

WITH GRANT OPTION can be used when granting an object privilege to both users and roles

WITH GRANT OPTION cannot be used when granting an object privilege to PUBLIC

Revoking a system privilege that was granted with the WITH ADMIN OPTION has a cascading effect.

Revoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect

Adding a primary key constraint to an existing table in another schema requires a system privilege

Adding a foreign key constraint pointing to a table in another schema requires the REFERENCEs object privilege

Full Access

Question # 19

Which three statements are true about inner and outer joins?

A full outer join returns matched and unmatched rows.

A full outer join must use Oracle syntax.

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

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

An inner join returns matched rows.

A left or right outer join returns only unmatched rows.

Full Access

Question # 20

Examine this query which executes successfully;

Select job,deptno from emp

Union all

Select job,deptno from jobs_history;

What will be the result?

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

It will return rows common to both select statements.

It will return rows both select statements including duplicate rows.

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

Full Access

Question # 21

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?

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

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

User FINANCE can grant CREATE SESSION to user FIN MANAGER.

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

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

Full Access

Question # 22

SELECT *

FROM bricks,colors;

Which two statements are true?

You can add an ON clause with a join condition.

You can add a WHERE clause with filtering criteria.

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

You can add a USING clause with a join condition.

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

Full Access

Question # 23

Examine the description of the PRODUCT_STATUS table:

The STATUS column contains the values 'IN STOCK' or 'OUT OF STOCK' for each row

Which two queries will execute successfully?

SELECT prod_id "CURRENT AVAILABILITY" || q'('s not available)' FROM product_status WHERE status = ’OUT OF STOCK';

SELECT prod_id || q's not available'' FROM product_status WHERE status=’OUT OF STOCK’;

SELECT prod_id || q'('s not available)’ "CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK';

SELECT prod_id || q'('s not available)' FROM product_status WHERE status = ’OUT OF STOCK’;

SELECT prod_id || q’(’s not available)' 'CURRENT AVAILABILITY' FROM product_status WHERE status = 'OUT OF STOCK';

SELECT prod_id || q"'s not available" FROM product_status WHERE status = 'OUT OF STOCK';

Full Access

Question # 24

Which two queries execute successfully?

SELECT prod_id, exp_date FROM products

UNION ALL

SELECT prod_id, NULL FROM new_products;

SELECT prod_id, prod_name FROM products

INTERSECT

SELECT 100, prod_name FROM newproducts;

SELECT * FROM products

UNION

SELECT * FROM new_products;

SELECT k FROM products

MINUS

SELECT prod_id FROM new_products;

SELECT prod_id FROM products

UNION ALL

SELECT prod_id, prod_name FROM new_products;

Full Access

Question # 25

Which two statements are true about substitution variables?

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

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

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

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

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

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

Choose the best answer.

Examine the description of the EMPLOYEES table:

Which query is valid?

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

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

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

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

Full Access

Question # 27

Which two statements are true regarding the UNION ALL operators?

NULLS are not ignored during duplicate checking.

Duplicates are eliminated automatically by the UNION ALL operator

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

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

The output is sorted by the UNION ALL operator.

Full Access

Question # 28

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?

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

Andrew will be unable to see the changes you have made

Andrew will be able to see the changes you habe made

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

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

Full Access

Question # 29

Which two queries only return CUBE?

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

SELECT shape FROM bricks JOIN boxes ON weight > min_weight;

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

SELECT shape FROM bricks JOIN boxes ON weight < max_weight;

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

Full Access

Question # 30

Which two statements are true about the ORDER BY clause?

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

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

NULLS are not included in the sort operation.

Column aliases can be used In the ORDER BY cause.

Ina character sort, the values are case-sensitive.

Full Access

Question # 31

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?

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

The subquery is not a correlated subquery.

The subquery is executed before the DELETE statement is executed.

All existing rows in the EMPLOYEE table are deleted.

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

Full Access

Question # 32

Which three statements are true about a self join?

It must be an inner join.

It can be an outer join.

The ON clause must be used.

It must be an equijoin.

The query must use two different aliases for the table.

The ON clause can be used.

Full Access

Question # 33

Examine these statements:

CREATE TABLE dept (

deptno NUMBER PRIMARY KEY,

diname VARCHAR2(10) ,

mgr NUMBER ,

CONSTRAINT dept_fkey FOREIGN KEY(mgr) REFERENCES emp (empno));

CREATE TABLE emp (

Empno NUMBER PRIMARY KEY,

Ename VARCHAR2 (10) ,

deptno NUMBER,

CONSTRAINT emp_fkey FOREIGN KEY (deptno) REFERENCES dept (deptno) DISABLE);

ALTER TABLE emp MODIFY CONSTRAINT emp_fkey ENABLE;

Which two are true?

The MGR column in the DEPT table will not be able to contain NULL values.

The CREATE TABLE EMP statement must precede the CREATE TABLE DEPT statement for all threestatements to execute successfully.

Both foreign key constraint definitions must be removed from the CREATE TABLE statements, andbe added with ALTER TABLE statements once both tables are created, for the two CREATE TABLEstatements to

execute successfully in the order shown.

The DEFT FKEY constraint definition must be removed from the CREATE TABLE DEF statement.and be added with an AITER TABLE statement once both tables are created, for the two CREATE TABLE statements

to execute successfully in the order shown.

The Deptno column in the emp table will be able to contain nulls values.

All three statements execute successfully in the order shown

Full Access

Question # 34

Examine these statements and results：

SQL> SELECT COUNT(*) FROM emp

COUNT(*)

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

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?

Full Access

Question # 35

Which two will execute successfully?

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

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

SELECT COALESCE(O,SYSDATE) TRCH DUAL;

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

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

Full Access

Question # 36

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?

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

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

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

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

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

Full Access

Question # 37

Examine the description of the EMPLOYEES table：

Which two queries return the highest salary in the table?

SELECT department_id, MAX(salary)

FROM employees

GROUP BY department_id;

SELECT MAX (salary)

FROM employees;

SELECT MAX (salary)

FROM employees

GROUP BY department_id;

SELECT MAX (salary)

FROM employees

GROUP BY department_id

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

SELECT MAX (MAX (salary))

FROM employees

GROUP BY department_id;

Full Access

Question # 38

Which two queries execute successfully?

SELECT INTERVAL '1' DAY - SYSDATE FROM DUAL;

SELECT SYSTIMESTAMP + INTERVAL '1' DAY FROM DUAL;

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

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

SELECT SYSDATE “INTERRVAL '1' DAY FROM DUAL;

Full Access

Question # 39

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?

GRANT CREATE SEQUENCE TO manager, emp;

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

GRANT CREATE TABLE, emp TO manager;

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

GRANT CREATE ANY SESSION, CREATE ANY TABLE TO manager;

Full Access

Question # 40

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?

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

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

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

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

Full Access

Question # 41

Examine the ORDER _ITEms table:

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

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

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

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

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

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

Full Access

Question # 42

Which statement is true about aggregate functions?

The AVG function implicitly converts NULLS to zero

The MAX and MIN functions can be used on columns with character data types

Aggregate functions can be used in any clause of a SELECT statement

Aggregate functions can be nested to any number of levels

Full Access

Question # 43

Examine the description of the transactions table:

Which two SQL statements execute successfully?

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

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

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

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

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

Full Access

Question # 44

Which statement is true regarding the SESSION_PRIVS dictionary view?

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

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

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

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

Full Access

Question # 45

Which two statements are true about external tables?

Indexes can be created on them.

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

DML statements cannot be used on them.

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

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

Full Access

Question # 46

Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY.

Only the EMPLOYEES_ID column is indexed.

Rows exist for employees 100 and 200.

Examine this statement:

UPDATE employees

SET (job_id, salary) =

(SELECT job_id, salary

FROM employees

WHERE employee_id = 200)

WHERE employee id=100;

Which two statements are true?

Employees 100 and 200 will have the same SALARY as before the update command.

Employee 100 will have SALARY set to the same value as the SALARY of employee 200.

Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200.

Employees 100 and 200 will have the same JOB ID as before the update command.

Employee 200 will have SALARY set to the same value as the SALARY of employee 100.

Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100

Full Access

Question # 47

Which two are true about unused columns?

The DESCRIBE command displays unused columns

A primary key column cannot be set to unused.

A query can return data from unused columns, but no DML is possible on those columns.

Once a column has been set to unused, a new column with the same name can be added to the table.

A foreign key column cannot be set to unused.

Unused columns retain their data until they are dropped

Full Access

Question # 48

Which three statements are true about single-row functions?

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

They can be nested to any level.

They return a single result row per table.

They can accept only one argument.

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

They can be used only in the WHERE clause of a SELECT statement.

Full Access

Question # 49

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?

The subquery is not a correlated subquery.

The subquery is executed before the DELETE statement is executed.

All existing rows in the EMPLOYEES table are deleted,

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

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

Full Access

Question # 50

Examine the description of the PRODUCTS table:

Which three queries use valid expressions?

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

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

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

SPLECT product_id, expiry_date * 2 FROM products;

SELEGT product_id, unit_price, unit_price + surcharge FROM products;

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

Full Access

Question # 51

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?

TIMESTAMP WITH TIMEZONE

TIMESTAMP

TIMESTAMP WITH LOCAL TIMEZONE

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

Full Access

Question # 52

Which statement will execute successfully?

SELECT 1, 2 FROM DUAL

UNION

SELECT 3, 4 FROM DUAL

ORDER BY 1, 2;

SELECT 3 FROM DUAL

UNION

SELECT 4 FROM DUAL

ORDER BY 3 ;

SELECT 1, 2 FROM DUAL

UNION

SELECT 3, 4 FROM DUAL

ORDER BY 3, 4;

SELECT 1 FROM DUAL

UNION

SELECT 2 FROM DUAL

ORDER BY 1, 2;

Full Access

Question # 53

Examine the description of the employees table:

Examine these requirements:

1- Display the last name, date of hire and the number of years of service for each employee.

2. If the employee has been employed 5 or more years but less than 10, display -5+ years of service".

3. If the employee has been employed 10 or more years but less than 15, display "10+ years of

service".

4. If the employee has been employed 15 or more years, display "15-*- years of service".

5. If none of these conditions matches, display "<5 years of service".

6. Sort the results by the hire_date column.

Which statement satisfies all the requirements?

Option A

Option B

Option C

Option D

Full Access

Answer:

Explanation:

Option D is the correct SQL statement that satisfies all the requirements mentioned. The CASE statement correctly compares the hire_date to date intervals subtracted from the current date (SYSDATE) to determine the number of years of service. This CASE statement is also appropriately ordered to ensure that the first condition matched is the one returned, preventing overlapping of the conditions.

Here is how Option D works according to the requirements:

It selects the last_name and hire_date from the employees table.
The CASE statement is used to calculate the number of years of service and to display the appropriate message according to the intervals defined.
The ORDER BY hire_date clause ensures the results are sorted by the date of hire.

In Option D, the intervals are defined in the correct order, ensuring that the first true condition is the one that is used:

The first WHEN checks if the hire date is 15 or more years ago.
The second WHEN checks if it is 10 or more years ago.
The third WHEN checks if it is 5 or more years ago.
The ELSE clause covers any hire dates less than 5 years ago.

The correct syntax for the intervals is SYSDATE - hire_date >= TO_YMINTERVAL('15-0') and similarly for the 10 and 5 years intervals.

Options A, B, and C are incorrect because they have various issues, such as incorrect ordering of the CASE statement's conditions, which could lead to incorrect results due to overlapping intervals, or the use of the TO_YMINTERVAL function that may not properly cover the intended date ranges.

References:

Oracle Documentation on CASE Expressions: SQL Language Reference - CASE Expression
Oracle Documentation on TO_YMINTERVAL Function: SQL Language Reference - TO_YMINTERVAL
Oracle Documentation on ORDER BY Clause: SQL Language Reference - ORDER BY Clause

Therefore, Option D is the statement that fulfills all the requirements for displaying the number of years of service based on the employee's hire date and ordered by the hire date.

Question # 54

Examine the description of the PRODUCTS table:

Which two statements execute without errors?

MERGE INTO new_prices n

USING (SELECT * FROM products) p

WHEN MATCHED THEN

UPDATE SET n.price= p.cost* 01

WHEN NOT MATCHED THEN

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

WHERE(p.cost<200);

MERGE INTO new_prices n

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

ON (n.prod_id= p.prod_id)

WHEN MATCHED THEN

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

DELETE WHERE (p.cost<200);

MERGE INTO new_prices n

USING products p

ON (p.prod_id =n.prod_id)

WHEN NOT MATCHED THEN

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

WHERE (p.cost<200);

MERGE INTO new_prices n

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

ON (n.prod_id= p.prod_id)

WHEN MATCHED THEN

DELETE WHERE (p.cost<200)

Full Access

Question # 55

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?

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

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

an error

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

Full Access

Question # 56

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?

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

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

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

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

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

Full Access

Question # 57

Which statement will return the last sequence number generated by the EMP_ SEQ sequence?

SELECT NEXTVAL FROM emp_ seq;

SELECT CURRVAL FROM emp_ seq;

SELECT emp_ seq. CURRVAL FROM DUAL;

SELECT emp_ seq . NEXTVAL FROM DUAL;

Full Access

Question # 58

Examine the description of the PROMOTIONS TABLE:

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

Which two queries can be used?

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

SELECT promo_cost, promo_category FROM promotions ORDER BY 1

SELECT promo_category, DISTINCT promo_cost FROM promotiong ORDER BY 2:

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

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

Full Access

Question # 59

Examine the description of the CUSTOMERS table:

Which two SELECT statements will return these results:

CUSTOMER_ NAME

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

Mandy

Mary

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

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

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

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

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

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

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

Full Access

Question # 60

Which two statements execute successfully?

SELECT TO_ DATE('2019-DEC-25 15:30'， 'YYYY-MON-DD HH24:MI', 'NLS_ DATE_ LANGUAGE

=AMERICAN' ) FROM DUAL;

SELECT TO_CHAR('2019-DEC-25 15:30"， YY-MON-D HH24:M2', 'NLS_DATE LANGUAGE =

AMERICAN')

FROM DUAL;

SELECT TO _DATE (TO_ CHAR (‘2019-DEC-25 03:30’, ‘YYYY-MON-DD HH12:MI’))

FROM DUAL;

SELECT TO _ CHAR (TO_ DATE (‘2019-DEC-25 03:30’,’YYYY-MON-DD HH12:MI’))

FROM DUAL

SELECT TO _ CHAR (‘2019-DEC-25 15:30’.’YYYY-MON-DD HH24:MI’)

FROM DUAL

Full Access

Question # 61

Examine this statement:

SELECT 1 AS id, ' John' AS first name

FROM DUAL

UNION

SELECT 1 , ' John' AS name

FROM DUAL

ORDER BY 1;

What is returned upon execution?

0 rows

an error

1 row

2 rows

Full Access

Question # 62

Examine these statements:

CREATE TABLE alter_test (c1 VARCHAR2(10), c2 NUMBER(10));

INSERT INTO alter_test VALUES ('123'， 123);

COMMIT;

Which is true ahout modifyIng the columns in AITER_TEST?

c1 can be changed to NUMBER(10) and c2 can be changed to VARCHAN2 (10).

c2 can be changed to NUMBER(5) but c1 cannot be changed to VARCHAN2 (5).

c2 can be changed to VARCHAR2(10) but c1 cannot be changed to NUMBER (10).

c1 can be changed to NUMBER(10) but c2 cannot be changed to VARCHAN2 (10).

c1 can be changed to VARCHAR2(5) and c2 can be changed to NUMBER (12,2).

Full Access

Question # 63

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?

SELECT ”

FROM employees

WHERE salary > ANY

SELECT AVG (salary)

EROM employees

GROUP BY department_ id);

SELECT

FROM employees

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

SELECT”

FROM employees e1

WHERE salary >!

SELECT AVG (salary)

FROM employees e2

WHERE e1. Department _id = e2, department_ id

SELECT.

FROM

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

FROM employees e

WHERE salary > avg_ sal;

SELECT”

FROM employees

WHERE salary >

( SELECT AVG

(salary) FROM

employees

GROUP BY department _ id

Full Access

Question # 64

Which three are true about privileges and roles?

A role is owned by the user who created it.

System privileges always set privileges for an entire database.

All roles are owned by the SYS schema.

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

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

PUBLIC can be revoked from a user.

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

Full Access

Question # 65

Examine the description of the CUSTOMERS table:

Which three statements will do an implicit conversion?

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

SELECT * FROM customers WHERE customer_id=’0001’;

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

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

SELECT * FROM customers WHERE customer_id=0001;

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

Full Access

Question # 66

Examine the description of the sales table.

The sales table has 55,000 rows.

Examine this statements:

Which two statements are true?

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

SALES1 created with 55, 000 rows

SALES1 created with no rows.

SALES1 created with 1 row.

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

Full Access

Question # 67

You execute this query:

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

What is the result?

It executes successfully but does not return any result.

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

It generates an error.

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

Full Access

Question # 68

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

2 rows

0 rows

An error

1 ROW

Full Access

Question # 69

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?

Execute the SET VERIFY ON command before executing the query.

Execute the SET VERIFY OFF command before executing the query.

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

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

Use the UNDEFINE command before executing the query.

Use the DEFINE command before executing the query

Full Access

Question # 70

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

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

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

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

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

A session can always see uncommitted updates made by itself.

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

Full Access

Question # 71

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.

ORDER BY“CUST NO"

ORDER BY 2, cust_id

ORDERBY2, 1

ORDER BY "Last Name"

ORDER BY CUSTNO

Full Access

Question # 72

Which statement falls to execute successfully?

SELECT *

FROM employees e

JOIN department d

WHERE e.department_id=d.department_id

AND d.department_id=90;

SELECT *

FROM employees e

JOIN departments d

ON e.department_id=d.department_id

WHERE d.department_id=90;

SELECT *

FROM employees e

JOIN departments d

ON e.department_id=d.department_id

AND d.department_id=90;

SELECT *

FROM employees e

JOIN departments d

ON d.departments_id=90

WHERE e.department_id=d.department_id;

Full Access

Question # 73

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?

WHERE TO_NUMBER(start_date - SYSDATE)<=25

WHERE ADD_MONTHS (start date , 25)<= SYSDATE

WHERE MONTHS_BETWEEN(SYSDATE, start_date)<=25

WHERE MONTHS_BETWEEN (start_date, SYSDATE)<=25

Full Access

Question # 74

Which two are true about multiple table INSERT statements?

They always use subqueries.

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

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

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

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

Full Access

Question # 75

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

The first expression in NVL2 is never returned.

NVL2 can have any number of expressions in the list.

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

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

NVL must have expressions of the same data type.

NVL can have any number of expressions in the list.

Full Access

Question # 76

In the PROMOTIONS table, the PROMO_ BEGIN_DATE column is of data type and the default date format is DD-MON-RR

Which two statements are true about expressions using PROMO_ BEGIN_DATE in a query?

TONUMBER (PROMO BEGIN_DATE) - 5 will return a number

PROMO_ BEGIN_DATE - 5 will return a date

PROMO_ BEGIN_DATE - SYSDATE will return a number

PROMO_ BEGIN_DATE - SYSDATE will return an error

TODATE(PROMO BEGIN_DATE *5) will return a date

Full Access

Question # 77

Which two are true about transactions in the Oracle Database?

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

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

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

DML statements always start new transactions.

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

Full Access

Question # 78

Which two are true about the USING clause when joining tables?

All column names in a USING clause must be qualified with a table name or table alias.

It can never be used with onatural join.

It is used to specify an equijoin of columns that have the same name in both tables.

It can never be used with a full outer join.

It is used to specify an explicit join condition involving operators.

Full Access

Question # 79

Examine the data in the EMPLOYEES table:

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

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

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

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

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

Full Access

Question # 80

Examine these statements executed in a single Oracle session:

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

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

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

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

SAVEPOINT a;

UPDATE product SET pcode=10 WHERE pcode =1;

COMMIT;

DELETE FROM product WHERE pcode =2;

SAVEPOINT b;

UPDATE product SET pcode=30 WHERE pcode =3;

SAVEPOINT c;

DELETE FROM product WHERE pcode =10;

ROLLBACK TO SAVEPOINT b;

COMMIT;

Which three statements are true？

The code for pen is 10.

There is no row containing fountain pen.

There is no row containing pencil.

The code for pen is 1.

The code for fountain pen is 3

There is no row containing pen

Full Access

Question # 81

Which statement is true about the INTERSECT operator used in compound queries?

It processes NULLS in the selected columns.

INTERSECT is of lower precedence than UNION or UNION ALL.

It ignores NULLS.

Multiple INTERSECT operators are not possible in the same SQL statement.

Full Access

Question # 82

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

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

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

SELECT product id, quantity on hand, supplier id

FROM product information

NATURAL JOIN inventories AND quantity .on hand < 5;

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

FROM product_information pi JOIN inventories i

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

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

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

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

FROM product information pi JOIN inventories i

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

Full Access

Question # 83

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

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

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

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

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

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

Full Access

Question # 84

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

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

It best supports relational databases

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

It requires that data be contained in hierarchical data storage

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

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

Full Access

Question # 85

Examine this statement,which executes successfully:

In which order are the rows displayed?

sorted by DEPARTMENT_NAME

sorted by DEPARTMENT_NAME and AVGSAL

sorted by DEPARTMENT_NAME and MAXSAL

sorted by AVGSAL

Sorted by MAXSAL

Full Access

Question # 86

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

ALTER

REFERENCES

UPDATE

SELECT

INDEX

INSERT

DELETE

Full Access

Question # 87

Examine the description products table:

Examine the description of the new_projects table;

Which two queries execute successfully?

Option A

Option B

Option C

Option D

Option E

Full Access

Answer:

A, D

Explanation:

To determine which queries will execute successfully, we need to consider the compatibility of the column definitions and the structure of the SELECT statements:

Option A uses the MINUS set operator, which subtracts rows returned by the second SELECT statement from the rows returned by the first. For MINUS to work, the number and the order of columns and their data types must be the same in both queries. This query will not execute successfully because the second SELECT statement does not include all columns from the first SELECT statement, and the data types and sizes of PROD_ID do not match (CHAR(2) vs CHAR(4)).
Option B uses the UNION ALL set operator, which appends the results of the second SELECT statement to the results of the first. Unlike UNION, UNION ALL does not eliminate duplicate rows. This query will execute successfully because UNION ALL does not require the same data types or sizes, and the result will contain all columns from the first SELECT statement filled with NULL for non-matching columns from the second SELECT statement.
Option C uses the UNION set operator, which requires the same number of columns and compatible data types. This query will not execute successfully because PROD_NAME has different data types (CHAR(4) vs VARCHAR2(10)), and the result of a UNION must have the same number of columns with compatible data types in the two SELECT statements.
Option D uses the UNION set operator as well, but unlike Option C, it does not require a specific data type match because both SELECT statements include all columns and UNION is used (which will automatically handle type conversion where necessary). This query will execute successfully.
Option E uses the INTERSECT set operator, which requires the same number and order of columns and their data types to be identical or compatible. This query will not execute successfully because the data types and sizes of PROD_ID do not match (CHAR(2) vs CHAR(4)).

References:

Oracle Documentation on Set Operators: SQL Language Reference - Set Operators
Oracle Documentation on Data Type Precedence: SQL Language Reference - Data Type Precedence

In conclusion, only Option B and Option D will execute successfully because they adhere to the rules of the UNION ALL and UNION operators respectively, regarding column count and data type compatibility.

Question # 88

Examine the description of the SALES1 table:

SALES2 is a table with the same description as SALES1,

Some sales data is duplicated In both tables.

You want to display the rows from the SALES1 table which are not present in the SALIES2 table.

Which set operator generates the required output?

SUBTRACT

INTERSECT

UNION ALL

MINUS

UNION

Full Access

Question # 89

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

SELECT last_name, salary

FROM employees

WHERE ROWNUM<=3

SELECT last_name,salary

FROM employees

ORDER BY salary

FETCH FIRST 3 ROWS ONLY;

SELECT last_name,salary

FROM employees

WHERE ROWNUM<=3

ORDER BY (SELECT salary FROM employees);

SELECT last_name,salary

FROM (SELECT * FROM employees ORDER BY salary)

SELECT last_name,salary

FROM employees

FETCH FIRST 3 ROWS ONLY

ORDER BY salary;

Full Access

Question # 90

Examine this business rule:

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

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

STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK Which two statements are true?

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

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

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

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

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

Full Access

Question # 91

Which three statements about roles are true?

Roles are assigned to roles using the ALTER ROLE statement.

A single user can be assigned multiple roles.

Roles are assigned to users using the ALTER USER statement.

A single role can be assigned to multiple users.

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

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

Privileges are assigned to a role using the GRANT statement.

Full Access

Question # 92

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?

SELECT promo_cost, | pxomo_category FROM promotions ORDER BY 1;

SELECT promo_category, DISTINCT promo_cost PROM promotions ORDER BY 2:

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

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

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

Full Access

Question # 93

The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.

The table has two rows whose COST_LAST_MANE values are Anderson and Ausson.

Which query produces output for CUST_LAST_SAME containing Oder for the first row and Aus for the second?

SELECT REPLACE (REPLACE(cust_last_name,'son',''),'An','O') FROM customers;

SELECT REPLACE (TRIM(TRALING'son' FROM cust_last_name),'An','O') FROM customers;

SELECT INITCAP (REPLACE(TRIM('son' FROM cust_last_name),'An','O')) FROM customers;

SELECT REPLACE (SUBSTR(cust_last_name,-3),'An','O') FROM customers;

Full Access

Question # 94

Which two are true about the precedence of opertors and condtions

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

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

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

Operators are evaluated before conditions.

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

Full Access

Question # 95

Which two statements are true about date/time functions in a session where NLS_DATE_PORMAT is set to DD-MON-YYYY SH24:MI:SS

SYSDATE can be used in expressions only if the default date format is DD-MON-RR.

CURRENT_TIMESTAMP returns the same date as CURRENT_DATE.

CURRENT_DATE returns the current date and time as per the session time zone

SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server.

CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of functional seconds.

SYSDATE can be queried only from the DUAL table.

Full Access

Question # 96

Which two statements are true?

AIl conditions evaluated using DECODE can also be evaluated using CASE.

All conditions evaluated using CASE can also be evaluated using DECODE.

CASE is a function and DECODE is not.

DECODE is a function and CASE is not.

Neither CASE nor DECODE is a function.

Both CASE and DECODE are functions.

Full Access

Question # 97

Examine the BRICKS table:

You write this query:

SELECT

FROM bricks b1 CROSS JOIN bricks b2

WHERE b1. Weight < b2. Weight:

How many rows will the query return?

Full Access

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

Exact2Pass Menu

Exact2Pass

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer:

Explanation:

Answer: