sql多表连接library练习题(含部分答案).doc

多表连接习题：1 求在2005年2月3日借书的人的姓名及其所借书的书名。Select , from borrow join reader on reader.reader_id=borrow.reader_id join book on reader.reader_id=book.book_id where borrowdate=2005-2-32求读者施秋乐所借的图书编号、书名和出版社信息及借书时间。3查询借阅了数据库原理这本书的读者编号和姓名。4查询所有的借阅信息，要求列出借阅人编号、姓名、所借图书编号、书名和借阅日期，并用中文显示列名。5. 练习：查询借书价格在20-40之间的读者的姓名Select from book join borrow on book.book_id=borrow.book_id join reader on reader.reader_id =borrow.reader_idWhere price between 20 and 406. 练习：查询胡晓丽借阅计算机文化基础的日期Select borrowdate from book join borrow on book.book_id=borrow.book_id join reader on reader.reader_id=borrow.reader_id where R= 胡晓丽 and = 计算机文化基础-Select borrowdate from borrow where reader_id in(select reader_id from reader where name= 胡晓丽 ) and book_id in (select book_id from book where name= 计算机文化基础)7. 练习：查询所借图书的平均价格多余30元的读者的姓名Select from book join borrow on book.book_id=borrow.book_id join reader on reader.reader_id=borrow.reader_id Group by reader.reader_idHaving avg(price)30Select name from reader where reader_id in(select reader_id from borrow join book on book.book_id=borrow.book_id Group by reader_id having avg(price)30)多表连接作业：（能用多种方法的题目请用多种方法实现）1. 查询成绩大于90分的学生的学号、姓名和选修的课程号。select , from Book join borrow on book.book_id=borrow.book_idjoin reader on reader.reader_id=borrow.reader_id where borrowdate=2005-2-32 查询选修了“数据库基础”课程的学生的学号、姓名和所在系。select , from Book join borrow on book.book_id=borrow.book_idjoin reader on reader.reader_id=borrow.reader_id where borrowdate=2005-2-34 查询选修了课程“C01”的学生姓名和成绩。select , from Book join borrow on book.book_id=borrow.book_idjoin reader on reader.reader_id=borrow.reader_id where borrowdate=2005-2-35. 查询每个学生的学号、姓名、选修的课程及成绩。select , from Book join borrow on book.book_id=borrow.book_idjoin reader on reader.reader_id=borrow.reader_id where borrowdate=2005-2-36 查询选修2号课程且成绩在90分以上的所有学生的学号、姓名和所在系。select , from Book join borrow on book.book_id=borrow.book_idjoin reader on reader.reader_id=borrow.reader_id where borrowdate=2005-2-37 查询计算机系学生的选课情况，要求列出学生的名字、所修课程的课程号和成绩。select , from Book join borrow on book.book_id=borrow.book_idjoin reader on reader.reader_id=borrow.reader_id where borrowdate=2005-2-38 查询所有选修了VB课程的学生的情况，要求列出学生姓名和所在的系。select , from Book join borrow on book.book_id=borrow.book_idjoin reader on reader.reader_id=borrow.reader_id where borrowdate=2005-2-39 查询信息系选修VB课程的学生的成绩，要求列出学生姓名、课程号和成绩。select ,reade