借鉴的一个哥们的组合查询例子

create database library

on

(

name='library_data',

filename='d:\DB\library_data.mdf',

size=5MB,

maxsize=10MB,

filegrowth=10%

)

 

log on

(

name='library_log',

filename='d:\DB\library_log.ndf',

size=5MB,

maxsize=10MB,

filegrowth=10%

)

use library

--------------------------表student--------------

create table student

(

stuID char(5) not null,

stuName varchar(20) not null,

maJor varchar(20) not null

)

alter table student

 add constraintPK_stuID primary key(stuID)

 

go

 

------------------------表book----------------

create table book

(

BID char(5) not null,

title varchar(20) not null,

author varchar(20) not null

)

alter table book

 add constraintPK_BID primary key(BID)

go

 

--------------------------表borrow ------------------

create table borrow

(

borrowID char(5) not null,

stuID char(5) not null,

BID char(5) not null,

T_time datetime not null,

B_time datetime

)

alter table borrow

 add constraintPK_borrowID primary key(borrowID),

  constraintFK_stuID foreign key(stuID)

   referencesstudent(stuID),

  constraintFK_BID foreign key(BID)

   referencesbook(BID)

go

 

---------------------添加数据--------------------------

--学生信息表中插入数据--

INSERT INTOstudent(stuID,stuName,major)VALUES('1001','林林','计算机')

INSERT INTOstudent(stuID,stuName,major)VALUES('1002','白杨','计算机')

INSERT INTOstudent(stuID,stuName,major)VALUES('1003','虎子','英语')

INSERT INTOstudent(stuID,stuName,major)VALUES('1004','北漂的雪','工商管理')

INSERT INTOstudent(stuID,stuName,major)VALUES('1005','五月','数学')

--图书信息表中插入数据--

INSERT INTO book(BID,title,author)VALUES('B001','人生若只如初见','安意如')

INSERT INTO book(BID,title,author)VALUES('B002','入学那天遇见你','晴空')

INSERT INTO book(BID,title,author)VALUES('B003','感谢折磨你的人','如娜')

INSERT INTO book(BID,title,author)VALUES('B004','我不是教你诈','刘庸')

INSERT INTO book(BID,title,author)VALUES('B005','英语四级','白雪')

--借书信息表中插入数据--

INSERT INTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T001','1001','B001','2007-12-26',null)

INSERT INTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T002','1004','B003','2008-1-5',null)

INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T003','1005','B001','2007-10-8','2007-12-25')

INSERT INTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T004','1005','B002','2007-12-16','2008-1-7')

INSERT INTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T005','1002','B004','2007-12-22',null)

INSERT INTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T006','1005','B005','2008-1-6',null)

INSERT INTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T007','1002','B001','2007-9-11',null)

INSERT INTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T008','1005','B004','2007-12-10',null)

INSERT INTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T009','1004','B005','2007-10-16','2007-12-18')

INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T010','1002','B002','2007-9-15','2008-1-5')

INSERT INTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T011','1004','B003','2007-12-28',null)

INSERT INTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T012','1002','B003','2007-12-30',null)

 

--题1 查询'计算机'专业学生在'2007-12-15'至'2008-1-8'时间段内借书的学生编号,学生名称,图书编号,图书名称,借书日期

select 学生编号=stuID,学生名称=(select stuName from student where stuID=borrow.stuID),

图书馆编号=BID,图书名称=(select title frombook where BID=borrow.BID),借出日期=T_time

 from borrowwhere stuID in (select stuID from student where major='计算机')andT_time between '2007-12-15'and '2008-1-8'

 

--题2 查询所有借过图书的学生编号,学生名称,专业

select 学生编号=stuID,学生名称=stuName,专业=maJor from student where stuIDin(select stuID from borrow where T_time is not null)

 

--题3 查询借过作者为'安意如'的图书的学生姓名,图书名称,借出日期,归还日期

select 学生名称=(select stuName from studentwhere stuID=borrow.stuID),图书名称=(select title from bookwhere BID=borrow.BID),借出日期=T_time,归还日期=B_time from borrow where BID in(select BID from book where author='安意如')

 

--题4 查询目前借书但未归还的学生名称及未还图书数量

select 学生名称=(select stuName from studentwhere stuID=borrow.stuID),未还书数量=count(*)from borrowwhere B_time is null group by stuID