Skip to content
Advertisement

SQL Query Confused

Display:

  • account no.
  • customer id
  • initial amount in terms of lakhs (rounded off to 2 decimals) [change the column heading to ‘INITIALAMTIN_LAKHS’]
  • amount category(amt < 50000 show as ‘Low’, amt > 5000000 as ‘High’, otherwise, ‘Medium’) [change the column heading to “AMOUNT_CATEGORY”]
  • start date
  • maturity date (use term in months to calculate it) [change the column heading to “MATURITY_DATE”]

of all ‘ACTIVE’ (upper case) FD accounts which were started after year 2004.

This is what i have written so far

SELECT ACCOUNT_NO, CUST_ID, ROUND(INITIAL_AMT,2) AS INITIALAMTIN_LAKHS, 
CASE 
    WHEN INITIALAMTIN_LAKHS<50000 THEN 'low'
    WHEN INITIALAMTIN_LAKHS>5000000 THEN 'High'
    ELSE 'Medium'
END 'AMOUNT_CATEGORY',
START_DT, FROM BANK_FD_ACCOUNT;

cannot understand further

Table i:

BANK_FD_ACCOUNT
(
  ACCOUNT_NO, 
  CUST_ID, 
  START_DT, 
  FD_TERM_MNTH, 
  INITIAL_AMT, 
  ACC_STATUS 
)

Advertisement

Answer

SELECT account_no, 
   cust_id, 
   ROUND(initial_amt/100000,2) AS initialamtin_lakhs, 
   CASE 
        WHEN initial_amt < 50000 THEN 'Low' 
        WHEN initial_amt > 500000 THEN 'High' 
        ELSE 'Medium' 
   END as amount_category, 
   start_dt, 
   ADD_MONTHS(start_dt, fd_term_mnth) AS maturity_date 
   FROM bank_fd_account 
   WHERE UPPER(acc_status) = 'ACTIVE' AND TO_CHAR(start_dt,'YYYY') > 2004
5 People found this is helpful
Advertisement