Section 1
1. The SQL multiple-row subquery extends the capability of the single-row syntax through the use of what three comparison operators? Mark for Review
(1) Points
IN, ANY and EQUAL
IN, ANY and ALL (*)
IN, ANY and EVERY
IN, ALL and EVERY
2. There can be more than one subquery returning information to the outer query. True or False? Mark for Review
(1) Points
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.
Mark for Review
(1) Points
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 <>
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. Multiple-row subqueries must have NOT, IN or ANY in the WHERE clause of the inner query. True or False? Mark for Review
(1) Points
True
False (*)
5. 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: Mark for Review
(1) Points
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 (*)
6. Group functions, such as HAVING and GROUP BY can be used in multiple-row subqueries. True or False? Mark for Review
(1) Points
True (*)
False
7. In a subquery the ALL operator compares a value to every value returned by the inner query. True or False? Mark for Review
(1) Points
True (*)
False
8. Group functions can be used in subqueries even though they may return many rows. True or False? Mark for Review
(1) Points
True (*)
False
( Vrei sa traduci ceva?Want to translate something? http://translate.google.ro/# )
duminică, 4 aprilie 2010
Test: Quiz: Single-Row Subqueries
Section 1
1. Subqueries are limited to four per SQL transaction. True or False? Mark for Review
(1) Points
True
False (*)
2. In a non-correlated subquery, the outer query always executes prior to the inner query's execution. True or False? Mark for Review
(1) Points
True
False (*)
3. Single row subqueries may not include this operator: Mark for Review
(1) Points
ALL (*)
=
<>
>
4. 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)
Mark for Review
(1) Points
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 (*)
5. If the subquery returns no rows will the outer query return any values? Mark for Review
(1) Points
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
( Vrei sa traduci ceva?Want to translate something? http://translate.google.ro/# )
1. Subqueries are limited to four per SQL transaction. True or False? Mark for Review
(1) Points
True
False (*)
2. In a non-correlated subquery, the outer query always executes prior to the inner query's execution. True or False? Mark for Review
(1) Points
True
False (*)
3. Single row subqueries may not include this operator: Mark for Review
(1) Points
ALL (*)
=
<>
>
4. 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)
Mark for Review
(1) Points
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 (*)
5. If the subquery returns no rows will the outer query return any values? Mark for Review
(1) Points
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
( Vrei sa traduci ceva?Want to translate something? http://translate.google.ro/# )
Test: Quiz: Subqueries
Section 1
1. What will the following statement return:
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
Mark for Review
(1) Points
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.
2. What will the following statement return:
SELECT last_name, salary
FROM employees
WHERE salary < (SELECT salary
FROM employees
WHERE employee_id = 103)
Mark for Review
(1) Points
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 103
Nothing. It is an invalid statement.
3. What will the following statement return:
SELECT last_name, salary
FROM employees
WHERE (department_id, job_id) IN (SELECT (department_id, job_id)
FROM employees
WHERE employee_id = 103)
Mark for Review
(1) Points
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. (*)
A list of last_names or salaries of employees that works in the same department and has the same job_id as that of employee 103.
A list of last_names and salaries of employees that works in the same department or has the same job_id as that of employee 103.
Nothing. It is an invalid statement.
4. Which of the following statements is a true guideline for using subqueries? Mark for Review
(1) Points
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 more 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.
5. Subqueries can only be placed in the WHERE clause. True or False? Mark for Review
(1) Points
True
False (*)
6. 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?
Mark for Review
(1) Points
This is an example of a non-pair-wise subquery. (*)
This is an example of a pair-wise subquery.
Neither. This statement is illegal, and will not run.
( Vrei sa traduci ceva?Want to translate something? http://translate.google.ro/# )
1. What will the following statement return:
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
Mark for Review
(1) Points
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.
2. What will the following statement return:
SELECT last_name, salary
FROM employees
WHERE salary < (SELECT salary
FROM employees
WHERE employee_id = 103)
Mark for Review
(1) Points
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 103
Nothing. It is an invalid statement.
3. What will the following statement return:
SELECT last_name, salary
FROM employees
WHERE (department_id, job_id) IN (SELECT (department_id, job_id)
FROM employees
WHERE employee_id = 103)
Mark for Review
(1) Points
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. (*)
A list of last_names or salaries of employees that works in the same department and has the same job_id as that of employee 103.
A list of last_names and salaries of employees that works in the same department or has the same job_id as that of employee 103.
Nothing. It is an invalid statement.
4. Which of the following statements is a true guideline for using subqueries? Mark for Review
(1) Points
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 more 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.
5. Subqueries can only be placed in the WHERE clause. True or False? Mark for Review
(1) Points
True
False (*)
6. 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?
Mark for Review
(1) Points
This is an example of a non-pair-wise subquery. (*)
This is an example of a pair-wise subquery.
Neither. This statement is illegal, and will not run.
( Vrei sa traduci ceva?Want to translate something? http://translate.google.ro/# )
Test: Quiz: Group By and Having Clauses, ROLLUP and CUBE Operations, and Grouping Sets
Section 1
1. Which of the following SQL statements could display the number of people with the same last name: Mark for Review
(1) Points
SELECT first_name, last_name, COUNT(employee_id)
FROM EMPLOYEES
GROUP BY last_name;
SELECT employee_id, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
(*)
SELECT employee_id, DISTINCT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
2. The use of GROUP BY GROUPING SETS(....) can speed up the execution of complex report statements? (True or False) Mark for Review
(1) Points
True (*)
False
3. The following is a valid statement:
SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;
True or False?
Mark for Review
(1) Points
True (*)
False
4. Read the following SELECT statement. Choose the column or columns that must be included in the GROUP BY clause.
SELECT COUNT(last_name), grade, gender
FROM STUDENTS
GROUP_BY ?????;
Mark for Review
(1) Points
last_name
last_name, grade
grade, gender (*)
last_name, gender
5. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS((department_id, manager_id), (department_id, job_id))
What data will this query generate?
Mark for Review
(1) Points
Total salaries for (department_id, job_id) and (department_id, manager_id) (*)
Total salaries for (department_id, job_id, manager_id)
Total for (job_id, manager_id)
The statement will fail.
6. If you want to include subtotals and grant totals for all columns mentioned in a GROUP BY clause you should use which of the following extensions to the GROUP BY clause? Mark for Review
(1) Points
ROLLUP
CUBE (*)
GROUP BY ALL COLUMNS
HAVING
7. Is the following statement correct?
SELECT department_id, AVG(salary)
FROM employees;
Mark for Review
(1) Points
No, because a GROUP BY department_id clause is needed (*)
No, because the SELECT clause cannot contain both individual columns and group functions
No, because the AVG function cannot be used on the salary column
Yes
8. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS(.......);
Select the correct GROUP BY GROUPING SETS clause from the following list:
Mark for Review
(1) Points
GROUP BY GROUPING SETS (department_id, AVG(salary)), (department_id, job_id), (department_id, manager_id)
GROUP BY GROUPING SETS (department_id, salary), (department_id, job_id), (department_id, manager_id)
GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, job_id), (manager_id, job_id)) (*)
GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, SUM(salary), (manager_id, job_id))
9. How would you alter the following query to list only employees where more than one employee exists with the same last_name:
SELECT last_name, COUNT(employee_id)
FROM EMPLOYEES
GROUP BY last_name;
Mark for Review
(1) Points
SELECT last_name, COUNT(employee_id)
FROM EMPLOYEES
WHERE COUNT(*) > 1
GROUP BY last_name
SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name
HAVING COUNT(last_name) > 1;
(*)
SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name
EXISTS COUNT(last_name) > 1;
SELECT employee_id, DISTINCT(last_name)
FROM EMPLOYEES
GROUP BY last_name
HAVING last_name > 1;
10. Is the following statement correct:
SELECT first_name, last_name, salary, department_id, COUNT(employee_id)
FROM employees
WHERE department_id = 50
GROUP BY last_name, first_name, department_id;
Mark for Review
(1) Points
Yes
No, beause you cannot have a WHERE-clause when you use group functions.
No, because the statement is missing salary in the GROUP BY clause (*)
Yes, because Oracle will correct any mistakes in the statement itself
11. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, manager_id)
What extra data will this query generate?
Mark for Review
(1) Points
Subtotals for department_id, and grand totals for salary.
Subtotals for department_id, job_id and grand totals for salary.
Subtotals for department_id, job_id, manager_id and grand totals for salary.
The statement will fail. (*)
( Vrei sa traduci ceva?Want to translate something? http://translate.google.ro/# )
1. Which of the following SQL statements could display the number of people with the same last name: Mark for Review
(1) Points
SELECT first_name, last_name, COUNT(employee_id)
FROM EMPLOYEES
GROUP BY last_name;
SELECT employee_id, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
(*)
SELECT employee_id, DISTINCT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
2. The use of GROUP BY GROUPING SETS(....) can speed up the execution of complex report statements? (True or False) Mark for Review
(1) Points
True (*)
False
3. The following is a valid statement:
SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;
True or False?
Mark for Review
(1) Points
True (*)
False
4. Read the following SELECT statement. Choose the column or columns that must be included in the GROUP BY clause.
SELECT COUNT(last_name), grade, gender
FROM STUDENTS
GROUP_BY ?????;
Mark for Review
(1) Points
last_name
last_name, grade
grade, gender (*)
last_name, gender
5. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS((department_id, manager_id), (department_id, job_id))
What data will this query generate?
Mark for Review
(1) Points
Total salaries for (department_id, job_id) and (department_id, manager_id) (*)
Total salaries for (department_id, job_id, manager_id)
Total for (job_id, manager_id)
The statement will fail.
6. If you want to include subtotals and grant totals for all columns mentioned in a GROUP BY clause you should use which of the following extensions to the GROUP BY clause? Mark for Review
(1) Points
ROLLUP
CUBE (*)
GROUP BY ALL COLUMNS
HAVING
7. Is the following statement correct?
SELECT department_id, AVG(salary)
FROM employees;
Mark for Review
(1) Points
No, because a GROUP BY department_id clause is needed (*)
No, because the SELECT clause cannot contain both individual columns and group functions
No, because the AVG function cannot be used on the salary column
Yes
8. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS(.......);
Select the correct GROUP BY GROUPING SETS clause from the following list:
Mark for Review
(1) Points
GROUP BY GROUPING SETS (department_id, AVG(salary)), (department_id, job_id), (department_id, manager_id)
GROUP BY GROUPING SETS (department_id, salary), (department_id, job_id), (department_id, manager_id)
GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, job_id), (manager_id, job_id)) (*)
GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, SUM(salary), (manager_id, job_id))
9. How would you alter the following query to list only employees where more than one employee exists with the same last_name:
SELECT last_name, COUNT(employee_id)
FROM EMPLOYEES
GROUP BY last_name;
Mark for Review
(1) Points
SELECT last_name, COUNT(employee_id)
FROM EMPLOYEES
WHERE COUNT(*) > 1
GROUP BY last_name
SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name
HAVING COUNT(last_name) > 1;
(*)
SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name
EXISTS COUNT(last_name) > 1;
SELECT employee_id, DISTINCT(last_name)
FROM EMPLOYEES
GROUP BY last_name
HAVING last_name > 1;
10. Is the following statement correct:
SELECT first_name, last_name, salary, department_id, COUNT(employee_id)
FROM employees
WHERE department_id = 50
GROUP BY last_name, first_name, department_id;
Mark for Review
(1) Points
Yes
No, beause you cannot have a WHERE-clause when you use group functions.
No, because the statement is missing salary in the GROUP BY clause (*)
Yes, because Oracle will correct any mistakes in the statement itself
11. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, manager_id)
What extra data will this query generate?
Mark for Review
(1) Points
Subtotals for department_id, and grand totals for salary.
Subtotals for department_id, job_id and grand totals for salary.
Subtotals for department_id, job_id, manager_id and grand totals for salary.
The statement will fail. (*)
( Vrei sa traduci ceva?Want to translate something? http://translate.google.ro/# )
joi, 4 martie 2010
The Piano - Amazing Short
( Vrei sa traduci ceva?Want to translate something? http://translate.google.ro/# )
NASA a pozat o... "gorila" extraterestra!
Se pare ca cei de la NASA au inceput sa "vaneze maimute" pe Marte :)). Eu unu ma mir ca n'au vazut iepuri :)) sau vreun elefant :)))
Internautii sunt convinsi ca nu este vorba de o stanca de genul Sfinxului din Bucegi, cum ati fi tentati sa credeti.
De exemplu, Nigel Cooper, in varsta de 43 de ani, a declarat ca este sigur ca NASA a surprins o entitate vie si e absolut convins ca pe Marte exista deja forme de viata evoluate.
Voi ce credeti? este o gorila? Sau iar s'a jucat cineva in photoshop ? :))
luni, 1 martie 2010
Abonați-vă la:
Postări (Atom)