SELECT avg(credit_score) FROM customer WHERE cust_id IN (SELECT cust_id FROM loan)
Find the average credit score of the customers who have some loan.
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20));
CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID));
CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID));
INSERT into bank values (1, 'morningside', 203, 'New York City', 'New York');
INSERT into customer values (1, 'Mary', 'saving', 2000, 2, 30, 2, 'Utah');
INSERT into loan values (1, 'Mortgages', 1, 1, 2050);
INSERT INTO bank VALUES [(1, 'morningside', 203, 'New York City', 'New York'), (2, 'downtown', 123, 'Salt Lake City', 'Utah')]
INSERT INTO customer VALUES [('1', 'Mary', 'saving', 2000, 2, 30, 2, 'Utah'), ('2', 'Jack', 'checking', 1000, 1, 20, 1, 'Texas')]
INSERT INTO loan VALUES [('1', 'Mortgages', '1', '1', 2050), ('2', 'Auto', '1', '2', 3000)]