Question 1

There are 30 rows in the ORDERS table. Column ORDER_VALUE is nullable, and has 10 NULL values. When you perform AVG(ORDER_VALUE) how many records will be considered for calculating the average?

	a.	10.
	b.	30.
	c.	20.
	d.	0.

Question 2

In the following SQL Statement, the Blank 1 Blank 2 clause is missing: SELECT DEPARTMENT_ID, SUM(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = 110

Question 3

1. Which function requires numeric column as input?

   	a.	COUNT.
   	b.	MAX.
   	c.	SUM.
   	d.	All of above.

Question 4

1. Which clause is used to filter aggregate results?

   	a.	WHERE.
   	b.	GROUP BY.
   	c.	GROUPING.
   	d.	HAVING.

Question 5

1. Which structure is used to create aggregations of aggregates?

   	a.	CUBE and ROLLUP.
   	b.	GROUP BY and HAVING.
   	c.	WHERE and HAVING.
   	d.	SUM and COUNT.

Question 6

1. Choose which statement is false.

   	a.	If you use SELECT * with NATURAL JOIN syntax, common columns are listed only once in the result set.
   	b.	The column names not appearing in the USING clause cannot be qualified in the SELECT clause.
   	c.	Using the JOIN ON syntax over the traditional join method separates the table joins from the other filter conditions.
   	d.	If you do not specify a join condition for the tables listed in the FROM clause, Oracle joins each row from the first table to every row in the second table.

Question 7

1. If tables A and B are outer-joined (FROM A, B) and you need all rows from B, where would you place the outer join operator (+)?

   	a.	Beside all columns of A.
   	b.	Beside join columns of A.
   	c.	Beside all columns of B.
   	d.	Beside join columns of B.

Question 8

1. Which ANSI join syntax cannot be easily represented using traditional join syntax?

   	a.	LEFT OUTER JOIN.
   	b.	NATURAL JOIN.
   	c.	FULL OUTER JOIN.
   	d.	None.

Question 9

1. The DEPARTMENT_ID column in EMPLOYEES table is nullable, and has 3 NULL records. If the subquery in the following query returns no rows, what is the result of the query "SELECT first_name, salary FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE salary < 0)"?

   	a.	Parent query returns 3 records.
   	b.	Parent query returns no records.
   	c.	Query errors out.

Question 10

1. Which statement is true about scalar subquery?

   	a.	Returns one column, but may have multiple rows.
   	b.	Returns one or more columns, but only one row.
   	c.	Returns exactly one column from one row.
   	d.	Returns an exception as this is not permitted.