page 154 to page 184.txt

Sql statement it is very cute

USE SAMPLE
CREATE TABLE Invoice (
	InvoiceNo 	int 		IDENTITY (1, 1) NOT NULL	PRIMARY KEY NONCLUSTERED,
	InvoiceDate 	datetime 			NOT NULL,
	CustomerID 	int 				NOT NULL,
	SalesID 	int 				NOT NULL,
	ShipmentDate 	datetime 			NULL)
CREATE TABLE InvoiceDetails(
	InvoiceNo 	int 				NOT NULL,
	ProductID 	int 				NOT NULL,
	Quantity 	int 				NOT NULL,
	UnitPrice 	money 				NOT NULL,
	PRIMARY KEY NONCLUSTERED (InvoiceNo, ProductID))
CREATE TABLE ProductList(
	ProductID 	int 		IDENTITY (1, 1)	NOT NULL	PRIMARY KEY NONCLUSTERED,
	Name 		nchar(10) 			NOT NULL,
	UnitWeight 	decimal(18, 2) 			NOT NULL,
	UnitCost 	money 				NOT NULL,
	QtyInStock 	int 				NOT NULL)
CREATE TABLE Customer(
	CustomerID 	int 		IDENTITY (1, 1)	NOT NULL	PRIMARY KEY NONCLUSTERED,
	Name 		varchar(50) 			NOT NULL,
	Address 	varchar(50) 			NOT NULL,
	Country 	char(10) 			NOT NULL,
	Telephone 	char(15) 			NOT NULL,
	Fax 		char(15) 			NULL)
CREATE TABLE SalesStaff(
	SalesID 	int 		IDENTITY (1, 1)	NOT NULL	PRIMARY KEY NONCLUSTERED,
	LastName 	varchar(50) 			NOT NULL,
	FirstName 	varchar(50) 			NOT NULL,
	CommissionRate 	decimal(2, 2) 			NOT NULL,
	MobilePhone 	char(10) 			NULL)



ALTER TABLE InvoiceDetails ADD CONSTRAINT FK_InvoiceDetails_Invoice
	FOREIGN KEY (InvoiceNo) REFERENCES Invoice (InvoiceNo)



ALTER TABLE Invoice ADD CONSTRAINT FK_Invoice_Customer 
	FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID)



ALTER TABLE Invoice ADD CONSTRAINT FK_Invoice_SalesStaff 
	FOREIGN KEY (salesID) REFERENCES SalesStaff (SalesID)



ALTER TABLE InvoiceDetails ADD CONSTRAINT FK_InvoiceDetails_ProductList
	FOREIGN KEY (ProductID) REFERENCES ProductList (ProductID)



USE Sample
INSERT Customer (Name, Address, Country, Telephone, Fax) 
	VALUES ('ABC Co.', '1 King Road', 'Hong Kong', '1234-2234', '3234-4234')
INSERT SalesStaff (LastName, FirstName, CommissionRate, MobilePhone) 
	VALUES ('Chan', 'Paul', 0.10, '8888-8888')



SELECT * FROM Customer
SELECt * FROM SalesStaff



INSERT Invoice (InvoiceDate, CustomerID, SalesID) 
	VALUES ('10/15/2001', 1, 1)



SELECT * FROM Invoice



INSERT Invoice (InvoiceDate, CustomerID, SalesID) 
	VALUES ('10/16/2001', 2, 1)



INSERT Customer (Name, Address, Country, Telephone) 
	VALUES ('XYZ Co.', '1 Queen Road', 'Hong Kong', '5234-6234')
SELECT * FROM Customer



INSERT Invoice (InvoiceDate, CustomerID, SalesID) 
	VALUES ('10/16/2001', 2, 1)



UPDATE Invoice SET CustomerID=3 WHERE CustomerID=2



DELETE Customer WHERE CustomerID=2



DELETE Invoice WHERE CustomerID=2
DELETE Customer WHERE CustomerID=2



SELECT * FROM Customer
SELECt * FROM Invoice



USE Sample
INSERT ProductList (Name, UnitWeight, UnitCost, QtyinStock) VALUES ('VCD Player', 5, 500, 80)
INSERT ProductList (Name, UnitWeight, UnitCost, QtyinStock) VALUES ('MP3 Player', 2, 600, 130)
INSERT ProductList (Name, UnitWeight, UnitCost, QtyinStock) VALUES ('DVD Player', 7, 1200, 20)
SELECT * FROM ProductList



INSERT InvoiceDetails (InvoiceNo, ProductID, Quantity, UnitPrice) VALUES (1, 1, 20, 600)
INSERT InvoiceDetails (InvoiceNo, ProductID, Quantity, UnitPrice) VALUES (1, 2, 10, 800)
INSERT InvoiceDetails (InvoiceNo, ProductID, Quantity, UnitPrice) VALUES (1, 3, 3, 1600)
SELECT * FROM InvoiceDetails



SELECT * FROM Invoice
SELECT * FROM InvoiceDetails
SELECT * FROM ProductList
SELECT * FROM Customer
SELECT * FROM SalesStaff



SELECT Invoice.InvoiceNo, Invoice.InvoiceDate, InvoiceDetails.ProductID, InvoiceDetails.Quantity
	FROM Invoice INNER JOIN InvoiceDetails ON Invoice.InvoiceNo = InvoiceDetails.InvoiceNo



SELECT A.InvoiceNo, A.InvoiceDate, D.Name, C.Name, B.ProductID, B.Quantity, B.UnitPrice, E.FirstName
	FROM Invoice AS A 
	INNER JOIN InvoiceDetails AS B ON A.InvoiceNo = B.InvoiceNo
	INNER JOIN ProductList AS C ON B.ProductID = C.ProductID
	INNER JOIN Customer AS D ON A.CustomerID = D.CustomerID
	INNER JOIN SalesStaff AS E ON A.SalesID = E.SalesID



USE Sample
CREATE TABLE MobileCharges (
	MobilePhone 	char(10)			NOT NULL,
	BillingDate 	datetime 			NOT NULL,
	LocalCharges 	money 				NOT NULL,
	IDDCharges 	money 				NOT NULL,
	OtherCharges 	money	 			NOT NULL)


ALTER TABLE SalesStaff ADD CONSTRAINT UNIQUE_MobilePhone UNIQUE (MobilePhone)




ALTER TABLE MobileCharges ADD CONSTRAINT FK_MobileCharges_SalesStaff 
	FOREIGN KEY (MobilePhone) REFERENCES SalesStaff (MobilePhone)



INSERT MobileCharges (MobilePhone, BillingDate, LocalCharges, IDDCharges, OtherCharges) 
	VALUES ('8888-8888', '8/31/2001', 400, 200, 0)
INSERT MobileCharges (MobilePhone, BillingDate, LocalCharges, IDDCharges, OtherCharges) 
	VALUES ('8888-8888', '9/30/2001', 320, 900, 88)



INSERT MobileCharges (MobilePhone, BillingDate, LocalCharges, IDDCharges, OtherCharges) 
	VALUES ('9999-9999', '9/30/2001', 320, 900, 88)




INSERT SalesStaff (LastName, FirstName, CommissionRate, MobilePhone) 
	VALUES ('Lee', 'Ann', 0.15, '8888-8888')




USE Sample
SELECT * FROM SalesStaff
SELECT * FROM MobileCharges




UPDATE SalesStaff SET MobilePhone = '9999-9999' WHERE SalesID = 1




ALTER TABLE MobileCharges DROP CONSTRAINT FK_MobileCharges_SalesStaff
ALTER TABLE MobileCharges ADD CONSTRAINT FK_MobileCharges_SalesStaff 
	FOREIGN KEY (MobilePhone) REFERENCES SalesStaff (MobilePhone) 
	ON UPDATE CASCADE




UPDATE SalesStaff SET MobilePhone = '9999-9999' WHERE SalesID = 1



SELECT * FROM SalesStaff
SELECT * FROM MobileCharges