SQL Foundations I Module 2

Use HAVING clause with GROUP BY

Only editable by group admins

  • Last updated August 24, 2016 at 1:05 PM
  • Evidence visible to public
Two queries will filter aggregate results with a HAVING clause. Provide a link to your work.

All posted evidence

Having It passed!

select subject_id, AVG(grade_num) from grade group by subject_id HAVING AVG(grade_num) > 1   SELECT name, AVG(salary) FROM teacher group by name HAVING AVG(salary) > 1   SELECT teach_id, AVG(salary) FROM teacher group by name HAVING min(salary) > 80000

https://parkland1-my.sharepoint.com/:w:/r/personal/sdow3_stu_parkland_edu/_layouts/15/Doc.aspx?sourcedoc=%7B49198A6F-18AB-40C0-BF1C-94B3CD5DF86B%7D&file=Module%202_%20CSC176%20Data%20Systems_Sharon%20Dow%20102318.docx&action=default&mobileredirect=true

sdow3 About 7 years ago

One last time

http://sqlfiddle.com/#!9/ff5c2f/38
mpurnell1 About 8 years ago

(Hopefully) Final tweaks

http://sqlfiddle.com/#!9/ff5c2f/33

http://sqlfiddle.com/#!9/ff5c2f/34
mpurnell1 About 8 years ago

2 tweaked queries using HAVING

http://sqlfiddle.com/#!9/ff5c2f/28

http://sqlfiddle.com/#!9/ff5c2f/29
mpurnell1 About 8 years ago

Query 2

SQL Fiddle

SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.

Application for testing and sharing SQL queries.
mpurnell1 About 8 years ago

Query 1

SQL Fiddle

SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.

Application for testing and sharing SQL queries.
mpurnell1 About 8 years ago

Query 1 & 2 Results

jmfisher About 8 years ago

My HAVING, GROUP BY Statements

/*Query 1 - Show Number of signs for each type of material*/
SELECT  Materials.name as 'Material', SUM(Signs.quantity) as 'Total'
FROM Signs 
INNER JOIN Materials ON Signs.mat_id=Materials.mat_id
GROUP BY Signs.mat_id HAVING COUNT(*)>0;
/*Query 2 - Show Number of Banners That Need Grommets or Power Tabs*/
SELECT CONCAT(Signs.hor_dimens, ' ft x ', Signs.vert_dimens, ' ft') as 'Banners', Finishes.name as 'Finish', SUM(Signs.quantity) as 'Total'
FROM Signs
INNER JOIN Finishes ON Signs.finish_id=Finishes.finish_id
WHERE (Signs.finish_id=1 or Signs.finish_id=2) and (Signs.hor_dimens>4 or Signs.vert_dimens>4) GROUP BY Signs.hor_dimens,Signs.vert_dimens,Signs.finish_id HAVING COUNT(*)>0;
jmfisher About 8 years ago

Added a restriction to only show data for directories HAVING less than 3 files to one of my statements submitted for Task 4.

jaco Almost 9 years ago

Added a HAVING (less than 10 characters in filename) clause to one of my submissions for Task 4 that already contained GROUP BY.

jaco Almost 9 years ago

2 having queries, specifying that an SQL SELECT statement should only return rows where aggregate values meet constraints, and GROUP BY

SELECT title, year_released
FROM movies
HAVING MAX(year_released)
vbecu1 Almost 9 years ago

Task 5 REVISED

Link to revised queries:
https://drive.google.com/open?id=0B5hBlRd1P7vEdk9zdzZRNVBpaDQ

SELECT CustomerID, COUNT(*) AS NumberOfOrders FROM Orders GROUP BYCustomerID HAVING COUNT(*) > 1;
SELECT ItemNumber, ItemDescription, SUM(NumberInStock) ASNumberOfProductsInStock FROM Products GROUP BY ItemNumber HAVINGSUM(NumberInStock) > 10;
slewis52 Almost 9 years ago