Log out Go to App
Go to App

Learning Materials

Features

Discover

Chapter 6: Problem 12

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

Expert verified
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

01

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

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

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.

Unlock Step-by-Step Solutions & Ace Your Exams!

  • Full Textbook Solutions

    Get detailed explanations and key concepts

  • Unlimited Al creation

    Al flashcards, explanations, exams and more...

  • Ads-free access

    To over 500 millions flashcards

  • Money-back guarantee

    We refund you if you fail your exam.

Start your free trial

Over 30 million students worldwide already upgrade their learning with Vaia!

One App. One Place for Learning.

All the tools & learning materials you need for study success - in one app.

Get started for free

Most popular questions from this chapter

Consider the following SQL statements and show how each statement would appear in Design View. You can confirm your result if you create a query, switch to SQL View, type the query statement and then switch to Design View. Unfortunately, if you make any syntax errors, Access will be unable to switch to Design View. Your database must contain the tables in the From clause. 1\. SELECT Products.ProductID, Products.ProductName, Categories.CategoryName FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID WHERE Categories.CategoryName = "Beverages" AND Products.Discontinued = Yes; 2\. SELECT Auto.Year, Dealer.name FROM Auto INNER JOIN Dealer ON Auto.Did = Dealer.Did WHERE Auto.Price > 10000 OR Auto.Colour = “blue” ; 3\. SELECT Auto.Year, Dealer.name FROM Auto RIGHT OUTER JOIN Dealer ON Auto.Did = Dealer.Did WHERE Auto.Price > 10000 OR Auto.Colour = “blue” ;

Consider the University database: 1\. For each department list the department code and the largest value for credit hours. 2\. For each department list the department code, department name, and the number of courses.

Consider the Genealogy database. 1\. Create a query with two parameters: a start date and an end date. The query will list all persons whose birth dates fall in the range from start date to end date.

Consider the University database. 1\. Create a query to list all courses in a department (for which the user supplies the department code). 2\. Create a query to list all course titles where the user supplies both the department code and the credit hours. Note that two separate criteria, each with their own parameter, must be specified.

Consider the Company database. 1\. Write a query to list the employees who manage a department where the department code is provided by the person running the query. 2\. Write a query to list all employees in some department where the department code is provided by the person running the query. 3\. Modify the employee data in the Company database so at least two employees have the same first and last names. Develop a query that lists all employees having a specific first name and last name that will be specified by the end user.
See all solutions

Recommended explanations on Computer Science Textbooks

Functional Programming

Read Explanation

Problem Solving Techniques

Read Explanation

Game Design in Computer Science

Read Explanation

Big Data

Read Explanation

Data Structures

Read Explanation

Data Representation in Computer Science

Read Explanation
View all explanations

What do you think about this solution?

We value your feedback to improve our textbook solutions.

Study anywhere. Anytime. Across all devices.

Sign-up for free