《数据库系统原理》实验二.docx
- 文档编号:728879
- 上传时间:2022-10-12
- 格式:DOCX
- 页数:7
- 大小:84.78KB
《数据库系统原理》实验二.docx
《《数据库系统原理》实验二.docx》由会员分享,可在线阅读,更多相关《《数据库系统原理》实验二.docx(7页珍藏版)》请在冰豆网上搜索。
《数据库系统原理》实验二
《数据库系统原理》实验报告
姓名:
姜秀连指导教师:
康辉
学号:
55140727实验日期:
2016年6月14日
一、实验目的
1、 巩固数据库的基础知识;
2、 了解结构化查询语言SQL的概念及其特点;
3、 学习使用SQL Server 2000数据库管理系统软件的一些基本操作;
4、 掌握用SQL创建数据库的两种方法;
5、 掌握用SQL创建数据表的方法;
6、 掌握数据表的更新的方法;
7、 掌握数据表的各种查询方法。
2、实验原理:
创建数据库,准备数据,根据题目写sql语句
三、基本操作
创建数据库,准备数据,根据题目写sql语句
四、实验内容及数据记录
1、Findthename,loannumberandloanamountofallcustomers;renamethecolumnnameloan_numberasloan_id.
selectcustomer.customer_name,loan.loan_number,loan.amountfromcustomer,loan,borrower
wherecustomer.customer_name=borrower.customer_nameandborrower.loan_number=loan.loan_number
altertableloanchangecolumnloan_numberloan_idvarchar(255)notnull
2、Findthenamesofallcustomerswhosestreetincludesthesubstring“Main”.
select*fromcustomerwherecustomer_streetlike'%Main%'
3、Findallcustomerswhohavealoan,anaccount,orboth:
Selectcustomer.customer_namefromcustomer,depositor,account,borrower,loan
Wheredepositor.customer_name=customer.customer_nameorcustomer.customer_name=borrower.customer_namegroupbycustomer.customer_name
4、Findallcustomerswhohavebothaloanandanaccount.
Selectcustomer.customer_namefromcustomer,depositor,account,borrower,loan
Wheredepositor.customer_name=customer.customer_nameandcustomer.customer_name=borrower.customer_namegroupbycustomer.customer_name
5、Findallcustomerswhohaveanaccountbutnoloan.
Selectdistinctcustomer.customer_name
fromcustomer,depositor,borrower
Wheredepositor.customer_name=customer.customer_name
and
customer.customer_namenotin
(Selectcustomer.customer_name
fromcustomer,depositor,borrower
Wherecustomer.customer_name=borrower.customer_name)
6、FindtheaverageaccountbalanceatthePerryridgebranch.
selectavg(balance)fromaccountwherebranch_name='Perryridge'
7、Findthenumberoftuplesinthecustomerrelation.
selectcount(customer_name)fromcustomer
8、Findthenumberofdepositorsinthebank.
Selectcount(customer.customer_name)frombranch,account,depositor,customer
Wherebranch.branch_name=account.branch_nameandaccount.account_number=depositor.account_numberanddepositor.customer_name=customer.customer_name
9、Findthenumberofdepositorsforeachbranch.
Selectbranch.branch_name,count(customer.customer_name)frombranch,account,depositor,customer
Wherebranch.branch_name=account.branch_nameandaccount.account_number=depositor.account_numberanddepositor.customer_name=customer.customer_name
groupbybranch.branch_name
10、Findthenamesofallbrancheswheretheaverageaccountbalanceismorethan$1,200.
select branch_name, avg (balance) from account group by branch_name having avg (balance) > 1200
11、Findallloannumberwhichappearintheloanrelationwithnullvaluesforamount.
select loan_number from loan where amount is null
12、Findallcustomerswhohavebothanaccountandaloanatthebank.
selectcustomer_namefromdepositor
INTERSECT
selectcustomer_namefromborrower
Selectdistinctc.customer_namefromcustomerc,depositord,borrowerbwherec.customer_name=d.customer_nameandc.customer_name=b.customer_name
13、Findallcustomerswhohavealoanatthebankbutdonothaveanaccountatthebank
Selectdistinctcustomer.customer_name
fromcustomer,depositor,borrower
Wherecustomer.customer_name=borrower.customer_name
and
customer.customer_namenotin
(Selectcustomer.customer_name
fromcustomer,depositor,borrower
Wheredepositor.customer_name=customer.customer_name)
14、FindallcustomerswhohavebothanaccountandaloanatthePerryridgebranch
Selectdistinctc.customer_namefromcustomerc,accounta,loanl,depositord,borrowerb
wherec.customer_name=d.customer_name
andd.account_number=a.account_number
anda.branch_name='Perryridge'
andc.customer_name=b.customer_name
andb.loan_number=l.loan_number
andl.branch_name='Perryridge'
15、FindallbranchesthathavegreaterassetsthansomebranchlocatedinBrooklyn.
Selectbranch.*frombranchwhere
assets>(selectmin(assets)frombranchwherebranch_city='Brooklyn')
16、FindthenamesofallbranchesthathavegreaterassetsthanallbrancheslocatedinBrooklyn.
Selectbranch.branch_namefrombranchwhere
assets>(selectmax(assets)frombranchwherebranch_city='Brooklyn')
5、实验过程中遇到哪些问题,如何解决的?
实验中除去因敲错符号的错自己或找同学帮忙解决外,还有一些知识比如notin,notexist等掌握的不是很好,一般都是上网XX或找老师帮忙解决,收获很大
THANKS!
!
!
致力为企业和个人提供合同协议,策划案计划书,学习课件等等
打造全网一站式需求
欢迎您的下载,资料仅供参考
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库系统原理 数据库 系统 原理 实验