Scribd Logo
Upload

Welcome to Scribd!

  • 41 views

    Code

    Uploaded by

    Avinash
    The document creates a loans database and loan_acc table with fields for loan account number, customer name, loan amount, installments, interest rate, start date, and interest. It then inserts records for 7 customers and runs various SELECT queries to retrieve, filter, sort, and aggregate data from the table. It also updates interest rates and calculates interest fields, and alters the table to add a category field.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Code

    Uploaded by

    Avinash
    41 views3 pages
    The document creates a loans database and loan_acc table with fields for loan account number, customer name, loan amount, installments, interest rate, start date, and interest. It then inserts records for 7 customers and runs various SELECT queries to retrieve, filter, sort, and aggregate data from the table. It also updates interest rates and calculates interest fields, and alters the table to add a category field.

    Original Description:

    code

    Original Title

    code

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document creates a loans database and loan_acc table with fields for loan account number, customer name, loan amount, installments, interest rate, start date, and interest. It then inserts records for 7 customers and runs various SELECT queries to retrieve, filter, sort, and aggregate data from the table. It also updates interest rates and calculates interest fields, and alters the table to add a category field.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    41 views3 pages

    Code

    Uploaded by

    Avinash
    The document creates a loans database and loan_acc table with fields for loan account number, customer name, loan amount, installments, interest rate, start date, and interest. It then inserts records for 7 customers and runs various SELECT queries to retrieve, filter, sort, and aggregate data from the table. It also updates interest rates and calculates interest fields, and alters the table to add a category field.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 3

    Create database loans;

    use loans;

    Create table loan_acc(


    Accno int(5),
    Cust_name char (30),
    Loan_amount int(9),
    Installments int(5),
    Int_rate decimal(4,2),
    Start_Date date,
    Interest decimal(5,2));

    Insert into loan_acc values(1,"R K Gupta",300000,36,12.00,"2009-07-19",NULL);


    Insert into loan_acc values(2,"S P Sharma",500000,48,10.00,"2008-03-22",NULL);
    Insert into loan_acc values(3,"K P Jain",300000,36,NULL,"2007-03-08",NULL);
    Insert into loan_acc values(4,"M P Yadav",800000,60,10.00,"2008-12-06",NULL);
    Insert into loan_acc values(5,"S P Sinha",200000,36,12.50,"2010-01-03",NULL);
    Insert into loan_acc values(6,"P Sharma",700000,60,12.50,"2008-06-05",NULL);
    Insert into loan_acc values(7,"K S Dhall",500000,48,NULL,"2008-03-05",NULL);

    Select*from loan_acc;

    Select Accno,Cust_name,Loan_Amount from loan_acc;

    Select* from loan_acc


    where installments <40;

    Select Accno,Loan_amount from loan_acc


    where start_date<"2009-04-01";

    Select int_rate from loan_acc


    where start_date>"2008-12-31" and installments>36;

    Select cust_name,loan_amount from loan_acc


    where installments<>36;

    Select cust_name,Loan_amount from loan_acc


    where loan_amount<500000 or int_rate>12;

    Select Cust_name,loan_amount from loan_acc


    where start_date like "%2009%";

    select* from loan_acc


    where loan_amount>=400000 and loan_amount<=500000;

    select*from loan_acc
    where int_rate>=11 and int_rate<=12;

    Select cust_name,Loan_amount from loan_acc


    where installments in(24,36,48);

    Select *from loan_acc


    where loan_amount between 400000 and 500000;

    select*from loan_acc
    where int_rate between 11 and 12;

    select cust_name,Accno ,loan_amount from loan_acc


    where cust_name like "%Sharma";

    Select Accno,Loan_amount,Cust_name from loan_acc


    where cust_name like "%a";

    Select Accno,Loan_amount,Cust_name from loan_acc


    where cust_name like "%a%";

    Select Accno,Loan_amount,Cust_name from loan_acc


    where cust_name not like "%p%";

    Select Accno,Loan_amount,Cust_name from loan_acc


    Where cust_name like "%a_";

    Select *from loan_Acc


    Order by loan_amt asc;

    Select *from loan_acc


    Order by start_date desc;

    Select *from loan_Acc


    Order by loan_Amt asc,start_date desc;

    select*from loan_acc
    Where int_rate is NULL;

    select*from loan_acc
    Where int_rate is not NULL;
    Select distinct loan_amt from loan_Acc;

    Select distinct installments from loan_acc;

    Update loan_acc
    Set int_rate=11.50
    Where int_rate is NULL;

    Update loan_acc
    Set int_rate="int_rate+0.5"
    Where loan_amt>400000;

    Update loan_acc
    Set int="loan_amt*int_rate*installments*1200";

    Delete from loan_acc


    Where start_date<"2007-01-01";

    Delete from loan_acc


    Where cust_name="K PJain";

    Alter table lona_acc add category char(1);

    You might also like