31.
How will the results of the following two statements differ?
Statement 1:
SELECT MAX(longitude), MAX(latitude)
FROM zip_state_city;
Statement 2:
SELECT MAX(longitude), MAX(latitude)
FROM zip_state_city
GROUP BY state;
- [A]Statement 1 will fail because it is missing a GROUP BY clause.
- [B]Statement 2 will return one row, and statement 1 may return more than one row.
- [C]Statement 2 will fail because it does not have the columns used in the GROUP BY clause in the SELECT clause.
- [D]Statement 1 will display two columns, and statement 2 will display two values for each state.
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| Though you do not have a state column in the SELECT clause, having it in the GROUP BY clause will group the results by state, so you end up getting two values (two columns) for each state. |
32.
Which line in the following SQL has an error?
1 SELECT department_id, SUM(salary) 2 FROM employees 3 WHERE department_id <> 40 4 ORDER BY department_id;
- [A]1
- [B]3
- [C]4
- [D]No errors in SQL
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| Since the department_id column does not have any aggregate function applied to it, it must appear in the GROUP BY clause. The ORDER BY clause in the SQL must be replaced with a GROUP BY clause to make the query work. |
33.
Admin is trying to find out the average salary of employees in each department. He noticed
that the SALARY column can have NULL values, and he does not want the NULLs included
when calculating the average. Identify the correct SQL that will produce the desired results.
- [A]
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;
- [B]
SELECT department_id, AVG(NVL(salary,0)) FROM employees GROUP BY department_id;
- [C]
SELECT department_id, NVL(AVG(salary), 0) FROM employees GROUP BY department_id;
- [D]
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING salary IS NOT NULL;
- Answer & Explanation
- Report
Answer : [A]
Explanation :
Explanation :
| Since group functions do not include NULL values in their calculation, you do not have to do anything special to exclude the NULL values. Only COUNT(*) includes NULL values. |
34.
Review the following two SQL statements, and choose the appropriate option.
FROM employees HAVING COUNT(*) > 10 GROUP BY department_id; 2. SELECT department_id, COUNT(*) FROM employees WHERE COUNT(*) > 10 GROUP BY department_id;
- [A]Statement 1 and statement 2 will produce the same results.
- [B]Statement 1 will succeed, and statement 2 will fail.
- [C]Statement 2 will succeed, and statement 1 will fail.
- [D]Both statements fail.
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| An aggregate function is not allowed in the WHERE clause. You can have the GROUP BY and HAVING clauses in any order, but they must appear after the WHERE clause. |
35.
Read the following SQL carefully, and choose the appropriate option. The JOB_ID column
shows the various jobs.
SELECT MAX(COUNT(*)) FROM employees GROUP BY job_id, department_id;
- [A]Aggregate functions cannot be nested.
- [B]The columns in the GROUP BY clause must appear in the SELECT clause for the query to work.
- [C]The GROUP BY clause is not required in this query.
- [D]The SQL will produce the highest number of jobs within a department.
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| The SQL will work fine and produce the result. Since group functions are nested, a GROUP BY clause is required. |
|
|
||||||||||||||||||||||||||||
