数据库若干sql查询题目

最新推荐文章于 2024-06-23 15:30:17 发布

炽天使_晓

最新推荐文章于 2024-06-23 15:30:17 发布

阅读量585

点赞数

分类专栏： SQL 文章标签： sql mysql

本文链接：https://blog.csdn.net/qq183837971/article/details/107099594

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

给定6张表。

题目如下：

Q:Find the number of tuples in the customer relation.

Select count(*)
From customer;

Q:Find all customers who have both an account and a loan at the Perryridge branch.(ps:MySQL不支持intersect运算符)

Answer 1:
(Select customer_name 
from borrower
where loan_number in (Select loan_number from loan where branch_name = 'Perryridge'))
intersect 
(Select customer_name 
from depositor
where account_number in (Select account_number from account where branch_name = 'Perryridge'));

Answer 2:
select distinct customer_name
from depositor, account
where depositor.account_number = account.account_number and branch_name = 'Perryridge' 
and customer_name in(select customer_name from borrower, loan where borrower.loan_number = loan.loan_number and branch_name = 'Perryridge');

Answer 3:
select distinct B.customer_name
from borrower as B, depositor as D
where B.customer_name = D.customer_name 
	and loan_number in (
		select loan_number from loan where branch_name = 'Perryridge')
	and account_number in (
		select account_number from account where branch_name='Perryridge');

Q:Find the number of depositors for each branch.

Select branch_name,count(distinct customer_name) 
from account,depositor 
where account.account_number = depositor.account_number 
group by branch_name;

Q:Find the names of all branches where the average account balance is more than $1,200.
Q:Find the names of all branches that have greater assets than all branches located in Brooklyn.
Q:Find all accounts with the maximum balance.
Q:Find all branches that have greater assets than some branch located in Brooklyn.
Q:Find all customers who have both an account and a loan at the bank.
Q:Find all customers who have accounts at all branches located in Brooklyn.
Q:Find the average account balance at the Perryridge branch.
Q:Find the number of tuples in the customer relation.
Q:Find the number of depositors in the bank.
Q:Find the number of depositors for each branch.
Q:Find all customers who have a loan at the bank but do not have an account at the bank.
Q:Find all branches where the total account deposit is greater than the average of the total account deposits at all branches.
Q:Find all customers who have both an account and a loan at the bank.
Q:Find all customers who have at most one account at the Perryridge branch.
Q:Provide as a gift for all loan customers of the Perryridge branch, a $200 savings account. Let the loan number serve as the account number for the new savings account.
Q:Increase all accounts with balances over $10,000 by 6%, all other accounts receiver 5%.

其余题目的答案附上：https://download.csdn.net/download/qq183837971/12558741

同时附上了建表SQL语句、插入表中数据SQL语句、题目相关的表截图。

答案中除了包含intersect、except关键词的答案，其余都在MySQL上跑过，确保运行无误（MySQL不支持intersect、except关键词），因为脑细胞死得有些多，资源分不少请大家见谅。