USE AdventureWorks
GO

select 1
select 'select 1 '
select 'this is a character constant', 'this is another character constant', 1, '7/27/2008'
GO

SELECT *
FROM Person.Address
GO

SELECT AddressID, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
GO

SELECT Person.Address.AddressID, Person.Address.AddressLine1, Person.Address.AddressLine2,
Person.Address.City, Person.Address.StateProvinceID, Person.Address.PostalCode
FROM Person.Address
GO

SELECT MyAddress=AddressID, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address

SELECT AddressID AS MyAddress, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address

SELECT AddressID MyAddress, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address

SELECT a.AddressID MyAddress, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID,
a.PostalCode
FROM Person.Address a
GO

SELECT CAST(AddressID AS varchar(30)), AddressLine1, AddressLine2, City, StateProvinceID
FROM Person.Address

SELECT CAST(AddressID AS varchar), AddressLine1, AddressLine2, City, StateProvinceID
FROM Person.Address

SELECT CONVERT(varchar(30),AddressID), AddressLine1, AddressLine2, City, StateProvinceID
FROM Person.Address
GO

SELECT CONVERT(varchar(30),ModifiedDate,101) AS MonthDay4DigitYear, AddressLine1
FROM Person.Address

SELECT CONVERT(varchar(30),ModifiedDate,103) AS DayMonth4DigitYear, AddressLine1
FROM Person.Address

SELECT CONVERT(varchar(30),ModifiedDate,1) AS MonthDay2DigitYear, AddressLine1
FROM Person.Address

SELECT CONVERT(varchar(30),ModifiedDate,3) AS DayMonth2DigitYear, AddressLine1
FROM Person.Address
GO

SELECT AddressID, AddressLine1 + ' ' + AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
GO

SELECT AddressID, AddressLine1 + ' ' + ISNULL(AddressLine2,''), City, StateProvinceID
FROM Person.Address

SELECT AddressID, AddressLine1 + ' ' + COALESCE(AddressLine2,''), City, StateProvinceID
FROM Person.Address

SELECT ProductID, Color
FROM Production.Product

SELECT ProductID, ISNULL(Color,'Not Applicable')
FROM Production.Product
GO

SELECT COUNT(*) FROM Person.Address
GO

SELECT MAX(DATALENGTH(AddressLine1))
FROM Person.Address
GO

SELECT ShipMethodID, CASE ShipMethodID 
                WHEN 1 THEN 'A.Datum'
                WHEN 2 THEN 'Contoso'
                WHEN 3 THEN 'Consolidated Messenger'
                ELSE 'Unknown'
                END
FROM Purchasing.PurchaseOrderHeader
GO

SELECT ShipMethodID, CASE  
                WHEN ShipMethodID = 2 THEN 'Contoso'
                WHEN ShipMethodID = 3 THEN 'Consolidated Messenger'
                WHEN ShipMethodID >= 2 AND ShipMethodID <= 4 THEN 'A.Datum'
                ELSE 'Unknown'
                END ShipMethod
FROM Purchasing.PurchaseOrderHeader
GO

SELECT ShipMethodID, CASE  
                WHEN ShipMethodID >= 2 AND ShipMethodID <= 4 THEN 'A.Datum'
                WHEN ShipMethodID = 2 THEN 'Contoso'
                WHEN ShipMethodID = 3 THEN 'Consolidated Messenger'
                ELSE 'Unknown'
                END ShipMethod
FROM Purchasing.PurchaseOrderHeader
GO

SELECT AddressID, AddressLine1 + ' ' + ISNULL(AddressLine2,''), City, StateProvinceID
FROM Person.Address
ORDER BY AddressID

SELECT AddressID, AddressLine1 + ' ' + ISNULL(AddressLine2,''), City, StateProvinceID
FROM Person.Address
ORDER BY AddressID DESC

SELECT AddressID, AddressLine1 + ' ' + ISNULL(AddressLine2,''), City, StateProvinceID
FROM Person.Address
ORDER BY City, AddressID DESC

SELECT AddressID, AddressLine1 + ' ' + ISNULL(AddressLine2,''), City, StateProvinceID
FROM Person.Address
ORDER BY PostalCode DESC

SELECT AddressID, AddressLine1 + ' ' + ISNULL(AddressLine2,''), City, StateProvinceID
FROM Person.Address
ORDER BY PostalCode DESC, AddressID
GO

SELECT * FROM Person.Address WHERE AddressID = 102
SELECT * FROM Person.Address WHERE AddressID = 2
GO

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE ListPrice < 15

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE ListPrice < 15

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE ListPrice < 15

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE ListPrice > 15

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE DaysToManufacture <> 4
GO

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE ListPrice > 8.99 AND ListPrice < 34.99

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE ListPrice > 8.99 OR ListPrice < 34.99
GO

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE ListPrice >= 8.99 AND ListPrice <= 34.99
GO

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE ListPrice BETWEEN 2 and 15 

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE ListPrice >= 2 AND ListPrice <= 15
    OR ListPrice = 0

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE (ListPrice >= 2 AND ListPrice <= 15)
    OR ListPrice = 0

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE (ListPrice BETWEEN 2 AND 15)
    OR (ListPrice BETWEEN 18 AND 50)

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE (ListPrice BETWEEN 2 AND 15)
    AND (ListPrice BETWEEN 18 AND 50)
GO

select * from Production.Product where 1 = 2
GO

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE ProductSubCategoryID IN (1,2,3)
GO

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE Name LIKE 'M%'

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE Name LIKE 'Mountain%'

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE Name LIKE 'Mountain__0%'

SELECT ProductID, Name, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE Name LIKE '%ountain__0%'
GO

SELECT ProductID, Name, Color, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE Color = NULL

SELECT ProductID, Name, Color, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE Color IS NULL

SELECT ProductID, Name, Color, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
WHERE Color IS NOT NULL
GO

SELECT TOP 10 ProductID, Name, Color, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product

SELECT TOP 10 PERCENT ProductID, Name, Color, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product

SELECT TOP 10 ProductID, Name, Color, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
ORDER BY Color

SELECT TOP 10 ProductID, Name, Color, ProductNumber, ListPrice, DaysToManufacture
FROM Production.Product
ORDER BY Color DESC
GO

SELECT a.FirstName, a.LastName, b.Title
FROM Person.Contact a INNER JOIN HumanResources.Employee b on a.ContactID = b.ContactID
GO

SELECT a.ProductID, a.Name, b.SalesOrderID
FROM Production.Product a INNER JOIN Sales.SalesOrderDetail b 
        ON a.ProductID = b.ProductID AND a.Color = 'Black'

SELECT a.ProductID, a.Name, b.SalesOrderID
FROM Production.Product a INNER JOIN Sales.SalesOrderDetail b 
        ON a.ProductID = b.ProductID 
WHERE a.Color = 'Black'
GO

SELECT a.ProductID, a.Name, b.SalesOrderID
FROM Production.Product a LEFT OUTER JOIN Sales.SalesOrderDetail b 
        ON a.ProductID = b.ProductID
WHERE a.Color = 'Black'
ORDER BY 1

SELECT a.ProductID, a.Name, b.SalesOrderID
FROM Sales.SalesOrderDetail b RIGHT OUTER JOIN Production.Product a 
        ON a.ProductID = b.ProductID
WHERE a.Color = 'Black'
ORDER BY 1
GO

SELECT a.FirstName, a.LastName, b.Title
FROM Person.Contact a LEFT OUTER JOIN HumanResources.Employee b on a.ContactID = b.ContactID

SELECT a.FirstName, a.LastName,
    (SELECT e.Title
        FROM HumanResources.Employee e
        WHERE a.ContactID = e.ContactID) AS Title
FROM Person.Contact a
GO

SELECT a.ProductID, a.Name, b.SalesOrderID
FROM Production.Product a FULL OUTER JOIN Sales.SalesOrderDetail b 
        ON a.ProductID = b.ProductID
WHERE a.Color = 'Black'
ORDER BY 1
GO

SELECT a.FirstName, a.LastName, b.Title
FROM Person.Contact a CROSS JOIN HumanResources.Employee b
GO

SELECT b.ProductID, b.Name, b.ListPrice, a.UnitPrice AS 'Sales Price'
FROM Sales.SalesOrderDetail AS a
    JOIN Production.Product AS b 
    ON a.ProductID = b.ProductID AND a.UnitPrice < b.ListPrice
WHERE b.ProductID = 718

SELECT p1.VendorID, p1.ProductID
FROM Purchasing.ProductVendor p1
    INNER JOIN Purchasing.ProductVendor p2
    ON p1.ProductID = p2.ProductID
WHERE p1.VendorID <> p2.VendorID
ORDER BY p1.ProductID
GO

SELECT a.ProductID, a.Name, a.ListPrice
FROM Production.Product a
WHERE a.ListPrice > (SELECT AVG(b.ListPrice) FROM Production.Product b)
GO

SELECT a.ProductID, a.Name, a.ListPrice
FROM Production.Product a
WHERE a.ListPrice > (SELECT AVG(b.ListPrice) FROM Production.Product b)
GO

SELECT a.ProductID, a.ListPrice
FROM Production.Product a
WHERE EXISTS (SELECT 1 FROM Sales.SalesOrderDetail b 
                WHERE b.ProductID = a.ProductID)
GO

SELECT b.ProductID, b.Name, b.ListPrice, a.UnitPrice AS 'Sales Price'
FROM Sales.SalesOrderDetail AS a
    JOIN Production.Product AS b 
    ON a.ProductID = b.ProductID AND a.UnitPrice < b.ListPrice
WHERE b.ProductID = 718

SELECT DISTINCT b.ProductID, b.Name, b.ListPrice, a.UnitPrice AS 'Sales Price'
FROM Sales.SalesOrderDetail AS a
    JOIN Production.Product AS b 
    ON a.ProductID = b.ProductID AND a.UnitPrice < b.ListPrice
WHERE b.ProductID = 718

SELECT p1.VendorID, p1.ProductID
FROM Purchasing.ProductVendor p1
    INNER JOIN Purchasing.ProductVendor p2
    ON p1.ProductID = p2.ProductID
WHERE p1.VendorID <> p2.VendorID
ORDER BY p1.ProductID

SELECT DISTINCT p1.VendorID, p1.ProductID
FROM Purchasing.ProductVendor p1
    INNER JOIN Purchasing.ProductVendor p2
    ON p1.ProductID = p2.ProductID
WHERE p1.VendorID <> p2.VendorID
ORDER BY p1.ProductID
GO

SELECT COUNT(*) FROM HumanResources.Employee

SELECT COUNT(ManagerID) FROM HumanResources.Employee

SELECT DISTINCT COUNT(ManagerID) FROM HumanResources.Employee

SELECT COUNT(DISTINCT ManagerID) FROM HumanResources.Employee
GO