数据库系统原理试验课1.DOC

《数据库系统原理》实验课1 考核内容： 利用企业管理器 建立数据库Salemanagement，数据文件放在D盘中，参数任意设定。 建立表结构， 考核内容： 利用企业管理器 1）建立主键约束 2）建立Check约束 3）建立外键约束 考核内容：根据下面图示内容，用SQL语句建表结构，录入数据，并完成查询。 客户表Customer 帐户表Account 存储表Depositor 银行支行表Branch 贷款表Loan 借贷表Borrower create table branch (branch_name char(15), branch_city char(30), assets integer, primary key (branch_name)) 查询所有客户的姓名、待款编号和额度，重名命loan_number 为loan_id. select customer_name, borrower.loan_number as loan_id, amountfrom borrower, loanwhere borrower.loan_number = loan.loan_number 查询居住街道名称含有子串“Main”的客户名称. select customer_name from customer where customer_street like % Main% 3、查询有存款、贷款或二者皆有的客户名称: (select customer_name from depositor)union(select customer_name from borrower) 4、查询存款、贷款二者皆有的客户名称. (select customer_name from depositor)intersect(select customer_name from borrower) 5、查询有存款无贷款客户名称. (select customer_name from depositor)except(select customer_name from borrower) 6、查询Perryridge 支行的帐户余额均值. select avg (balance) from account where branch_name = Perryridge 查询客户的数量 select count (*) from customer 查询储户的数量. select count (distinct customer_name) from depositor 查询每个支行储户的数量.. select branch_name, count (distinct customer_name) from depositor, account where depositor.account_number = account.account_number group by branch_name 10、查询帐户余额均值超过$1,200的支行名称. select branch_name, avg (balance) from account group by branch_name having avg (balance) 1200 11、查询有待款编号而额度为空值的贷款信息。. select loan_number from loan where amount is null