《數(shù)據(jù)庫系統(tǒng)原理》實驗二

1、數(shù)據(jù)庫系統(tǒng)原理實驗報告姓名：姜秀連指導教師：康輝學號：55140727實驗日期：2016年6月14日一、實驗目的1、鞏固數(shù)據(jù)庫的基礎知識；2、了解結構化查詢語言SQL的概念及其特點；3、學習使用SQLServer2000數(shù)據(jù)庫管理系統(tǒng)軟件的一些基本操作；4、掌握用SQL創(chuàng)建數(shù)據(jù)庫的兩種方法；5、 掌握用SQL創(chuàng)建數(shù)據(jù)表的方法；6、 掌握數(shù)據(jù)表的更新的方法；7、 掌握數(shù)據(jù)表的各種查詢方法。2、 實驗原理：創(chuàng)建數(shù)據(jù)庫，準備數(shù)據(jù)，根據(jù)題目寫sql語句三、基本操作創(chuàng)建數(shù)據(jù)庫，準備數(shù)據(jù)，根據(jù)題目寫sql語句四、實驗內容及數(shù)據(jù)記錄1、 Find the name, loan number and loan

2、 amount of all customers; rename the column name loan_number as loan_id.select customer.customer_name, loan.loan_number ,loan.amount from customer,loan,borrower where customer.customer_name =borrower.customer_name and borrower.loan_number=loan.loan_numberalter table loan change column loan_number lo

3、an_id varchar (255) not null2、 Find the names of all customers whose street includes the substring “Main”.select * from customer where customer_street like %Main%3、 Find all customers who have a loan, an account, or both:Select customer.customer_name from customer,depositor,account,borrower,loanWher

4、e depositor.customer_name=customer.customer_name or customer.customer_name=borrower.customer_name group by customer.customer_name4、 Find all customers who have both a loan and an account.Select customer.customer_name from customer,depositor,account,borrower,loanWhere depositor.customer_name=customer

5、.customer_name and customer.customer_name=borrower.customer_name group by customer.customer_name5、 Find all customers who have an account but no loan.Select distinct customer.customer_name from customer,depositor,borrowerWhere depositor.customer_name=customer.customer_nameandcustomer.customer_name n

6、ot in(Select customer.customer_name from customer,depositor,borrowerWhere customer.customer_name=borrower.customer_name)6、 Find the average account balance at the Perryridge branch.select avg(balance) from account where branch_name=Perryridge7、 Find the number of tuples in the customer relation.sele

7、ct count(customer_name) from customer8、 Find the number of depositors in the bank.Select count(customer.customer_name) from branch,account,depositor,customerWhere branch.branch_name=account.branch_name and account.account_number=depositor.account_number and depositor.customer_name=customer.customer_

8、name9、 Find the number of depositors for each branch.Select branch.branch_name,count(customer.customer_name) from branch,account,depositor,customerWhere branch.branch_name=account.branch_name and account.account_number=depositor.account_number and depositor.customer_name=customer.customer_namegroup

9、by branch.branch_name10、 Find the names of all branches where the average account balance is more than $1,200.selectbranch_name,avg(balance)fromaccountgroupbybranch_namehavingavg(balance)120011、Find all loan number which appear in the loan relation with null values for amount.selectloan_numberfromlo

10、anwhereamountisnull12、 Find all customers who have both an account and a loan at the bank.select customer_name from depositorINTERSECTselect customer_name from borrowerSelect distinct c.customer_name from customer c ,depositor d ,borrower b where c.customer_name=d.customer_name and c.customer_name=b

11、.customer_name 13、 Find all customers who have a loan at the bank but do not have an account at the bankSelect distinct customer.customer_name from customer,depositor,borrowerWhere customer.customer_name=borrower.customer_nameandcustomer.customer_name not in(Select customer.customer_name from custom

12、er,depositor,borrowerWhere depositor.customer_name=customer.customer_name)14、 Find all customers who have both an account and a loan at the Perryridge branchSelect distinct c.customer_name from customer c,account a,loan l,depositor d,borrower bwhere c.customer_name=d.customer_name and d.account_numb

13、er=a.account_number and a.branch_name=Perryridgeand c.customer_name=b.customer_name and b.loan_number=l.loan_numberand l.branch_name=Perryridge15、 Find all branches that have greater assets than some branch located in Brooklyn.Select branch.* from branch where assets(select min(assets) from branch where branch_city=Brooklyn)16、 Find the names of all branches that have greater assets than all branches located in Brookly