Pertemuan 4 - Pemrograman Basis Data : Query Lanjutan (Aggregates)

Introduction

This tutorial provides a step-by-step guide on advanced database programming with a focus on aggregate queries. It is designed for those looking to enhance their SQL skills, particularly in manipulating and extracting information from databases using aggregate functions. By the end of this guide, you'll understand how to utilize these functions effectively in your data queries.

Step 1: Understanding Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single value. Here are some key aggregate functions to know:

• COUNT: Counts the number of rows that match a specified condition.
• SUM: Adds up the values in a numeric column.
• AVG: Calculates the average value of a numeric column.
• MAX: Finds the maximum value in a column.
• MIN: Finds the minimum value in a column.

Practical Tip

Always consider the context of your dataset when selecting an aggregate function to ensure accurate results.

Step 2: Writing Basic Aggregate Queries

To write a basic aggregate query, follow these steps:

1. Select the Aggregate Function: Choose the function you want to use (e.g., COUNT, SUM).
2. Specify the Table: Identify the table you want to query.
3. Apply Conditions: Use the WHERE clause to filter results if needed.
4. Group Data: Use the GROUP BY clause to group the results based on one or more columns.

Example Query

SELECT COUNT(*) 
FROM employees 
WHERE department = 'Sales';

Step 3: Using GROUP BY with Aggregate Functions

Grouping results allows you to perform aggregate calculations on subsets of data. Here’s how to do it:

1. Choose the Columns: Decide which columns to group by.
2. Combine with Aggregate Functions: Use an aggregate function to summarize the grouped data.

Example Query

SELECT department, COUNT(*) AS employee_count 
FROM employees 
GROUP BY department;

Step 4: Implementing HAVING Clause

The HAVING clause filters results after the GROUP BY clause has been applied. This is useful for filtering groups based on aggregate values.

Steps to Use HAVING

1. Write a GROUP BY Query: Start with your GROUP BY query.
2. Add HAVING Clause: Include the HAVING clause to set conditions on the grouped results.

Example Query

SELECT department, AVG(salary) AS average_salary 
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 50000;

Step 5: Combining Multiple Aggregate Functions

You can use multiple aggregate functions in a single query to gain deeper insights.

Steps to Combine Functions

1. Select Multiple Functions: Choose the aggregate functions you want to use.
2. Ensure Proper Grouping: Group by relevant columns to avoid errors.

Example Query

SELECT department, COUNT(*) AS employee_count, AVG(salary) AS average_salary 
FROM employees 
GROUP BY department;

Conclusion

In this tutorial, we covered the essentials of advanced SQL queries using aggregate functions. You learned how to write basic aggregate queries, use the GROUP BY and HAVING clauses, and combine multiple aggregate functions.

As a next step, practice these concepts on your own dataset to solidify your understanding and explore more complex queries to enhance your database programming skills.