3.1
CREATE TABLE employee
( employeeid   char(6)  NOT NULL l,
 name   varchar(20)  NOT NULL,
 sex   char(2)   NULL,
 birthday    datetime   null,
 department   char(6)   not  null,
 address varchar(50) null,
tel varchar(20),
constraint employeekey primary key(employeeid)
)


3.2 
CREATE VIEW employee_view
AS
SELECT employeeid,name, address, tel
From employee


3.3
ALTER TABLE employee
  AS
ADD  email  varchar(30)


3.4
DROP TABLE employee

3.5
use pubs
go
select  pub_id, pub_name, city,state,country
from  publishers
go


3.6
use pubs
go
select  *
from  publishers


3.7
select  @@language
go


3.8
use  northwind
go
select  top  10  *
from  orders
go


3.9
use  northwind
go
select   top  10  percent *
from   orders
go


3.10
use  northwind
go
select   country
from   employees
go


3.11
use  pubs
go
select  title_id,  price,  price-price*0.05
from  titles
go


3.12
use  pubs
go
select  au_lname+. +au_fname , city +.+state
from  authors
go


3.13
use  pubs
go
select  title_id  'ͼ',  price'ͼԭ',  price-price*0.2  'ͼּ'
from  titles
go


3.14
use  pubs
go
select  'ͼ'= title_id, 'ͼԭ'= price, 'ͼּ'= price-price*0.2
from  titles
go


3.15
use  pubs
go
select title_id  AS  'ͼ', price  AS  'ͼԭ, price-price*0.2  AS  'ͼּ'
from  titles
go


3.16
use pubs
go

select au_fname, au_lname, phone AS Telephone
from authors
where state = 'CA' and au_lname <> 'McBadden'
go

3.17
use  northwind
select   orderid,customerid
from   orders
where orderdate > '10/1/1997'
go

3.18
use  pubs
go

select  title_id ,  type ,  price
from  titles
where  price between  10  and  30


3.19
use  pubs
go
select  au_id,  au_lname,  au_fname,  state
from  authors
where   state  not  in  ('CA','KS','MI','IN')

3.20
use  pubs
go
select  au_lname, au_fname,  city,  state
from  authors
where   au_fname   like  'M%'
go

3.21
use  pubs
go

select  au_lname,  au_fname,   phone,  au_id
from  authors
where   au_id  like  '89_-%'

3.22
use  pubs
go
select  au_lname,   au_fname,  phone,  au_id
from  authors
where  au_id  like  '89[2345]-%'


3.23
use  northwind
go

select  lastname,firstname,birthdate,city,country
from employees
where  firstname  like  'an[a-z]%'

3.24
use  northwind
go

select  lastname,firstname,birthdate,city,country
from    employees
where  firstname  like '\%%'
ESCAPE  '\'


3.25
use  northwind
go
select  lastname,firstname,birthdate,city,country
from    employees
where  firstname  like  't[H[abc]L]'
ESCAPE  't'


3.26
use northwind
go

select avg(unitprice)
from products
go


3.27
use northwind
go
select sum(quantity)
from [order details]
go


3.28
use northwind
go

select avg(unitprice)
from products
where supplierid = 12


3.29
use northwind
go
select avg(quantity) 'avg_qty',sum(quantity) 'sum_qty',
count(distinct orderid)
from [order details]
go


3.30
use pubs
go

select count(*)
from authors
where au_lname = 'Ringer'
go

3.31
use pubs
go

select country,count(*)
from publishers
where country in ('USA','France')
group by country
go

3.32
use pubs
go

select country
from publishers
group by country
having count(*)<3
go


3.33 
use pubs
go

select type,price,advance
from titles
order by type
compute sum(advance)
go

3.34
use pubs
go

select type,price,advance
from titles
order by type
compute sum(advance)  by type
compute sum(price)
go


3.35
use pubs
go

select publishers.pub_id,pub_name,city,state,country,logo,pr_info
from publishers,pub_info
where publishers.pub_id = pub_info.pub_id and country = 'USA'


3.36
use pubs
go
select publishers.pub_id, pub_name, pr_info
from publishers join pub_info
on publishers.pub_id = pub_info.pub_id
where  country = 'USA'


3.37
use pubs
go
select au_lname,au_fname,title,pub_name
from authors a
join titleauthor ta
on a.au_id = ta.au_id
join titles t
on t.title_id = ta.title_id
join publishers p
on t.pub_id = p.pub_id


3.38
use pubs
go
select au_lname, au_fname
from authors
where au_id in(select au_idfrom titleauthorwhere title_id    
in(select title_idfrom titles
where type = 'popular_comp'))

3.39 
use pubs
go

select title_id
from titles
where price > 50
union
select title_id
from titleauthor
where royaltyper>=75
go


3.40
IF EXISTS(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'T1')

DROP TABLE T1
GO

CREATE TABLE T1 (
column1 int NOT NULL,
column2 varchar(20),
column3 int NULL,
column4 varchar(40))

INSERT INTO T1 (column1,column2,column3,column4)
VALUES (1234,'aaaa',-20,'cccccc')

SELECT * FROM T1

3.41
update T1
set column2 = 'bbcc', column3 = 46
where column1 = 1234

3.42
delete T1
where column2 = 'bbcc'

3.43
use northwind
go

create table T2
( col1 int not null,
col2 char(10) not null,
col3 varchar(30)
)
go
insert into T2
values(123,'testdata','thisisatestingdata')
go
select *
from T2
go


3.44
declare @firstvar int,@secondvar varchar(20)
select @firstvar = 100,@secondvar = 'you are welcome!'
print @firstvar
Print @secondvar
go


3.45
use northwind
go
declare @maxvalue money,@minvalue money
select @maxvalue = max(UnitPrice),
@minvalue = min(unitPrice)
from products
where supplierid = 12
print convert(varchar(20),@maxvalue)
print convert(varchar(20),@minvalue)
go


3.46
use northwind
go
declare @rows int
set @rows = (select count(*)
from orders)
print 'Ϊ' + convert(char(10),@rows)
go


3.47
use pubs
go
declare @vLastName varchar(40),@vFirstName varchar(20)
set @vLastName = 'Green'
select @vFirstName = au_fname
from authors
where au_lname = @vLastName
print @vFirstName + '.' +@vLastName
go


3.48
SELECT GETDATE() AS 'ǰںʱ',
@@CONNECTIONS AS 'ͼĿ'


3.49 
SELECT @@IDLE AS 'Idle ms', GETDATE() AS 'As of'

3.50
SELECT @@IO_BUSY AS 'IO ms', GETDATE() AS 'As of'

3.51
SELECT @@LANGUAGE AS 'Language Name'

3.52
SET LANGUAGE 'English'
SELECT @@LANGID AS 'Language ID'

3.53
SELECT @@MAX_CONNECTIONS

3.54
SELECT @@MAX_PRECISION

3.55
SELECT @@SERVERNAME

3.56
SELECT @@VERSION

3.57
SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'

3.58
USE Pubs
GO
SELECT LEN(Pub_Name) AS 'Length', Pub_Name
FROM Publishers
WHERE State = 'MA'


3.59
USE pubs
GO
SELECT CHARINDEX('medical', notes)
FROM titles
WHERE title_id = 'BU2075'
GO


3.60 
SELECT STR(623.48, 6, 1)
GO


3.61
USE Northwind
Go
SELECT FirstName, SUBSTRING(FirstName, 1, 1)
FROM Employees
Go


3.62
SELECT DATEPART(month, GETDATE())  AS  '·'

3.63
Select month('05/26/2004'),day('05/26/2004'),year('05/26/2004')

3.64
select datediff(mm,'2004-05-26',getdate())

3.65
select datediff(mm,'2004-05-26',getdate())

3.66
select  ceiling(12.5),  floor(12.5),  round(12.4567,3)

3.67
SELECT RADIANS(6.25)

3.68
SELECT RAND(), RAND(8)

3.69
USE pubs
GO
SELECT title,royalty
FROM titles
WHERE CAST(royalty AS char(10)) LIKE '2%'
GO


3.70
DECLARE @sys_usr char(30)
SET @sys_usr = SYSTEM_USER
SELECT 'The current system user is: '+ @sys_usr
GO


3.71
USE master
SELECT dbid, DB_NAME(dbid) AS DB_NAME
FROM sysdatabases
ORDER BY dbid
GO


3.72
SELECT ISDATE('10/1/2005')
SELECT ISDATE('2005.2.29')
GO


3.73
USE pubs
GO
SELECT AVG(ISNULL(price, $10.00))
FROM titles
GO


3.74
use pubs
GO
IF EXISTS (SELECT zip FROM authors WHERE zip = '94705')
PRINT 'Berkeley author'
GO


3.75
PRINT 'This message was printed on ' +
RTRIM(CONVERT(varchar(30), GETDATE())) + '.'

3.76
USE pubs

IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') < $10
BEGIN
PRINT 'The following titles are excellent mod_cook books:'
PRINT ' '
SELECT SUBSTRING(title, 1, 35) AS Title
FROM titles
WHERE type = 'mod_cook'
END
ELSE
PRINT 'Average title price is more than $10.'


3.77
USE pubs

IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') < $15
BEGIN
PRINT 'The following titles are excellent mod_cook books:'
PRINT ' '
SELECT SUBSTRING(title, 1, 35) AS Title
FROM titles
WHERE type = 'mod_cook'
END
ELSE
IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') > $15
BEGIN
PRINT 'The following titles are expensive mod_cook books:'
PRINT ' '
SELECT SUBSTRING(title, 1, 35) AS Title
FROM titles
WHERE type = 'mod_cook'
END


3.78
USE Northwind
GO
IF EXISTS(SELECT OrderID
FROM Orders
WHERE CustomerID = 'Frank')
PRINT 'cannot be deleted'
ELSE
BEGIN
DELETE Customers WHERE CustomerID = 'Frank'
PRINT  'deleted!'
END


3.79
USE pubs
GO
SELECT   Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO


3.80
USE pubs
GO
WHILE (SELECT AVG(price) FROM titles) < $30
BEGIN
UPDATE titles
SET price = price * 2
SELECT MAX(price) FROM titles
IF (SELECT MAX(price) FROM titles) > $50
BREAK
ELSE
CONTINUE
END
PRINT 'Too much for the market to bear'


3.81
declare  @sum int, @count int
select   @sum=1, @count=1
label_1:
select  @sum=@sum * @count
select  @count=@count+1
if   @count<=6
goto  label_1
select  @count, @sum


3.82
BEGIN
WAITFOR TIME '18:30'
EXECUTE store_all_data
END


3.83
CREATE PROCEDURE checkstate @param varchar(11)
AS
IF (SELECT state FROM authors WHERE au_id = @param) = 'CA'
RETURN 1
ELSE
RETURN 2


3.84
USE Northwind
GO
CREATE FUNCTION func1
(@varmoney money)
RETURNS nvarchar(5)
BEGIN
DECLARE @returnvalue nvarchar(5)
IF @varmoney < 1000
SET @returnvalue = 'Ʒ'
ELSE
SET @returnvalue = 'Ʒ'
RETURN @returnvalue
END
select dbo.func1(2000)


3.85
create database mydb1
on
(name=mydb1_dat,
filename='e:\tsql\mydb1dat.mdf',
size=10,
maxsize=50,
filegrowth=5)
log on
(name='mydb1_log',
filename='e:\tsql\ mydb1dat.ldf',
size=5MB,
maxsize=25MB,
filegrowth=5MB)
go


3.86 
create database score
on
primary (name=scor1,
filename='e:\tsql\scordat1.mdf',
size=10MB,
maxsize=20,
filegrowth=2),
(name=scor2,
filename='e:\tsql\scordat2.ndf',
size=10,
maxsize=20,
filegrowth=2),
(name=scor3,
filename='e:\tsql\scordat3.ndf',
size=10,
maxsize=20,
filegrowth=2)
log on
(name=scorlog1,
filename='e:\tsql\scorlog1.ldf',
size=10,
maxsize=20,
filegrowth=2),
(name=scorlog2,
filename='e:\tsql\scorlog2.ldf',
size=10,
maxsize=20,
filegrowth=2)
go


3.87
create database Manager
on
primary
(name=man1_dat,
filename='e:\tsql\man1dat.mdf',
size=10,
maxsize=50,
filegrowth=15%),
(name=man2_dat,
filename='e:\tsql\man2dat.ndf',
size=10,
maxsize=50,
filegrowth=15%),
filegroup ManGroup1
(name=Grp1Fil_dat,
filename='e:\tsql\G1Fildt.ndf',
size=10,
maxsize=50,
filegrowth=5),
(name=Grp1Fil2_dat,
filename='e:\tsql\G1Fi2dt.ndf',
size=10,
maxsize=50,
filegrowth=5),
filegroup ManGroup2
(name=Grp2Fil_dat,
filename='e:\tsql\G2Fi1dt.ndf',
size=10,
maxsize=50,
filegrowth=5),
(name=Grp2Fi1_dat,
filename='e:\tsql\G2Fi2dt.ndf',
size=10,
maxsize=50,
filegrowth=5)
log on
(name='Company_log',
filename='e:\tsql\comlog.ldf',
size=5,
maxsize=25,
filegrowth=5)
go


3.88 
USE Manager
GO
sp_helpfile
GO
USE Manager
GO
sp_helpfile Grp2Fil_dat
GO


3.89
Use  Manager
Go
Sp_helpfilegroup
Go
Use Manager
Sp_helpfilegroup ManGroup2
Go


3.90
exec sp_dboption  'Manager', 'single_user', 'FALSE'

3.91
ALTER DATABASE Manager
ADD  FILE
(NAME=Test1dat2,
FILENAME=e:\tsql\t1dat2.ndf,
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTh=5MB)
Go


3.92 
USE  Manager
GO
DBCC  SHRINKFILE(TEST1DAT2,15)
GO


3.93
exec sp_droptype addr

3.94
CREATE TABLE employee
( empid   char(6)   unique not null,
name   varchar(20)   NOT NULL,
sex   char(2)   NULL,
birthday    datetime   null,
addr  varchar(50)   null,
telphone varchar(20) null,
salary   money   null,
memo   ntext   null
)


3.95
create table student(
id  char(6)  primary key,
name  char(10)  not null,
age  int  null,
sex char(2) null,
department  char(20)  null,
memo  char(30)  null
)


3.96
sp_rename  student1, student

3.97
use  pubs
go
insert  publishers
(pub_id, pub_name, city, state, country)
values
(5886, Mato, Berkeley, CA, USA)
go


3.98
use  pubs
go
insert  into  publishers
values
(5886, Mato, Berkeley, CA, USA)
go


3.99 
USE northwind
GOINSERT customers
SELECT substring(firstname, 1, 3)+ substring (lastname, 1, 2) ,lastname,  firstname, title, address, city ,region, postalcode, country, homephone, NULL
FROM employees
GO


3.100
use  pubs
go
update  authors
set   au_lname=Mike
where  au_id=341-22-1782
go


3.101
use  pubs
go
update  authors
set   au_id=881-881-881,
au_lname= au_fname,
au_fname=John,
phone=415 375-6558,
address=PO Box 790,
city=Covelo,
state=CA,
zip=95428,
contract=0,
where  au_id=341-22-1782
go


3.102
use  pubs
go
delete
from  authors
where  city=Oakland
go


3.103
use  pubs
go
delete
from   authors
go


3.104
use  pubs
truncate  table authors
go


3.105
create view stutea_view
as
Select student.name,
student.age,student.sex,teacher.name,age
from
student,teacher
where student.age < 22

3.106
use  pubs
go
create  view  author_view
as
select  au_id,  au_fname,  au_lname, city, state
from  authors
where state = 'CA'
go

use  pubs
go
select   *  from  author_view
go

3.107
use  pubs
go
alter view author_view
as
select au_id,au_lname,au_fname,phone
from authors
where phone like '6%'
go


3.108
sp_rename  view_name, view_id

3.109
drop  view  student_view, teacher_view

3.110
create  view  employees_view(empID, name, sex, salary)
as
select  empID, name, sex, salary
from  employees
where  name=ǡ


3.111
create  view  employees_view
as
select  *  from  employees
update  employees_view
set  name=Ȼ
where  name=Ƚ


3.112
delete  from  employees_view
where  name=Ȼ

3.113
USE Northwind
GO
CREATE UNIQUE  CLUSTERED INDEX emp_id_index
ON employees (EmployeeID desc)


3.114
USE Northwind
GO

CREATE UNIQUE  INDEX  emp_name_index
ON employees (firstname,lastname desc)


3.115
sp_helpindex employees

3.116
USE Northwind
GO
Exec sp_rename 'employees.[emp_name_index]', 'emp_name_ind', 'index'


3.117
drop index employees.a

3.118
use pubs
go
if exists (select name from sysobjects
where name='au_info' and type='p')
drop procedure au_info
go
create procedure au_info
as
select au_lname,au_fname,title,pub_name
from authors a join titleauthor ta on a.au_id=ta.au_id
join titles t on t.title_id =ta.title_id join publishers p
on t.pub_id = p.pub_id
go


3.119
use pubs
if  exists (select name from sysobjects
where name = 'au_info1' and type = 'P')
drop prodedure au_info1
GO
use pubs
go
create procedure au_info1
@lastname varchar(40),
@firstname varchar(20)
as
select au_lname, au_fname, title, pub_name
from authors a inner join titleauthor ta
on a.au_id = ta.au_id inner join titles t
on t.title_id = ta.title_id inner join publishers p
on t.pub_id = p.pub_id
where  au_fname = @firstname
and au_lname = @lastname
go


3.120
use pubs
if exists (select name from sysobjects
where name = 'au_info3' and type = 'p')
drop procedure au_info3
go
use pubs
go
create procedure au_info3
@lastname varchar(30) = 'd%',
@firstname varchar(18) = '%'
as
select au_lname, au_fname, title, pub_name
from authors a inner join titleauthor ta
on a.au_id = ta.au_id inner join titles t
on t.title_id = ta.title_id inner join publishers p
on t.pub_id = p.pub_id
where au_fname like @firstname
and au_lname like @lastname
go


3.121
use pubs
go
if exists(select name from sysobjects
where name = 'titles_sum' and type = 'p')
drop procedure titles_sum
go
use pubs
go
create procedure titles_sum @title varchar(40) = '%', @sum money output
as
select 'title name' = title
from titles
where title like @title
select @sum = sum(price)
from titles
where title like @title
go


declare @totalcost money
execute titles_sum 'the%', @totalcost output
if @totalcost < 200
begin
print ' '
print 'all of these titles can be purchased for less than $200.'
end
else
select 'the total cost of these titles is $'
+ rtrim(cast(@totalcost as varchar(20)))

declare @totalcost money
execute titles_sum 'the%', @totalcost output
if @totalcost < 200
begin
print ' '
print 'all of these titles can be purchased for less than $200.'
end
else
select 'the total cost of these titles is $'
+ rtrim(cast(@totalcost as varchar(20)))

3.122
use pubs
go
create trigger my_trigger
on employee
instead of delete
as
raiserror('Ȩɾ¼!',10,1)


3.123
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'employee_insupd' AND type = 'TR')
DROP TRIGGER employee_insupd
GO
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
DECLARE @min_lvl tinyint,
@max_lvl tinyint,
@emp_lvl tinyint,
@job_id smallint
SELECT @min_lvl = min_lvl,
@max_lvl = max_lvl,
@emp_lvl = i.job_lvl,
@job_id = i.job_id
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id
JOIN jobs j ON j.job_id = i.job_id
IF (@job_id = 1) and (@emp_lvl <> 10)
BEGIN
RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
RAISERROR ('The level for job_id:%d should be between 
%d and %d.',16, 1, @job_id, @min_lvl, @max_lvl)
ROLLBACK TRANSACTION
END






