Skip to Main Content

SQL query to return name and balance of all users having balance greater than 10000

User_X9S6MDec 7 2022

CREATE TABLE ACCOUNTS(ACC_ID,NAME) AS
(
SELECT 1,'ALICE' FROM DUAL UNION ALL
SELECT 2,'BOB' FROM DUAL UNION ALL
SELECT 3,'CHARLIE' FROM DUAL
)

CREATE TABLE TRANSACTIONS(TRAN_ID,ACC_ID,AMOUNT) AS
(
SELECT 1,1,7000 FROM DUAL UNION ALL
SELECT 2,1,10000 FROM DUAL UNION ALL
SELECT 3,1,-6000 FROM DUAL UNION ALL
SELECT 4,2,8000 FROM DUAL UNION ALL
SELECT 5,3,4000 FROM DUAL UNION ALL
SELECT 6,3,-1000 FROM DUAL UNION ALL
SELECT 7,3,9000 FROM DUAL UNION ALL
SELECT 8,3,5000 FROM DUAL
)

My effort :
SELECT
A.NAME AS NAME,
SUM(B.AMOUNT) TOT_AMOUNT
FROM ACCOUNTS A JOIN TRANSACTIONS B
ON A.ACC_ID = B.ACC_ID
GROUP BY A.ACC_ID
HAVING TOT_AMOUNT >10000;

Result table:
Name Balance
Alice 11000
Charlie 17000
ORA-00904: "TOT_AMOUNT": invalid identifier

This post has been answered by Hub Tijhuis on Dec 7 2022
Jump to Answer
Comments
Post Details
Added on Dec 7 2022
4 comments
253 views