SQL Queries for Bank and Employee Databases

Assignment for Chapter 3作业内容：

Q1. Bank Database Queries

表结构：

- branch(branch_name, branch_city, assets)

- customer(customer_name, customer_street, customer_city)

- loan(loan_number, branch_name, amount)

- borrower(customer_name, loan_number)

- account(account_number, branch_name, balance)

- depositor(customer_name, account_number)

请构建以下SQL查询：

a. 查找所有在“Brooklyn”所有分支都有账户的客户。

SELECT customer_name
FROM customer
WHERE customer_name IN (
    SELECT depositor.customer_name
    FROM depositor, account, branch
    WHERE depositor.account_number = account.account_number
      AND account.branch_name = branch.branch_name
      AND branch.branch_city = 'Brooklyn'
)
GROUP BY customer_name
HAVING COUNT(DISTINCT branch.branch_name) = (SELECT COUNT(branch_name) FROM branch WHERE branch_city = 'Brooklyn');

b. 查找银行所有贷款金额的总和。

SELECT SUM(amount) AS total_loan_amount
FROM loan;

c. 查找资产大于至少一个位于“Brooklyn”的分支资产的所有分支名称。

SELECT DISTINCT branch_name
FROM branch
WHERE assets > ANY (
    SELECT assets
    FROM branch
    WHERE branch_city = 'Brooklyn'
);

Q2. Employee Database Queries

表结构：

- employee(employee_name, street, city)

- works(employee_name, company_name, salary)

- company(company_name, city)

- manages(employee_name, manager_name)

请构建以下SQL查询：

a. 查找...(继续书写其他查询)