SQL SERVER 5

1-2
select DATEADD(YY,2,GETDATE())

2-1
create database question5 on primary(
name ='question5_data.mdf',
fileName='C:\GATAnswer\question5_data.mdf',
size=5MB,
maxsize =8MB,
filegrowth =12% 

)
log on(
name ='question5_log.ldf',
fileName='C:\GATAnswer\question5_log.ldf',
size=5MB,
maxsize =8MB,
filegrowth = 14%

2-2
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 publishers
2-7
drop database question5

3-1
insert into publishers (PubCode,PubName) values('TDBC','人民出版社')

3-2
update category set CategoryName ='面向对象语言开发'  where CategoryName ='JAVA'

3-3
delete from publishers where PubName ='人民出版社'

3-4
select * from publishers order by CityCode  Asc

3-5
select distinct price from titles 

3-6
select TitleCode 出版物代码, TitleName 出版物名称 , Price 价格  from titles where Price > 40

4-1
select TitleName,PubDate,Price from titles where Price between 40 and 80 order by Price Desc

4-2
select CategoryID,CategoryName
from category
where CategoryName in ('JAVA','电子商务','C#基础')

4-3
select TitleName,Price,Author
from titles
where Author like '张%

4-4
select TitleName,Author,PubCode
from titles
where PubCode like '_X%'

4-5
select Price*0.5 as '5折价格', Price, titleName
from titles

4-6
select TitleName, LEFT(TitleName,1)+' '+RIGHT(TitleName,1) as 出版物首位, Author
from titles

4-7
select CEILING(4.52)
select FLOOR(4.52)

4-8
select @@ERROR

5-1
select MIN(PubDate) as 最早出版日期
from titles

5-2
select year(MIN(PubDate)) as 最早年份, CategoryID 
from titles
group by CategoryID

5-3
select YEAR(MIN(PubDate)) as 最早年份, CategoryID
from titles
group by CategoryID
Having YEAR(MIn(PubDate)) > 2009

5-4
select c.CityCode,CityName,PubName
from city c left join publishers p on c.CityCode = p.CityCode
order by c.CityCode desc

5-5
select TitleName,Author,Price 
from titles 
where Price >(
select min(Price) from titles
)

5-6
select CategoryID,CategoryName from category where CategoryName = 'ASP.NET' 
union
select CategoryID,CategoryName from category where CategoryName = '移动互联网'

6-1
declare @vname char(8),@vtime time
set @vname = '体育新闻'
set @vtime = '19:55:00'

6-2
declare @vname char(8),@vtime time
set @vname = '体育新闻'
set @vtime = '19:55:00'
print '局部变量： @vanme:'+ @vname +'  @vtime: '+ CONVERT(Varchar,@vtime,120)
print '全局变量：@@Language(当前所用语言的名称)：'+@@Language

6-3
declare @vcode char(4),@vdate date
set @vcode = 'T012'
set @vdate = (
    select PubDate
    from titles
    where TitleCode = @vcode
)
select * from titles
select @vcode+'出版物的的出版日期是：'+convert(varchar, @vdate, 120) as 显示出版日期

6-4
create view view1 as (
select TitleName as 出版物名称, Author as 作者, Price as 价格, PubCode as 出版社代码, PubDate as 出版日期
from titles
where TitleName like '%数据库%'
)

select * from view1

6-5
create view view2 as
select c.CategoryID as 类别ID, CategoryName as 类别名称, Max(Price) as 最高价格
from category c left join titles t on c.CategoryID = t.CategoryID
order by c.CategoryID,c.CategoryName

select * from view2

6-6
create index newidx on titles(Author)

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

7-1
create procedure sproc1
as
select PubName,Phone
from publishers p inner join titles t on p.PubCode = t.PubCode
where t.TitleName = 'Oracle数据库初学者指南'
exec sproc1

7-2
create procedure sproc2
@titleName varchar(50)
as
select PubName,Phone
from publishers p inner join titles t on p.PubCode = t.PubCode
where t.TitleName = @titleName

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

7-3
create procedure sproc3
@pubName varchar(50),
@addr varchar(100) output
as
begin
    select @addr = Address
    from publishers 
    where PubName = @pubName
end

declare @pa varchar(100)
exec sproc3 '新希望出版社', @pa output
print '新希望出版社的地址是'+@pa

7-4
create TRIGGER newtrigger
on publishers 
for update
as 
    if update(Phone)
        print '出版社电话已更改，触发器起到作用'

update publishers
set Phone = '020-87056742'
where PubName = '白云出版社'

7-5
drop procedure sproc1
drop procedure sproc2
drop procedure sproc3

drop trigger newtrigger

8-1
exec sp_addlogin 'Login5','555555'
8-2
exec sp_grantdbaccess 'Login5','dbUser5'
8-3
grant select,insert on titles to dbUser5