MySQL Commands for Practical File XII IP

 Table Structure:  loanaccounts

Q1. Write the command to create the database named ABCBank

Sol: Create database ABCBank;

Q2. Write the command to change the current database to ABCBank.

Sol: USE ABCBank;

Q3. Write the command to see all the tables in the current database abcbank.

Sol: show tables();

There is now table in the above database.

Q4.  Write the command to create the table for the above data.

Sol:

    create table loanaccounts

    (

    accountno int not null primary key,

    customername varchar(50),

    loanamount decimal(10,2),

    installment int,

    intrate decimal(5,2),

    startdate date,

    interest decimal(10,2)

    );

Q5. Write the command to insert the following data in the loanaccounts table.

Sol:

insert into loanaccounts values(1,'Jatinder',300000,48,12.50,'2010-07-19',NULL);

insert into loanaccounts values(3 ,'Banta',300000,48,13.00,'2008-03-08',NULL);

insert into loanaccounts values(4,'Sandy',550000,36,NULL,'2010-12-06',NULL);

insert into loanaccounts values(5,'Mandy',600000,60,12.50,'2010-01-03',NULL);

insert into loanaccounts values(6 ,'John',700000,48,13.00,'2008-06-06',NULL);

insert into loanaccounts values(7 ,'Mohan',200000,60,NULL,'2009-03-05',NULL);

insert into loanaccounts values(8 ,'Ram',500000,36,11.50,'2010-12-06',NULL);

insert into loanaccounts values(9 ,'Sham',550000,33,12.00,'2008-03-08',NULL);

insert into loanaccounts values(10,'Gopal',800000,45,13.00,'2009-03-22',NULL);

Q6. Print the details of all the loan accounts.

Sol: Select * from loanaccounts;

Q7. Display the details of account number, account holder name and loan amount for all the loan accounts.

Sol: SELECT accountno,customername,loanamount FROM loanaccounts;

Q8. Display the details from all the loanaccounts whos installments are greater than 40.

Sol: SELECT * FROM loanaccounts WHERE installment > 40;

Q9. Display the details of account number, account holder name,installments and loan amount for all the loan accounts who has loan amount greater the 500000 and installment less then 55.

Sol: SELECT accountno, customername, loanamounts FROM loanaccounts WHERE loanamount>500000 AND installment<55; 

Q10. Display the details of all the accounts where loan accounts has started after 2009-03-01

Sol: SELECT * FROM loanaccounts WHERE startdate > '2009-03-01';

Q11. Display all the details of all loan accounts where interest rate is null.

Sol: SELECT * FROM loanaccounts WHERE intrate is NULL;

Q12. Display all the details of loan accounts who has interest rate.

Sol: SELECT * FROM loanaccounts WHERE intrate is  NOT NULL;

Q13. Print the details of all unique interest rates.

Sol: SELECT DISTINCT intrate FROM loanaccounts;

Q14. Display the details of all the loan accounts where installment are greater than 50 and the loan amount is less than 600000.

Sol: SELECT * FROM loanaccounts WHERE installment >50 AND loanamount<600000;