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

Introduction

This tutorial aims to guide you through advanced database programming concepts, specifically focusing on subqueries in SQL. Understanding subqueries is essential for performing complex data retrievals and enhancing the efficiency of your database queries. This guide will help you grasp the concept of subqueries and how to implement them effectively.

Step 1: Understanding Subqueries

• Define a subquery as a query nested inside another SQL query.
• Explain the two types of subqueries:
  • Single-row subqueries: Return a single value.
  • Multiple-row subqueries: Return multiple values.

Practical Tip

• Use subqueries when you need to compare a value against a set of values returned by another query.

Step 2: Writing a Basic Subquery

• Start with a simple example of a subquery.
• Structure the SQL command as follows:

SELECT column_name
FROM table_name
WHERE column_name = (SELECT column_name FROM table_name WHERE condition);

• Replace column_name, table_name, and condition with your specific database fields and criteria.

Common Pitfall

• Avoid using subqueries in the SELECT clause when simple joins can achieve the same result, as this can lead to performance issues.

Step 3: Using Subqueries in Different Clauses

• Subqueries can be utilized in various SQL clauses:
  • WHERE clause: To filter results based on another query.
  • FROM clause: To treat the result of a subquery as a temporary table.
  • SELECT clause: To return calculated values.

Example of a Subquery in the FROM Clause

SELECT AVG(salary)
FROM (SELECT salary FROM employees WHERE department = 'Sales') AS sales_salaries;

Step 4: Correlated Subqueries

• Explain that a correlated subquery references columns from the outer query.
• Show an example:

SELECT employee_name
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);

Practical Advice

• Use correlated subqueries for comparisons that depend on the outer query's current row.

Step 5: Combining Subqueries with JOINs

• Discuss how to enhance data retrieval by combining subqueries and JOIN operations.
• Provide an example:

SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = d.id);

Conclusion

Subqueries are powerful tools for executing advanced queries in SQL. By understanding the different types of subqueries and how to implement them effectively, you enhance your ability to manipulate and retrieve data efficiently. Next, consider practicing these concepts in your database projects to solidify your understanding and explore further advanced SQL techniques.