用SQL语句执行

create table student

( sid int identity primary key,

name1 char(8),

sex char(2),

age int,

address char(70),

xid int foreign key references college

)

create table book

(bid int identity(2007010100,1) primary key,

name2 char(40),

author char(8),

price money,

amount bigint,

stock bigint

)

create table college

( xid int identity primary key,

name3 char(40)

)

create table bookborrow

(

sid int foreign key references student,

bid int foreign key references book,

xid int foreign key references college,

date datetime

)

2.insert book values('心灵鸡汤','amy',12.6,3,2)

3.select name2,author,bid from book where sort='计算机' or sort='英语'

4.select name1,sex from student where name1 like '李%'

5.select * from student where sid in

(select sid from bookborrow where bid=2007010102)

6.select * from bookborrow where datepart(mm,date)<=6

7.select max(amount)图书最大值,min(amount)图书数量最小值,

avg(amount)图书数量平均值,sum(amount)图书总量 from book

8.select xid,sex,count(xid) number into #temp from student group by xid,sex

select * from #temp compute sum(number)

9.select name2,avg(price)平均价格 from book group by name2 having avg(price)>25

10.select name1,name3,date from bookborrow join student on bookborrow.sid=student.sid

join college on student.xid=college.xid where name3='计算机' order by date desc

表示根据题意创建的。通过测试

1.select 书号,单价 from 图书 where 出版单位='清华大学出版社' order by 单价

2.select count(*) from 读者

3.insert into 借阅 values ('JSJ001','CJ005','2010-11-11')

4.update 图书 set 单价=单价*0.1

5.select 出版单位,sum(单价)/count(单价) from 图书 group by 出版单位

1. 求总藏书量、藏书总金额，总库存册数、最高价、最低价。

select count(图书编号) as 总藏书量,

sum(定价) as 藏书总金额,

sum(实际数量) as 总库存册数,

max(定价) as 最高价,

min(定价) as 最低价

from 图书卡片

go

2. 列出藏书在10本以上的书（书名、作者、出版社、年份）。

select 图书名称,作者姓名,出版社,出版日期

from 图书卡片

group by 图书编号 having(coung(1)>10)

order by 图书名称

go

3. 哪些出版社的藏书种类数超过100种。

select 出版社 as '藏书种类数超过100种的出版社'

from 图书卡片

group by 出版社 having(count(类别)>100)

order by 出版社

go

4. 目前实际已借出多少册书？

select sum(借出数量) as '借出数量'

from 图书卡片

go

5. 年份最久远的书。

select top 1 with ties 图书名称 from 图书卡片

order by 出版日期

go

6. “数据库系统原理教程，王珊编，清华大学出版社，1998年出版”还有几本？

select count(1) from 图书卡片

where concaints(摘要,'"数据库系统原理教程，王珊编，清华大学出版社，1998年出版"')

go

7. 哪一年的图书最多？

select top 1 with ties convert(substring(出版日期,1,4)) as 年份,count(1) as '图书数量'

from 图书卡片

group by 出版日期

order by 图书数量 desc

go

8. 哪本借书证未归还的图书最多？

select top 1 with ties A.读者编号,count(1) as '借书数量'

from 图书卡片 A,借阅 B

where A.图书编号=B.图书编号

group by A.读者编号

order by 借书数量 desc

go

9、平均每本借书证的借书册数。

select avg(借阅数量) as '平均每本借书证的借书册数'

from 借阅

go

10.哪个系的同学平均借书册数最多？

select top 1 with ties A.工作单位,avg(借阅数量) as '平均借阅数量'

from 读者 A,借阅 B

where A.读者编号=B.读者编号

group by A.工作单位

order by 平均借阅数量' desc

go

11. 最近两年都未被借过的书。

select 图书名称

from 图书卡片

where 图书编号 in(select 图书编号 from 借阅 where datediff(year,借阅日期,getdate())>2)

go

12. 列出那些借了图书逾期未归还的借书证号和图书名。

select A.读者编号 as '借书证号',B.图书名称

from 读者 as A inner join 图书卡片 as B on A.图书编号=B.图书编号

where A.应归还日期<getdate() and A.实际归还日期 is null

go

13.今年未借过书的借书证。

select 读者编号

from 读者

where 读者编号 not in(select 读者编号

from 读者

where datediff(year,借阅日期,getdate())=0)

go

14. 今年那种书出借最多？

select top 1 with ties A.类别,count(1) as '借出数量'

from 图书卡片 A,借阅 B

where datediff(year,B.借阅日期,getdate())=0

group by A.类别

order by 借出数量' desc

go

---