Scribd
Upload
6 views4 pages

PostgreSQL Java Stream Reference Guide

The document provides a complete reference for PostgreSQL and Java Stream, including table definitions for BANK_BRANCH, ACCOUNT_TYPE, and ACCOUNT. It also includes Java Stream examples for grouping and counting elements, as well as advanced PostgreSQL Common Table Expressions (CTEs) for various queries such as account type summaries, top branches by balance, and customers with multiple account types. Each SQL example demonstrates practical applications of data aggregation and filtering in a banking context.

Uploaded by

joejokelly
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
6 views4 pages

PostgreSQL Java Stream Reference Guide

The document provides a complete reference for PostgreSQL and Java Stream, including table definitions for BANK_BRANCH, ACCOUNT_TYPE, and ACCOUNT. It also includes Java Stream examples for grouping and counting elements, as well as advanced PostgreSQL Common Table Expressions (CTEs) for various queries such as account type summaries, top branches by balance, and customers with multiple account types. Each SQL example demonstrates practical applications of data aggregation and filtering in a banking context.

Uploaded by

joejokelly
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

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 [Link];
import [Link];
import [Link];
import [Link];
import [Link];

public class IntQuest1x {


public static void main(String[] args) {
[Link]("IntQuest1x");

List<String> listStr = [Link](


"ew", "swg", "pwx4", "ew", "pwx4", "swg", "swg", "pwx4", "pwx4"
);

[Link]().forEach(x -> [Link](x));

Map<String, Long> collect = [Link]()


.collect([Link]([Link](),
[Link]()));

[Link](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
[Link],
AT.TYPE_NAME,
COUNT(*) AS TOTAL_ACCOUNTS
FROM ACCOUNT A
JOIN BANK_BRANCH BB ON [Link] = [Link]
JOIN ACCOUNT_TYPE AT ON A.TYPE_ID = AT.TYPE_ID
GROUP BY [Link], 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
[Link],
SUM([Link]) AS TOTAL_BALANCE
FROM BANK_BRANCH BB
LEFT JOIN ACCOUNT A ON [Link] = [Link]
GROUP BY [Link]
),
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 [Link]
FROM ACCOUNT A
JOIN ACCOUNT_TYPE AT ON A.TYPE_ID = AT.TYPE_ID
WHERE AT.TYPE_NAME = 'Current'
)
SELECT [Link]
FROM BANK_BRANCH BB
LEFT JOIN BranchWithCurrent BWC ON [Link] = [Link]
WHERE [Link] 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([Link]) 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
[Link],
AVG([Link]) AS BRANCH_AVG
FROM BANK_BRANCH BB
JOIN ACCOUNT A ON [Link] = [Link]
GROUP BY [Link]
),
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;

You might also like