《数据库系统原理》实验2.doc

《数据库系统原理》实验报告 姓名： 指导教师： 学号： 实验日期： 2016/6/7 一、实验目的 根据如下内容创建数据表结构，自行定义主键和外键约束，并完成如下查询： 实验原理： Find the name, loan number and loan amount of all customers; rename the column name loan_number as loan_id. alter table loan change column loan_number loan_id char(10); Find the names of all customers whose street includes the substring “Main”. Select customer_name from customer where customer_street like “%main%”; select customer_name from customer where customer_street like %Main%; Find all customers who have a loan, an account, or both: (select customer_name from borrower where loan_number in (select loan_id from loan)) union (select account_number from depositor where account_number in (select account_number from account)); Find all customers who have both a loan and an account. select customer_name from borrower where customer_name in (select customer_name from depositor); Find all customers who have an account but no loan. (select customer_name from borrower where customer_name not in (select custormer_name from depositor)); Find the average account balance at the Perryridge branch. select avg(balance) from account; Find the number of tuples in the customer relation. select count(*) from customer; Find the number of depositors in the bank. select count(*) from depositor; Find the number of depositors for each branch. mysql select A.branch_name, count(D.customer_name) from depositor as D,account as A where A.account_number=D.account_number group by branch_name; Find the names of all branches where the average account balance is more than $1,200. mysql select branch_name,avg(balance) from account group by branch_name having avg(balance)1200; Find all loan number which appear in the loan relation with null values for amount. select loan_id from loan where amount is null; Find all customers who have both an account and a loan at the bank. select customer_name from borrower where customer_name in (select customer_name from depositor