Saturday, October 15, 2016

23 Using GROUP BY and HAVING Clauses

  • ·         Used to specify which groups are to be displayed; restricts groups that do not meet group criteria-HAVING
  • ·         Divides the rows in a table into groups-GROUP BY

1.  In the SQL query shown below, which of the following is true about this query?

_______ a.  Kimberly Grant would not appear in the results set.
_______ b.  The GROUP BY clause has an error because the manager_id is not listed in the SELECT clause.
FALSE – GROUP function require that any column listed in the SELECT clause that is not part of group function must be listed in a GROUP BY Clause. But it doesn’t mandate presence of all columns in the group by clause in SELECT.
_______ c.  Only salaries greater than 16001 will be in the result set.
FALSE. Greater than or equal to 16001 may must come here. That too, ‘16001 or greater will come here’ only if the row is not filtered by WHERE before group application, AND also the whole group satisfy the HAVING clause.
_______ d.  Names beginning with Ki will appear after names beginning with Ko.
FALSE,  Names here means the last_name. ‘i’ is before  ‘o’ if DESC is skipped.
_______ e.  Last names such as King and Kochhar will be returned even if they don’t have salaries > 16000.
FALSE. The WHERE is applied even before the GROUP BY.
SELECT last_name, MAX(salary)
FROM employees
WHERE last_name LIKE 'K%'
GROUP BY manager_id, last_name
HAVING MAX(salary) >16000
ORDER BY last_name DESC ;

2.  Each of the following SQL queries has an error. Find the error and correct it. Use Oracle Application Express to verify that your corrections produce the desired results.

a.  SELECT manager_id
FROM employees
WHERE AVG(salary) <16000
GROUP BY manager_id;
Above query will say ORA-00934: group function is not allowed here.
If 'desired' result is getting manger ids, under whom, average salary is less than 16000:

SELECT manager_id,AVG(salary)
FROM employees
GROUP BY manager_id
HAVING AVG(salary) <16000;

b.  SELECT cd_number, COUNT(title)
FROM d_cds
WHERE cd_number < 93;
Above query will say ORA-00937: not a single-group group function

If ‘desired’ result is getting row count, excluding anything greater than or equal to 93:
FROM d_cds
WHERE cd_number < 93;

c.  SELECT ID, MAX(ID), artist AS Artist
FROM d_songs
WHERE duration IN('3 min', '6 min', '10 min')

Above query will say ORA-00979: not a GROUP BY expression

id is primary key of the table, so most likely my intention may not be to group by id here.
Desired may have been grouping by type_code and getting maximum duration
but limiting the input to group by with duration IN('3 min', '6 min', '10 min')  and ID < 50

SELECT type_code, MAX(TO_NUMBER(REPLACE(duration,' min',''))) || ' min' as "max duration"
FROM d_songs
WHERE duration IN('3 min', '6 min', '10 min') AND id < 50
GROUP BY type_code;

d.  SELECT loc_type, rental_fee AS Fee
FROM d_venues
WHERE id <100
1) Above query will say ORA-00904: "Fee": invalid identifier
2) Even if I replace "Fee" alias in group by [don't use alias in group by clause] it will give me error : ORA-00979: not a GROUP BY expression
3) Seems to be question wanted average rental_fee of each loc_type with id less than 100 before grouping.
I assumed rental_fee  is a number field:
SELECT loc_type, AVG(rental_fee) AS Fee
FROM d_venues
WHERE id <100
GROUP BY loc_type
But above query gives error ORA-01722: invalid number, because rental_fee is a VARCHAR2(50)
4) Now, I need assumptions to view the calculations with same eyes: /hour, /flat fee, /per person.
Let’s assume that we are group of 10 people and event I am organizing occurs for 5 hours.

SELECT loc_type, AVG(
 WHEN INSTR(rental_fee, '/hour') != 0 THEN  TO_NUMBER(REPLACE(rental_fee,'/hour',''))*5
  WHEN INSTR(rental_fee, '/flat fee') != 0 THEN  TO_NUMBER(REPLACE(rental_fee,'/flat fee',''))
  WHEN INSTR(rental_fee, '/per person') != 0 THEN  TO_NUMBER(REPLACE(rental_fee,'/per person',''))*10
) AS Fee
FROM d_venues
WHERE id <100
GROUP BY loc_type

3.  Rewrite the following query to accomplish the same result:

FROM d_track_listings
WHERE track IN ( 1, 2, 3);
SELECT track, MAX(song_id)
FROM d_track_listings
WHERE track IN ( 1, 2, 3)
GROUP BY track;

4.  Indicate True or False

_____ a.  If you include a group function and any other individual columns in a SELECT clause, then each individual column must also appear in the GROUP BY clause.

_____ b.  You can use a column alias in the GROUP BY clause.
FALSE.  query will say ORA-00904: "Some Alias": invalid identifier
_____ c.  The GROUP BY clause always includes a group function.
FALSE- GROUP BY clause of SQL statement contains the column name- value of which is used to divide input into subgroups, each subgroup represented as a row in output. The group function- goes in SELECT part. HAVING clause may also contain group function. If I put group function in group by clause I get error ‘ORA-00934: group function is not allowed here’ e.g. SELECT AVG(salary) FROM employees GROUP BY AVG(salary) HAVING AVG(salary) <16000; will give error.
5.  Write a query that will return both the maximum and minimum average salary grouped by department from the employees table.
SELECT ROUND(MAX(AVG(salary)),2) as "Maximum Average of Departments", ROUND(MIN(AVG(salary)),2) "Minimum Average of Departments"
FROM employees
GROUP BY department_id;

Verify MAX and MIN working against:

6.  Write a query that will return the average of the maximum salaries in each department for the employees table.

FROM employees
GROUP BY department_id;

Verify AVG working against:

No comments:

Post a Comment