OCA Oracle Database 11g Administrator Certified Associate Set 1

The sales order number (ORDER_NO) is the primary key in the table SALES_ORDERS. Which query will return the total number of orders in the SALES_ORDERS table?


Options are :

  • SELECT COUNT(order_no) FROM sales_orders;
  • SELECT COUNT(NVL(order_no,0) FROM sales_orders;
  • SELECT COUNT(DISTINCT order_no) FROM sales_orders;
  • All of the above (Correct)
  • SELECT COUNT(ALL order_no) FROM sales_orders;

Answer : All of the above

OCA Oracle Database 11g Administrator Certified Associate Set 2

Which clauses in the SELECT statement can use single-row functions nested in aggregate functions?


Options are :

  • GROUP BY
  • SET
  • WHERE
  • SELECT (Correct)

Answer : SELECT

Review the following two SQL statements, and choose the appropriate option.

FROM employees
HAVING COUNT(*) > 10
GROUP BY department_id;
2. SELECT department_id, COUNT(*)
FROM employees
WHERE COUNT(*) > 10
GROUP BY department_id;



Options are :

  • Statement 1 will succeed, and statement 2 will fail. (Correct)
  • Statement 1 and statement 2 will produce the same results.
  • Statement 2 will succeed, and statement 1 will fail.
  • Both statements fail.

Answer : Statement 1 will succeed, and statement 2 will fail.

Review the SQL code, and choose the line number that has an error.

1 SELECT DISTINCT department_id
2 FROM employees
3 ORDER BY department_id
4 UNION ALL
5 SELECT department_id
6 FROM departments
7 ORDER BY department_id



Options are :

  • 1
  • 3 (Correct)
  • No error
  • 6
  • 7

Answer : 3

OCA Oracle Database 11g Administrator Certified Associate Set 3

Which clause will generate an error when the following query is executed?

SELECT department_id, AVG(salary) avg_sal
FROM employees
GROUP BY department_id
HAVING TRUNC(department_id) > 50;



Options are :

  • None of the above. The SQL statement will not return an error. (Correct)
  • The GROUP BY clause, because it is missing the group function.
  • None of the Above
  • The HAVING clause, because the AVG function used in the SELECT clause is not used in the HAVING clause.
  • The HAVING clause, because single-row functions cannot be used.

Answer : None of the above. The SQL statement will not return an error.

Read the following two statements, and choose the best option.

1. HAVING clause should always appear after the GROUP BY clause.
2. GROUP BY clause should always appear after the WHERE clause.


Options are :

  • Statement 1 is true, and statement 2 is false.
  • Statement 1 and 2 are false.
  • Statement 1 is false, and statement 2 is true. (Correct)
  • Statements 1 and 2 are true.

Answer : Statement 1 is false, and statement 2 is true.

Which assertion about the following queries is true?

SELECT COUNT(DISTINCT mgr), MAX(DISTINCT salary)
FROM emp;
SELECT COUNT(ALL mgr), MAX(ALL salary)
FROM emp;


Options are :

  • They may return different numbers in both columns 1 and 2.
  • They will always return the same number in column 1 but may return different numbers in column 2.
  • They will always return the same numbers in columns 1 and 2.
  • They may return different numbers in column 1 but will always return the same number in column 2. (Correct)

Answer : They may return different numbers in column 1 but will always return the same number in column 2.

OCA Oracle Database 11g Administrator Certified Associate Set 4

onsider the following SQL, and choose the most appropriate option.
SELECT COUNT(DISTINCT SUBSTR(first_name, 1,1))
FROM employees;


Options are :

  • The query will execute successfully without any modification. (Correct)
  • Removing the DISTINCT qualifier will fix the error in the query.
  • The GROUP BY clause is required to successfully run this query.
  • A single-row function nested inside a group function is not allowed.

Answer : The query will execute successfully without any modification.

How will the results of the following two statements differ? Statement 1: SELECT MAX(longitude), MAX(latitude) FROM zip_state_city; Statement 2: SELECT MAX(longitude), MAX(latitude) FROM zip_state_city GROUP BY state;


Options are :

  • Statement 1 will fail because it is missing a GROUP BY clause.
  • Statement 1 will display two columns, and statement 2 will display two values for each state. (Correct)
  • Statement 2 will fail because it does not have the columns used in the GROUP BY clause in the SELECT clause.
  • Statement 2 will return one row, and statement 1 may return more than one row.

Answer : Statement 1 will display two columns, and statement 2 will display two values for each state.

Read the following SQL carefully, and choose the appropriate option. The JOB_ID column shows the various jobs.

SELECT MAX(COUNT(*))
FROM employees
GROUP BY job_id, department_id;


Options are :

  • Aggregate functions cannot be nested.
  • The columns in the GROUP BY clause must appear in the SELECT clause for the query to work.
  • The GROUP BY clause is not required in this query.
  • None
  • The SQL will produce the highest number of jobs within a department. (Correct)

Answer : The SQL will produce the highest number of jobs within a department.

OCP Oracle 12C Advanced Administration(1Z0-063) Set 1

Admin wants to find the highest salary within each department of the EMPLOYEES table. Which query will help him get what he wants?


Options are :

  • SELECT MAX(salary) FROM employees;
  • SELECT MAX(salary BY department_id) FROM employees;
  • SELECT department_id, MAX(salary) max_sal FROM employees;
  • SELECT department_id, MAX(salary) FROM employees USING department_id;
  • SELECT department_id, MAX(salary) FROM employees GROUP BY department_id; (Correct)

Answer : SELECT department_id, MAX(salary) FROM employees GROUP BY department_id;

Which of the following is a correlated subquery?


Options are :

  • select cty_name from city, state where city.st_code = state.st_code and city.cnt_code = state.cnt_code and st_name = 'TENNESSEE';
  • select cty_name from city, state where city.st_code = state.st_code (+) and city.cnt_code = state.cnt_code (+) and st_name = 'TENNESSEE';
  • select cty_name from city where st_code in (select st_code from state where st_name = 'TENNESSEE');
  • select cty_name from city where st_code in (select st_code from state where st_name = 'TENNESSEE' and city.cnt_code = state.cnt_code); (Correct)

Answer : select cty_name from city where st_code in (select st_code from state where st_name = 'TENNESSEE' and city.cnt_code = state.cnt_code);

What will the following SQL statement return?

select max(prod_pack_size)
from sh.products
where min(prod_weight_class) = 5;  


Options are :

  • An exception will be raised. (Correct)
  • The largest PROD_PACK_SIZE for rows containing PROD_WEIGHT_CLASS of 5 or higher
  • The largest PROD_PACK_SIZE in the SH.PRODUCTS table
  • The largest PROD_PACK_SIZE for rows containing PROD_WEIGHT_CLASS of 5

Answer : An exception will be raised.

OCP Oracle 12C Advanced Administration(1Z0-063) Set 2

Admin is trying to find out the average salary of employees in each department. He noticed that the SALARY column can have NULL values, and he does not want the NULLs included when calculating the average. Identify the correct SQL that will produce the desired results.


Options are :

  • SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING salary IS NOT NULL;
  • SELECT department_id, NVL(AVG(salary), 0) FROM employees GROUP BY department_id;
  • SELECT department_id, AVG(salary) FROM employees GROUP BY department_id; (Correct)
  • SELECT department_id, AVG(NVL(salary,0)) FROM employees GROUP BY department_id;

Answer : SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

How will the results of the following two SQL statements differ?

Statement 1:
SELECT COUNT(*), SUM(salary)
FROM hr.employees;

Statement 2:
SELECT COUNT(salary), SUM(salary)
FROM hr.employees;


Options are :

  • Statement 2 may return a smaller COUNT value than statement 1. (Correct)
  • Statement 1 will return one row, and statement 2 may return more than one row.
  • Both statements will fail because they are missing a GROUP BY clause.
  • Both statements will return the same results.

Answer : Statement 2 may return a smaller COUNT value than statement 1.

Why does the following SELECT statement fail?

SELECT colorname Colour, MAX(cost)
FROM itemdetail
WHERE UPPER(colorname) LIKE '%WHITE%'
GROUP BY colour
HAVING COUNT(*) > 20;



Options are :

  • The condition COUNT(*) > 20 should be in the WHERE clause.
  • The GROUP BY clause must contain the group functions used in the SELECT list.
  • The HAVING clause can contain only the group functions used in the SELECT list.
  • A GROUP BY clause cannot contain a column alias. (Correct)

Answer : A GROUP BY clause cannot contain a column alias.

OCP Oracle 12C Advanced Administration(1Z0-063) Set 3

Which line in the following query contains an error?

1 SELECT deptno, ename, sal
2 FROM emp e1
3 WHERE sal = (SELECT MAX(sal) FROM emp
4 WHERE deptno = e1.deptno
5 ORDER BY deptno);



Options are :

  • Line 2
  • Line 4
  • Line 5 (Correct)
  • Line 3

Answer : Line 5

Why will the following query raise an exception?

select dept_no, avg(distinct salary),
count(job) job_count
from emp
where mgr like 'J%'
or abs(salary) > 10
having count(job) > 5
order by 2 desc;



Options are :

  • The HAVING clause cannot contain a group function.
  • ABS() is not an Oracle function.
  • The GROUP BY clause is missing. (Correct)
  • The query will not raise an exception.

Answer : The GROUP BY clause is missing.

Which statements are true? 


Options are :

  • A group function can be used only if the GROUP BY clause is present
  • Group functions along with nonaggregated columns can appear in the SELECT clause as long as a GROUP BY clause and a HAVING clause are present.
  • The HAVING clause is optional when the GROUP BY clause is used. (Correct)
  • The HAVING clause and the GROUP BY clause are mutually exclusive; you can use only one clause in a SELECT statement.

Answer : The HAVING clause is optional when the GROUP BY clause is used.

OCP Oracle 12C Advanced Administration(1Z0-063) Set 4

Consider the following two SQL statements. Choose the most appropriate option.

1. select substr(first_name, 1,1) fn, SUM(salary) FROM employees GROUP BY
first_name;
2. select substr(first_name, 1,1) fn, SUM(salary) FROM employees GROUP BY
substr(first_name, 1,1);



Options are :

  • Statement 1 and 2 will produce the same result.
  • Statement 1 will fail
  • Statement 2 will fail, but statement 1 will succeed.
  • Statement 1 and 2 will produce different results. (Correct)

Answer : Statement 1 and 2 will produce different results.

Which line in the following SQL has an error?

1 SELECT department_id, SUM(salary)
2 FROM employees
3 WHERE department_id <> 40
4 ORDER BY department_id;



Options are :

  • 4 (Correct)
  • 3
  • 1
  • No errors in SQL

Answer : 4

What will be the result of the following query?

SELECT c.cust_id, c.cust_name, o.ord_date, o.prod_id
FROM customers c, orders o
WHERE c.cust_id = o.cust_id (+);



Options are :

  • For each record in the CUSTOMERS table, list the information from the ORDERS table.
  • List all the customer names in the CUSTOMERS table and the orders they made from the ORDERS table, even if the customer has not placed an order. (Correct)
  • List only the names of customers from the CUSTOMERS table who have placed an order in the ORDERS table.
  • List all orders from the ORDERS table, even if there is no valid customer record in the CUSTOMERS table.

Answer : List all the customer names in the CUSTOMERS table and the orders they made from the ORDERS table, even if the customer has not placed an order.

OCP Oracle Database 11g Administrator Certified Professional Set 1

Which line of code has an error?


Options are :

  • ORDER BY 1, 2;
  • FROM emp e, dept d
  • SELECT dname, ename
  • WHERE emp.deptno = dept.deptno (Correct)

Answer : WHERE emp.deptno = dept.deptno

Consider the following query:

SELECT deptno, ename, salary salary, average,
salary-average difference
FROM emp,
(SELECT deptno dno, AVG(salary) average FROM emp
GROUP BY deptno)
WHERE deptno = dno
ORDER BY 1, 2;

Which of the following statements is correct?


Options are :

  • GROUP BY cannot be used inside a subquery.
  • The query will fail because no alias name is provided for the subquery.
  • The query will fail because a column selected in the subquery is referenced outside the scope of the subquery.
  • The query will work without errors. (Correct)

Answer : The query will work without errors.

The columns of the EMPLOYEES and DEPARTMENTS tables are shown in question 8. Which query will show you the top five highest-paid employees in the company?


Options are :

  • SELECT last_name, salary FROM employees WHERE ROWNUM <= 5 ORDER BY salary DESC;
  • SELECT * FROM (SELECT last_name, salary FROM employees ORDER BY salary) WHERE ROWNUM <= 5;
  • SELECT last_name, salary FROM (SELECT * FROM employees WHERE ROWNUM <= 5 ORDER BY salary DESC ) WHERE ROWNUM <= 5;
  • SELECT * FROM (SELECT last_name, salary FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 5; (Correct)

Answer : SELECT * FROM (SELECT last_name, salary FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 5;

OCP Oracle Database 11g Administrator Certified Professional Set 2

Which two statements regarding substitution variables are true?


Options are :

  • &&variable is defined by SQL*Plus, and its value will be available for the duration of the session. (Correct)
  • &&variable is defined by SQL*Plus, and its value will be available only for every reference to that variable in the current SQL.
  • &variable is defined by SQL*Plus, and its value will be available for the duration of the session.

Answer : &&variable is defined by SQL*Plus, and its value will be available for the duration of the session.

OCP Oracle Database 11g Administrator Certified Professional Set 3

The EMP table has the following data:

Consider the following two SQL statements:

1. SELECT empno, ename, sal, comm


FROM emp WHERE comm IN (0, NULL);


2. SELECT empno, ename, sal, comm


FROM emp WHERE comm = 0 OR comm IS NULL;


Options are :

  • 1 and 2 will work but will not return any rows.
  • 1 and 2 will produce the same result.
  • 1 will error; 2 will work fine
  • 1 and 2 will produce different results. (Correct)

Answer : 1 and 2 will produce different results.

Which function(s) accept arguments of any datatype? (Choose all that apply.)


Options are :

  • NVL (Correct)
  • SIGN
  • SUBSTR
  • SIGN
  • ROUND

Answer : NVL

When you try to save 34567.2255 into a column defined as NUMBER(7,2), what value is actually saved?


Options are :

  • 34567.00
  • 34567.22
  • 34567.23 (Correct)
  • 3456.22

Answer : 34567.23

OCP Oracle Database 11g Administrator Certified Professional Set 4

Which assertion about the following statements is most true?

SELECT name, region_code||phone_number
FROM customers;
SELECT name, CONCAT(region_code,phone_number)
FROM customers;



Options are :

  • Both statements will return the same data. (Correct)
  • If REGION_CODE is NULL, the second statement will not include that customer's PHONE_ NUMBER.
  • If REGION_CODE is NULL, the first statement will not include that customer's PHONE_ NUMBER.
  • The second statement will raise an error if REGION_CODE is NULL for any customer.

Answer : Both statements will return the same data.

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions