CLASS NOTES 2014-09-22

SELECT TOP 1 * FROM [Sales].[SalesOrderHeader]

[Production].[ProductCostHistory]

[Production].[ProductCategory]

[Production].[Product]

[Sales].[SalesOrderDetail]

[Sales].[SalesOrderHeader]

--QUERY > RESULTSET / CHANGE DATA / DELETE DATA

--CRUD

---CREATE

---READ

---UPDATE

---DELETE

--READ<--THIS IS WHAT WE ARE GOING TO START WITH TODAY AND GET THROUGH THE ½ of it

--SELECT COLUMNS,.... FROM TableName

USE AdventureWorks2008

--SELECT

--SELECT SalesOrderID,AccountNumber, TaxAmt,CustomerID

--FROM [Sales].[SalesOrderHeader]

--SELECT + FIELD ALIAS

--SELECT SalesOrdId=SalesOrderID , AccountNumber as AccNum, TaxAmt TXAmt,CustomerID, MagicNumber = 42

--FROM [Sales].[SalesOrderHeader]

--BASIC TYPES

nvarchar(length) - unicode variable length

varchar(length) - ascii variable length

char(length) - fixed size character sring

nchar -

int - 4 byte integer

double - double precision 8 bytes

--SELECT + FIELD ALIAS + ORDER BY

--SELECT SalesOrdId=SalesOrderID , AccountNumber as AccNum, TaxAmt TXAmt,CustomerID, MagicNumber = 42

--FROM [Sales].[SalesOrderHeader]

--ORDER BY SalesOrderID DESC -- SORT BY SalesOrderID DESC

----SELECT + FIELD ALIAS + ORDER BY + WHERE

--SELECT SalesOrdId=SalesOrderID , AccountNumber as AccNum, TaxAmt TXAmt,CustomerID, MagicNumber = 42

--FROM [Sales].[SalesOrderHeader]

--WHERE SalesOrderID > 69000

--ORDER BY SalesOrderID DESC -- SORT BY SalesOrderID DESC

----SELECT + FIELD ALIAS + ORDER BY + WHERE + LOGICAL OPERATORS + LIKE

--SELECT SalesOrdId=SalesOrderID , AccountNumber as AccNum, TaxAmt TXAmt,CustomerID, MagicNumber = 42

--FROM [Sales].[SalesOrderHeader]

--WHERE SalesOrderID > 69000 AND AccountNumber like '%1' -- everything that is like 'blah blah 1' blah blah can be 0 or more characters

--ORDER BY SalesOrderID DESC -- SORT BY SalesOrderID DESC

----SELECT + FIELD ALIAS + ORDER BY + WHERE + LOGICAL OPERATORS + LIKE with patterns

--SELECT SalesOrdId=SalesOrderID , AccountNumber as AccNum, TaxAmt TXAmt,CustomerID, MagicNumber = 42

--FROM [Sales].[SalesOrderHeader]

--WHERE SalesOrderID > 69000 AND AccountNumber like '%-0%1' -- everything that is like 'blah blah 1' blah blah can be 0 or more characters

--ORDER BY SalesOrderID DESC -- SORT BY SalesOrderID DESC

----SELECT + FIELD ALIAS + ORDER BY + WHERE + LOGICAL OPERATORS + LIKE with patterns

--SELECT SalesOrdId=SalesOrderID , AccountNumber as AccNum, TaxAmt TXAmt,CustomerID, MagicNumber = 42

--FROM [Sales].[SalesOrderHeader]

--WHERE SalesOrderID > 69000 AND AccountNumber like '%-0%1' -- everything that is like 'blah blah 1' blah blah can be 0 or more characters

--ORDER BY SalesOrderID DESC -- SORT BY SalesOrderID DESC

--SELECT + AGGREGATE FUNCTION 1 - COUNT

--SELECT COUNT(SalesOrderID) CountOfOrders -- , AccountNumber as AccNum, TaxAmt TXAmt,CustomerID, MagicNumber = 42

--FROM [Sales].[SalesOrderHeader]

--WHERE SalesOrderID > 69000 AND AccountNumber like '%-0%1' -- everything that is like 'blah blah 1' blah blah can be 0 or more characters

--ORDER BY SalesOrderID DESC -- SORT BY SalesOrderID DESC

--SELECT + GROUP BY + AGGREGATE FUNCTION 1 - COUNT + GROUP BY

--SELECT AccountNumber, COUNT(SalesOrderID) CountOfOrders -- , AccountNumber as AccNum, TaxAmt TXAmt,CustomerID, MagicNumber = 42

--FROM [Sales].[SalesOrderHeader]

--WHERE SalesOrderID > 69000 AND AccountNumber like '%-0%1' -- everything that is like 'blah blah 1' blah blah can be 0 or more characters

----ORDER BY SalesOrderID DESC -- SORT BY SalesOrderID DESC

--GROUP BY AccountNumber

----SELECT + GROUP BY + AGGREGATE FUNCTION 1 - COUNT + GROUP BY

--SELECT AccountNumber, COUNT(SalesOrderID) CountOfOrders, SUM([TotalDue]) TotalSpend -- , AccountNumber as AccNum, TaxAmt TXAmt,CustomerID, MagicNumber = 42

--FROM [Sales].[SalesOrderHeader]

--WHERE SalesOrderID > 69000 AND AccountNumber like '%-0%1' -- everything that is like 'blah blah 1' blah blah can be 0 or more characters

--GROUP BY AccountNumber

--ORDER BY TotalSpend DESC -- SORT BY SalesOrderID DESC

-- PK - Primary Key Unique Identifier for a row

-- FK - Foreign Key - Unique Identifier in some other other table that is used in

-- a current table to identify that OTHER tables' unique rows

----SELECT BAD WRONG JOIN DONT DO COMMA SEPARTED TABLES, JOINED BY WHERE CLAUSE

--SELECT AccountNumber, [SalesOrderDetailID]--, SalesOrderID -- , AccountNumber as AccNum, TaxAmt TXAmt,CustomerID, MagicNumber = 42

--FROM [Sales].[SalesOrderHeader],

-- [Sales].[SalesOrderDetail]

--WHERE AccountNumber like '%-0%1'

--AND [Sales].[SalesOrderDetail].SalesOrderID = SalesOrderHeader.SalesOrderID

--PROPER JOIN

--SELECT AccountNumber, [SalesOrderDetailID]--, SalesOrderID -- , AccountNumber as AccNum, TaxAmt TXAmt,CustomerID, MagicNumber = 42

--FROM [Sales].[SalesOrderHeader] JOIN

-- [Sales].[SalesOrderDetail]

-- ON [Sales].[SalesOrderDetail].SalesOrderID = SalesOrderHeader.SalesOrderID

--WHERE AccountNumber like '%-0%1'

--SELECT SalesOrderId, AccountNumber, [SalesOrderDetailID]--, SalesOrderID -- , AccountNumber as AccNum, TaxAmt TXAmt,CustomerID, MagicNumber = 42

--FROM [Sales].[SalesOrderHeader] JOIN

-- [Sales].[SalesOrderDetail]

-- ON [Sales].[SalesOrderDetail].SalesOrderID = SalesOrderHeader.SalesOrderID

--WHERE AccountNumber like '%-0%1'

--Msg 209, Level 16, State 1, Line 108

--Ambiguous column name 'SalesOrderId'.

----ambiguity solved

--SELECT [Sales].[SalesOrderDetail].SalesOrderId, AccountNumber, [SalesOrderDetailID]--, SalesOrderID -- , AccountNumber as AccNum, TaxAmt TXAmt,CustomerID, MagicNumber = 42

--FROM [Sales].[SalesOrderHeader] JOIN

-- [Sales].[SalesOrderDetail]

-- ON [Sales].[SalesOrderDetail].SalesOrderID = SalesOrderHeader.SalesOrderID

--WHERE AccountNumber like '%-0%1'

----ambiguity solved + aliases

--SELECT H.SalesOrderId, H.AccountNumber, [SalesOrderDetailID]--, SalesOrderID -- , AccountNumber as AccNum, TaxAmt TXAmt,CustomerID, MagicNumber = 42

--FROM [Sales].[SalesOrderHeader] H JOIN

-- [Sales].[SalesOrderDetail] D

-- ON H.SalesOrderID = D.SalesOrderID

--WHERE AccountNumber like '%-0%1'

--ASSIGNMENT SHOW TOTAL SALES FOR YEAR 2007 BY SALESPERSON

--SORT BY TOTAL SALES DESCENDING

--DESIRED RESULTS:

--Salesperson's Name | TOTAL SALES

-----------------------------------

--LastName, FirstName | $234234

-----------------------------------

--Person, SalesPerson, OrderHeader

--SELECT LastName + ', ' + FirstName as [Sales Person Name],

-- Sum(TotalDue) [Total Sales]

--FROM Person.Person as P

-- Join Sales.SalesPerson as SP

-- ON SP.BusinessEntityID = P.BusinessEntityID

-- Join Sales.SalesOrderHeader as SOH

-- ON SOH.SalesPersonID = SP.BusinessEntityID

--GROUP BY LastName, FirstName

--ORDER BY [Total Sales] DESC

--Person, SalesPerson, OrderHeader + all people even the ones with no sales

SELECT LastName + ', ' + FirstName as [Sales Person Name],

Sum(TotalDue) [Total Sales]

FROM Person.Person as P

left outer Join Sales.SalesPerson as SP

ON SP.BusinessEntityID = P.BusinessEntityID

left outer Join Sales.SalesOrderHeader as SOH

ON SOH.SalesPersonID = SP.BusinessEntityID

WHERE P.PersonType in('SP','EM')

GROUP BY LastName, FirstName

ORDER BY [Total Sales] DESC