SQL server3

1-2
select  DAY( replace(replace(replace('2014年03月15日','年','-'),'月','-'),'日','') )

2-1
create database question3
on primary(
    name='question3_data.mdf',
    filename='C:\GATAnswer\question3_data.mdf',
    size= 7MB,
    filegrowth=3%,
    maxsize= 13MB
)
log on(
    name='question3_log.ldf',
    filename='C:\GATAnswer\question3_log.ldf',
    size= 7MB,
    filegrowth=5%,
    maxsize= 13MB
)

2-2
create table category(
    CategoryID int not null,
    CategoryName varchar(50) not null,
    primary key (CategoryID)

)

create table publishers(
    PubCode Char(4) not null,
    PubName char(50) not null,
    Phone char(15),
    Address Varchar(100),
    CityCode Char(4),
    primary key (PubCode)
)

2-3
alter table publishers add pnum int

2-4
alter table publishers alter column address varchar(120)

2-5
alter table publishers drop column pnum

2-6
drop table category
drop table publishers

2-7
drop database question3

3-1
insert into category (CategoryName) values('互联网安全')  

3-2
update publishers set PubName='电子出版社' where PubCode = 'DDXC'

3-3
delete from category where CategoryName ='互联网安全'

3-4
select * from publishers order by CityCode desc

3-5
select distinct(CityCode) from  city

3-6
select PubCode 出版社代码,PubName 出版社名字,Address 地址, CityCode 城市代码 from         publishers where CityCode ='HBJ'

4-1
select TitleName,Author,PubDate 
from  titles 
where DAY(PubDate)
between 10 and 20
order by DAY(PubDate) desc

4-2
select* from category where CategoryID IN (1,3,5)

4-3
select * from category where CategoryName like '%开发'

4-4
select * from city where CityName like '_州'

4-5
select PubDate,DATEADD(YEAR,2,PubDate) newYear from titles  order by YEAR(DATEADD(YEAR,2,PubDate))  DESC

4-6
select * from titles where TitleName like '%数据库%'

4-7
select SQRT(6.4)
select PI()

4-8
select ORIGINAL_LOGIN()

5-1
select SUM(Price) from titles

5-2
select sum(price)出版物价值, PubCode from titles group by PubCode

5-3
select sum(price)出版物价值, PubCode from titles group by PubCode  having sum(price)>100

5-4
select a.*,b.PubName from city a inner join publishers b  on a.CityCode = b.CityCode     where a.CityName in ('广州','北京')

5-5
select a.*,b.PubName from city a inner join publishers b  on a.CityCode = b.CityCode     where a.CityName = '北京'

5-6
select * from category where CategoryName='数据库'
union
select * from category where CategoryName='JAVA'
union
select * from category where CategoryName='电子商务'

6-1
declare @vcity char(8),@vdate datetime
set @vcity ='重庆'
set @vdate ='1997-06-18'

6-2
declare @vcity char(8),@vdate datetime
set @vcity ='重庆'
set @vdate ='1997-06-18'
print '局部变量：@vcity:'+@vcity+'@vdate:'+cast(@vdate as varchar)
print '全局变量：@Language (当前所有语言的名称)：'+@@Language

6-3
declare @titlecode char(6), @price money
set @titlecode = 'T008'
select @price = Price from titles where TitleCode = @titlecode
select * from titles
select 显示价格= @titlecode+'出版社的价格是：'+CAST(@price as varchar(20))

6-4
create view view1 as
select TitleName,Author,Price,CategoryID from titles where Price<40

select * from view1

6-5
create view view2 as
select a.*,b.avgprice 平均价格 from category a left join (select CategoryID,AVG(price)     avgprice from titles group by CategoryID) b on a.CategoryID = b.CategoryID

select * from view2

6-6
create index newidx
on titles(CategoryID)

6-7
drop view view1,view2
drop index newidx on titles

7-1
create proc sproc1
as 
begin
select CategoryName from category where CategoryID=(select CategoryID from titles where TitleName = 'Oracle数据库初学者指南')
end

use examDB
go 
exec sproc1
go

7-2
create proc sproc2
@titlename varchar(80)
as 
begin
select CategoryName from category where CategoryID=(select CategoryID from titles where TitleName = @titlename)
end

use examDB
go 
exec sproc2 'Oracle数据库初学者指南'
go

7-3
create proc sproc3
@pubcode char(4),
@pubname char(50) output
as 
begin
select @pubname = PubName from publishers where PubCode = @pubcode
print @pubcode+'的名称是：'+@pubname
end

declare @pn char(50)
exec sproc3 'AXXW',@pn output
go

7-4
create trigger newtrigger
on titles
for update
as 
if update(author)
begin 
print('出版物作者已更改，触发器起到作用')
end

use examDB
go
update titles set Author = '张田' where TitleName = '数据库管理'
go

7-5
drop trigger newtrigger
drop proc sproc1,sproc2,sproc3

8-1
exec sp_addlogin 'Login3','333333'
8-2
exec sp_grantdbaccess 'Login3','dbUser3'
8-3
grant select,insert,delete on publishers to dbUser3