HAVING | BUS 104 – SQL Exam

HAVING explained

Meet Mary, an avid runner and fitness guru.

Mary running in a park

Mary is competing to be selected to participate in The Best Race Ever against 2 other runners, Sam and David.

Sam and David running in a park

Each runner must run a mile 3 times and record their times.

Mary ran the following times: 8:05, 7:45, 8:07.

Sam ran the following times: 8:17, 8:01, 7:54.

David ran the following times: 7:48, 8:00, 8:06.

In order to make the cut to run in The Best Race Ever, the runner's average mile time must be below 8 minutes.

So, let's find each runners' average time. First, we'll convert their times to seconds.

Mary: 485, 465, 487
Sam: 497, 481, 474
David: 468, 480, 492

Next, we'll find the average seconds…

Mary: (485 + 465 + 487) / 3 = 479 avg. seconds
Sam: (497 + 481 + 474) / 3 = 484 avg. seconds
David: (468 + 480 + 486) / 3 = 478 avg. seconds

…and divide by 60 seconds to find the average minutes.

Mary: 479 / 60 = 7.98 avg. minutes
Sam: 484 / 60 = 8.07 avg. minutes
David: 478 / 60 = 7.97 avg. minutes

Now, let's cross out any times that aren't below 8 minutes.

Mary: 479 / 60 = 7.98 avg. minutes
Sam: 484 / 60 = 8.07 avg. minutes
David: 478 / 60 = 7.97 avg. minutes

Looks like Mary and David made the cut for The Best Race Ever!

In this situation, we essentially performed the purpose of a HAVING statement. We took groups of running times for each runner, averaged them, and then filtered out any results that weren't below 8 minutes.

HAVING statements are used to filter the results of a query, based on aggregate function outputs.

How to code a HAVING

Here's the template for a HAVING statement:

PAID CONTENT

This is the end of the preview. To unlock the rest, get the SQL Cram Kit or Lifetime Access.

Already purchased? Click here to log in.

Leave a Comment