OCA Oracle Database 11g Administrator Certified Associate Set 2

In the following SELECT statement, which component is a literal? (Choose all that apply.)
SELECT 'Employee Name: ' || ename
FROM emp where deptno = 10;


Options are :

  • 10
  • ||
  • ename
  • Employee

Answer : 10

Which function will return a TIMESTAMP WITH TIME ZONE datatype?


Options are :

  • SYSDATE
  • CURRENT_DATE
  • LOCALTIMESTAMP
  • CURRENT_TIMESTAMP

Answer : CURRENT_TIMESTAMP

1Z0-517 Oracle EBS R12.1 Payables Essentials Practice Exam Set 9

You issue the following query:
SELECT salary "Employee Salary"
FROM employees;


Options are :

  • EMPLOYEE_SALARY
  • EMPLOYEE SALARY
  • employee_salary
  • Employee Salary

Answer : Employee Salary

Which single-row function could you use to return a specific portion of a character string?


Options are :

  • LEAST
  • SUBSTR
  • LPAD
  • INSTR

Answer : SUBSTR

Which statement would change all occurrences of the string 'IBM' to the string 'SUN' in the DESCRIPTION column of the VENDOR table?


Options are :

  • SELECT TRANSLATE(description, 'IBM', 'SUN') FROM vendor
  • SELECT CONVERT(description, 'IBM', 'SUN') FROM vendor
  • SELECT REPLACE(description, 'IBM', 'SUN') FROM vendor
  • SELECT EXTRACT(description, 'IBM', 'SUN') FROM vendor

Answer : SELECT REPLACE(description, 'IBM', 'SUN') FROM vendor

Oracle 1Z0-207 Billing & Revenue Mgmt Pricing Practice Exam Set 7

Which expression will always return the date one year later than the current date?


Options are :

  • SYSDATE + 365
  • SYSDATE + TO_YMINTERVAL('01-00')
  • NEW_TIME(CURRENT_DATE,1,'YEAR')
  • None of the above
  • CURRENT_DATE + 1

Answer : None of the above

What will the following SQL statement return?

SELECT COALESCE(NULL,'Oracle ','Certified') FROM dual;


Options are :

  • NULL
  • Certified
  • Oracle Certified
  • Oracle

Answer : Oracle

Using the SALESPERSON_REVENUE table described here, which statements will properly display the TOTAL_REVENUE (CAR_SALES + WARRANTY_SALES) of each salesperson?



Options are :

  • SELECT salesperson_id, car_sales, warranty_sales, car_sales + NVL2(warranty_sales,0) total_sales FROM salesperson_revenue;
  • SELECT salesperson_id, car_sales, warranty_sales, NVL2(warranty_sales, car_sales + warranty_sales, car_sales) total_sales FROM salesperson_revenue;
  • SELECT salesperson_id, car_sales, warranty_sales, car_sales + warranty_ sales total_sales FROM salesperson_revenue;
  • SELECT salesperson_id, car_sales, warranty_sales, car_sales + COALESCE(car_sales, warranty_sales, car_sales + warranty_sales) total_sales FROM salesperson_revenue;

Answer : SELECT salesperson_id, car_sales, warranty_sales, NVL2(warranty_sales, car_sales + warranty_sales, car_sales) total_sales FROM salesperson_revenue;

1Z0-409 Oracle Linux Fundamentals Practice Exam Set 2

Consider the following two SQL statements, and choose the best option:

1. SELECT TO_DATE('30-SEP-07','DD-MM-YYYY') from dual;
2. SELECT TO_DATE('30-SEP-07','DD-MON-RRRR') from dual;



Options are :

  • Both statements will generate an error.
  • The resulting date value from the two statements will be different.
  • The resulting date value from the two statements will be the same.
  • Statement 1 will error; 2 will produce result.

Answer : The resulting date value from the two statements will be different.

Which function implements IF…THEN…ELSE logic?


Options are :

  • DECODE
  • REPLACE
  • INITCAP
  • IFELSE

Answer : DECODE

Which clause in a query limits the rows selected?


Options are :

  • SELECT
  • ORDER BY
  • WHERE
  • FROM

Answer : WHERE

1Z0-521 Oracle E-Business Suite R12.1Oracle Order Managment Set 3

Which date components does the CURRENT_TIMESTAMP function display?


Options are :

  • Session date and session time
  • Session date and session time zone offset
  • Session time zone offset
  • Session date, session time, and session time zone offset

Answer : Session date, session time, and session time zone offset

Column alias names cannot be used in which clause?


Options are :

  • WHERE clause
  • SELECT clause
  • None of the above
  • ORDER BY clause

Answer : WHERE clause

You want to display each project's start date as the day, week, number, and year. Which statement will give output like the following?
Tuesday Week 23, 2008


Options are :

  • SELECT proj_id, TO_CHAR(start_date, 'Day Week# , YYYY') FROM projects;
  • SELECT proj_id, TO_CHAR(start_date, 'Day" Week" WW, YYYY') FROM projects;
  • SELECT proj_id, TO_CHAR(start_date,'Day'||' Week'||' WOY, YYYY') FROM projects;
  • You can't calculate week numbers with Oracle.
  • SELECT proj_id, TO_CHAR(start_date, 'DOW Week WOY YYYY') FROM projects;

Answer : SELECT proj_id, TO_CHAR(start_date, 'Day" Week" WW, YYYY') FROM projects;

Oracle 12c SQL Certified Associate 1Z0-071 Real Exam 2019 Set 1

What will the following statement return?

SELECT last_name, first_name, start_date
FROM employees
WHERE hire_date < TRUNC(SYSDATE) – 5;



Options are :

  • Employees hired within the past five days
  • Employees hired more than five hours ago
  • Employees hired more than five days ago
  • Employees hired within the past five hours

Answer : Employees hired more than five days ago

What is wrong with the following statements submitted in SQL*Plus?


DEFINE V_DEPTNO = 20
SELECT LAST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = V_DeptNo;



Options are :

  • The DEFINE statement declaration is wrong.
  • Nothing is wrong. The query lists the employee name and salary of the employees who belong to department 20.
  • The substitution variable in the WHERE clause should be V_DEPTNO instead of V_DeptNo.
  • The substitution variable is not preceded with the & character.

Answer : The substitution variable is not preceded with the & character.

What is the default display length of the DATE datatype column?


Options are :

  • 19
  • 18
  • 9
  • 6

Answer : 9

1Z0-516 Oracle EBS R12 General Ledger Essentials Exam Set 8

What will be the result of executing the following SQL, if today's date is February 28, 2009? SELECT ADD_MONTHS('28-FEB-09', -12) from dual;


Options are :

  • 28-JAN-08
  • 28-FEB-08
  • 28-FEB-10
  • 29-FEB-08

Answer : 29-FEB-08

Which two SQL statements will replace the last two characters of last_name with 'XX' in the employees table when executed?


Options are :

  • SELECT RTRIM(last_name, SUBSTR(last_name, LENGTH(last_name)-1)) || 'XX' new_col FROM employees;
  • SELECT REPLACE(last_name, SUBSTR(last_name, LENGTH(last_name)-1), 'XX') new_col FROM employees;
  • SELECT REPLACE(SUBSTR(last_name, LENGTH(last_name)-1), 'XX') new_col FROM employees;

Answer : SELECT RTRIM(last_name, SUBSTR(last_name, LENGTH(last_name)-1)) || 'XX' new_col FROM employees;

When doing pattern matching using the LIKE operator, which character is used as the default escape character by Oracle?


Options are :

  • /
  • NONE
  • \
  • There is no default escape character in Oracle.
  • |

Answer : There is no default escape character in Oracle.

1Z0-520 Oracle EBS R12 Purchasing Essentials Practice Exam Set 4

What will the salary of employee Arsinoe be at the completion of the following SQL statements?

UPDATE emp
SET salary = 1000
WHERE name = 'Arsinoe';
SAVEPOINT Point_A;

UPDATE emp
SET salary = salary * 1.1
WHERE name = 'Arsinoe';
SAVEPOINT Point_B;

UPDATE emp
SET salary = salary * 1.1
WHERE name = 'Berenike';
SAVEPOINT point_C;

ROLLBACK TO SAVEPOINT point_b;
COMMIT;
UPDATE emp
SET salary = 1500
WHERE name = 'Arsinoe';
SAVEPOINT point_d;

ROLLBACK TO point_d;

COMMIT;



Options are :

  • 1100
  • 1000
  • 1500
  • 1111

Answer : 1500

1Z0-517 Oracle EBS R12.1 Payables Essentials Practice Exam Set 8

Which of the following statements do not end a transaction?


Options are :

  • COMMIT
  • SELECT
  • TRUNCATE TABLE

Answer : SELECT

Tom executed the following SQL statement.
create table xx (n number, x long, y clob);
Choose the best option.


Options are :

  • A table named xx will be created.
  • When using the LONG datatype, other LOB datatypes cannot be used in table definitions.
  • It provides a means of storing all RMAN backup sets physically in an Oracle database server.
  • Single-character column names are not allowed in table definitions.
  • One of the datatypes used in the column definition needs the size specified.

Answer : A table named xx will be created.

Which is the best option that describes the following SQL statement?

1. UPDATE countries
2. CNT_NAME = UPPER(CNT_NAME)
3. WHERE country_code BETWEEN 1 and 99;



Options are :

  • The statement is missing the keyword SET, but the statement will work just fine because SET is an optional keyword.
  • The statement is missing keyword SET; hence, the statement will fail.
  • The function UPPER(CNT_NAME) should be changed to UPPER('CNT_NAME').
  • The BETWEEN operator cannot be used in the WHERE clause used in an UPDATE statement.

Answer : The statement is missing keyword SET; hence, the statement will fail.

Oracle 12c SQL Certified Associate 1Z0-071 Real Exam 2019 Set 1

Sara wants to update the SALARY column in the OLD_EMPLOYEES table with the value from the EMPLOYEES table for employees in department 90. Which SQL will accomplish the task?


Options are :

  • UPDATE old_employees a SET salary = (SELECT salary FROM employees b WHERE a.employee_id = b.employee_id AND department_id = 90);
  • UPDATE old_employees a FROM employees b SET a.salary = b.salary WHERE department_id = 90;
  • UPDATE old_employees SET salary = (SELECT salary FROM employees) WHERE department_id = 90;
  • None
  • UPDATE old_employees a SET salary = (SELECT salary FROM employees b WHERE a.employee_id = b.employee_id) WHERE department_id = 90;

Answer : UPDATE old_employees a SET salary = (SELECT salary FROM employees b WHERE a.employee_id = b.employee_id) WHERE department_id = 90;

The table ORDERS has 35 rows. The following UPDATE statement updates all 35 rows. Which is the best option?

UPDATE orders
SET ship_date = TRUNC(ship_date)
WHERE ship_date != TRUNC(ship_date)



Options are :

  • When all rows in a table are updated, the LOCK TABLE orders IN EXCLUSIVE MODE statement must be executed before the UPDATE statement.
  • Since all rows are updated, there is no need for any locking, and hence Oracle does not lock the records.
  • The statement locks all the rows until the transaction ends.
  • No other session can query from the table until the transaction ends.

Answer : The statement locks all the rows until the transaction ends.

Review the following code snippet. Which line has an error?

1 UPDATE EMPLOYEES
2 WHERE EMPLOYEE_ID = 127
3 SET SALARY = SALARY * 1.25,
4 COMMISSION_PCT = 0


Options are :

  • There is no error
  • 4
  • 2
  • 1

Answer : 2

1Z0-516 Oracle EBS R12 General Ledger Essentials Exam Set 7

When using ANSI join syntax, which clause is used to specify a join condition?


Options are :

  • USING
  • JOIN
  • VON
  • VWHERE

Answer : VON

Which two operators are not allowed when using an outer join operator in the query? 


Options are :

  • AND
  • OR
  • =

Answer : OR

You are connected to the database as user JOHN. You need to rename a table named NORDERS to NEW_ORDERS, owned by SMITH. Consider the following two statements: 1. RENAME SMITH.NORDERS TO NEW_ORDERS;
2. ALTER TABLE SMITH.NORDERS RENAME TO NEW_ORDERS;
Which of the following is correct?


Options are :

  • Statements 1 and 2 will not work.
  • Statement 1 will not work; statement 2 will work.
  • Statements 1 and 2 will work.
  • Statement 1 will work; statement 2 will not.

Answer : Statement 1 will not work; statement 2 will work.

Oracle 1Z0-516 EBS R12 General Essential Practice Exam Set 8

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions