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

数据库系统原理实验报告姓名：姜秀连指导教师：康辉学号：55140727实验日期：2016年6月14日一、实验目的1、巩固数据库的基础知识；2、了解结构化查询语言SQL的概念及其特点；3、学习使用SQLServer2000数据库管理系统软件的一些基本操作；4、掌握用SQL创建数据库的两种方法；5、 掌握用SQL创建数据表的方法；6、 掌握数据表的更新的方法；7、 掌握数据表的各种查询方法。2、 实验原理：创建数据库，准备数据，根据题目写sql语句三、基本操作创建数据库，准备数据，根据题目写sql语句四、实验内容及数据记录1、 Find the name, loan number and loan 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 loan_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,loanWhere 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.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 not 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.select 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_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 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_numberfromloanwhereamountisnull12、 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.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 customer,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_number=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