Oracle__10. Oracle 1Z0-051 Exam - Subquery Flashcards
What is a nested subquery?
A subquery in the WHERE clause
What is an inline view?
A subquery in the FROM clause
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 subquery in a SELECT clause can only return a single row.Termed: single-row subquery
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 subquery is needed.SELECT item_no, MIN(qty)FROM table1HAVING MIN(qty) > (Select AVG(qty) FROM table1)GROUP BY item_no
Can a subquery within a subquery return multiple rows?
Yes
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)
errorBecause the subquery will return more than one record
Which is executed first, main query or the subquery?
subquery
Can the main query and subquery get data from different tables?
yes
What clause cannot be included when the insert statement has a subquery?
VALUES clause
The SOME operator is a synonym for what other operator?
ANY
What does it mean when a value is compared to a subquery using the < ANY operator?
The value will be compared to the maximum value returned from the subquery.
What does it mean when a value is compared to a subquery using the > ANY operator?
The value will be compare to the minimum value returned from the subquery.
What does it mean when a value is compared to a subquery using the = ANY operator?
= ANY operator is equivalent to the IN operator
What does it mean when a value is compared to a subquery using the < ALL operator?
The value will be compare to the maximum value returned from the subquery.
What does it mean when a value is compared to a subquery using the > ALL operator?
The value will be compare to the minimum value returned from the subquery.
What does it mean when a value is compared to a subquery using the = ALL operator?
= ALL operator is invalid
Can the NOT operator be used with IN, ANY and ALL operators?
Yes
Can the ANY operator be used with multiple-row subqueries?
Yes
Can the ALL operator be used with multiple-row subqueries?
Yes
Can the main query and the subquery get data from different tables?
Yes
Are subqueries required to return only one column?
NoExample of 2 columns compared:WHERE (name, age) in (Select name, age from table1)
Can subqueries contain GROUP BY and ORDER BY clauses?
Yes
Can you use an expression as a return in a subquery?
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
What does the following clause mean? Where x < ANY (subquery)
This is similar to x being less than the Maximum value of the subquery.
What is the maximum number of nesting subqueries
255
What symbols must a subquery be surrounded by?
parenthesis
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);
NoThe table alias from the outer subquery is recognized in the inner subquery
What is the limit of the number of subqueries in a clause in a SQL statement?
255
What is a subquery?
A query which return 1 or more records which are used by the main query.
In a subquery which part is executed first, Subquery or Main query?
- Subquery
What are the 2 main types of subqueries?
- single row2. multiple rowbased on what is returned from the subquery
List the 6 single row operators.
- =2. >3. <4. >=5. <=6. <>
List the multiple row operators.
- IN2. ANY3. ALL
What are subqueries enclosed by?
(subquery) - parenthesis
Must subqueries be on the right side of the operator?
NoThey are placed on the right side for readability.
What is called a Select statement embedded in the clause of another Select statement?
a subquery
What type of subquery is in this Select statement? SELECT * FROM employees WHERE id = (SELECT id FROM employees WHERE emp_id = 12)
single row
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);
Yes, because it is a single row subquery.All departments are compared to the min salary of department 50.
What is return by this subquery? SELECT MIN(AVG(salary) FROM employees GROUP by Job_ID
A single number.The single number is the minimum of the average salary from each job;
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’)
the main query would also have zero records.
The ANY and ALL key words in a subquery comparison must be proceed by one of 6 symbols. What are those symbols?
=!=>«=>=Example: Where x >= ANY (subquery)
What does the following clause mean? Where x is < ALL (subquery)
This is similar to x being less than the minimum value of the subquery.
What is =ANY equivalent to?
IN
What operator is similar to ANY?
SOME
Can subqueries be used in the SET Clause and WHERE clause of an INSERT statement?
yes
Can subqueries in a WHERE clause of a DELETE statement?
yes
Why could this statement fail? DELETE FROM employees WHERE department = (SELECT department FROM departments WHERE department_name LIKE ‘%Public%’)’
If the subquery returned more than one row.
What one thing would be changed if statement fails? DELETE FROM employees WHERE department = (SELECT department FROM departments WHERE department_name LIKE ‘%Public%’)’
Change the = to an IN
What is an inline VIEW?
a subquery that is part of the FROM clause
Is this a valid SQL statement? SELECT COUNT(), prod_id FROM products GROUP BY prod_id HAVING COUNT() = (SELECT MAX (COUNT(*)) FROM products)
Yes
The term inner query and outer query can also be called what in a query that has only 1 subquery?
inner query = subqueryouter query = main query
Is this a valid sql statement? SELECT prod_name FROM products Where price = (SELECT MAX (price) FROM products)
Yes
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);
each subquery is referred as a correlated subquery
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;
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.
Define the meaning of the multi-row operators when using subqueries. >ALL ANY =ANY
[> 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)
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
All employees who earn less than the maximum salary in their department
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;
No error.The (SELECT MAX(Cust_credit_limit) FROM customers) only produces a single row value.
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)
No Error.There seems to be an implicit Join between main query and subquery.