Oracle__10. Oracle 1Z0-051 Exam - Subquery

Question 1

What is a nested subquery?

Answer 1

A subquery in the WHERE clause

Question 2

What is an inline view?

Answer 2

A subquery in the FROM clause

Question 3

What is necessary about the return value of a subquery in a SELECT clause that is not necessary of a subquery in either a FROM or a WHERE clause?

Answer 3

A subquery in a SELECT clause can only return a single row.Termed: single-row subquery

Question 4

How would you modify this SQL Statement to the minimum is greater than the average for all items in the table? SELECT item_no, AVG(qty) FROM table1 HAVING AVG(qty) > MIN(qty) * 2 GROUP BY item_no

Answer 4

A subquery is needed.SELECT item_no, MIN(qty)FROM table1HAVING MIN(qty) > (Select AVG(qty) FROM table1)GROUP BY item_no

Question 5

Can a subquery within a subquery return multiple rows?

Answer 5

Yes

Question 6

If there are 2 employees named Smith what will be returned by this statement? SELECT * FROM employees WHERE emp_name = (SELECT emp_name FROM employees)

Answer 6

errorBecause the subquery will return more than one record

Question 7

Which is executed first, main query or the subquery?

Answer 7

subquery

Question 8

Can the main query and subquery get data from different tables?

Answer 8

yes

Question 9

What clause cannot be included when the insert statement has a subquery?

Answer 9

VALUES clause

Question 10

The SOME operator is a synonym for what other operator?

Answer 10

ANY

Question 11

What does it mean when a value is compared to a subquery using the < ANY operator?

Answer 11

The value will be compared to the maximum value returned from the subquery.

Question 12

What does it mean when a value is compared to a subquery using the > ANY operator?

Answer 12

The value will be compare to the minimum value returned from the subquery.

Question 13

What does it mean when a value is compared to a subquery using the = ANY operator?

Answer 13

= ANY operator is equivalent to the IN operator

Question 14

What does it mean when a value is compared to a subquery using the < ALL operator?

Answer 14

The value will be compare to the maximum value returned from the subquery.

Question 15

What does it mean when a value is compared to a subquery using the > ALL operator?

Answer 15

The value will be compare to the minimum value returned from the subquery.

Question 16

What does it mean when a value is compared to a subquery using the = ALL operator?

Answer 16

= ALL operator is invalid

Question 17

Can the NOT operator be used with IN, ANY and ALL operators?

Answer 17

Yes

Question 18

Can the ANY operator be used with multiple-row subqueries?

Answer 18

Yes

Question 19

Can the ALL operator be used with multiple-row subqueries?

Answer 19

Yes

Question 20

Can the main query and the subquery get data from different tables?

Answer 20

Yes

Question 21

Are subqueries required to return only one column?

Answer 21

NoExample of 2 columns compared:WHERE (name, age) in (Select name, age from table1)

Question 22

Can subqueries contain GROUP BY and ORDER BY clauses?

Answer 22

Yes

Question 23

Can you use an expression as a return in a subquery?

Answer 23

YesExample of an expression returned from a subquery:WHERE (name, limit) in (Select name, 12 * 4 from table1)Note: no alias is needed, just need same number of values and same data types to compare

Question 24

What does the following clause mean? Where x < ANY (subquery)

Answer 24

This is similar to x being less than the Maximum value of the subquery.

Question 25

What is the maximum number of nesting subqueries

Answer 25

255

Question 26

What symbols must a subquery be surrounded by?

Answer 26

parenthesis

Question 27

Will this statement produce an error? SELECT select_list FROM table1 t_alias1 WHERE expr operator (SELECT column_list FROM table2 t_alias2 WHERE t_alias1.column operator t_alias2.column);

Answer 27

NoThe table alias from the outer subquery is recognized in the inner subquery

Question 28

What is the limit of the number of subqueries in a clause in a SQL statement?

Answer 28

255

Question 29

What is a subquery?

Answer 29

A query which return 1 or more records which are used by the main query.

Question 30

In a subquery which part is executed first, Subquery or Main query?

Answer 30

1. Subquery

Question 31

What are the 2 main types of subqueries?

Answer 31

1. single row2. multiple rowbased on what is returned from the subquery

Question 32

List the 6 single row operators.

Answer 32

1. =2. >3. <4. >=5. <=6. <>

Question 33

List the multiple row operators.

Answer 33

1. IN2. ANY3. ALL

Question 34

What are subqueries enclosed by?

Answer 34

(subquery) - parenthesis

Question 35

Must subqueries be on the right side of the operator?

Answer 35

NoThey are placed on the right side for readability.

Question 36

What is called a Select statement embedded in the clause of another Select statement?

Answer 36

a subquery

Question 37

What type of subquery is in this Select statement? SELECT * FROM employees WHERE id = (SELECT id FROM employees WHERE emp_id = 12)

Answer 37

single row

Question 38

Does the following statement execute without errors? SELECT dept_id, Min(Salary) FROM employees GROUP by dept_id HAVING MIN (Salary) > (SELECT MIN (Salary) FROM employees WHERE dept_id = 50);

Answer 38

Yes, because it is a single row subquery.All departments are compared to the min salary of department 50.

Question 39

What is return by this subquery? SELECT MIN(AVG(salary) FROM employees GROUP by Job_ID

Answer 39

A single number.The single number is the minimum of the average salary from each job;

Question 40

How many rows are returned from this Select statement, if the subquery return no records? SELECT * FROM employees WHERE job_id IN (SELECT job_id FROM employees WHERE lastname = 'XX')

Answer 40

the main query would also have zero records.

Question 41

The ANY and ALL key words in a subquery comparison must be proceed by one of 6 symbols. What are those symbols?

Answer 41

=!=><<=>=Example: Where x >= ANY (subquery)

Question 42

What does the following clause mean? Where x is < ALL (subquery)

Answer 42

This is similar to x being less than the minimum value of the subquery.

Question 43

What is =ANY equivalent to?

Answer 43

IN

Question 44

What operator is similar to ANY?

Answer 44

SOME

Question 45

Can subqueries be used in the SET Clause and WHERE clause of an INSERT statement?

Answer 45

yes

Question 46

Can subqueries in a WHERE clause of a DELETE statement?

Answer 46

yes

Question 47

Why could this statement fail? DELETE FROM employees WHERE department = (SELECT department FROM departments WHERE department_name LIKE '%Public%')'

Answer 47

If the subquery returned more than one row.

Question 48

What one thing would be changed if statement fails? DELETE FROM employees WHERE department = (SELECT department FROM departments WHERE department_name LIKE '%Public%')'

Answer 48

Change the = to an IN

Question 49

What is an inline VIEW?

Answer 49

a subquery that is part of the FROM clause

Question 50

Is this a valid SQL statement? SELECT COUNT(*), prod_id FROM products GROUP BY prod_id HAVING COUNT(*) = (SELECT MAX (COUNT(*)) FROM products)

Answer 50

Yes

Question 51

The term inner query and outer query can also be called what in a query that has only 1 subquery?

Answer 51

inner query = subqueryouter query = main query

Question 52

Is this a valid sql statement? SELECT prod_name FROM products Where price = (SELECT MAX (price) FROM products)

Answer 52

Yes

Question 53

What type of subquery is in each of these statements? SELECT select_list FROM table1 t_alias1 WHERE expr operator (SELECT column_list FROM table2 t_alias2 WHERE t_alias1.column operator t_alias2.column); UPDATE table1 t_alias1 SET column = (SELECT expr FROM table2 t_alias2 WHERE t_alias1.column = t_alias2.column); DELETE FROM table1 t_alias1 WHERE column operator (SELECT expr FROM table2 t_alias2 WHERE t_alias1.column = t_alias2.column);

Answer 53

each subquery is referred as a correlated subquery

Question 54

If the subquery return 5 rows one time and 0 rows another time, what is the different in the final result by the statement? SELECT * FROM departments WHERE EXISTS (SELECT * FROM employees WHERE departments.department_id = employees.department_id AND employees.salary > 2500) ORDER BY department_name;

Answer 54

All rows will be returned from department if a minimum of 1 row is returned from employees.If there are zero rows returned from employees then there will be zero rows returned from departments.

Question 55

Define the meaning of the multi-row operators when using subqueries. >ALL ANY =ANY

Answer 55

[> ALL] More than the highest value returned by the subquery[< ALL] Less than the lowest value returned by the subquery[< ANY] Less than the highest value returned by the subquery[> ANY] More than the lowest value returned by the subquery[= ANY] Equal to any value returned by the subquery (same as IN)

Question 56

What is returned by this statement? SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal) FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal

Answer 56

All employees who earn less than the maximum salary in their department

Question 57

Will this statement produce and error? SELECT custname, grade FROM customers, grades WHERE (SELECT MAX (cust_credit_limit) from CUSTOMERS) BETWEEN startval AND endval and cust_credit_limit BETWEEN startval AND endval;

Answer 57

No error.The (SELECT MAX(Cust_credit_limit) FROM customers) only produces a single row value.

Question 58

Will this statement produce and error? SELECT COUNT(*), prod_id FROM products GROUP by prod_id HAVING COUNT(*) = (SELECT MAX( COUNT(*)) FROM products GROUP BY prod_id)

Answer 58

No Error.There seems to be an implicit Join between main query and subquery.