design_page 1 to page 38.txt

Sql statement it is very cute

CREATE DATABASE MyData ON
	(NAME = DataFile, FILENAME = 'C:\MyData\DataFile.mdf', SIZE = 2)
	LOG ON (NAME = LogFile, FILENAME = 'C:\MyData\LogFile.ldf', SIZE = 2)


USE Sample
CREATE TABLE ProductUpdate(
	ProductID 	int		NOT NULL	PRIMARY KEY NONCLUSTERED,
	UnitCost 	money 		NOT NULL,
	QtyInStock 	int 		NOT NULL)



INSERT ProductUpdate (ProductID, UnitCost, QtyinStock) VALUES (1, 499, 79)
INSERT ProductUpdate (ProductID, UnitCost, QtyinStock) VALUES (3, 1199, 19)



SELECT * FROM ProductList



UPDATE PL SET PL.UnitCost = PU.UnitCost, PL.QtyInStock = PU.QtyInStock 
	FROM ProductList PL, ProductUpdate PU
	WHERE PL.ProductID = PU.ProductID



SELECT COUNT(*) FROM ProductList



SELECT SUM(UnitCost * QtyInStock) FROM ProductList



SELECT MAX(UnitWeight) FROM ProductList



SELECT MIN(UnitWeight) FROM ProductList



CREATE TABLE ProductSales(
	Period 		int		NOT NULL,
	ProductName	char(20)	NOT NULL,
	QtySold 	int 		NOT NULL,
	SalesAmount 	money 		NOT NULL)



INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200101, 'Walkman', 5, 2000)
INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200101, 'Discman', 1, 1600)
INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200101, 'MP3 Player', 10, 10000)
INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200101, 'MD Player', 15, 30000)
INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200101, 'Radio', 8, 2400)
INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200102, 'Walkman', 10, 4000)
INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200102, 'Discman', 2, 3200)
INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200102, 'MP3 Player', 20, 20000)
INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200102, 'MD Player', 30, 60000)
INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200102, 'Radio', 8, 2400)
INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200103, 'Walkman', 6, 2400)
INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200103, 'Discman', 3, 4800)
INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200103, 'MP3 Player', 9, 9000)
INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200103, 'MD Player', 30, 60000)
INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200103, 'Radio', 2, 600)
INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200104, 'Walkman', 3, 1200)
INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200104, 'Discman', 5, 8000)
INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200104, 'MP3 Player', 1, 1000)
INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200104, 'MD Player', 13, 26000)
INSERT ProductSales (Period, ProductName, QtySold, SalesAmount) VALUES (200104, 'Radio', 6, 1800)



SELECT ProductName, SUM(SalesAmount) FROM ProductSales GROUP BY ProductName



SELECT ProductName, SUM(SalesAmount) FROM ProductSales 
	GROUP BY ProductName ORDER BY 2



SELECT ProductName, SUM(SalesAmount) FROM ProductSales 
	GROUP BY ProductName ORDER BY 2 DESC


SELECT ProductName, SUM(SalesAmount) AS 'Total Sales' FROM ProductSales 
	GROUP BY ProductName ORDER BY 'Total Sales' DESC



SELECT TOP 5 ProductName, Period, SalesAmount FROM ProductSales 
	ORDER BY SalesAmount DESC



SELECT TOP 3 ProductName, SUM(SalesAmount) FROM ProductSales 
	GROUP BY ProductName ORDER BY 2 DESC



SELECT TOP 3 ProductName, SUM(QtySold) FROM ProductSales 
	GROUP BY ProductName ORDER BY 2 DESC



SELECT ProductName, SUM(QtySold) FROM ProductSales 
	GROUP BY ProductName ORDER BY 2 DESC



SELECT TOP 3 WITH TIES ProductName, SUM(QtySold) FROM ProductSales 
	GROUP BY ProductName ORDER BY 2 DESC



SELECT ProductName FROM ProductSales



SELECT DISTINCT ProductName FROM ProductSales



CREATE TABLE AudioProducts (
	ProductName	char(20)	NOT NULL)
INSERT INTO AudioProducts (ProductName)
	SELECT DISTINCT ProductName FROM ProductSales




SELECT * FROM AudioProducts



CREATE TABLE OverseasEmployees (
	FirstName 	char(20)	NOT NULL,
	LastName	char(20)	NOT NULL,
	LondonPound 	decimal(10,5)	NULL,
	JapaneseYen 	int		NULL,
	USDollar	decimal		NULL)
INSERT OverseasEmployees (FirstName, LastName, LondonPound) 
	VALUES ('Bill', 'Chan', 2222.22222)
INSERT OverseasEmployees (FirstName, LastName, JapaneseYen) 
	VALUES ('John', 'Lee', 300000)
INSERT OverseasEmployees (FirstName, LastName, USDollar) 
	VALUES ('Ann', 'Fung', 3333)



SELECT * FROM OverseasEmployees



SELECT FirstName, LastName, LondonPound * 11, JapaneseYen * 0.07, USDollar * 7.8
	FROM OverseasEmployees



SELECT FirstName, LastName, (LondonPound * 11 + JapaneseYen * 0.07 + USDollar * 7.8)
	FROM OverseasEmployees



SELECT FirstName, LastName, COALESCE(LondonPound * 11, JapaneseYen * 0.07,
	USDollar * 7.8) FROM OverseasEmployees


SELECT FirstName, LastName, CAST(COALESCE(LondonPound * 11, JapaneseYen * 0.07, 
	USDollar * 7.8) AS MONEY) AS HKDEquivalent FROM OverseasEmployees



SELECT AVG(SalesAmount) FROM ProductSales WHERE ProductName = 'Walkman'


SELECT * FROM ProductSales WHERE ProductName = 'Walkman' AND SalesAmount < 2400 


SELECT * FROM ProductSales WHERE ProductName = 'Walkman' AND SalesAmount < 
	(SELECT AVG(SalesAmount) FROM ProductSales WHERE ProductName = 'Walkman')




SELECT PS1.* FROM ProductSales AS PS1 WHERE PS1.SalesAmount < 
	(SELECT AVG(PS2.SalesAmount) FROM ProductSales AS PS2 
	WHERE PS1.ProductName = PS2.ProductName)




SELECT PS1.* FROM ProductSales AS PS1 WHERE PS1.SalesAmount < 
	(SELECT AVG(PS2.SalesAmount) FROM ProductSales AS PS2 
	WHERE PS1.ProductName = PS2.ProductName)
	ORDER BY PS1.ProductName, PS1.Period



SET SHOWPLAN_TEXT ON



SELECT * FROM NW_Customers ORDER BY ContactTitle



SELECT * FROM Employees ORDER BY EmployeeID



SELECT * FROM NW_Customers ORDER BY ContactTitle



SELECT * FROM Employees ORDER BY EmployeeID



INSERT NW_Customers SELECT * FROM Northwind.dbo.Customers



SELECT * FROM NW_Customers ORDER BY ContactTitle



SELECT * FROM NW_Customers ORDER BY ContactName