PROGRAMMING: Section 6

FUNDAMENTALS OF SUBQUERIES
What will the following statement return:
SELECT last_name, salary
FROM employees
WHERE (department_id, job_id) = (SELECT department_id, job_id FROM employees WHERE employee_id = 103)
A list of last_names and salaries of employees that works in the same department and has the same job_id as that of employee 103.

What will the following statement return:
SELECT employee_id, last_name
FROM employees
WHERE salary =(SELECT MIN(salary) FROM employees GROUP BY department_id);
Nothing. It is an invalid statement.    

Which of the following statements is a true guideline for using subqueries?
The outer and inner queries can reference more than one table. They can get data from different tables.
          
What will the following statement return:
SELECT last_name, salary
FROM employees
WHERE salary < (SELECT salary FROM employees WHERE employee_id = 103)
A list of last_names and salaries of employees that makes less than employee 103
          
Examine the following statement:
SELECT last_name, salary
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE employee_id = 103) AND job_id = (SELECT job_id FROM employees WHERE employee_id = 103)
Is this a pair-wise or non-pair-wise Subquery?
This is an example of a non-pair-wise subquery.
          
Subqueries can only be placed in the WHERE clause. True or False?
False


SINGLE ROW SUBQUERIES
If the subquery returns no rows will the outer query return any values?
No, because the subquery will be treated like a null value.
          
Subqueries are limited to four per SQL transaction. True or False?
False
          
In a non-correlated subquery, the outer query always executes prior to the inner query's execution. True or False?
False
          
Single row subqueries may not include this operator:
ALL
          
The result of this statement will be:
SELECT last_name, job_id, salary, department_id
FROM employees
WHERE job_id =(SELECT job_id FROM employees WHERE employee_id = 141) AND department_id = (SELECT department_id FROM departments WHERE location_id =1500)
Only the employees whose job id matches employee 141 and who work in location 1500


MULTIPLE-ROW SUBQUERIES
In a subquery the ALL operator compares a value to every value returned by the inner query. True or False?     
True
          
The SQL multiple-row subquery extends the capability of the single-row syntax through the use of what three comparison operators?
IN, ANY and ALL
          
Multiple-row subqueries must have NOT, IN or ANY in the WHERE clause of the inner query. True or False?
False
          
Group functions, such as HAVING and GROUP BY can be used in multiple-row subqueries. True or False?   
True
      
When a multiple-row subquery uses the NOT IN (<>ALL) operator, if one of the values returned by the inner query is a null value, the entire query returns:
No rows returned
          
Group functions can be used in subqueries even though they may return many rows. True or False?
True
          
The salary column of the f_staffs table contains the following values: 4000 5050 6000 11000 23000
Which of the following statements will return the last_name and first_name of those employees who earn more than 5000.
SELECT last_name, first_name
FROM f_staffs
WHERE salary IN (SELECT salary FROM f_staffs WHERE salary > 5000);

There can be more than one subquery returning information to the outer query. True or False?
True
          

CORRELATED SUBQUERIES
The WITH-clause is a way of creating extra tables in the database? (True or False)
False
          
Correlated Subqueries must work on the same tables in both the inner and outer query?
False
         
In a correlated subquery the outer and inner query are joined on one or more columns?
True
          
Table aliases must be used when you are writing correlated subqueries?
True