USE AdventureWorks

-- Style counts --- http://www.dpriver.com/pp/sqlformat.htm

SELECT     Person.Contact.Title, Person.Contact.FirstName, Person.Contact.LastName  Sales.SalesOrderHeader.OrderDate, Sales.SalesOrderHeader.DueDate,
                      Sales.SalesOrderHeader.ShipDate, Sales.SalesOrderDetail.OrderQty, Production.Product.Name, Sales.SalesOrderHeader.RevisionNumber
FROM         Sales.SalesOrderHeader INNER JOIN
                      Sales.SalesOrderDetail ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID INNER JOIN
                      Person.Contact ON Sales.SalesOrderHeader.ContactID = Person.Contact.ContactID INNER JOIN
                      Production.Product ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
WHERE     Person.Contact.FirstName LIKE 'A%'
ORDER BY Person.Contact.LastName
GO

--

SELECT   Person.Contact.Title,
         Person.Contact.FirstName,
         Person.Contact.LastName
         Sales.SalesOrderHeader.OrderDate,
         Sales.SalesOrderHeader.DueDate,
         Sales.SalesOrderHeader.ShipDate,
         Sales.SalesOrderDetail.OrderQty,
         Production.Product.Name,
         Sales.SalesOrderHeader.RevisionNumber
FROM     Sales.SalesOrderHeader
         INNER JOIN Sales.SalesOrderDetail
           ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID
         INNER JOIN Person.Contact
           ON Sales.SalesOrderHeader.ContactID = Person.Contact.ContactID
         INNER JOIN Production.Product
           ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
WHERE    Person.Contact.FirstName LIKE 'A%'
ORDER BY Person.Contact.LastName
GO

/*
STANDARD SQL
*/

/*

SELECT ---
... MANDATORY
... Tabular result is returned

FROM ---
...MANDATORY

WHERE ---
... Filter

GROUP BY ---
... Database examines every that is generated by the
    FROM clause; then applies optional WHERE Filter

HAVING ---
... Used in conjunction with GROUP BY

ORDER BY ---
...Only way to ENSURE Order ... Indexes or data entry is not guarenteed - Page bleed


JOIN ---
... Left Inner - (Natural)
... Left Outer
... Right Outer
... Cross
... Cartesian Product

*/

-- SELECT (MINIMUM)
SELECT AddressLine1, City
FROM Person.Address
GO

--- WHERE
SELECT AddressLine1, City
FROM Person.Address
WHERE City = 'Ottawa'
GO

--- GROUP BY
SELECT City, COUNT(*) AS CNT
FROM Person.Address
GROUP BY City

SELECT City, COUNT(*) AS CNT
FROM Person.Address
WHERE City  = 'Ottawa'
GROUP BY City
GO

--- HAVING -
SELECT City, COUNT(*) AS CNT    --- ERROR DEMO
FROM Person.Address
WHERE City  = 'Ottawa' and COUNT(*) > 1
GROUP BY City

SELECT City, COUNT(*) AS CNT
FROM Person.Address
WHERE City  = 'Ottawa'
GROUP BY City
HAVING COUNT(*) >= 7
GO

-- ORDER BY
SELECT City, COUNT(*) AS CNT
FROM Person.Address
GROUP BY City
HAVING COUNT(*) >= 7
ORDER BY City DESC
GO

-- WHAT Clause is Parsed by the DB engine 1st ???
SELECT ID, City   --- ERROR DEMO
FROM Person.Address
GO

-- JOIN ANSI --- Versus --- Old Style
--ANSI Join Style
SELECT Person.Address.City, HumanResources.EmployeeAddress.ModifiedDate
FROM Person.Address
JOIN HumanResources.EmployeeAddress
ON Person.Address.AddressID = HumanResources.EmployeeAddress.AddressID

--Old Join Style
SELECT Person.Address.City, HumanResources.EmployeeAddress.ModifiedDate
FROM Person.Address, HumanResources.EmployeeAddress
WHERE Person.Address.AddressID = HumanResources.EmployeeAddress.AddressID

----------------   Optimization

USE AdventureWorks

SELECT   Person.Contact.Title,
         Person.Contact.FirstName,
         Person.Contact.LastName,
         Sales.SalesOrderHeader.OrderDate,
         Sales.SalesOrderHeader.DueDate,
         Sales.SalesOrderHeader.ShipDate,
         Sales.SalesOrderDetail.OrderQty,
         Production.Product.Name,
         Sales.SalesOrderHeader.RevisionNumber
FROM     Sales.SalesOrderHeader
         INNER JOIN Sales.SalesOrderDetail
           ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID
         INNER JOIN Person.Contact
           ON Sales.SalesOrderHeader.ContactID = Person.Contact.ContactID
         INNER JOIN Production.Product
           ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
WHERE    Person.Contact.FirstName LIKE 'A%'
ORDER BY Person.Contact.LastName


SELECT   Sales.SalesOrderHeader.OrderDate,
         Sales.SalesOrderHeader.DueDate,
         Sales.SalesOrderHeader.ShipDate,
         Sales.SalesOrderDetail.OrderQty,
         Production.Product.Name,
         Sales.SalesOrderHeader.RevisionNumber
FROM     Sales.SalesOrderHeader
         INNER JOIN Sales.SalesOrderDetail
           ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID
         INNER JOIN Person.Contact
           ON Sales.SalesOrderHeader.ContactID = Person.Contact.ContactID
         INNER JOIN Production.Product
           ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
WHERE     Left(Person.Contact.FirstName ,1) IN ('A')
ORDER BY Person.Contact.LastName
GO

-------- Another Optimization

USE AdventureWorks
SELECT Person.Address.City, HumanResources.EmployeeAddress.ModifiedDate
FROM Person.Address
JOIN HumanResources.EmployeeAddress
ON Person.Address.AddressID = HumanResources.EmployeeAddress.AddressID
GO

--Old Join Style
SELECT Person.Address.City, HumanResources.EmployeeAddress.ModifiedDate
FROM Person.Address, HumanResources.EmployeeAddress
WHERE Person.Address.AddressID = HumanResources.EmployeeAddress.AddressID
GO