design_page 69 to page 108.txt

Sql statement it is very cute

ALTER TABLE ProductList
	ADD CONSTRAINT CK_QtyInStock CHECK (QtyInStock >= 0)



SELECT * FROM ProductList



UPDATE ProductList SET QtyInStock = QtyInStock - 20 WHERE ProductID = 3



CREATE TRIGGER EmployeesChanges ON Employees FOR INSERT, UPDATE, DELETE AS
	EXEC master.dbo.xp_cmdshell 'net send administrator Employees Table Changed!'



SELECT * FROM Employees



UPDATE Employees SET Salary = Salary + 1000  WHERE EmployeeID = 7



SELECT * FROM Employees



CREATE TRIGGER EmployeesNoCheck ON Employees FOR INSERT AS
	IF ((SELECT COUNT(*) FROM Employees) > 7)
		BEGIN
			PRINT 'Employees Table cannot have more than 7 records'
			ROLLBACK
		END



INSERT Employees (FirstName, LastName, DepartmentName, DateHired, Salary)
	VALUES ('Jacky', 'Chan', 'Sales', '11/11/2001', 10000)



SELECT * FROM Employees



DROP TRIGGER EmployeesChanges
DROP TRIGGER EmployeesNoCheck



CREATE TABLE CostHistory(
	ChangedDate	datetime	NOT NULL,
	ProductName	nchar(10)	NOT NULL,
	OldCost		int		NOT NULL)



SELECT * FROM ProductList
SELECT * FROM CostHistory



CREATE TRIGGER ProductCostChange ON ProductList FOR DELETE, UPDATE AS
	INSERT INTO CostHistory
		SELECT GETDATE(), d.Name, d.UnitCost FROM deleted d
		LEFT OUTER JOIN ProductList PL ON d.Name = PL.Name
		WHERE (PL.UnitCost IS NULL) OR (d.UnitCost <> PL.UnitCost)



INSERT ProductList (Name, UnitWeight, UnitCost, QtyInStock)
	VALUES ('MD Player', 2, 1200, 100)
SELECT * FROM ProductList
SELECT * FROM CostHistory



UPDATE ProductList SET UnitCost = 1222 WHERE ProductID = 4
SELECT * FROM ProductList
SELECT * FROM CostHistory



UPDATE ProductList SET QtyInStock = 22 WHERE ProductID = 4
SELECT * FROM ProductList
SELECT * FROM CostHistory



DELETE ProductList WHERE ProductID = 4
SELECT * FROM ProductList
SELECT * FROM CostHistory



TRUNCATE TABLE InvoiceDetails



SELECT * FROM ProductList
SELECT * FROM InvoiceDetails



CREATE TRIGGER StockCheck ON InvoiceDetails FOR INSERT, UPDATE AS
	IF ((SELECT COUNT(*) FROM inserted i
	INNER JOIN ProductList PL ON i.ProductID = PL.ProductID
	WHERE (i.Quantity > PL.QtyInStock)) > 0)
		BEGIN
			PRINT 'One or more products do not have enough stock!'
			ROLLBACK
		END



INSERT InvoiceDetails (InvoiceNo, ProductID, Quantity, UnitPrice)
	VALUES (1, 2, 20, 800)



INSERT InvoiceDetails (InvoiceNo, ProductID, Quantity, UnitPrice)
	VALUES (1, 3, 20, 1500)



SELECT * FROM InvoiceDetails



CREATE TABLE GoodsReceived(
	DateIn		datetime	NOT NULL,
	ProductID	int		NOT NULL,
	QtyIn		int		NOT NULL)
CREATE TABLE GoodsDispatched(
	DateOut		datetime	NOT NULL,
	ProductID	int		NOT NULL,
	QtyOut		int		NOT NULL)



CREATE TRIGGER ProductIn ON GoodsReceived FOR INSERT, UPDATE, DELETE AS
	UPDATE PL SET PL.QtyInStock = PL.QtyInStock + i.QtyIn FROM ProductList PL
	INNER JOIN inserted i ON i.ProductID = PL.ProductID
	UPDATE PL SET PL.QtyInStock = PL.QtyInStock - d.QtyIn FROM ProductList PL
	INNER JOIN deleted d ON d.ProductID = PL.ProductID



CREATE TRIGGER ProductOut ON GoodsDispatched FOR INSERT, UPDATE, DELETE AS
	UPDATE PL SET PL.QtyInStock = PL.QtyInStock + d.QtyOut FROM ProductList PL
	INNER JOIN deleted d ON d.ProductID = PL.ProductID
	UPDATE PL SET PL.QtyInStock = PL.QtyInStock - i.QtyOut FROM ProductList PL
	INNER JOIN inserted i ON i.ProductID = PL.ProductID



SELECT * FROM ProductList




INSERT GoodsReceived (DateIn, ProductID, QtyIn)
	VALUES ('11/11/2001', 1, 300)
SELECT * FROM GoodsReceived
SELECT * FROM ProductList



INSERT GoodsDispatched (DateOut, ProductID, QtyOut)
	VALUES ('11/11/2001', 2, 100)
SELECT * FROM GoodsDispatched
SELECT * FROM ProductList



CREATE TABLE Staff (
	StaffID		int		NOT NULL	PRIMARY KEY,
	StaffName	char(30)	NOT NULL,
 	HKIDNo		char(10)	NOT NULL, 
  	Department	char(10)	NOT NULL,
	Salary		money		NOT NULL) 
CREATE TABLE StaffPrivate (
	HKIDNo		char(10)	NOT NULL	PRIMARY KEY,
	Address		varchar(30)	NOT NULL,
	Phone		char(10)	NOT NULL) 
INSERT Staff(StaffID, StaffName, HKIDNo, Department, Salary) 
	VALUES (1, 'Annie Ho', 'A111111(1)', 'Marketing', 20000)
INSERT StaffPrivate(HKIDNo, Address, Phone) 
	VALUES ( 'A111111(1)', '1 King Road', '21111111')
GO
CREATE VIEW StaffFullInfo AS
	SELECT S.StaffID, S.StaffName, S.HKIDNo, S.Department, S.Salary, SP.Address,
	SP.Phone FROM Staff S INNER JOIN StaffPrivate SP
	ON S.HKIDNo = SP.HKIDNo



SELECT * FROM StaffFullInfo



INSERT StaffFullInfo (StaffID, StaffName, HKIDNo, Department, Salary, Address, Phone)
	VALUES (2, 'Raymond Lau', 'B222222(2)', 'Sales', 15000, '2 Queen Road', '22222222')



CREATE TRIGGER InsertStaff ON StaffFullInfo INSTEAD OF INSERT AS
	INSERT INTO Staff(StaffID, StaffName, HKIDNo, Department, Salary) 
	SELECT StaffID, StaffName, HKIDNo, Department, Salary FROM inserted
	INSERT INTO StaffPrivate(HKIDNo, Address, Phone) 
	SELECT HKIDNo, Address, Phone FROM inserted



INSERT StaffFullInfo (StaffID, StaffName, HKIDNo, Department, Salary, Address, Phone)
	VALUES (2, 'Raymond Lau', 'B222222(2)', 'Sales', 15000, '2 Queen Road', '22222222')
SELECT * FROM Staff
SELECT * FROM StaffPrivate
SELECT * FROM StaffFullInfo



SELECT * FROM ProductSales



CREATE VIEW SalesSummary AS
	SELECT ProductName, SUM(SalesAmount)AS TotalSales 
	FROM ProductSales GROUP BY ProductName



UPDATE SalesSummary SET ProductName = 'MiniDisc Player'
	WHERE ProductName = 'MD Player'



CREATE TRIGGER UpdateSalesSummary ON SalesSummary INSTEAD OF UPDATE AS
	UPDATE ProductSales SET ProductName = (SELECT ProductName FROM inserted)
	WHERE ProductName = (SELECT ProductName FROM deleted)



UPDATE SalesSummary SET ProductName = 'MiniDisc Player'
	WHERE ProductName = 'MD Player'
SELECT * FROM ProductSales
SELECT * FROM SalesSummary



SELECT ProductName, PurchaseDate, UnitPrice, SupplierName FROM Purchase AS p
	INNER JOIN PurchaseDetails AS pd ON p.PurchaseNo = pd.PurchaseNo
	ORDER BY ProductName
SELECT SupplierName, SUM(UnitPrice * Quantity) AS TotalPurchase
	FROM Purchase AS p INNER JOIN PurchaseDetails AS pd 
	ON p.PurchaseNo = pd.PurchaseNo	GROUP BY SupplierName



CREATE PROCEDURE ShowPurchases AS
	SELECT ProductName, PurchaseDate, UnitPrice, SupplierName FROM Purchase AS p
	INNER JOIN PurchaseDetails AS pd ON p.PurchaseNo = pd.PurchaseNo
	ORDER BY ProductName
	SELECT SupplierName, SUM(UnitPrice * Quantity) AS TotalPurchase
	FROM Purchase AS p INNER JOIN PurchaseDetails AS pd 
	ON p.PurchaseNo = pd.PurchaseNo	GROUP BY SupplierName



EXEC ShowPurchases



SELECT * FROM Productlist



CREATE PROCEDURE UpdateProduct AS
	UPDATE ProductList SET QtyInStock = QtyInStock - 1 WHERE ProductID = 1



EXEC UpdateProduct
SELECT * FROM ProductList



ALTER PROCEDURE UpdateProduct 
	@ProductID	int,
	@DeductQty	int
AS
	UPDATE ProductList SET QtyInStock = QtyInStock - @DeductQty
	WHERE ProductID = @ProductID



EXEC UpdateProduct 2, 20
SELECT * FROM ProductList



ALTER PROCEDURE UpdateProduct 
	@ProductID	int = 1,
	@DeductQty	int = 1
AS
	UPDATE ProductList SET QtyInStock = QtyInStock - @DeductQty
	WHERE ProductID = @ProductID



EXEC UpdateProduct
SELECT * FROM ProductList



ALTER PROCEDURE UpdateProduct 
	@ProductID	int = 1,
	@DeductQty	int = 1,
	@RemainQty	int	OUTPUT
AS
	UPDATE ProductList SET QtyInStock = QtyInStock - @DeductQty
	WHERE ProductID = @ProductID
	SELECT @RemainQty = QtyInStock FROM ProductList WHERE ProductID = @ProductID



DECLARE @NewQty int
EXEC UpdateProduct 2, 3, @NewQty OUTPUT
PRINT 'Remaining Quantity is' + STR(@NewQty)



DECLARE @NewQty int
EXEC UpdateProduct 6, 1, @NewQty OUTPUT
PRINT 'Remaining Quantity is' + STR(@NewQty)



ALTER PROCEDURE UpdateProduct 
	@ProductID	int = 1,
	@DeductQty	int = 1,
	@RemainQty	int	OUTPUT
AS
	UPDATE ProductList SET QtyInStock = QtyInStock - @DeductQty
	WHERE ProductID = @ProductID
	IF @@ROWCOUNT = 0
		RETURN(-1)
	ELSE
	BEGIN
		SELECT @RemainQty = QtyInStock FROM ProductList WHERE ProductID = @ProductID
		RETURN(0)
	END



DECLARE @ReturnValue int
DECLARE @NewQty int
EXEC @ReturnValue = UpdateProduct 6, 1, @NewQty OUTPUT
IF @ReturnValue < 0
	PRINT 'Product Not Found'
ELSE
	PRINT 'Remaining Quantity is' + STR(@NewQty)



DECLARE @ReturnValue int
DECLARE @NewQty int
EXEC @ReturnValue = UpdateProduct 2, 1, @NewQty OUTPUT
IF @ReturnValue < 0
	PRINT 'Product Not Found'
ELSE
	PRINT 'Remaining Quantity is' + STR(@NewQty)