Example 6-1
-------------------------------------------------------------------------------------------------------------------------------------------------
Create Table tblSales
(
	SalesID varchar(10) not null,
	SaleName nvarchar(50) not null,
	SaleTarget float default 20000,
	SaleMinTarget float default 10000,
	SaleAcitvate bit default 0
)
GO

Example 6-2
-------------------------------------------------------------------------------------------------------------------------------------------------
Create Table tblProvinces
(
	ProvinceID char(3) not null primary key,
	ProvinceName nvarchar(3) not null
)
GO
Example 6-3
-------------------------------------------------------------------------------------------------------------------------------------------------
Create Table tblCustomers
(
	CustomerID int not null identity(1,1) primary key,
	CustomerName nvarchar(50) not null,
	Address nvarchar(50),
	Tel varchar(20),
	Fax varchar(20),
	Email varchar(50),
	JoinDate smalldatetime default getdate(),
	Activate bit default 0
)
GO
Example 6-4
-------------------------------------------------------------------------------------------------------------------------------------------------
Create Table tblSuppliers
(
	SupplierID int not null identity(1,1),
	SupplierName nvarchar(50) not null,
	Address nvarchar(50),
	Tel varchar(20),
	Fax varchar(20),
	Email varchar(50),
	JoinDate smalldatetime default getdate(),
	Activate bit default 0
)
GO
Example 6-5
-------------------------------------------------------------------------------------------------------------------------------------------------
Create Table tblPurchaseDetails
(
	OrderNo tinyint not null,
	InvoiceNo varchar(10) not null,
	ItemID int not null,
	Quantity float default 0,
	Price float default 0
	primary key (OrderNo,InvoiceNo,ItemID)
)
GO


drop table tblPurchaseDetails
GO
Create Table tblPurchaseDetails
(
	OrderNo tinyint not null,
	InvoiceNo varchar(10) not null,
	ItemID int not null,
	Quantity float default 0,
	Price float default 0,
	Amount as Quantity*Price
	primary key (OrderNo,InvoiceNo,ItemID)
)
GO

Create Table tblInvoices
(
	InvoiceNo varchar(10) not null primary key ,
	InvoiceDate SmallDatetime,
	CustomerID int not null,
	Description VARCHAR(100),
)
GO


Create Table tblInvoiceDetails
(
	OrderNo tinyint not null,
	InvoiceNo varchar(10) not null,
	ItemID int not null,
	Quantity float default 0,
	Price float default 0,
	Amount as Quantity*Price
	primary key (OrderNo,InvoiceNo,ItemID)
)
GO

Example 6-6
-------------------------------------------------------------------------------------------------------------------------------------------------
Alter Table tblInvoices
Add SalesID varchar(10) Not Null default 'ADMIN'
with values
GO
Example 6-7
-------------------------------------------------------------------------------------------------------------------------------------------------
Alter Table tblInvoices
Alter Column Description nvarchar(100)

GO
Example 6-8
-------------------------------------------------------------------------------------------------------------------------------------------------
Insert into tblCustomers
(CustomerName,Address,Tel,Fax,Email)
values('HuuKhang.com','56 Nguyen Hue',
'095 880 7447','095 880 7447',
'sales@huukhang.com')
GO

Example 6-9
-------------------------------------------------------------------------------------------------------------------------------------------------
'Class trong VB6
 Sub DoInsert
  Dim  gsConnect As String
  Dim Conn
  gsConnect = "DRIVER=SQL Server;SERVER=.;UID=sa;PWD=sa;DATABASE=Northwind"
  set conn = server.createObject ("ADODB.connection")
  conn.open gsConnect
  conn.execute "Insert into tblCustomers (CustomerName,Address,Tel,Fax,Email) values('HuuKhang.com','56 Nguyen Hue', '095 880 7447','095 880 7447', 'sales@huukhang.com') "
  conn.Close
  set conn=nothing
End Sub


Example 6-10
-------------------------------------------------------------------------------------------------------------------------------------------------
Drop Table tblPurchaseDetails
GO

Example 6-11
-------------------------------------------------------------------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblSuppliers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblSuppliers]
GO

Example 6-12
-------------------------------------------------------------------------------------------------------------------------------------------------
sp_rename 'tblItems' , 'tblProducts'
GO
Example 6-13
-------------------------------------------------------------------------------------------------------------------------------------------------
sp_rename  'tblProducts','tblItems' 
Go
Example 6-14
-------------------------------------------------------------------------------------------------------------------------------------------------
CREATE UNIQUE CLUSTERED INDEX salename_index
   ON tblSales(SaleName)
   WITH IGNORE_DUP_KEY
GO
Example 6-15
-------------------------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT name FROM sysindexes 
      WHERE name = 'sale_index')
   DROP INDEX tblSales.sale_index
GO

Example 6-16
-------------------------------------------------------------------------------------------------------------------------------------------------
CREATE VIEW vwInvoices
AS
SELECT  C.CustomerName, C.CustomerID, 
	I.InvoiceDate, D.ItemID, 
	tblItems.ItemName, D.Quantity, 
	D.Price, D.Amount
FROM    tblCustomers C 
	INNER JOIN tblInvoices I         
	ON C.CustomerID = I.CustomerID 
	INNER JOIN tblInvoiceDetails D 
	ON I.InvoiceNo = D.InvoiceNo 
	INNER JOIN tblItems 
	ON D.ItemID = tblItems.ItemID
GO
Example 6-17
-------------------------------------------------------------------------------------------------------------------------------------------------
CREATE VIEW vwInvoice
With Encryption
AS
SELECT  C.CustomerName, C.CustomerID, 
	I.InvoiceDate, D.ItemID, 
	tblItems.ItemName, D.Quantity, 
	D.Price, D.Amount
FROM    tblCustomers C 
	INNER JOIN tblInvoices I         
	ON C.CustomerID = I.CustomerID 
	INNER JOIN tblInvoiceDetails D 
	ON I.InvoiceNo = D.InvoiceNo 
	INNER JOIN tblItems 
	ON D.ItemID = tblItems.ItemID

GO

