"A script enabled browser is required for this page to function properly"

Oracle Certification

Oracle Database SQL Expert

Sample Questions

View answers below

Sample questions are provided solely to familiarize candidates with the multiple-choice format and writing style of questions that will be found on the exam.  Sample questions may not cover the full spectrum of difficulty that is covered by the exam questions. Success on the sample questions does not predict success on the exam.

1. View the Exhibit and examine the structure of the EMP and SALGRADE tables. You want to display the names of all employees whose salaries belong to GRADE 5. Which SQL statements give the required output? (Choose all that apply)
  1. SELECT ename
    FROM emp JOIN salgrade
    USING (sal BETWEEN losal AND hisal) AND grade = 5;
  2. SELECT ename
    FROM emp e JOIN salgrade s
    ON (e.sal BETWEEN s.losal AND s.hisal AND s.grade = 5);
  3. SELECT ename
    FROM emp e JOIN salgrade s
    ON (e.sal BETWEEN s.losal AND s.hisal) AND s.grade = 5;
  4. SELECT ename
    FROM emp e JOIN salgrade s
    ON (e.sal BETWEEN s.losal AND s.hisal) WHERE s.grade=5;
  5. SELECT ename
    FROM emp e JOIN salgrade s
    WHERE e.sal BETWEEN s.losal AND s.hisal AND s.grade = 5;

    1a "Exhibit"

      EMP          
    Name
    Null?
    Type
     
         
    EMPNO
    ENAME
    JOB
    HIREDATE
    SAL
    DEPTNO
    NOT NULL NUMBER(4)
    VARCHAR2(10)
    VARCHAR2(9)
    DATE
    NUMBER(7,2)
    NUMBER(2)
    SALGRADE    
    Name
    Null?
    Type
         
    GRADE
    LOSAL
    HISAL
      NUMBER
    NUMBER
    NUMBER
2. View the Exhibit and examine the structure of the DEPARTMENTS and LOCATIONS tables. You want to display all the cities and the corresponding departments in them, if any. Which query would give you the required output?
  1. SELECT location_id LOC, city, department_id DEPT
    FROM locations LEFT OUTER JOIN departments
    USING (location_id);
  2. SELECT location_id LOC, city, department_id DEPT
    FROM locations RIGHT OUTER JOIN departments
    USING (location_id);
  3. SELECT l.location_id LOC, l.city, d.department_id DEPT
    FROM locations l LEFT OUTER JOIN departments d
    USING (location_id);
  4. SELECT l.location_id LOC, l.city, d.department_id DEPT
    FROM locations l FULL OUTER JOIN departments d
    USING (location_id);

    2a "Exhibit"

      DEPARTMENTS      
    Name
    Null?
    Type
     
         
    DEPARTMENT_ID
    DEPARTMENT_NAME
    MANAGER_ID
    LOCATION_ID
    NOT NULL
    NOT NULL
    NUMBER(4)
    VARCHAR2(30)
    NUMBER(6)
    NUMBER(4)
    LOCATIONS    
    Name
    Null?
    Type
         
    LOCATION_ID
    STREET_ADDRESS
    POSTAL_CODE
    CITY
    STATE_PROVINCE
    COUNTRY_ID

    NOT NULL


    NOT NULL

    NUMBER(4)
    VARCHAR2(40)
    VARCHAR2(12)
    VARCHAR2(30)
    VARCHAR2(25)
    CHAR(2)
> 3. View the Exhibit and examine the structure of the EMPLOYEES and DEPARTMENTS tables. You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query:

SQL>SELECT last_name, hire_date
FROM employees
WHERE (department_id, hire_date) IN
(SELECT department_id, MAX(hire_date)
FROM employees JOIN departments
USING(department_id)
WHERE location_id = 1700
GROUP BY department_id);

What is the outcome?

  1. It executes but does not give the correct result
  2. It executes successfully and gives the correct result
  3. It generates an error because of the pairwise comparison
  4. It generates an error because the GROUP BY clause cannot be used with table joins in a subquery

    3a "Exhibit"

      EMPLOYEES      
    Name
    Null?
    Type
     
         
    EMPLOYEE_ID
    FIRST_NAME
    LAST_NAME
    HIRE_DATE
    JOB_ID
    SALARY
    DEPARTMENT_ID
    NOT NULL

    NOT NULL
    NOT NULL
    NOT NULL
    NUMBER(6)
    VARCHAR2(20)
    VARCHAR2(25)
    DATE
    VARCHAR2(10)
    NUMBER(8, 2)
    NUMBER(4)
    DEPARTMENTS    
    Name
    Null?
    Type
         
    DEPARTMENT_ID
    DEPARTMENT_NAME
    MANAGER_ID
    LOCATION_ID
    NOT NULL
    NOT NULL
    NUMBER(4)
    VARCHAR2(30)
    NUMBER(6)
    NUMBER(4)
4. View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables. You need to display all those cities that have only one department. Which query gives the correct output?
  1. SELECT location_id, city
    FROM locations l
    WHERE 1 = (SELECT COUNT(*)
    FROM departments
    WHERE location_id = l.location_id);
  2. SELECT location_id, city
    FROM locations WHERE EXISTS (SELECT COUNT(*)
    FROM departments
    GROUP BY location_id HAVING COUNT(*) = 1);
  3. SELECT location_id, city
    FROM locations WHERE
    1 = (SELECT COUNT(*) FROM departments
    GROUP BY location_id);
  4. SELECT l.location_id, city
    FROM locations l JOIN departments d ON (l.location_id = d.location_id)
    WHERE EXISTS (SELECT COUNT(*)
    FROM departments d
    WHERE l.location_id =d.location_id);

    4a "Exhibit"

      LOCATIONS      
    Name
    Null?
    Type
     
         
    LOCATION_ID
    STREET_ADDRESS
    POSTAL_CODE
    CITY
    STATE_PROVINCE
    COUNTRY_ID
    NOT NULL NUMBER(4)
    VARCHAR2(40)
    VARCHAR2(12)
    VARCHAR2(30)
    VARCHAR2(25)
    CHAR(2)
    DEPARTMENTS    
    Name
    Null?
    Type
         
    DEPARTMENT_ID
    DEPARTMENT_NAME
    MANAGER_ID
    LOCATION_ID
    NOT NULL
    NOT NULL
    NUMBER(4)
    VARCHAR2(30)
    NUMBER(6)
    NUMBER(4)
5. View the Exhibit and examine the structure of the EMP table. You want to display the names and salaries of only those employees who earn the highest salaries in their departments. Which two SQL statements give the required output? (Choose two.)
  1. SELECT ename, sal
    FROM emp e
    WHERE sal = (SELECT MAX(sal)
    FROM emp
    WHERE deptno = e.deptno);
  2. SELECT ename, sal
    FROM emp
    WHERE sal = ALL (SELECT MAX(sal)
    FROM emp
    GROUP BY deptno);
  3. SELECT ename, sal
    FROM emp e
    WHERE EXISTS (SELECT MAX(sal)
    FROM emp WHERE deptno = e.deptno);
  4. SELECT ename, sal
    FROM emp
    NATURAL JOIN (SELECT deptno, MAX(sal) sal
    FROM emp
    GROUP BY deptno);
6. Evaluate the following SQL statement:
(Note that the numbers 2,3 etc in the SQL statement are line numbers and not part of the syntax)

SQL> CREATE TABLE product
2 (prod_id NUMBER(3),
3 prod_name VARCHAR2(25),
4 qty NUMBER(7,2),
5 price NUMBER(10,2),
6 CONSTRAINT prod_id_pk PRIMARY KEY(prod_id),
7 CONSTRAINT prod_name_uq UNIQUE (prod_name),
8 CONSTRAINT price_nn NOT NULL (price));

What is the outcome of executing this command?

  1. It generates an error at line 6.
  2. It generates an error at line 7.
  3. It generates an error at line 8.
  4. It executes successfully and creates the PRODUCTS table.
7. Examine the structure of the DEPT table:
 
Name
 
Null?
 
Type
 
 
     
DEPTNO
DNAME
LOC
NOT NULL NUMBER(2)
VARCHAR2(14)
VARCHAR2(13)
 
You successfully execute the following SQL statement:

SQL>CREATE TABLE emp
(emp_no NUMBER(3) PRIMARY KEY,
emp_name VARCHAR2(25) UNIQUE,
job_id VARCHAR2(10) NOT NULL,
deptno   NUMBER(2) REFERENCES dept(deptno),
salary NUMBER(10,2) CHECK (salary > 0));

For which columns would an index be generated automatically? (Choose all that apply)

  1. EMP_NO
  2. SALARY
  3. JOB_ID
  4. DEPT_NO
  5. EMP_NAME
8. View the Exhibit and examine the structure of the EMP table belonging to the user SCOTT. The EMP table contains the details of all the current employees in your organization.
EMPNO is the PRIMARY KEY.
User SCOTT has created an ENAME_IDX index on the ENAME column and an EMP_VW view that displays the ENAME and SALARY columns.
The recyclebin is enabled in the database. SCOTT executes the following command:
 
SQL> DROP TABLE emp;
Which details would be stored in the recycle bin? (Choose all that apply)

  1. EMP_VW
  2. ENAME_IDX
  3. The PRIMARY KEY constraint
  4. Only the structure of the EMP table
  5. Structure and data of the EMP table

    8a "Exhibit”

      EMP      
    Name
    Null?
    Type
     
         
    EMPNO
    ENAME
    HIREDATE
    SAL
    DEPTNO
    NOT NULL NUMBER(4)
    VARCHAR2(10)
    DATE
    NUMBER(7,2)
    NUMBER(2)
     
9. Examine the data in the DOCNO column of the DOC_DETAILS table:


DOCNO

123-456-7890
233-67-90876
45-789-23456

You need to extract the digits between the hyphens as follows:


SUBSTR

456
67
789

Which SQL statement gives the required result?

  1. SELECT REGEXP_SUBSTR(docno,'-[^-]+') "SUBSTR" FROM doc_details;
  2. SELECT REGEXP_SUBSTR(docno,'^-[^-]+-')"SUBSTR"
    FROM doc_details;
  3. SELECT REGEXP_SUBSTR(docno,'-[^-]+',2) "SUBSTR"
    FROM doc_details;
  4. SELECT REGEXP_SUBSTR(docno, '[^-]+',1,2) "SUBSTR"
    FROM doc_details;
10. View the Exhibit and examine a sample of the data existing in the STORES table.
You need to generate a report that shows the following details:

1) The total QTY_SOLD of each product in each region of each country.
2) The total QTY_SOLD of all products in each region of each country.
3) The total QTY_SOLD of all products in each country.

Which SQL statement gives the required output?
 
  1. SELECT country_id, region, prod_no, SUM(qty_sold)
    FROM stores
    GROUP BY CUBE(country_id,region,prod_no);
  2. SELECT country_id, region, prod_no, SUM(qty_sold)
    FROM stores
    GROUP BY ROLLUP(country_id,region,prod_no);
  3. SELECT country, region, prod_no, SUM(qty_sold)
    FROM stores
    GROUP BY GROUPING SETS(country_id,region),prod_no);
  4. SELECT country, region, prod_no, SUM(qty_sold), GROUPING(country_id) G1, GROUPING(region) G2, GROUPING(prod_no)
    FROM stores
    GROUP BY CUBE (country_id,region,prod_no);

Answers

  1.  BCD
  2.  A
  3.  B
  4.  A
  5.  AD
  6.  C
  7.  AE
  8.  BCE
  9.  D
  10.  B