design_page 39 to page 68.txt

Sql statement it is very cute

SELECT * FROM Employees
SELECT * FROM Employees_View




ALTER TABLE Employees ADD CONSTRAINT DF_DateHired DEFAULT '1/1/2000' FOR DateHired




INSERT Employees_View (FirstName, LastName, DepartmentName, Salary)
	VALUES ('John', 'Lau', 'Sales', 9000)





SELECT * FROM Employees
SELECT * FROM Employees_View




INSERT Employees_View (FirstName, LastName, DepartmentName, Salary)
	VALUES ('Peter', 'Pan', 'IT', 7000)





SELECT * FROM Employees
SELECT * FROM Employees_View



ALTER VIEW Employees_View AS
	SELECT EmployeeID, FirstName, LastName, DepartmentName, Salary
	FROM Employees WHERE DepartmentName = 'Sales'
	WITH CHECK OPTION





INSERT Employees_View (FirstName, LastName, DepartmentName, Salary)
	VALUES ('Annie', 'Ho', 'IT', 19000)





SELECT * FROM Employees
SELECT * FROM Employees_View




CREATE VIEW EmployeeSalary_View WITH SCHEMABINDING AS
	SELECT EmployeeID, FirstName, LastName, Salary FROM dbo.Employees



CREATE UNIQUE CLUSTERED INDEX IX_EmployeeID ON EmployeeSalary_View (EmployeeID)



SELECT * FROM EmployeeSalary_View


SELECT * FROM EmployeeSalary_View WHERE EmployeeID > 2



SELECT * FROM EmployeeSalary_View WITH (NOEXPAND)




SELECT * FROM Employees


BEGIN TRAN
UPDATE Employees SET DepartmentName = 'Account' WHERE EmployeeID = 7


ROLLBACK TRAN


SET IMPLICIT_TRANSACTIONS ON



UPDATE Employees SET DepartmentName = 'Account' WHERE EmployeeID = 7



SELECT * FROM Employees



SET IMPLICIT_TRANSACTIONS ON



UPDATE Employees SET DepartmentName = 'Account' WHERE EmployeeID = 7


COMMIT TRAN



SELECT * FROM Employees



CREATE TABLE Purchase (
	PurchaseNo	char(8)		NOT NULL,
	PurchaseDate	datetime	NOT NULL,
	SupplierName	char(20)	NOT NULL,
	CONSTRAINT PK_Purchase PRIMARY KEY (PurchaseNo))
CREATE TABLE PurchaseDetails (
	PurchaseNo		char(8)		NOT NULL,
	ProductName		char(20)	NOT NULL,
	UnitPrice		money		NOT NULL,
	Quantity		int		NOT NULL,
	CONSTRAINT FK_PurchaseDetails_Purchase Foreign Key (PurchaseNo) REFERENCES Purchase (PurchaseNo))
INSERT Purchase (PurchaseNo, PurchaseDate, SupplierName)
	VALUES ('20011023', '10/3/2001', 'ABC Co.')
INSERT Purchase (PurchaseNo, PurchaseDate, SupplierName)
	VALUES ('20011024', '10/4/2001', 'Apple Co.')
INSERT Purchase (PurchaseNo, PurchaseDate, SupplierName)
	VALUES ('20011025', '10/8/2001', 'Orange Co.')
INSERT Purchase (PurchaseNo, PurchaseDate, SupplierName)
	VALUES ('20011026', '10/8/2001', 'Apple Co.')
INSERT Purchase (PurchaseNo, PurchaseDate, SupplierName)
	VALUES ('20011027', '10/9/2001', 'ABC Co.')
INSERT PurchaseDetails (PurchaseNo, ProductName, UnitPrice, Quantity)
	VALUES ('20011023', 'Mouse', 30, 100)
INSERT PurchaseDetails (PurchaseNo, ProductName, UnitPrice, Quantity)
	VALUES ('20011023', 'Keyboard', 80, 50)
INSERT PurchaseDetails (PurchaseNo, ProductName, UnitPrice, Quantity)
	VALUES ('20011024', 'Display card', 340, 30)
INSERT PurchaseDetails (PurchaseNo, ProductName, UnitPrice, Quantity)
	VALUES ('20011025', 'SCSI Controller Card', 300, 20)
INSERT PurchaseDetails (PurchaseNo, ProductName, UnitPrice, Quantity)
	VALUES ('20011025', 'SCSI Harddisk', 230, 50)
INSERT PurchaseDetails (PurchaseNo, ProductName, UnitPrice, Quantity)
	VALUES ('20011026', 'IDE Harddisk', 140, 20)
INSERT PurchaseDetails (PurchaseNo, ProductName, UnitPrice, Quantity)
	VALUES ('20011027', 'Sound Card', 200, 80)




SELECT * FROM Purchase
SELECT * FROM PurchaseDetails




SELECT * FROM PurchaseDetails AS a INNER JOIN Purchase AS b
	ON a.PurchaseNo = b.PurchaseNo 




SELECT * FROM PurchaseDetails AS a INNER JOIN Purchase AS b
	ON a.PurchaseNo = b.PurchaseNo 
	WHERE PurchaseDate >= '10/8/2001' AND PurchaseDate < '10/9/2001'



UPDATE a SET UnitPrice = UnitPrice * 7.8
	FROM PurchaseDetails AS a INNER JOIN Purchase AS b
	ON a.PurchaseNo = b.PurchaseNo 
	WHERE PurchaseDate >= '10/8/2001' AND PurchaseDate < '10/9/2001'



SELECT * FROM PurchaseDetails



CREATE TABLE ColorTable (
	ColorName	Char(10)	NOT NULL)
INSERT ColorTable (ColorName) VALUES ('Silver')
INSERT ColorTable (ColorName) VALUES ('Black')



SELECT * FROM ProductList
SELECT * FROM ColorTable




SELECT Name, ColorName FROM ProductList CROSS JOIN ColorTable