Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

DSO SQL > Oracle__10. Oracle 1Z0-051 Exam - Subquery > Flashcards

Oracle__10. Oracle 1Z0-051 Exam - Subquery Flashcards

1
Q

What is a nested subquery?

A

A subquery in the WHERE clause

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is an inline view?

A

A subquery in the FROM clause

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Can a subquery within a subquery return multiple rows?

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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)

A

errorBecause the subquery will return more than one record

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Which is executed first, main query or the subquery?

A

subquery

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A

VALUES clause

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

The SOME operator is a synonym for what other operator?

A

ANY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A

= ANY operator is equivalent to the IN operator

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

A

= ALL operator is invalid

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

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

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

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

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

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

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

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

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Are subqueries required to return only one column?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Can subqueries contain GROUP BY and ORDER BY clauses?

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

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

A

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

25
Q

What is the maximum number of nesting subqueries

A

255

26
Q

What symbols must a subquery be surrounded by?

A

parenthesis

27
Q

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

A

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

28
Q

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

A

255

29
Q

What is a subquery?

A

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

30
Q

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

A
  1. Subquery
31
Q

What are the 2 main types of subqueries?

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

List the 6 single row operators.

A
  1. =2. >3. <4. >=5. <=6. <>
33
Q

List the multiple row operators.

A
  1. IN2. ANY3. ALL
34
Q

What are subqueries enclosed by?

A

(subquery) - parenthesis

35
Q

Must subqueries be on the right side of the operator?

A

NoThey are placed on the right side for readability.

36
Q

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

A

a subquery

37
Q

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

A

single row

38
Q

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

A

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

39
Q

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

A

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

40
Q

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

A

the main query would also have zero records.

41
Q

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

A

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

42
Q

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

A

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

43
Q

What is =ANY equivalent to?

A

IN

44
Q

What operator is similar to ANY?

A

SOME

45
Q

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

A

yes

46
Q

Can subqueries in a WHERE clause of a DELETE statement?

A

yes

47
Q

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

A

If the subquery returned more than one row.

48
Q

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

A

Change the = to an IN

49
Q

What is an inline VIEW?

A

a subquery that is part of the FROM clause

50
Q

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

A

Yes

51
Q

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

A

inner query = subqueryouter query = main query

52
Q

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

A

Yes

53
Q

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

A

each subquery is referred as a correlated subquery

54
Q

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;

A

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.

55
Q

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

A

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

56
Q

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

A

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

57
Q

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;

A

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

58
Q

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)

A

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

DSO SQL (15 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions