Advanced Queries in PostgreSQL

Untuk mengelola database dengan lebih efisien, berikut beberapa query lanjutan di PostgreSQL.

1. JOIN Query

SELECT users.name, orders.amount 
FROM users 
JOIN orders ON users.id = orders.user_id;

2. Subquery

SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

3. Aggregation Query

SELECT COUNT(*), AVG(amount) FROM orders;

4. Indexing

CREATE INDEX idx_users_email ON users(email);

5. Stored Procedures

CREATE FUNCTION get_total_orders() RETURNS integer AS $$
    SELECT COUNT(*) FROM orders;
$$ LANGUAGE SQL;

6. Window Functions

SELECT name, SUM(amount) OVER (PARTITION BY user_id) FROM orders;

7. Transactions

BEGIN;
UPDATE users SET name = 'Updated Name' WHERE id = 1;
COMMIT;

Dengan memahami query lanjutan ini, Anda dapat mengoptimalkan pengelolaan database PostgreSQL.