TECHONESPOT
All Tech at one spot
Write a SQL command
Table :-
-> How to Create database Bank
Solution :-
Create database Bank;
-> Access database.
Solution :-
use Bank;
-> How to Create a Table.
Solution
Create table Bank
(
AccNo int primary key not null,
Cust_name varchar(100)not null,
FD_amount int not null,
months int not null,
int_rate decimal(5,2),
Fd_date date);
-> Describe the Structure Bank.
Solution :-
desc Bank;
-> How to insert data into Bank table.
Solution :-
Insert into bank values(1001,’Aman’,30000,36,6.00,’2018-07-01′);
Insert into bank values (1002,’Nishant’,50000,48,6.75,’2018-03-22′);
Insert into bank (AccNo,Cust_name,Fd_Amount,Months,Fd_Date)values (1003,’Harish’,30000,36,’2018-03-22′);
Insert into bank values (1004,’Sudha’,80000,60,8.25,’2017-06-12′);
Insert into bank values (1005,’Uma’,20000,36,6.50,2017-01-31);
Insert into bank values (1006,’Prakash’,70000,60,8.25,2018-06-15);
Insert into bank (AccNo,Cust_name,Fd_Amount,Months,Fd_Date)values (1007,’Monu’,50000,48,’2018-07-05′);
Insert into bank values(1008,’hardik’,60000,48,6.75,’2017-04-02′);
Insert into bank values (1009,’Shreya’,40000,42,6.50,’2018-06-15′);
Insert into bank values (1010,’Yash’,25000,36,6.50,’2018-09-27′);
->Display the Structure of the table bank.
Solution :-
Desc bank;
-> Display the details of all the bank.
Solution :-
Select * from bank;
-> Display the AccNo , Cust_name and FD_amount.
Solution :-
Select accno,cust_name,fd_amount from bank;
-> Display the details of all the FD’s having maturity time is less than 40 months.
Solution :-
Select * from bank where months<40;
-> Display the AccNo and Fdamount which started before 01-04-2018.
Solution :-
select accno,fd_amount from bank where fd_date<‘2018-04-01’;
-> Display details of all FD whose rate of interest are NULL.
Solution :-
Select * from bank where int_rate is NULL;
-> Display details of all FD whose rate of interest is NOT NULL.
Solution :-
Select * from Bank where int_rate is not null;
-> Display amounts of various FD from the table Bank.An FD Amount should appear only once.
Solution :-
Select distinct(FD_amount)from bank;
-> Display the number of Months of various loans from the table Bank. A month should appear only once.
Solution :-
Select distinct(Months) from bank;
->Display the customer name and FD amount for all the bank which do not have a number of month is 36.
Solution :-
Select cust_name , fd_amount from bank where months <>36;
-> Display the Customer Name and FD Amount for which the FD amount is more than 50,000 or int_rate is more than 7.
Solution :-
Select cust_name,fd_amount from bank where fd_amount>50000 and Int_rate > 7.00;
-> Display the detail of all FD which started in the year 2018.
Solution :-
Select * from bank where year(fd_date)=2018;
->Display the details of all FD whose FD_amount is in the range 40000 to 50000.
Solution :-
Select * from bank where fd_amount in(40000,50000);
->Display the details of all FD whose rate of Interest is in the range 6% to 7%.
Solution :-
Select * from bank where int_rate >=6.00 and int_rate <=7.00;
-> Display the customer name and fd amount for all the loans for which the number of month is 24,36 or 48(using in operator)
Solution :-
Select cust_name , fd_amount from bank where months in(24,36,48);
-> Display the account number customer name and fd amount for all the FD for which the customer name end with “ant”.
Solution :-
Select * from bank where cust_name like ‘%ant’;
->Delete the records of “Shreya”,
Solution :-
delete from bank where cust_name = ‘Shreya’;
-> Add another column Maturity_amt of type integer in the Bank table.
Solution :-
alter table bank add maturity_amt int;
-> To find the average FD amount.label the column as “Average FD Amount”.
Solution :-
Select avg(fd_amount)”Average FD amount”from bank;
-> To find the average FD amount. which started in the year 2018.
Solution :-
select sum(fd_amount) from bank where year(fd_date)=2018;
-> update Maturity amount of all the bank of Customers.
Solution :-
update bank set maturity_amt=((fd_amount)*months*int_rate)/(12*100);
Fun & Easy to follow
Works on all devices
Your own Pace
Super Affordable