ECNU数据库作业——Lab8

Lab 8

1. Find the name of customers who have both saving and checking account types.

SELECT DISTINCT c.cust_name 
FROM customer c 
WHERE EXISTS (
  SELECT 1 FROM customer 
  WHERE cust_ID = c.cust_ID AND acc_type = 'saving'
) AND EXISTS (
    SELECT 1 FROM customer 
    WHERE cust_ID = c.cust_ID AND acc_type = 'checking'
);

使用exists来检查同时存在saving和checking两种账户的用户，运行后发现不存在这样的用户：

2. What are the total account balances for each customer from Utah or Texas?

SELECT c.cust_name, c.state, SUM(c.acc_bal) AS total_balance
FROM customer c
WHERE c.state IN ('Utah', 'Texas')
GROUP BY c.cust_name, c.state;

3. Find the name of customers who do not have an saving account.

SELECT DISTINCT c.cust_name 
FROM customer c 
WHERE NOT EXISTS (SELECT 1 FROM customer 
                  WHERE cust_ID = c.cust_ID AND acc_type = 'saving');

使用NOT EXISTS来检查不存在saving账户的用户，运行结果如下：

4. What are the names of customers who have not taken a Mortage loan?

SELECT DISTINCT c.cust_name
FROM customer c
LEFT JOIN loan l ON c.cust_ID = l.cust_ID AND l.loan_type = 'Mortgages'
WHERE l.loan_ID IS NULL;

采用左连接，将loan_type = 'Mortgages'添加到连接条件中，这样不满足该条件的用户的loan_ID就会为空。运行结果如下：

5. Find the name of customers who have loans of both Mortgages and Auto.

SELECT DISTINCT c.cust_name
FROM customer c
WHERE EXISTS (
    SELECT 1 FROM loan l
    WHERE l.cust_ID = c.cust_ID AND l.loan_type = 'Mortgages'
) AND EXISTS (
    SELECT 1 FROM loan l
    WHERE l.cust_ID = c.cust_ID AND l.loan_type = 'Auto'
);

类似于练习1，采用exists语句来检查同时存在’Mortgages’和’Auto’，运行结果如下：

6. Find the name of customers whose credit score is below the average credit scores of all customers.

SELECT DISTINCT cust_name
FROM customer
WHERE credit_score < (SELECT AVG(credit_score) FROM customer);