Scribd Logo
Upload

Welcome to Scribd!

  • 1 views

    Ws 3

    Uploaded by

    lebronjameslmaofr
    worksheet 3

    Copyright:

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

    Ws 3

    Uploaded by

    lebronjameslmaofr
    1 views2 pages
    worksheet 3

    Original Title

    ws 3

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    worksheet 3

    Copyright:

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

    Ws 3

    Uploaded by

    lebronjameslmaofr
    worksheet 3

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 2

    THE EMIRATES NATIONAL SCHOOL – SHARJAH

    INFORMATICS PRACTICES Worksheet


    Grade XII (2024 – ‘25)
    CHAPTER 3 – Review of Database Concepts & SQL
    Worksheet 3
    Consider the database BANK with the following table LOANS

    AccNo Cust_Name Loan_Amount Instalments Int_Rate Start_Date Interest


    1 R. K. Gupta 300000 36 12.00 2009-07-19 1200
    2 S. P. Sharma 500000 48 10.00 2008-03-22 1800
    3 K. P. Jain 300000 36 NULL 2007-03-08 1600
    4 M. P. Yadav 800000 60 10.00 2008-12-06 2250
    5 S. P. Sinha 200000 36 12.50 2010-01-03 4500
    6 P. Sharma 700000 60 12.50 2008-06-05 3500
    7 K. S. Dhall 500000 48 NULL 2008-03-05 3800

    Write SQL commands for the following:

    1. To create the database BANK and open it.


    2. To create the table LOANS with the given specifications

    FieldName DataType Constraint


    AccNo Int(3) Primary Key
    Cust_Name Varchar(30) Not Null
    Loan_Amount Int
    Instalments Int(2)
    Int_Rate Decimal(5,2)
    Start_Date Date
    Interest Int(6)

    3. To insert the given records into the table LOANS


    4. To display the details of all the loans
    5. To display the Account number, customer name and loan amount of all the loans
    6. To display the details of all loans with less than 40 instalments.
    7. To display the account number and loan amount of all loans started before 01-04-2009
    8. To display the details of all loans whose rate of interest is null.
    9. To display the various instalments
    10. To display the details of all loans whose loan amount is in the range 400000 to 500000
    (both inclusive)
    11. To display the customer name and loan amount of all loans for which the number of
    instalments are 24, 36 or 48
    12. To display the account number name and loan amount for which the customer name ends
    with Sharma.
    13. To display the details of loans for which the customer name does not contain the letter P
    14. To display the details of loans for which the customer name contains ‘a’ as the second last
    letter.
    15. To display the customer name in the alphabetical order.
    16. To display the customer name and start date of all loans for which the loan amount is less
    than 500000 or interest rate is more than 12
    17. To display the details of all loans for which the interest rate is not 10.
    18. To change the interest rate to 11.50 for all loans whose interest rate is not known.
    19. To delete the record of K. P. Jain
    20. To add another column Category of type CHAR(2) in the table LOAN.

    You might also like