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.

TRUE

_______ 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:

SELECT COUNT(*)

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')

HAVING ID < 50

GROUP by ID;

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

GROUP BY "Fee"

ORDER BY 2;

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

ORDER BY 2;

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(

CASE

 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

 ELSE 0

 END

) AS Fee

FROM d_venues

WHERE id <100

GROUP BY loc_type

ORDER BY 2;

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

SELECT DISTINCT MAX(song_id)

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.

TRUE

_____ 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.

SELECT AVG(MAX(salary))

FROM employees

GROUP BY department_id;

Verify AVG working against: