PostgreSQL and Java Stream – Complete Reference Notes
1. Table Definitions
CREATE TABLE BANK_BRANCH (
BCODE VARCHAR(10) PRIMARY KEY,
BNAME VARCHAR(100),
CITY VARCHAR(50)
);
CREATE TABLE ACCOUNT_TYPE (
TYPE_ID INT PRIMARY KEY,
TYPE_NAME VARCHAR(50)
);
CREATE TABLE ACCOUNT (
ACC_NO INT PRIMARY KEY,
CUST_NAME VARCHAR(100),
BALANCE DECIMAL(10, 2),
BCODE VARCHAR(10),
TYPE_ID INT,
FOREIGN KEY (BCODE) REFERENCES BANK_BRANCH(BCODE),
FOREIGN KEY (TYPE_ID) REFERENCES ACCOUNT_TYPE(TYPE_ID)
);
2. Java Stream Example – Grouping with Counting
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;
public class IntQuest1x {
public static void main(String[] args) {
System.out.println("IntQuest1x");
List<String> listStr = Arrays.asList(
"ew", "swg", "pwx4", "ew", "pwx4", "swg", "swg", "pwx4", "pwx4"
);
listStr.stream().forEach(x -> System.out.println(x));
Map<String, Long> collect = listStr.stream()
.collect(Collectors.groupingBy(Function.identity(),
Collectors.counting()));
System.out.println(collect);
}
}
3. Advanced PostgreSQL CTE Examples
1. Branch-wise Account Type Summary
Show how many accounts of each type exist in every branch.
WITH AccountTypeCounts AS (
SELECT
BB.BNAME,
AT.TYPE_NAME,
COUNT(*) AS TOTAL_ACCOUNTS
FROM ACCOUNT A
JOIN BANK_BRANCH BB ON A.BCODE = BB.BCODE
JOIN ACCOUNT_TYPE AT ON A.TYPE_ID = AT.TYPE_ID
GROUP BY BB.BNAME, AT.TYPE_NAME
)
SELECT *
FROM AccountTypeCounts
ORDER BY BNAME, TYPE_NAME;
2. Top 3 Branches by Total Balance
Find the top 3 branches ranked by the sum of their account balances.
WITH BranchBalances AS (
SELECT
BB.BNAME,
SUM(A.BALANCE) AS TOTAL_BALANCE
FROM BANK_BRANCH BB
LEFT JOIN ACCOUNT A ON BB.BCODE = A.BCODE
GROUP BY BB.BNAME
),
RankedBranches AS (
SELECT *,
RANK() OVER (ORDER BY TOTAL_BALANCE DESC) AS BAL_RANK
FROM BranchBalances
)
SELECT *
FROM RankedBranches
WHERE BAL_RANK <= 3;
3. Branches with No 'Current' Accounts
List all branches that do not have any accounts of type 'Current'.
WITH BranchWithCurrent AS (
SELECT DISTINCT A.BCODE
FROM ACCOUNT A
JOIN ACCOUNT_TYPE AT ON A.TYPE_ID = AT.TYPE_ID
WHERE AT.TYPE_NAME = 'Current'
)
SELECT BB.BNAME
FROM BANK_BRANCH BB
LEFT JOIN BranchWithCurrent BWC ON BB.BCODE = BWC.BCODE
WHERE BWC.BCODE IS NULL;
4. Total Balance and Account Count Per Account Type
Aggregate balances and account counts grouped by account type.
WITH TypeSummary AS (
SELECT
AT.TYPE_NAME,
COUNT(A.ACC_NO) AS TOTAL_ACCOUNTS,
SUM(A.BALANCE) AS TOTAL_BALANCE
FROM ACCOUNT A
JOIN ACCOUNT_TYPE AT ON A.TYPE_ID = AT.TYPE_ID
GROUP BY AT.TYPE_NAME
)
SELECT * FROM TypeSummary;
5. Branches Where Average Balance > Overall Average
Show branches whose average account balance is above the global average.
WITH BranchAvg AS (
SELECT
BB.BNAME,
AVG(A.BALANCE) AS BRANCH_AVG
FROM BANK_BRANCH BB
JOIN ACCOUNT A ON BB.BCODE = A.BCODE
GROUP BY BB.BNAME
),
OverallAvg AS (
SELECT AVG(BALANCE) AS ALL_AVG FROM ACCOUNT
)
SELECT BA.*
FROM BranchAvg BA, OverallAvg OA
WHERE BA.BRANCH_AVG > OA.ALL_AVG;
6. Customers Holding Multiple Account Types
Identify customers who hold more than one type of account.
WITH TypeCountPerCustomer AS (
SELECT
CUST_NAME,
COUNT(DISTINCT TYPE_ID) AS TYPE_COUNT
FROM ACCOUNT
GROUP BY CUST_NAME
)
SELECT *
FROM TypeCountPerCustomer
WHERE TYPE_COUNT > 1;
