Assignment_SQL.txt

1.*Write a query which will display the customer id, account type they hold, their account number and bank name.

SQL-->select c.customer_id,c.account_type,c.account_no,b.bank_name 
from account_info c,bank_info b 
where c.ifsc_code=b.ifsc_code;

2.*Write a query which will display the customer id, account type and the account number of HDFC customers who registered after 12-JAN-2012 and before 04-APR-2012.

SQL-->select c.customer_id,c.account_type,c.account_no 
from account_info c,bank_info b 
where b.bank_name='HDFC' and c.activation_date between '12-01-2012' and '04-04-2012' and c.ifsc_code=b.ifsc_code;

3.*Write a query which will display the customer id, customer name, account no, account type and bank name where the customers hold the account.

SQL-->select c.customer_id,p.customer_name,c.account_no,c.account_type,b.bank_name
from account_info c,customer_personal_info p,bank_info b
where c.ifsc_code=b.ifsc_code and c.customer_id=p.customer_id;

4.*Write a query which will display the customer id, customer name, gender, marital status along with the unique reference string and
 sort the records based on customer id in descending order. <br/>
<br/><b>Hint:  </b>Generate unique reference string as mentioned below
:
<br/> CustomerName_Gender_MaritalStatus
<br/><b> Example, </b>
<br/> C-005           KUMAR              M                 SINGLE            KUMAR_M_SINGLE
<BR/> 
Use ""UNIQUE_REF_STRING"" as alias name for displaying the unique reference string."

SQL-->SELECT customer_id,customer_name,gender,marital_status,(customer_name||'_'||gender||'_'||marital_status)as Unique_Reference_String
FROM customer_personal_info;

5.*Write a query which will display the account number, customer id, registration date, initial deposit amount of the customer
 whose initial deposit amount is within the range of Rs.15000 to Rs.25000.

SQL-->select account_no,customer_id,registration_date,initial_deposit 
from account_info
where initial_deposit BETWEEN 15000 and 25000;

6.*Write a query which will display customer id, customer name, date of birth, guardian name of the customers whose name starts with 'J'.

SQL-->select customer_id,customer_name,date_of_birth,guardian_name
from customer_personal_info
where customer_name like 'J%';

7.*Write a query which will display customer id, account number and passcode. 
<br/>
Hint:  To generate passcode, join the last three digits of customer id and last four digit of account number.
 
<br/>Example
<br/>C-001                   1234567898765432                0015432
<br/>Use ""PASSCODE"" as alias name for displaying the passcode."

SQL-->SELECT customer_id,account_no,substr(customer_id,-3)||substr(account_no,-4) as PASSCODE
FROM account_info;

8.*Write a query which will display the customer id, customer name, date of birth, Marital Status, Gender, Guardian name, 
contact no and email id of the customers whose gender is male 'M' and marital status is MARRIED.

SQL-->select customer_id,customer_name,date_of_birth,marital_status,guardian_name,contact_no,mail_id
from customer_personal_info
where gender='M' and marital_status='MARRIED';

9.*Write a query which will display the customer id, customer name, guardian name, reference account holders name of the customers 
who are referenced / referred by their 'FRIEND'.

SQL-->SELECT f.customer_id,f.customer_name,f.guardian_name,g.reference_acc_name
FROM customer_reference_info g,customer_personal_info f
WHERE f.customer_id = g.customer_id and g.relation='FRIEND';

10.*Write a query to display the customer id, account number and interest amount in the below format with INTEREST_AMT as alias name
 Sort the result based on the INTEREST_AMT in ascending order.  <BR/>Example: 
$5<BR/>Hint: Need to prefix $ to interest amount and round the result without decimals.

SQL-->SELECT customer_id,account_no,'$'||((interest/100)*initial_deposit) INTEREST_AMT
FROM account_info
order by to_number(substr(INTEREST_AMT,2));
 
11.*Write a query which will display the customer id, customer name, account no, account type, activation date,
 bank name whose account will be activated on '10-APR-2012'

SQL--> select c.customer_id,c.customer_name,a.account_no,a.account_type,a.activation_date,b.bank_name
 from account_info a,bank_info b,customer_personal_info c
 where c.customer_id=a.customer_id and a.ifsc_code=b.ifsc_code and activation_date='10-4-2012';

12.*Write a query which will display account number, customer id, customer name, bank name, branch name, ifsc code
, citizenship, interest and initial deposit amount of all the customers.

SQL-->SELECT b.account_no,b.customer_id,b.interest,b.initial_deposit,c.customer_name,c.citizenship,a.bank_name,a.branch_name,a.ifsc_code
FROM account_info b,bank_info a,customer_personal_info c
WHERE a.ifsc_code = b.ifsc_code AND c.customer_id = b.customer_id;

13.*Write a query which will display customer id, customer name, date of birth, guardian name, contact number,
 mail id and reference account holder's name of the customers who has submitted the passport as an identification document.

SQL-->SELECT d.customer_id,d.customer_name,d.date_of_birth,d.guardian_name,d.contact_no,d.mail_id,e.reference_acc_name
FROM customer_reference_info e,customer_personal_info d
WHERE d.customer_id = e.customer_id and d.identification_doc_type='PASSPORT';

14.*Write a query to display the customer id, customer name, account number, account type, initial deposit, 
interest who have deposited maximum amount in the bank.

SQL-->SELECT i.customer_id,i.account_no,i.account_type,i.initial_deposit,i.interest,h.customer_name
FROM account_info i,customer_personal_info h
WHERE h.customer_id = i.customer_id and i.initial_deposit=(select max(initial_deposit) from account_info);

15.*Write a query to display the customer id, customer name, account number, account type, interest, bank name 
and initial deposit amount of the customers who are getting maximum interest rate.

SQL-->SELECT k.customer_id,k.account_no,k.account_type,k.interest,k.initial_deposit,j.customer_name,l.bank_name
FROM account_info k,customer_personal_info j,bank_info l
WHERE j.customer_id = k.customer_id AND l.ifsc_code = k.ifsc_code and k.Interest=(select max(interest)from account_info);

16.Write a query to display the customer id, customer name, account no, bank name, contact no 
and mail id of the customers who are from BANGALORE.

SQL-->SELECT n.customer_id,n.account_no,m.customer_name,m.contact_no,m.mail_id,o.bank_name
FROM account_info n,customer_personal_info m,bank_info o
WHERE m.customer_id = n.customer_id AND o.ifsc_code = n.ifsc_code and m.address like '%BANGALORE';

17.Write a query which will display customer id, bank name, branch name, ifsc code, registration date, 
activation date of the customers whose activation date is in the month of march (March 1'st to March 31'st).

SQL-->SELECT customer_id,bank_name,branch_name,account_info.ifsc_code,registration_date,activation_date
FROM account_info,bank_info
WHERE bank_info.ifsc_code = account_info.ifsc_code AND to_char(activation_date,'mm') = '03';

18.Write a query which will calculate the interest amount and display it along with customer id, customer name, 
account number, account type, interest, and initial deposit amount.<BR>Hint :Formula for interest amount, 
calculate: ((interest/100) * initial deposit amt) with column name 'interest_amt' (alias)

SQL-->SELECT account_info.customer_id,customer_name,account_no,account_type,interest,initial_deposit,((interest/100)*initial_deposit) interest_amt
FROM account_info,customer_personal_info
WHERE account_info.customer_id = customer_personal_info.customer_id;

19.Write a query to display the customer id, customer name, date of birth, guardian name, contact number, 
mail id, reference name who has been referenced by 'RAGHUL'.

SQL-->SELECT customer_personal_info.customer_id,customer_name,date_of_birth,guardian_name,contact_no,mail_id,reference_acc_name
FROM customer_personal_info,customer_reference_info
WHERE customer_personal_info.customer_id = customer_reference_info.customer_id AND reference_acc_name = 'RAGHUL';

20."Write a query which will display the customer id, customer name and contact number with ISD code of 
all customers in below mentioned format.  Sort the result based on the customer id in descending order. 
<BR>Format for contact number is :  
<br/> ""+91-3digits-3digits-4digits""
<br/> Example: +91-924-234-2312
<br/> Use ""CONTACT_ISD"" as alias name."

SQL-->SELECT customer_id,customer_name,'+91' || '-' || substr(contact_no,1,3) ||'-' || substr(contact_no,4,3) ||'-' || substr(contact_no,7,4) contact_isd
FROM customer_personal_info;

21.Write a query which will display account number, account type, customer id, customer name, date of birth, guardian name, 
contact no, mail id , gender, reference account holders name, reference account holders account number, registration date, 
activation date, number of days between the registration date and activation date with alias name "NoofdaysforActivation", 
bank name, branch name and initial deposit for all the customers.

SQL-->
SELECT
    account_no,
    account_type,
    account_info.customer_id,
    customer_name,
    date_of_birth,
    guardian_name,
    contact_no,
    mail_id,
    gender,
    reference_acc_no, 
    reference_acc_name,
    registration_date, 
    activation_date,
    activation_date-registration_date NoofdaysforActivation,
    bank_name, 
    branch_name,
    initial_deposit
FROM
    account_info,
    bank_info,
    customer_personal_info,
    customer_reference_info
WHERE 
    account_info.ifsc_code = bank_info.ifsc_code AND
    account_info.customer_id = customer_personal_info.customer_id AND
    account_info.customer_id = customer_reference_info.customer_id;

22."Write a query which will display customer id, customer name,  guardian name, identification doc type,
 reference account holders name, account type, ifsc code, bank name and current balance for the customers 
who has only the savings account. 
<br/>Hint:  Formula for calculating current balance is add the intital deposit amount and interest
 and display without any decimals. Use ""CURRENT_BALANCE"" as alias name."

SQL-->
SELECT
    account_info.customer_id,
    customer_name,  
    guardian_name,
    identification_doc_type,
    reference_acc_name,
    account_type,
    account_info.ifsc_code, 
    bank_name,
    initial_deposit+((interest/100)*initial_deposit)  current_balance
FROM
    customer_reference_info,
    customer_personal_info,
    account_info,
    bank_info
WHERE 
    account_info.ifsc_code = bank_info.ifsc_code and
    account_info.customer_id = customer_personal_info.customer_id and
    account_info.customer_id = customer_reference_info.customer_id and
    account_type='SAVINGS';

23."Write a query which will display the customer id, customer name, account number, account type, interest, initial deposit;
 <br/>check the initial deposit,<br/> if initial deposit is 20000 then display ""high"",<br/> if initial deposit is 16000 display 'moderate'
,<br/> if initial deposit is 10000 display 'average', <br/>if initial deposit is 5000 display 'low', <br/>if initial deposit is 0 display
 'very low' otherwise display 'invalid' and sort by interest in descending order.<br/>
Hint: Name the column as ""Deposit_Status"" (alias). 
<br/>Strictly follow the lower case for strings in this query."

SQL-->
select
    account_info.customer_id,
    CUSTOMER_NAME,
    account_no,
    account_type,
    interest,
    initial_deposit,
    case initial_deposit 
        when 20000 then 'high'
        when 16000 then 'moderate'
        when 10000 then 'average'
        when 5000 then 'low'   
        when 0 then 'very low'   
         else 'invalid' end
from
    account_info,
    customer_personal_info
where
    account_info.customer_id = customer_personal_info.customer_id
ORDER BY interest DESC;

24."Write a query which will display customer id, customer name,  account number, account type, bank name, ifsc code, initial deposit amount
 and new interest amount for the customers whose name starts with ""J"". 
<br/> Hint:  Formula for calculating ""new interest amount"" is 
if customers account type is savings then add 10 % on current interest amount to interest amount else display the current interest amount.
 Round the new interest amount to 2 decimals.<br/> Use ""NEW_INTEREST"" as alias name for displaying the new interest amount.

<br/>Example, Assume Jack has savings account and his current interest amount is 10.00, then the new interest amount is 11.00 i.e (10 + (10 * 10/100)). 
"

SQL-->
select
    account_info.customer_id,
    CUSTOMER_NAME,
    account_no,
    account_type,
    bank_name,
    account_info.ifsc_code,
    initial_deposit,
    case
        when account_type = 'SAVINGS'
           then ((interest/100)*initial_deposit)+((interest/100)*initial_deposit*10/100)
    else
           ((interest/100)*initial_deposit)
    end  new_interest
from
    account_info,
    customer_personal_info,
    bank_info
where 
    account_info.ifsc_code = bank_info.ifsc_code and
    account_info.customer_id = customer_personal_info.customer_id and 
    customer_name like 'J%';

25.Write query to display the customer id, customer name, account no, initial deposit, tax percentage as calculated below.
<BR>Hint: <BR>If initial deposit = 0 then tax is '0%'<BR>If initial deposit &lt;= 10000 then tax is '3%' 
<BR>If initial deposit &gt; 10000 and initial deposit &lt; 20000 then tax is '5%' <BR>If initial deposit &gt;= 20000 and
 initial deposit&lt;=30000 then tax is '7%' <BR>If initial deposit &gt; 30000 then tax is '10%' <BR>Use the alias name 'taxPercentage'

SQL-->
select
    account_info.customer_id,
    customer_name,
    account_no,
    initial_deposit,
    case
        when initial_deposit = 0
           then '0%'       
        when initial_deposit <= 10000
           then '3%'     
        when initial_deposit > 10000 AND initial_deposit<20000
           then '3%'     
        when initial_deposit >=20000 AND initial_deposit<=30000
           then '7%'     
        when initial_deposit > 30000
           then '10%'
    end taxPercentage
from
    account_info,
    customer_personal_info
where
    account_info.customer_id = customer_personal_info.customer_id