17 Building a Query to Fetch Simple Data

As an engineer, the ability to write queries is an essential skill for interacting with a database. In this article, we’ll explore how to build a simple query to fetch data. The goal is to establish a strong foundation so you can work with data effectively. I’ll also share a few simple case studies using SQL to make your understanding more practical.

Why Are Queries Important?

A query is an instruction sent to a database management system (DBMS) to retrieve, insert, update, or delete data. In the real world, most applications—from web and mobile to enterprise—rely on data returned by queries to function properly.

Simple everyday examples:

• Displaying a list of products in an online store
• Viewing transaction history in a finance app
• Showing user profiles in a social media app

All of these are powered by queries.

Basic Query Structure

As an engineer, you’ll start with the most fundamental SQL command: SELECT.

1SELECT kolom1, kolom2 FROM nama_tabel WHERE kondisi;

The Components

Case Study: Fetching Employee Data

Let’s use the following sample data.

Simulated karyawan Table

1. Fetching All Data

The most basic query retrieves all columns and all rows:

1SELECT * FROM karyawan;

Result:

2. Fetching Specific Columns

Often we only need certain columns.

1SELECT nama, departemen FROM karyawan;

Result:

3. Filtering Data with WHERE

Filter the data for the IT department only:

1SELECT * FROM karyawan WHERE departemen = 'IT';

Result:

4. Sorting Data

Sort by the highest salary:

1SELECT nama, gaji FROM karyawan ORDER BY gaji DESC;

Result:

5. Limiting Data

Fetch the 3 employees with the longest tenure:

1SELECT nama, tanggal_masuk FROM karyawan ORDER BY tanggal_masuk ASC LIMIT 3;

Result:

6. Counting the Number of Records

Count the number of employees in the IT department:

1SELECT COUNT(*) AS jumlah_IT FROM karyawan WHERE departemen = 'IT';

7. Example: Simulating a More Complex Query

Fetch the names of IT employees whose salary is above the average salary of all IT employees.

1SELECT nama, gaji
2FROM karyawan
3WHERE departemen = 'IT'
4AND gaji > (
5    SELECT AVG(gaji) FROM karyawan WHERE departemen = 'IT'
6);

8. Query Process Flow Diagram

Often we need to visualize the steps of a simple query on the backend:

flowchart TD
    A[Client Kirim Request] --> B(Proses Query di App)
    B --> C{Perlu Filter?}
    C -- Ya --> D(Query dengan WHERE)
    C -- Tidak --> E(Query tanpa WHERE)
    D --> F{Perlu Query Lanjutan?}
    E --> F
    F -- Ya --> G(Nested Query/Subquery)
    F -- Tidak --> H(Hasil Dikirim ke Client)

flowchart TD
    A[Client Kirim Request] --> B(Proses Query di App)
    B --> C{Perlu Filter?}
    C -- Ya --> D(Query dengan WHERE)
    C -- Tidak --> E(Query tanpa WHERE)
    D --> F{Perlu Query Lanjutan?}
    E --> F
    F -- Ya --> G(Nested Query/Subquery)
    F -- Tidak --> H(Hasil Dikirim ke Client)

The diagram above illustrates how an application handles a simple data request, then decides whether it needs to apply a filter or a subquery.

9. Engineer Tips for Building Simple Queries

1. Always use LIMIT when fetching data in a production environment
2. Retrieve only the columns you need
3. Use WHERE to reduce the load on the database
4. Test queries in development before applying them to production
5. Use aliases to make the results easier to read

10. Real-World Case Study: Employee Dashboard

Imagine you want to display employee data for an admin dashboard. Typically, users want to:

• See the total number of employees per department
• View a list of recently hired employees
• Identify the employees with the highest salaries

Here are some examples:

Total per Department

1SELECT departemen, COUNT(*) AS jumlah
2FROM karyawan
3GROUP BY departemen;

Employees Hired in the Last 6 Months

1SELECT nama, tanggal_masuk
2FROM karyawan
3WHERE tanggal_masuk >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

Highest-Paid Employee

1SELECT nama, gaji
2FROM karyawan
3ORDER BY gaji DESC
4LIMIT 1;

11. Conclusion

Building simple queries is a must-have skill for any engineer who wants to work with data. Mastering the fundamental commands SELECT, WHERE, ORDER BY, and aggregates like COUNT will help you design and build efficient data-driven applications.

Don’t underestimate these basics—because a well-written query can save your production application from dangerous data bottlenecks.

If you have an interesting query case study, share it in the comments! There’s always something new to learn from data and query logic. Happy coding, and may your data always stay clean and your queries always run optimally 🚀