MySQL经典问题

Table Defintions:

定义表：

 

Table name: Branch Schema; Column name: branch-name, branch-city, assets

表名：分支机构；列名：branch-name, branch-city, assets

 

Table name: Customer Schema; Column name: customer-name, customer-street, customer-city

表名：客户；列名：customer-name, customer-street, customer-city

 

Table name: Loan Schema; Column name: loan-number, branch-name, amount

表名：贷款；列名：loan-number, branch-name, amount

 

Table name: Borrower Schema; Column name: customer-name, loan-number

表名：贷款人；列名：customer-name, loan-number

 

Table name: Account Scheme; Column name: account-number, branch-name, balance

表名：账户；列名：account-number, branch-name, balance

 

Table name: Depositor Scheme; Column name: customer-name, account-number

表名：存款人；列名：customer-name, account-number

---

Find the average balance for each customer who lives in Harrision and has at least three accounts.

找出住在Harrison且在银行中至少有三个账户的客户的姓名和其平均余额

SELECT D.customer_name, AVG(balance) FROM Depositor D, Account A, Customer C WHERE D.account_number = A.account_number AND D.customer_name = C.customer_name AND C.customer_city = ‘Harrison’ GROUP BY D.customer_name HAVING COUNT(DISTINCT D.account_number) >= 3;

 

Find the names of customers who do have a loan at the bank, and whose names are neither Smith nor Jones.

找出在银行中有贷款的客户的名字，并且他们的名字既不是Smith也不是Jones

SELECT DISTINCT customer_name FROM Borrower WHERE customer_name NOT IN (‘Smith’, ‘Jones’);

 

Find the names of all branches that have assets greater than those of at least one branch located in Brooklyn. 

找出那些总资产至少比位于Brooklyn某一家银行要多的支行名称

SELECT DISTINCT T.branch_name FROM Branch as T, Branch as S WHERE T.assets > S.assets AND S.branch_city = ‘Brooklyn’;

 

Find all customers who have at most one account at the Perryridge branch.

找出所有在Perryridge支行中只有一个账户的客户

SELECT T.customer_name FROM Depositor AS T WHERE UNIQUE (SELECT R.customer_name FROM Depositor AS R, Account AS A WHERE T.customer_name = R.customer_name AND R.account_number = A.account_number AND A.branch_name = ‘Perryridge’);

 

Find all the loan number that appear in loan relation with null amount values.

找出Loan表中amount为空值的贷款号

SELECT loan_number FROM Loan WHERE amount IS NULL;

 

Find the number of depositors for each branch .

找出每个支行储户数

SELECT branch_name, COUNT(DISTINCT customer_name) FROM Depositor D, Account A WHERE D.account_number = A.account_number GROUP BY branch_name;

 

Find the branch where average account balance is more than 1200.

找出账户平均余额大于1200元的支行

SELECT branch_name FROM Depositor D, Account A WHERE D.account_number = A.account_number GROUP BY branch_name HAVING AVG(balance) > 1200;

 

Find the names of all customers whose street address includes the substring ‘Main’.

找出街道地址中包含子串Main的所有客户的姓名

SELECT customer_name FROM Customer WHERE customer_street LIKE ‘%Main%’；