SELECT TOP(n) ...

SELECT TOP(n) PERCENT ...

SELECT TOP(n) WITH TIES ...

SELECT TOP (1000) [WorkOrderID]
      ,[ProductID]
      ,[ScrappedQty]
      ,[ScrapReasonID]
  FROM [Production].[WorkOrder]
  WHERE [ScrapReasonID] IS NULL

SELECT TOP (1000) [WorkOrderID]
      ,[ProductID]
      ,[ScrappedQty]
      ,[ScrapReasonID]
  FROM [Production].[WorkOrder]
  WHERE [ScrapReasonID] IS NOT NULL

SELECT TOP (1000) [WorkOrderID]
      ,[ProductID]
      ,[ScrappedQty]
      , ISNULL([ScrapReasonID], -1) AS ScrapReasonID
  FROM [Production].[WorkOrder]
  WHERE ScrapReasonID <> -1

SELECT [FirstName]
FROM [AdventureWorks2017].[Person].[Person]
WHERE [FirstName] LIKE 'A___'  -- name starts with A and 4 chars long

SELECT LoginID, JobTitle
FROM HumanResources.Employee
WHERE JobTitle LIKE '%manager%';
GO

SELECT FirstName, LastName
FROM Person.Person
WHERE FirstName NOT LIKE '_ary';
GO

SELECT *
FROM Person.Person
WHERE FirstName LIKE '[g-m]ary';
GO

SELECT FirstName, LastName
FROM Person.Person
WHERE FirstName LIKE '[^g]ary';
GO

SELECT LastName + ', ' + FirstName AS Name
FROM Person.Person;
GO

-- Write a SELECT statement that returns a person's full name in a single column
SELECT FirstName + ' ' + MiddleName + ' ' + LastName AS [Full Name]
FROM Person.Person;
GO

-- To alleviate the issues with the abbreviations, we use a CASE statement
-- In this instance, we are using the CASE to match values in the PersonType column
-- the WHEN keyword tells SQL Server that "when" it finds 'SC' in the results
-- it should "then" change it in our displayed output to Store Contact
-- The ELSE statement is a default case when there is a value found that
-- doesn't match one of the WHEN clauses
-- Finish your CASE statement with the END keyword
-- Optionally use the AS keyword to indicate a column alias for the output
SELECT FirstName, LastName,
CASE PersonType
		WHEN 'SC' THEN 'Store Contact'
		WHEN 'IN' THEN 'Individual Customer'
		WHEN 'SP' THEN 'Sales Person'
		WHEN 'EM' THEN 'Employee'
		WHEN 'VC' THEN 'Vendor Contact'
		WHEN 'GC' THEN 'General Contact'
		ELSE 'Unknown Person Type'
END AS [Type of Contact]
FROM Person.Person;
GO

SELECT DISTINCT PersonType, EmailPromotion
FROM Person.Person;
GO

-- Select the product name from the Production.Product table
-- combine that with the comments and product review ID from the ProductReview table
-- using the ProductReviewID to establish the relationship
SELECT p.Name, pr.ProductReviewID, pr.Comments
FROM Production.Product p
INNER JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID;
GO

-- Using a left outer join, we will return all the rows in one table
-- regardless of whether there is a match in the other table
-- NOTE the placement of the table names in the FROM and JOIN clauses
-- determine which table is the LEFT.  In this case, Product is the left table
SELECT  p.Name, pr.ProductReviewID, pr.Comments
FROM Production.Product p
LEFT OUTER JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID;
GO

-- Using a right outer join, we will return all the rows in one table
-- regardless of whether there is a match in the other table
-- In this case, the Product table is listed first
-- in JOIN clause.  Also, the ProductReview table only has four entries total
SELECT p.Name, pr.ProductReviewID, pr.Comments
FROM Production.Product p
RIGHT OUTER JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID;
GO

-- Using a full outer join, we will retain the nonmatching rows
-- regardless of whether there is a match in the other table
-- the results look similar to the LEFT OUTER JOIN in this instance because if the way
-- the data exists in these tables.
SELECT p.Name, pr.ProductReviewID, pr.Comments
FROM Production.Product p
FULL OUTER JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID;
GO

SELECT City, StateProvinceID, COUNT(*) AS CountOfAddresses
FROM Person. Address
GROUP BY City, StateProvinceID
ORDER BY CountOfAddresses DESC;

SELECT ProductID, SUM (OrderQty) AS TotalQtySold
FROM Sales.SalesOrderDetail
GROUP BY ProductID
ORDER BY TotalQtySold DESC;

SELECT Color, COUNT(*) AS NumberOfProducts
FROM Production. Product
WHERE Color IS NOT NULL
GROUP BY Color
HAVING COUNT(*) > 25;

-- Sub-query that returns a single value
-- These are the simplest sub-queries to work with as they return a single
-- value for use in the outer query.
-- Here we ask for the quantity ordered and the total sale value
-- for the item in the SalesOrderDetail table that has the lowest unit price
-- The subquery can also be executed separately.  Highlight it and run that portion
SELECT OrderQty, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice = 
	(SELECT MIN(UnitPrice) FROM Sales.SalesOrderDetail);
GO

-- Use a subquery that returns multiple values
-- In this case, we cannot use the = operator
-- but instead will use the IN clause due to multiple
-- values being returned.
-- Here we look for the first and last name of sales people
-- who have had sales last year that exceeded $2M 
SELECT FirstName, LastName
FROM Person.Person
WHERE BusinessEntityID IN
	(SELECT BusinessEntityID
	FROM Sales.SalesPerson
	WHERE SalesLastYear > 2000000);
GO

-- Use a correlated subquery that references a field in the
-- SalesOrderDetail table and returns the minimum price
-- This query correlates the SalesOrderID between the two queries
-- even though they are the same table.
-- The results will be more than one row, which is in contrast
-- to the first query we executed above which returned one row.
SELECT OrderQty, LineTotal
FROM Sales.SalesOrderDetail AS s1
WHERE UnitPrice = 
	(SELECT MIN(UnitPrice)
	FROM Sales.SalesOrderDetail s2
	WHERE s1.SalesOrderID = s2.SalesOrderID);
GO

-- Using EXISTS predicate to check for existence of records in a subquery
-- In this instance, we are looking for customer account numbers who place orders online.
-- We do this by using EXISTS to check which records in the subquery,
-- in this case the SalesOrderHeader table, match the customer IDs and that also have
-- the online order flag set to 1.
-- We might run such a query to determine which customers place online orders so that
-- we can send them specials or other notification types.
SELECT AccountNumber
FROM Sales.Customer AS c
WHERE EXISTS
	(SELECT *
	FROM Sales.SalesOrderHeader AS soh
	WHERE c.CustomerID = soh.CustomerID
	AND OnlineOrderFlag = 1);
GO



-- We can also negate the results if we are looking for records that are NOT in the subquery
-- In this instance, we are looking for customer account numbers who do NOT place orders online.
-- We do this by using NOT EXISTS to check which records in the subquery.
SELECT AccountNumber
FROM Sales.Customer AS c
WHERE NOT EXISTS
	(SELECT *
	FROM Sales.SalesOrderHeader AS soh
	WHERE c.CustomerID = soh.CustomerID
	AND OnlineOrderFlag = 1);
GO

-- Define the CTE expression name and column list.
-- Here we are naming our CTE as Sales_CTE and we are
-- defining the column names it will use when referenced in a query later.

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- This is the query that generates the structure of the CTE
-- and returns the data values that will be contained in the CTE.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)

-- Now that the inner query has been created, we can define the CTE outer query
-- that will use the result set from the previous query.
-- Note that the previous query can execute by itself, but this query will
-- generate an error if you execute it alone, demonstrating that the CTE
-- only exists in the scope of the entire query operation
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO

-- declare some variables for use in the script
DECLARE @AvgWeight decimal(8,2), @BikeCount int

-- set up and IF statement like above, checking the number of Touring-3000 products
-- and then setup a statement block using BEGIN.
IF 
(SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5
BEGIN
	-- Set a value for the @BikeCount variable based on a query for the count of those products
   SET @BikeCount = 
        (SELECT COUNT(*) 
         FROM Production.Product 
         WHERE Name LIKE 'Touring-3000%');

    -- Set a value for the @AvgWeight variable using the AVG math function
   SET @AvgWeight = 
        (SELECT AVG(Weight) 
         FROM Production.Product 
         WHERE Name LIKE 'Touring-3000%');

    -- print out messages based on the results
   PRINT 'There are ' + CAST(@BikeCount AS varchar(3)) + ' Touring-3000 bikes.'
   PRINT 'The average weight of the top 5 Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.';


-- This END keyword marks the end of the statement block that was started with BEGIN
END

-- Setup the messages for the case of a false return from IF
ELSE 
BEGIN
SET @AvgWeight = 
        (SELECT AVG(Weight)
         FROM Production.Product 
         WHERE Name LIKE 'Touring-3000%' );
   PRINT 'Average weight of the Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.' ;
END ;
GO

-- Transactions are used to wrap statements that should be executed
-- successfully to prevent data corruption. They are mostly used with
-- INSERT, UPDATE, and DELETE statements that modify data as opposed
-- to SELECT statements where we just return information.
-- In this example we will attempt to delete a job candidate from 
-- the JobCandidate table with the ID of 13.
-- We want this delete statement to complete successfully or be rolled back if 
-- there are any issues with completing the command.  The main reason is that
-- SQL Server stores the data on disk and all the data associated with a specific
-- record may not stored all in one place.  If we delete only part of the information
-- for this record, then we corrupt data.

-- This statement marks the beginning of the transaction
BEGIN TRANSACTION;

USE AdventureWorks2017;

-- Nothing is recorded in the transaction log until this statement begins executing
DELETE FROM AdventureWorks2017.HumanResources.JobCandidate
    WHERE JobCandidateID = 13;

-- if we reach this point, then we assume the statement executed succesfully and we update 
-- the transaction log appropriately and commit the changes to the database.
COMMIT TRANSACTION;
GO


-- We can also mark a transaction by using a name.
-- This name will be placed in the transaction log so that
-- you can have a visually apparent, friendly name that indicats
-- what the transaction was doing.
-- We have also given this transaction a name called CandidateDelete
BEGIN TRANSACTION CandidateDelete
    WITH MARK N'Deleting a Job Candidate';
GO
USE AdventureWorks2017;
GO
DELETE FROM AdventureWorks2017.HumanResources.JobCandidate
    WHERE JobCandidateID = 14;
GO
COMMIT TRANSACTION CandidateDelete;
GO

USE AdventureWorks2017;
GO

BEGIN TRY
    -- Generate a divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO



USE AdventureWorks2017;
GO
BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO