Untitled

mail@pastecode.io avatar
unknown
plain_text
a month ago
1.2 kB
6
Indexable
Never
create database banking;
use banking;
create table customers(
customerid int primary key,
customername varchar(50),
address varchar(50),
phone varchar(50),
email varchar(50) unique
);

create table loan(
loannumber int primary key,
loantype varchar(50),
amount bigint
);

create table borrows(
boid int primary key,
customerid int,
loannumber int,
foreign key (customerid) references customers(customerid),
foreign key (loannumber) references loan(loannumber)
);

insert into customers
(customerid,customername,address,phone,email)
values
(1001,'karan','lalitpur','9800012345','karan@gmail.com'),
(1002,'arjun','ktm','9801234567','arjun1@gmail.com'),
(1003,'basanti','lalitpur','9867543212','basanti36@gmail.com');

insert into loan
(loannumber,loantype,amount)
values
(2001,'si',50000),
(2002,'annual-ci',60000),
(2003,'semi-ci',70000),
(2004,'quarterly-ci',80000);

insert into borrows
(boid,customerid,loannumber)
values
(5001,1001,2001),
(5002,1002,2002),
(5003,1003,2003);
select * from borrows;

select customername from
customers
where address='lalitpur'
order by 1 asc;

select count(c.customerid) as total_loan_count
from borrows b
join customers c on b.customerid=c.customerid
join loan l on b.loannumber=l.loannumber;
Leave a Comment