QUESTION 1

Based on the HR.EMPLOYEES table, how do you write a SQL to get the department numbers that have at least one employee working?

	a.	SELECT ALL DEPARTMENT_ID FROM EMPLOYEES;
	b.	SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES;
	c.	SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID IS NOT NULL;
	d.	SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID > 1;

10 points

QUESTION 2

Which operator can be used in SQL queries to join two text values?

	a.	+
	b.	|
	c.	||
	d.	++

10 points

QUESTION 3

Consider the following SQL and choose the best option that summarizes the result of the query. SELECT first_name, hire_date, salary, manager_id mid FROM employees WHERE department_id IN (110,100) ORDER BY mid ASC, salary DESC;

	a.	Returns all rows from employees table, sorted in the order of manager IDs.
	b.	Returns only rows from departments 110 or 100, with the employee drawing highest salary in department at the top.
	c.	Returns rows from departments 110 and 100, only if manager id is not null.
	d.	Does not return any row, as some columns in the SELECT clause are missing in the ORDER BY clause.
	e.	All of the above are wrong.

10 points

QUESTION 4

Which command is used to get information about database objects, using SQL*Plus?

	a.	DESCRIBE.
	b.	SELECT.
	c.	QUERY.
	d.	None of the above.

10 points

QUESTION 5

In which order should the following clauses appear in a SELECT statement: FROM, ORDER BY, WHERE?

	a.	It does not matter, they can appear in any order.
	b.	FROM, ORDER BY, WHERE.
	c.	WHERE, FROM, ORDER BY.
	d.	FROM, WHERE, ORDER BY.

10 points

QUESTION 6

Which function cannot be used to return a value, instead of NULL?

	a.	NULLIF.
	b.	NVL.
	c.	DECODE.
	d.	COALESCE.

10 points

QUESTION 7

In the employees table, the commission_pct is a nullable column. Which of the following SQL is best suited to calculate the total compensation?

	a.	SELECT first_name, salary, commission_pct, salary + (salary * commission_pct) compensation FROM employees ;
	b.	SELECT first_name, salary, commission_pct, NVL(salary + (salary * commission_pct), 0) compensation FROM employees ;
	c.	SELECT first_name, salary, NVL(commission_pct,0), salary + (salary * commission_pct) compensation FROM employees ;
	d.	SELECT first_name, salary, commission_pct, salary + (salary * NVL(commission_pct,0)) compensation FROM employees ;

10 points

QUESTION 8

Which function returns the first non-null value from the list of arguments?

	a.	NVL2
	b.	NULLIF
	c.	COALESCE
	d.	DECODE

10 points

QUESTION 9

The function _____ can be used to find the character position of the first occurrence of a string.

10 points

QUESTION 10

The result of evaluating function TO_DATE(01-01-98,DD-MM-YYYY) is _________.

10 points