4.1
 use  mydatabase
go
create  table  table_dept
(  dept_id   smallint  identity(1,1)  primary  key  clustered,
dept_desc  varchar(50)  not  null ,
)
go
 use  mydatabase
go
create  table  table_dept
( dept_id   smallint  identity(1,1) constraint PK_ID  primary  key  clustered,
dept_desc   varchar(50)  not  null ,
)
go

4.2
use  mydatabase
go
create  table  table_joy
(joy_type  int,
joy_time  datetime,
joy_site   char(50),
joy_desc  char(512),
constraint  joy_key  primary key (joy _type, joy _time)
)
go


4.3 
ALTER  TABLE   table_dept
DROP  CONSTRAINT  PK_ID


4.4
alter  table  table_dept
add  constraint  PK_ID
primary  key  clustered  (dept_id)
go


4.5
use  mydatabase
go
create  table  table_joy
(joy_name  char(20),
joy_type   char(20),
joy_time  datetime,
joy_id  int   primary  key  clustered,
constraint  unique_ joy  unique   (joy_type, joy_time)
)
go


4.6
use  mydatabase
go
create  table  books
(BookID   int   identity(100000,1)   not  for  replication
check  not  for  replication  (BookID<=150000),
BookName   char(50),
constraint   PK_ID  primary  key (BookID)
)
go


4.7
use  pubs
go
alter  table  authors
NOCHECK   CONSTRAINT   ALL
go


4.8
use  mydatabase
go
Create Table pub
(  pub_id  varchar(20)  primary key,
pub_name  varchar(50),
address  varchar(20),
city   varchar(10),
state   char(2),
country  char(10))
go

Create Table  author
( author_id  varchar(20) primary key,author_name  varchar(50),
phone  varchar(20),
zipcode char(10),
)
go

Create  Table  title
title_id  int  primary key,
title_name  varchar(50),
auhtor_id  varchar(20)
constraint  foreignkey_auid
foreign  key  references  author(author_id)
on  delete  cascade
not  for  replication,
pub_id   varchar(20)
constraint  foreignkey_pubid foreign  key  references pub(pub_id)
not  for  replication
)
go


4.9
use  mydatabase
go
alter  table  title
drop  constraint
foreignkey_pubid
go
alter  table  title
with  check  add

constraint  foreignkey_pub_id
foreign  key  (pub_id)
references  pub(pub_id)
not for replication
go


4.10 
BEGIN TRANSACTION royaltychange
UPDATE titleauthor
SET royaltyper = 65
FROM titleauthor, titles
WHERE royaltyper = 75
AND titleauthor.title_id = titles.title_id
AND title = The Gourmet Microwave
UPDATE titleauthor
SET royaltyper = 35
FROM titleauthor, titles
WHERE royaltyper = 25
AND titleauthor.title_id = titles.title_id
AND title = The Gourmet Microwave
SAVE TRANSACTION percentchanged

UPDATE titles
SET price = price * 1.1
WHERE title = The Gourmet Microwave
SELECT (price * royalty * ytd_sales) * royaltyper
FROM titles, titleauthor
WHERE title = The Gourmet Microwave
AND titles.title_id = titleauthor.title_id

ROLLBACK TRANSACTION percentchanged
COMMIT TRANSACTION


4.11
use master
go
exec sp_addumpdevice disk, backup_mydb, d:\backup\bk_mydb.bak


4.12
sp_dropdevice backup_mydb ,d:\backup\bk_mydb.bak

