section 6

Test : Fundaments of subquery probabil -> http://paulierco.ro/wp-content/uploads/2008/03/quizuridelasectiunea1panalasectiunea7/index.html

-------------------------------------------------------------------------------------

Test: Quiz: Subqueries 

Subqueries     

1.Subqueries can only be placed in the WHERE clause. True or False? 

True 

False(*)

  

2. 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. (*) 

A list of last_names and salaries of employees 

A list of first_names and salaries of employees in Department 50 

A list of last_names and salaries of employees grouped by department_id. 

      

3.  Which of the following statements is a true guideline for using 

subqueries?   

    

Do not enclose the subquery in parentheses.  

Place the subquery on the left side of the comparison condition. 

The outer and inner queries can reference than one table. They can get data from different tables. (*) 

Only one WHERE clause can be used for a SELECT statement, and if specified, it must be the outer query. 

   

4.  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 more than employee 103 

A list of last_names and salaries of employees that makes less than employee 103 (*) 

A list of first_names and salaries of employees making less than employee 

Nothing. It is an invalid statement. 

----------------------------------------------------------------------------------------------

Test: Quiz: Single-Row Subqueries 

Single-Row Subqueries 

1. Single row subqueries may not include this operator:  

ALL (*) 

= 

<> 

> 

2. If the subquery returns no rows will the outer query return any values? 

No, because you are not allowed to not return any rows from a subquery 

Yes. It will just run and ignore the subquery 

No, because the subquery will be treated like a null value. (*) 

Yes, Oracle will find the nearest value and rewrite your statement implicitly when you run it 

3. In a non-correlated subquery, the outer query always executes prior to the inner query's execution. True or False? 

True 

False (*) 

4. Subqueries are limited to four per SQL transaction. True or False? 

True 

False (*) 

5. 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)

All employees from Location 1500 will be displayed 

An error since you can?t get data from two tables in the same subquery

All employees with the department id of 141 

Only the employees whose job id matches employee 141 and who work in location 1500 (*) 

------------------------------------------------------------------------------------

Test: Quiz: Multiple-Row Subqueries 

Multiple-Row Subqueries 

1. Multiple-row subqueries must have NOT, IN or ANY in the WHERE clause of the inner query. True or False? 

True 

False (*) 

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

True (*) 

False 

3. 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 = (SELECT salary FROM f_staffs WHERE salary > 5000); 

SELECT last_name, first_name 

FROM f_staffs 

WHERE salary = (SELECT salary FROM f_staffs WHERE salary < 5000); <p> 

SELECT last_name, first_name 

FROM f_staffs 

WHERE salary IN (SELECT salary FROM f_staffs WHERE salary > 5000); (*)

SELECT last_name, first_name 

FROM f_staffs 

WHERE salary IN 

(SELECT last_name, first_name FROM f_staffs WHERE salary < 5000); 

4. Group functions can be used in subqueries even though they may return many rows. True or False? 

True (*) 

False 

5. The SQL multiple-row subquery extends the capability of the single-row syntax through the use of what three comparison operators? 

IN, ANY and EQUAL 

IN, ANY and ALL (*) 

IN, ANY and EVERY 

IN, ALL and EVERY 

6. In a subquery the ALL operator compares a value to every value returned by the inner query. True or False? 

True (*) 

False 

7. Group functions, such as HAVING and GROUP BY can be used in multiple-row subqueries. True or False? 

True (*) 

False 

8. 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: 

A list of Nulls 

All rows that were selected by the inner query including the null value(s) 

All rows, minus the null value(s), that were selected by the inner query 

No rows returned (*) 

-----------------------------------------------------------------------------------------------

Test: Quiz: Correlated Subqueries 

Correlated Subqueries 

1. The WITH-clause is a way of creating extra tables in the database? (True or False)

True 

False (*) 

2. Table aliases must be used when you are writing correlated subqueries? (True or false)

True (*) 

False 

3. Correlated Subqueries must work on the same tables in both the inner and outer query? (True or False) 

True 

False (*) 

4. In a correlated subquery the outer and inner query are joined on one or more columns? (True or False) 

True (*) 

False