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)

A) SELECT ename, NVL(12 * salary * comm_pct,0) FROM emp;
B) SELECT ename, NVL2(12 * salary * comm_pct,0) FROM emp;
C) SELECT ename, COALESCE(12 * salary * comm_pct,0) FROM emp;
D) 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?

A) 1,230
B) 01,230
C) 1,230.00
D) 01,230.00

3. Examine the structure of the EMP table:

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?

A) It generates an error
B) It executes successfully but displays no result
C) It executes successfully but displays results that fulfill only requirements 1 and 3
D) It executes successfully and displays results that fulfill all the requirements

4. Which statement is true regarding single-row functions?

A) They cannot be nested
B) They can accept only one argument
C) They act on each row returned and return only one result per row
D) They act on each row returned and can return multiple results per row


5. Which two statements are true regarding a transaction? (Choose two)

A) If a data manipulation statement fails in the middle of a transaction, only that statement is rolled back
B) If a data manipulation statement fails in the middle of a transaction, the entire transaction is rolled back
C) A transaction is committed when a user exits the session normally without issuing an explicit COMMIT command
D) 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?

A) It executes successfully and creates the table

B) It generates an error because SYSDATE cannot be used with the CHECK constraint

C) It generates an error because column size has not been specified for the EMPNO column

D) 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:

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?

A) It generates an error because column definitions cannot be altered to add default values
B) Only subsequent insertions where SALARY is not specified would store the value 10000
C) It generates an error because column definitions cannot be altered if the table contains rows
D) All existing rows and subsequent rows where SALARY contains NULL value get updated to 10000

8. Examine the structure of the EMP table:

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.)

A) It is not affected by any modifications to the EMP table

B) It is automatically dropped when the EMP table is dropped

C) It cannot be used to populate any other column in any table

D) It is automatically dropped when the EMPNO column is dropped

E) 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?

A) SELECT dname, AVG(sal) FROM emp LEFT OUTER JOIN dept USING(deptno) GROUP BY dname;

B) SELECT dname, AVG(sal) FROM emp JOIN dept USING(deptno) GROUP BY dname,sal;

C) SELECT dname, sal FROM emp JOIN dept USING(deptno) GROUP BY dname HAVING sal= AVG(sal);

D) SELECT dname, AVG(sal) FROM emp FULL OUTER JOIN dept USING(deptno) GROUP BY dname;

10. Examine the structure of the EMP table:

You want to find the highest average salary being paid in any department.

Which SQL statement gives the required result?

A) SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno;

B) SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno HAVING AVG(sal) >= MAX(sal);

C) SELECT deptno, MAX(AVG(sal)) FROM emp GROUP BY deptno;

D) SELECT deptno, MAX(AVG(sal)) FROM emp GROUP BY deptno, sal;

Respostas!

•  AC
•  D
•  B
•  C
•  AC
•  B
•  B
•  AE
•  D
•  A