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

Oracle Certification

Oracle Database 11g: SQL Fundamentals I

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. Examine the structure of the EMP table:

  EMP      
Name
Null?
Type
 
     
EMPNO
ENAME
SALARY
COMM_PCT
NOT NULL NUMBER(3)
VARCHAR2(25)
NUMBER(10,2)
NUMBER(4,2)
 
You want to display the annual commission amount payable to each employee.

Which two SQL statements ensure that a value is displayed in the calculated column for all the employees? (Choose two)

  1. SELECT ename, NVL(12 * salary * comm_pct,0) FROM emp;
  2. SELECT ename, NVL2(12 * salary * comm_pct,0) FROM emp;
  3. SELECT ename, COALESCE(12 * salary * comm_pct,0) FROM emp;
  4. SELECT ename, DECODE(12 * salary * comm_pct,NULL,0) FROM emp;
2. Evaluate the following SQL statement:

SQL>SELECT TO_CHAR(1230,'00,999.99') FROM DUAL;

What would be the outcome?

  1. 1,230
  2. 01,230
  3. 1,230.00
  4. 01,230.00
3. Examine the structure of the EMP table:

  EMP      
Name
Null?
Type
 
     
EMPNO
ENAME
SALARY
COMM_PCT
NOT NULL NUMBER(3)
VARCHAR2(25)
NUMBER(10,2)
NUMBER(4,2)
 

You want to generate a report that fulfills the following requirements:
1. Displays employees' names and commission amounts
2. Excludes employees who do not have a commission
3. Displays a zero for employees whose SALARY does not have a value

You issue the following SQL statement:

SQL>SELECT ename, NVL(salary * comm_pct, 0)
FROM emp
WHERE comm_pct <> NULL;

What is the outcome?

  1. It generates an error
  2. It executes successfully but displays no result
  3. It executes successfully but displays results that fulfill only requirements 1 and 3
  4. It executes successfully and displays results that fulfill all the requirements
4. Which statement is true regarding single-row functions?
  1. They cannot be nested
  2. They can accept only one argument
  3. They act on each row returned and return only one result per row
  4. They act on each row returned and can return multiple results per row
5. Which two statements are true regarding a transaction? (Choose two)
  1. If a data manipulation statement fails in the middle of a transaction, only that statement is rolled back
  2. If a data manipulation statement fails in the middle of a transaction, the entire transaction is rolled back
  3. A transaction is committed when a user exits the session normally without issuing an explicit COMMIT command
  4. A transaction is rolled back when a user exits the session normally without issuing an explicit COMMIT command
6. You issue the following SQL command:

SQL> CREATE TABLE emp
(empno NUMBER PRIMARY KEY,
ename VARCHAR2(25)  NOT NULL,
dob   TIMESTAMP DEFAULT SYSDATE,
hire_date  DATE CONSTRAINT h_dt_chk CHECK (hire_date <= SYSDATE));

What would be the outcome?

  1. It executes successfully and creates the table
  2. It generates an error because SYSDATE cannot be used with the CHECK constraint
  3. It generates an error because column size has not been specified for the EMPNO column
  4. It generates an error because the default value SYSDATE cannot be used with the TIMESTAMP data type
7. Examine the structure of the EMP table:

  EMP      
Name
Null?
Type
 
     
EMPNO
ENAME
SALARY
COMM_PCT
NOT NULL NUMBER(3)
VARCHAR2(25)
NUMBER(10,2)
NUMBER(4,2)
 

You insert some rows into the EMP table.

Then you issue the following command:

SQL>ALTER TABLE emp
MODIFY (salary DEFAULT 10000);

What would be the outcome?

  1. It generates an error because column definitions cannot be altered to add default values
  2. Only subsequent insertions where SALARY is not specified would store the value 10000
  3. It generates an error because column definitions cannot be altered if the table contains rows
  4. All existing rows and subsequent rows where SALARY contains NULL value get updated to 10000
8. Examine the structure of the EMP table:

  EMP      
Name
Null?
Type
 
     
EMPNO
ENAME
SALARY
COMM_PCT
NOT NULL NUMBER(3)
VARCHAR2(25)
NUMBER(10,2)
NUMBER(4,2)
 

EMPNO is the PRIMARY KEY.

You issue the following command and create a sequence EMP_SEQ:

SQL> CREATE SEQUENCE emp_seq;

You then insert some rows into the table using EMP_SEQ to populate the EMPNO column.

Which two statements are true regarding the EMP_SEQ sequence? (Choose two.)

  1. It is not affected by any modifications to the EMP table
  2. It is automatically dropped when the EMP table is dropped
  3. It cannot be used to populate any other column in any table
  4. It is automatically dropped when the EMPNO column is dropped
  5. It can be used to populate any other column in any table in the same schema
9. View the Exhibit below and examine the structures of DEPT and EMP tables.

You need to generate a report that displays all department names along with the corresponding average salary.

Which SQL statement would give the required result?

  1. SELECT dname, AVG(sal)
    FROM emp LEFT OUTER JOIN dept USING(deptno)
    GROUP BY dname;;
  2. SELECT dname, AVG(sal)
    FROM emp JOIN dept USING(deptno)
    GROUP BY dname,sal;
  3. SELECT dname, sal
    FROM emp JOIN dept USING(deptno)
    GROUP BY dname HAVING sal= AVG(sal);
  4. SELECT dname, AVG(sal)
    FROM emp FULL OUTER JOIN dept USING(deptno)
    GROUP BY dname;

    DEPT
    Name
    Null?
    Type
    DEPTNO
    DNAME
    LOC
    NOT NULL NUMBER(2)
    VARCHAR2(14)
    VARCHAR2(13)
    EMP
    Name
    Null?
    Type
    EMPNO
    ENAME
    HIRE_DATE
    SAL
    DEPTNO
    NOT NULL
    NUMBER(4)
    VARCHAR2(10)
    DATE
    NUMBER(7, 2)
    NUMBER(2)
10. Examine the structure of the EMP table:

EMP
Name
Null?
Type
EMPNO
ENAME
SALARY
COMM_PCT
DEPTNO
NOT NULL NUMBER(3)
VARCHAR2(25)
NUMBER(10,2)
NUMBER(4,2)
NUMBER(2)
You want to find the highest average salary being paid in any department.

Which SQL statement gives the required result?

  1. SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno;
  2. SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno HAVING AVG(sal) >= MAX(sal); )
  3. SELECT deptno, MAX(AVG(sal)) FROM emp GROUP BY deptno;
  4. SELECT deptno, MAX(AVG(sal)) FROM emp GROUP BY deptno, sal;

Answers

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