OCP071

Examine the data in the INVOICES table:

INVOICE_ID  CURRENCY_CODE  RAISED_DATE

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

1  EUR  01-JAN-2019

2  USD  01-FEB-2019

3  JPY  01-MAR-2019

Examine the data in the CURRENCIES table:

CURRENCY_CODE

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

JPY

GPB

CAD

EUR

USD

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

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:

DBTIMEZONE  SYSDATE

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

+00.00  11-JUL-2019 11:00:00

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

Examine this query:

SELECT SUBSTR (SYSDATE,1,5) ‘Result’ FROM DUAL

Which statement is true?

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?

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?

which is true about the round,truncate and mod functions>?

Examine this query:

SELECT INTERVAL '100' MONTH DURATION FROM DUAL;

What will be the output?

Which statement will return a comma-separated list of employee names in alphabetical order for each

department in the EMP table?

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?

You have the privileges to create any type of synonym.

Which stalement will create a synonym called EMP for the HCM.EMPLOYEE_RECORDS table that is

accesible to all users?

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:

CHANNEL TYPE  MONTH  co  SALES

——————————————————————————————————

internet           2000-09  GB  16569

internet            2000-09  US  124224

internet          2000-09

140793

internet        2000-10  GB  14539

internet          2000-10  US  137054

internet

292387

Direct Sales     2000-09  GB  85223

Direct Sales     2000-09  US  638201

Direct Sales     2000-09  723424

Direct Sales     2000-10  GB  91925

Direct Sales     2000-10  US  638201

Direct Sales     2000-09  774222

Direct Sales 

1497646

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

Examine the description of the EMPLOYEES table:

Name  Null?  Type

EMPLOYEE ID  NOT NULL  NUMBER(4)

EMPLOYEE NAME  NOT NULL  VARCHAR2(100)

SALARY  NOT NULL  NUMBER(6,2)

DEPARTMENT ID  NOT NULL  NUMBER(4)

Which statement will fail?

Examine these two queries and their output:

SELECT deptno, dname FROM dept;

Deptno  Dname

10  account ing

20  research

30  sales

40  operations

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

Ename  job  deptno

CLARK  MANAGER  10

KING  PRESIDENT  10

MILLER  CLERK  10

JONES  MANAGER  20

Now examine this query:

SELECT ename, dname

FROM emp CROSS JOIN dept WHERE job = 'MANAGER'

AND dept.deptno IN (10, 20) ;

You need to allow user ANDREW to:

1. Modify the TITLE and ADDRESS columns of your CUSTOMERS table.

2. GRANT tha permission to other users.

Which statement will do this?

Examine the description of the EMPLOYEES table:

Name                      Null?                                     Type

EMPLOYEE ID       NOT NULL                         NUMBER(38)

DEPARTMENT ID        NOT NULL                  NUMBER(38)

SALARY                       NOT NULL                    NUMBER (38)

Which statement increases each employee's SALARY by the minimum SALARY for their DEPARTM

ENT_ID?

Examine the data in the EMP table:

ENO  ENAME  SAL  DEPTNO

1001  John  12000  10

1002  Sam  40000  20

1003  Daniel  12000  20

1004  Andrea  5000  10

You execute this query:

SELECT deptno AS "Department", AVG(sal) AS AverageSalary, MAX(sal) AS "Max Salary"

FROM emp

WHERE sal >= 12000

GROUP BY "Department "

ORDER BY AverageSalary;

Why does an error occur?

Name  Null?  Type

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

EMP_NO  NOT NULL  NUMBER(4)

LAST_NAME  VARCHAR2(100)

HIRE_DATE  DATE

SALARY  NUMBER(8,2)

For each employee in department 90 you want to display:

1. their last name

2. the number of complete weeks they have been employed

The output must be sorted by the number of weeks, starting with the longest serving employee

first.Which statement will accomplish this?

Which statement will execute successfully?

Examine the data in the NEW_EMPLOYEES table:

EMPLOYEE_ID NAME DEPARTMENT_ID  MANAGER_ID  JOB_ID  SALARY

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

101  David  20  120  SA_REP  14000

102  Sam  10  105  CIERK  12500

103  Andrew  20  120  FIN_ADMIN 14200

104  adrian  30  108  MAR_CLERK 12500

105  Maria  30  108  FIN_ADMIN 15000

106  Tracy  40  110  AD_ASST  13000

108  Kate  30  110  FIN_DIR  16500

110  Anne  40  120  EX_DIR  18000

120  Fran  20  110  SQ_DIR  16500

Examine the data in the EMPLOYEES table:

EMPLOYEE_ID  NAME  JOB_ID  SALARY

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

101  David  CLERK  14000

102  Sam  SA_REP  11500

104  Adrian  MAR_CLERK  12500

108  Kate  FIN_DIR  16500

110  Annie  EX_DIR  18000

You want to: 

1. Update existing employee details in the EMPLOYEES table with data from the NEW EMPLOYEES

table.

2. Add new employee detail from the NEW_ EMPLOYEES able to the EMPLOYEES table.

Which statement will do this:

Examine the BRICKS table:

COLOUR SHAPE WEIGHT

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

Red cube 5

Red cylinder 10

Blue cube 15

Blue cylinder 20

You write this query:

SELECT *

FROM bricks b1 CROSS JOIN bricks b2

WHERE b1.weight < b2. weight;

How many rows will the query return?