数据库实验二-.docxVIP

数据库原理及应用实验报告实验名称：______ __数据查询_________实验类型：_________验证型实验_________实验环境：_____ Oracle 11g__ ______指导教师：__________张世玲_____________专业班级：__________计科1401__________姓 名：__________董嗣川____________学 号：__________512014656________联系电话：__________1558745548____ __电子邮件：__________21254345356@实验地点：__________东六E5-01________实 验 日 期： 2016 年10月18 日实验报告日期：? 2016 年11 月6 日成绩：__________________________数据查询一、实验目的?掌握查询语句的一般格式；?熟练掌握单表查询、连接查询、集合查询、统计查询和嵌套查询。实验平台Oracle 11g。实验步骤、出现的问题及解决方案（不能解决的将问题列出） /* 1查询“红楼梦”目前可借的各图书编号，及所属版本信息。（是否借出为‘否‘的图书)*/select bookslist.isbn,bookslist.bname,bookslist.bauthor,bookslist.bpublish,bookslist.bprice,bookslist.bcid,books.bid from BooksList,Bookswhere Bname = 红楼梦 and BooksList.ISBN = Books.ISBNand Bislend =否;/* 2查找高等教育出版社的所有书目及单价，结果按单价降序排序*/select Bname,Bprice from BooksListwhere Bpublish = 高等教育出版社order by Bprice desc;/* 3统计“红楼梦”各版的藏书数量（ISBN不同则版本不同）*/select BooksList.ISBN,count(Bid) from BooksList,Bookswhere BooksList.Bname =红楼梦 andBooksList.ISBN = Books.ISBNgroup by BooksList.ISBN;/* 4查询学号号借书证借阅未还的图书的信息*/ select * from BooksListwhere ISBN in(select ISBN from Books where Bid in(select Bid from Borrow where Rlid and returndate is null ));/* 5查询各个出版社的图书最高单价、平均单价*/ select Bpublish,max(Bprice),avg(Bprice) from BooksListgroup by Bpublish;/* 6要查询借阅了两本和两本以上图书的读者的个人信息*/select * from Reader where Rlid in (select Rlid from Borrowgroup by Rlidhaving count(Bid) =2);/* 7查询“王菲”的单位、所借图书的书名和借阅日期*/select Runit,Bname,Bordate from Reader,BooksList,Borrowwhere Rname = 王菲 and Reader.Rlid = Borrow.Rlid andBorrow.Bid in (select Bid from Books where Books.ISBN = BooksList.ISBN);/* 8查询每类图书的册数和平均单价*/ select BooksClass.BCname,count(BooksClass.BCname),avg(BooksList.Bprice) from BooksClass,BooksList where BooksClass.Bcid = BooksList.Bcid group by BooksClass.BCname; /* 9统计从未借书的读者人数*/select count(*) from Reader where not exists(select * from Borrowwhere Rlid = Reader.Rlid );/* 10统计参与借书的人数*/select count(*) from Reader where exists(select * from Borrowwhere Rlid