Question

Consider the last example where the number of courses per department is listed. The sample database is small and so many departments have just 1 course. Modify the query to list results only for departments where there is more than 1 course. For this you must include a criteria >1 for the field where COUNT is specified:

Short answer

To modify the query to list results only for departments where there is more than 1 course, the HAVING clause should be used along with the COUNT function. The final query should look like this: SELECT department_name, COUNT(*) AS number_of_courses FROM courses GROUP BY department_name HAVING COUNT(*) > 1. This will return a list of departments with more than one course along with the number of courses in each department.

Step-by-step solution

Understand the Current Query

The first thing you do is understand the existing SQL query which lists the number of courses per department. Assume it looked something like this: SELECT department_name, COUNT(*) AS number_of_courses FROM courses GROUP BY department_name. This query is returning a list of departments with the number of courses in each department.

Add a Criteria to Filter Results

In order to list only those departments with more than one course, a filter needs to be added to this query. This can be done using the HAVING clause which is used with the GROUP BY clause to filter the results of a query that includes aggregate functions like COUNT. The HAVING clause specifies a search condition for a group. The condition is that the COUNT of courses should be greater than 1. The adjusted query becomes: SELECT department_name, COUNT(*) AS number_of_courses FROM courses GROUP BY department_name HAVING COUNT(*) > 1.

Interpret the Results

After executing this query, the resulting list would display only those departments where there are more than one course. Each department name would be listed along with the number of courses that exist in each department. Departments with only one course are excluded from these results because of the HAVING clause in the query.

Key concepts

HAVING Clause

The HAVING clause is key to filtering results in SQL queries that use aggregate functions. It works similar to the WHERE clause, but with a crucial difference: the HAVING clause is designed for use with grouped data. This means it allows you to apply conditions to groups rather than individual rows.
When you group data using the GROUP BY clause and want to include only certain groups based on the result of an aggregate function, the HAVING clause comes into play. For example, if you want to list departments with more than one course, you can use the HAVING clause to filter out groups where the course count does not meet that criterion.
This clause is typically used in conjunction with aggregate functions like COUNT, SUM, AVG, etc. to restrict which groups are returned based on the aggregate value criteria. So, whenever you need to filter aggregated results, the HAVING clause is your go-to tool.

GROUP BY Clause

The GROUP BY clause is instrumental in organizing data into specified groups within SQL queries. Essentially, it serves as a tool for grouping the result set based on one or more columns. This is particularly useful when you want to calculate statistics or apply aggregate functions to data split into various categories.
When you include the GROUP BY clause, the database engine collects all identical values in the specified column(s) and places them into one group. Each group is treated as a single unit, allowing you to apply aggregate functions efficiently for each group.

• It’s often used with aggregate functions to perform calculations on each group.
• The clause ensures that the data is ordered by the grouped fields, which can simplify understanding complex data sets.
• It allows for cleaner, more organized results by breaking down large quantities of data into more manageable sections with summarized information.

In the example given, the GROUP BY department_name clause organizes all courses by their respective departments, facilitating the use of aggregate functions like COUNT to sum up the courses per department.

Aggregate Functions

Aggregate functions in SQL are powerful tools used for returning single values from multiple rows. They condense large amounts of data into more digestible and meaningful information. Common aggregate functions include COUNT, SUM, AVG, MIN, and MAX.
Each function has specialized uses:

• COUNT: Returns the number of rows that match a specified criterion, helping to quantify data, such as counting courses per department.
• SUM: Adds all numeric values in a column, useful for financial data or totals, like calculating total sales.
• AVG: Calculates the average of a set of values, ideal for finding average scores or prices.
• MIN/MAX: Identifies the smallest or largest value in a particular column, useful in competitive scenarios or determining price ranges.

When used with GROUP BY, these functions can provide insights into grouped data subsets. In the scenario where you need to count courses per department but only show departments with more than one course, COUNT with a HAVING clause filters for the specific threshold needed.