notes

Familiarize yourself with the Customer table by writing a Transact-SQL query that retrieves all columns for all customers.

SELECT * FROM SALESLT.CUSTOMER
-----------------------------------------------------------------------------------

Create a table that lists all customer contact names. The table should include the 
Title, FirstName, MiddleName, LastName and Suffix of all customers.

SELECT TITLE,FIRSTNAME,MIDDLENAME,LASTNAME,SUFFIX
FROM SalesLT.Customer;
--------------------------------------------------------------------------------------

Complete the query to list the following elements for all customers:
The salesperson
A column named CustomerName that displays how the customer contact should be greeted (e.g. "Mr Smith").
The customer's phone number (Phone)
Don't forget to space out the contents of your CustomerName column with + ' ' + and use the alias provided.

SELECT SalesPerson, Title + ' ' + LastName AS CustomerName, PHONE
FROM SalesLT.Customer;
-------------------------------------------------------------------------------------

Provide a list of all customer companies in the format <Customer ID>: <Company Name> 
(e.g. 78: Preferred Bikes). You'll need to use both CAST() and VARCHAR in your solution. Don't forget to use the alias provided.

SELECT CAST(CustomerID AS VARCHAR) + ': ' + CompanyName AS CustomerCompany
FROM SalesLT.Customer;
---------------------------------------------------------------------------------------

Complete the query on the right to create the 2-column table that's specified above.

SELECT SalesOrderNumber + ' (' + STR(RevisionNumber, 1) + ')' AS OrderRevision,
	   CONVERT(NVARCHAR(30), OrderDate, 102) AS OrderDate
FROM SalesLT.SalesOrderHeader;
----------------------------------------------------------------------------------------

Retrieve customer contact names including middle names when they're known.

SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName
AS CustomerName
FROM SalesLT.Customer;
---------------------------------------------------------------------------------------

Write a query that returns a list of customer IDs in one column, and a second column 
called PrimaryContact that contains the email address if known, and otherwise the phone number.

SELECT CustomerID, COALESCE(EmailAddress, Phone) AS PrimaryContact
FROM SalesLT.Customer;
------------------------------------------------------------------------------------------

Write a query to list sales order IDs and order dates with a column named ShippingStatus that contains the text 
'Shipped' for orders with a known ship date, and 'Awaiting Shipment' for orders with no ship date.

SELECT SalesOrderID, OrderDate,
  CASE
    WHEN Shipdate IS NULL THEN 'Awaiting Shipment'
    ELSE 'Shipped'
  END AS ShippingStatus
FROM SalesLT.SalesOrderHeader;
---------------------------------------------------------------------------------------------

Finish the Transact-SQL query that retrieves all values for City and StateProvince, without duplicates, from the Address table.

SELECT Distinct City, StateProvince
FROM SalesLT.Address;
------------------------------------------------------------------------------------------------

Finish the query to retrieve the names of the top ten percent of products by weight.

SELECT top 10 percent weight, Name
FROM SalesLT.Product
order by Weight desc;
--------------------------------------------------------------------------------------------

Tweak the query to list the heaviest 100 products not including the ten most heavy ones.

SELECT weight, productID, Name
FROM SalesLT.Product
ORDER BY Weight DESC
OFFSET 10 rows fetch NEXT 100 rows ONLY;
-----------------------------------------------------------------------------------------------

Write a query to find the names, colors, and sizes of the products with a product model ID of 1.

SELECT Name, Color, Size
FROM SalesLT.Product
WHERE ProductModelID = 1;
----------------------------------------------------------------------------------------------

Retrieve the product number and name of the products that have a Color of 'Black', 'Red', or 'White' and a Size of 'S' or 'M'.

SELECT ProductNumber, Name
FROM SalesLT.Product
WHERE Color IN ('Black', 'Red', 'White') AND Size IN ('S', 'M');
----------------------------------------------------------------------------------------------

Retrieve the product number, name, and list price of products that have a product number beginning with 'BK-'.

SELECT ProductNumber, Name, ListPrice
FROM SalesLT.Product
WHERE ProductNumber like 'BK-%';
-----------------------------------------------------------------------------------------------

Modify your previous query to retrieve the product number, name, and list price of products with product 
number beginning with 'BK-' followed by any character other than 'R', and ending with a '-' followed by any two numerals.
Remember:
to match any string of zero or more characters, use %
to match characters that are not R, use [^R]
to match a numeral, use [0-9]

SELECT ProductNumber, Name, ListPrice
FROM SalesLT.Product
WHERE ProductNumber LIKE 'BK-[^R]%-[0-9][0-9]';
------------------------------------------------------------------------------------------------

Write a query that returns the company name from the Sale.Customer table, 
the sales order ID and total due from the SalesLT.SalesOrderHeader table. Make sure to use the 
aliases provided, and default column names elsewhere.

SELECT c.CompanyName, oh.SalesOrderId, oh.TotalDue
FROM SalesLT.Customer AS c
JOIN SalesLT.SalesOrderHeader AS oh
ON c.CustomerID = oh.CustomerID;
-------------------------------------------------------------------------------------------------

Extend your customer orders query to include the main office address for each customer, 
including the full street address, city, state or province, postal code, and country or region. 
Make sure to use the aliases provided, and default column names elsewhere.

SELECT c.CompanyName, a.AddressLine1, ISNULL(a.AddressLine2, '') AS AddressLine2, a.City, a.StateProvince, a.PostalCode, a.CountryRegion, oh.SalesOrderID, oh.TotalDue
FROM SalesLT.Customer AS c
JOIN SalesLT.SalesOrderHeader AS oh
ON oh.CustomerID = c.CustomerID
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID AND AddressType = 'Main Office'
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID;
------------------------------------------------------------------------------------------------

Customers who have not placed any orders should be included at the bottom of 
the list with NULL values for the order ID and total due. Make sure to use the aliases provided, 
and default column names elsewhere.

SELECT c.CompanyName, c.FirstName, c.LastName, oh.SalesOrderID, oh.TotalDue
FROM SalesLT.Customer AS c
LEFT JOIN SalesLT.SalesOrderHeader AS oh
ON c.CustomerID = oh.CustomerID
ORDER By oh.SalesOrderID desc;
------------------------------------------------------------------------------------------------

Write a query that returns a list of customer IDs, company names, contact names 
(first name and last name), and phone numbers for customers with no address stored in the database. 
Make sure to use the aliases provided, and default column names elsewhere.

SELECT c.CompanyName, c.FirstName, c.LastName, c.Phone
FROM SalesLT.Customer AS c
LEFT JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
WHERE ca.AddressType IS NULL;
------------------------------------------------------------------------------------------------

Write a query that returns a column of customer IDs for customers who have never placed an order, 
and a column of product IDs for products that have never been ordered.
Each row with a customer ID should have a NULL product ID (because the customer 
has never ordered a product) and each row with a product ID should have a NULL customer ID 
(because the product has never been ordered by a customer).
Make sure to use the aliases provided, and default column names elsewhere.

SELECT c.CustomerID, p.ProductID
FROM SalesLT.Customer AS c
FULL JOIN SalesLT.SalesOrderHeader AS oh
ON c.CustomerID = oh.CustomerID
FULL JOIN SalesLT.SalesOrderDetail AS od
ON od.SalesOrderID = oh.SalesOrderID
FULL JOIN SalesLT.Product AS p
ON p.ProductID = od.ProductID
WHERE oh.SalesOrderNumber IS NULL
ORDER BY p.ProductID, c.CustomerID;
--------------------------------------------------------------------------------------------

Write a query that retrieves the company name, first line of the street address, city, 
and a column named AddressType with the value 'Billing' for customers where the address 
type in the SalesLT.CustomerAddress table is 'Main Office'. Make sure to use the aliases provided, 
and default column names elsewhere.

SELECT C.CompanyName, A.AddressLine1, A.CITY, 'Billing' AS AddressType
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON a.AddressID = ca.AddressID
WHERE ca.AddressType = 'Main Office';
-----------------------------------------------------------------------------------------

Adapt the query to retrieve the company name, first line of the street address, city, 
and a column named AddressType with the value 'Shipping' for customers where the address type 
in the SalesLT.CustomerAddress table is 'Shipping'. Make sure to use the aliases provided, 
and default column names elsewhere.

SELECT c.CompanyName, a.AddressLine1, a.City, 'Shipping' AS AddressType
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Shipping';

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

Use UNION ALL to combine the results returned by the two queries to create a 
list of all customer addresses that is sorted by company name and then address type.

SELECT c.CompanyName, a.AddressLine1, a.City, 'Billing' AS AddressType
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Main Office'
Union all
SELECT c.CompanyName, a.AddressLine1, a.City, 'Shipping' AS AddressType
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Shipping'
ORDER BY c.CompanyName, AddressType;
---------------------------------------------------------------------------------------------
Write a query that returns the company name of each company that appears in a table of 
customers with a 'Main Office' address, but not in a table of customers 
with a 'Shipping' address.

SELECT c.CompanyName
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Main Office'
EXCEPT
SELECT c.CompanyName
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Shipping'
ORDER BY c.CompanyName;
--------------------------------------------------------------------------------------------------

Write a query that returns the company name of each company that appears in a table of 
customers with a 'Main Office' address, and also in a table of customers with a 'Shipping' 
address. Make sure to use the aliases provided, and default column names elsewhere.

Select c.CompanyName
From SalesLT.Customer AS c
Join SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON a.AddressID = ca.AddressID
Where ca.AddressType = 'Main Office'
INTERSECT
Select c.CompanyName
FROM SalesLT.Customer AS c
Join SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON a.AddressID = ca.AddressID
Where ca.AddressType = 'Shipping'
ORDER BY c.CompanyName;
-------------------------------------------------------------------------------------------------

Write a query to return the product ID of each product, together with the 
product name formatted as upper case and a column named ApproxWeight with the weight of 
each product rounded to the nearest whole unit. Make sure to use the aliases provided, 
and default column names elsewhere.

SELECT ProductID, UPPER(Name) AS ProductName, Round(Weight, 0) AS ApproxWeight
FROM SalesLT.Product;
--------------------------------------------------------------------------------------------------

Extend your query to include columns named SellStartYear and SellStartMonth 
containing the year and month in which AdventureWorks started selling each product. 
The month should be displayed as the month name (e.g. 'January'). Make sure to use the aliases 
provided, and default column names elsewhere.

SELECT ProductID, UPPER(Name) AS ProductName, ROUND(Weight, 0) AS ApproxWeight,
       Year(SellStartDate) as SellStartYear,
       Datename(m, SellStartDate) as SellStartMonth
FROM SalesLT.Product;
--------------------------------------------------------------------------------------------------

Extend your query to include a column named ProductType that contains the leftmost two characters from 
the product number. Make sure to use the aliases provided, and default column names elsewhere.

SELECT ProductID, UPPER(Name) AS ProductName, ROUND(Weight, 0) AS ApproxWeight,
       YEAR(SellStartDate) as SellStartYear,
       DATENAME(m, SellStartDate) as SellStartMonth,
              Left(ProductNumber, 2) AS ProductType
FROM SalesLT.Product;
-------------------------------------------------------------------------------------------------------

Extend your query to filter the product returned so that only products with a 
numeric size are included. Make sure to use the aliases provided, and default column names elsewhere.

SELECT ProductID, UPPER(Name) AS ProductName, ROUND(Weight, 0) AS ApproxWeight,
       YEAR(SellStartDate) as SellStartYear,
       DATENAME(m, SellStartDate) as SellStartMonth,
       LEFT(ProductNumber, 2) AS ProductType
FROM SalesLT.Product
WHERE ISnumeric(Size) = 1;
-------------------------------------------------------------------------------------------------------

Write a query that returns a list of company names with a ranking of their 
place in a list of highest TotalDue values from the SalesOrderHeader table. 
Make sure to use the aliases provided, and default column names elsewhere.

SELECT CompanyName, TotalDue AS Revenue,
       Rank() OVER (ORDER BY SOH.TotalDue DESC) AS RankByRevenue
FROM SalesLT.SalesOrderHeader AS SOH
Join SalesLT.Customer AS C
ON SOH.CustomerID = C.CustomerID;
-------------------------------------------------------------------------------------------------------

Write a query to retrieve a list of the product names and the total revenue 
calculated as the sum of the LineTotal from the SalesLT.SalesOrderDetail table, 
with the results sorted in descending order of total revenue. Make sure to use the aliases 
provided, and default column names elsewhere.

SELECT p.Name, SUM(sod.LineTotal) AS TotalRevenue
FROM SalesLT.SalesOrderDetail AS sod
JOIN SalesLT.Product as p
ON sod.ProductID = p.ProductID
GROUP BY p.Name
ORDER BY TotalRevenue DESC;
--------------------------------------------------------------------------------------------------------

Modify the previous query to include sales totals for products that 
have a list price of more than 1000. Make sure to use the aliases provided, and default column names elsewhere.

SELECT Name, SUM(LineTotal) AS TotalRevenue
FROM SalesLT.SalesOrderDetail AS SOD
JOIN SalesLT.Product AS P
ON SOD.ProductID = P.ProductID
WHERE p.listprice > 1000
GROUP BY P.Name
ORDER BY TotalRevenue DESC;
--------------------------------------------------------------------------------------------------------

Modify the previous query to only include products with total sales greater than 20000. 
Make sure to use the aliases provided, and default column names elsewhere.

SELECT Name, SUM(LineTotal) AS TotalRevenue
FROM SalesLT.SalesOrderDetail AS SOD
JOIN SalesLT.Product AS P
ON SOD.ProductID = P.ProductID
WHERE P.ListPrice > 1000
GROUP BY P.Name
Having SUM(LineTotal) > 20000
ORDER BY TotalRevenue DESC;
--------------------------------------------------------------------------------------------------------

Retrieve the product ID, name, and list price for each product where the list 
price is higher than the average unit price for all products that have been sold.

SELECT ProductID, Name, ListPrice
FROM SalesLT.Product
WHERE ListPrice > 
(SELECT AVG(UnitPrice) FROM SalesLT.SalesOrderDetail)
ORDER BY ProductID;

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

Retrieve the product ID, name, and list price for each product where the list price is 100 or more, 
and the product has been sold for (strictly) less than 100.
Remember, the ProductID in your subquery will be from the SalesLT.SalesOrderDetail table.

SELECT ProductID, Name, ListPrice
FROM SalesLT.Product
WHERE ProductID IN
   (SELECT ProductID FROM SalesLT.SalesOrderDetail
   WHERE UnitPrice < 100)
AND ListPrice >= 100
ORDER BY ProductID;
------------------------------------------------------------------------------------------------------

Retrieve the product ID, name, cost, and list price for each product along with the average 
unit price for which that product has been sold. Make sure to use the aliases provided, and 
default column names elsewhere.

SELECT ProductID, Name, StandardCost, ListPrice,
(SELECT AVG(UnitPrice)
 FROM SalesLT.SalesOrderDetail AS SOD
 WHERE P.ProductID = SOD.ProductID) AS AvgSellingPrice
FROM SalesLT.Product AS P
ORDER BY P.ProductID;
-----------------------------------------------------------------------------------------------------

Filter the query for the previous exercise to include only products where the cost is 
higher than the average selling price. Make sure to use the aliases provided, and default column names elsewhere.

SELECT ProductID, Name, StandardCost, ListPrice,
(SELECT AVG(UnitPrice)
 FROM SalesLT.SalesOrderDetail AS SOD
 WHERE P.ProductID = SOD.ProductID) AS AvgSellingPrice
FROM SalesLT.Product AS P
WHERE StandardCost >
(SELECT AVG(UnitPrice)
 FROM SalesLT.SalesOrderDetail AS SOD
 WHERE P.ProductID = SOD.ProductID)
ORDER BY P.ProductID;
---------------------------------------------------------------------------------------------------------

Retrieve the sales order ID, customer ID, first name, last name, and total due for all sales 
orders from the SalesLT.SalesOrderHeader table and the dbo.ufnGetCustomerInformation function. 
Make sure to use the aliases provided, and default column names elsewhere.

SELECT SOH.SalesOrderID, SOH.CustomerID, CI.FirstName, CI.LastName, SOH.TotalDue
FROM SalesLT.SalesOrderHeader AS SOH
CROSS APPLY dbo.ufnGetCustomerInformation(SOH.CustomerID) AS CI
ORDER BY SOH.SalesOrderID;
--------------------------------------------------------------------------------------------------------

Retrieve the customer ID, first name, last name, address line 1 and city for all customers from the SalesLT.Address 
and SalesLT.CustomerAddress tables, using the dbo.ufnGetCustomerInformation function. Make sure to use the 
aliases provided, and default column names elsewhere.

SELECT CA.CustomerID, CI.FirstName, CI.LastName, A.Addressline1, A.City
FROM SalesLT.Address AS A
JOIN SalesLT.CustomerAddress AS CA
ON A.AddressID = CA.AddressID
CROSS APPLY dbo.ufnGetCustomerInformation(CA.CustomerID) AS CI
ORDER BY CA.CustomerID;
-----------------------------------------------------------------------------------------------------------

Retrieve the product ID, product name, product model name, and product model summary for each 
product from the SalesLT.Product table and the SalesLT.vProductModelCatalogDescription view. 
Make sure to use the aliases provided, and default column names elsewhere.

SELECT P.PRODUCTID, P.NAME AS ProductName, PM.Name AS ProductModel, PM.Summary
FROM SalesLT.Product AS P
JOIN SalesLT.vProductModelCatalogDescription AS PM
ON P.ProductModelID = PM.ProductModelID
ORDER BY ProductID;
-----------------------------------------------------------------------------------------------------------

Create a table variable and populate it with a list of distinct colors from the SalesLT.Product table. 
Then use the table variable to filter a query that returns the product ID, name, and color from the 
SalesLT.Product table so that only products with a color listed in the table variable are returned. 
You'll need to use NVARCHAR(15) in your solution and make sure to use the aliases provided.

DECLARE @Colors AS Table (Color NVARCHAR(15));

INSERT INTO @COLORS
SELECT DISTINCT COLOR FROM SalesLT.PRODUCT;

SELECT ProductID, Name, Color
FROM SalesLT.Product
WHERE COLOR IN (SELECT COLOR FROM @COLORS);
--------------------------------------------------------------------------------------------------------

Write a query that uses this function to return a list of all products including 
their parent category and their own category. Make sure to use the aliases provided, 
and default column names elsewhere.

SELECT C.ParentProductCategoryName AS ParentCategory,
       C.ProductCategoryName AS Category,
       P.ProductID, P.NAME AS ProductName
FROM SalesLT.Product AS P
JOIN dbo.ufnGetAllCategories() AS C
ON P.ProductCategoryID = C.ProductCategoryID
ORDER BY ParentCategory, Category, ProductName;
-------------------------------------------------------------------------------------------------------

Retrieve a list of customers in the format Company (Contact Name) together with the total revenue for each customer. 
Use a derived table or a common table expression to retrieve the details for each sales order, 
and then query the derived table or CTE to aggregate and group the data. Make sure to use the aliases provided, 
and default column names elsewhere.


SELECT COMPANYCONTACT, SUM(SALESAMOUNT) AS Revenue
FROM
	(SELECT CONCAT(c.CompanyName, CONCAT(' (' + c.FirstName + ' ', c.LastName + ')')), SOH.TotalDue
	 FROM SalesLT.SalesOrderHeader AS SOH
	 JOIN SalesLT.Customer AS c
	 ON SOH.CustomerID = c.CustomerID) AS CustomerSales(CompanyContact, SalesAmount)
GROUP BY CompanyContact
ORDER BY CompanyContact;
------------------------------------------------------------------------------------------------------

Modify the query so that the results include a grand total for all sales revenue and a subtotal for 
each country/region in addition to the state/province subtotals that are already returned. 
Make sure to use the aliases provided, and default column names elsewhere.

SELECT a.CountryRegion, a.StateProvince, SUM(soh.TotalDue) AS Revenue
FROM SalesLT.Address AS a
JOIN SalesLT.CustomerAddress AS ca
ON a.AddressID = ca.AddressID
JOIN SalesLT.Customer AS c
ON ca.CustomerID = c.CustomerID
JOIN SalesLT.SalesOrderHeader as soh
ON c.CustomerID = soh.CustomerID
GROUP BY ROLLUP(a.CountryRegion, a.StateProvince)
ORDER BY a.CountryRegion, a.StateProvince;

-----------------------------------------------------------------------------------------------------
Modify your query to include a column named Level that indicates at which level in the total, country/region, and state/province hierarchy the revenue figure in the row is aggregated.
For example, the grand total row should contain the value 'Total', the row showing the subtotal for United States should contain the value 'United States Subtotal', and the row showing the subtotal for California should contain the value 'California Subtotal'.
Make sure to use the aliases provided, and default column names elsewhere.

SELECT a.CountryRegion, a.StateProvince,
IIF(GROUPING_ID(a.CountryRegion) = 1 AND GROUPING_ID(a.StateProvince) = 1, 'Total', IIF(GROUPING_ID(a.StateProvince) = 1, a.CountryRegion + ' Subtotal', a.StateProvince + ' Subtotal')) AS Level,
SUM(soh.TotalDue) AS Revenue
FROM SalesLT.Address AS a
JOIN SalesLT.CustomerAddress AS ca
ON a.AddressID = ca.AddressID
JOIN SalesLT.Customer AS c
ON ca.CustomerID = c.CustomerID
JOIN SalesLT.SalesOrderHeader as soh
ON c.CustomerID = soh.CustomerID
GROUP BY ROLLUP(a.CountryRegion, a.StateProvince)
ORDER BY a.CountryRegion, a.StateProvince;
---------------------------------------------------------------------------------------------------

Extend your query to include a grouping for individual cities. 
Make sure to use the aliases provided, and default column names elsewhere.

SELECT a.CountryRegion, a.StateProvince, a.City,
CHOOSE (1 + GROUPING_ID(a.City) + GROUPING_ID(a.StateProvince) + GROUPING_ID(a.CountryRegion),
        a.City + ' Subtotal', a.StateProvince + ' Subtotal',
        a.CountryRegion + ' Subtotal', 'Total') AS Level,
SUM(soh.TotalDue) AS Revenue
FROM SalesLT.Address AS a
JOIN SalesLT.CustomerAddress AS ca
ON a.AddressID = ca.AddressID
JOIN SalesLT.Customer AS c
ON ca.CustomerID = c.CustomerID
JOIN SalesLT.SalesOrderHeader as soh
ON c.CustomerID = soh.CustomerID
GROUP BY ROLLUP(a.CountryRegion, a.StateProvince, a.City)
ORDER BY a.CountryRegion, a.StateProvince, a.City;
----------------------------------------------------------------------------------------------------

Retrieve a list of customer company names together with their total revenue for each parent category in 
Accessories, Bikes, Clothing, and Components. Make sure to use the aliases provided, and default column names elsewhere.

SELECT * FROM
(SELECT cat.ParentProductCategoryName, cust.CompanyName, sod.LineTotal
 FROM SalesLT.SalesOrderDetail AS sod
 JOIN SalesLT.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID
 JOIN SalesLT.Customer AS cust ON soh.CustomerID = cust.CustomerID
 JOIN SalesLT.Product AS prod ON sod.ProductID = prod.ProductID
 JOIN SalesLT.vGetAllCategories AS cat ON prod.ProductcategoryID = cat.ProductCategoryID) AS catsales
PIVOT (SUM(LineTotal) FOR ParentProductCategoryName
IN ([Accessories], [Bikes], [Clothing], [Components])) AS pivotedsales
ORDER BY CompanyName;
-------------------------------------------------------------------------------------------------

AdventureWorks has started selling the new product shown in the table above. Insert it into the SalesLT.Product table, using default or NULL values for unspecified columns.
Once you've inserted the product, run SELECT SCOPE_IDENTITY(); to get the last identity value that was inserted.
Add a query to view the row for the product in the SalesLT.Product table.

INSERT INTO SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, ProductCategoryID, SellStartDate)
VALUES
('LED Lights', 'LT-L123', 2.56, 12.99, 37, GETDATE());
SELECT SCOPE_IDENTITY();
select * FROM SalesLT.Product
WHERE ProductID = SCOPE_IDENTITY();
-----------------------------------------------------------------------------------------------------

The sales manager at AdventureWorks has mandated a 10% price increase for all products in the Bells and Horns category. 
Update the rows in the SalesLT.Product table for these products to increase their price by 10%.
If you want, you can use a SELECT statement afterwards to see if the records were properly updated, but we won't check that.

UPDATE SalesLT.Product
SET ListPrice = ListPrice * 1.1
WHERE ProductCategoryID =
  (Select ProductCategoryID FROM SalesLT.ProductCategory where Name = 'Bells and Horns');
  --------------------------------------------------------------------------------------------------
 
 Delete the records for the Bells and Horns category and its products. You must ensure that you delete the records 
 from the tables in the correct order to avoid a foreign-key constraint violation.
If you want, you can use a SELECT statement afterwards to see if the rows were properly deleted, but we won't check that.
  
Delete from SalesLT.Product
WHERE ProductCategoryID =
	(SELECT ProductCategoryID FROM SalesLT.ProductCategory where Name = 'Bells and Horns');

delete from SalesLT.ProductCategory
WHERE ProductCategoryID =
	(SELECT ProductCategoryID FROM SalesLT.ProductCategory where Name = 'Bells and Horns');
------------------------------------------------------------------------------------------------------------

Fill in the variable names to complete the DECLARE statements. You can infer these names from the 
INSERT statement further down the script.Finish the INSERT query. Because SalesOrderID is an IDENTITY column, this 
ID will automatically be generated for you. You can use the hardcoded value 'CARGO TRANSPORT 5' for the ShipMethod field.
Use SCOPE_IDENTITY() to print out the ID of the new sales order header.

DECLARE @OrderDate datetime = GETDATE();
DECLARE @DueDate datetime = DATEADD(dd, 7, GETDATE());
DECLARE @CustomerID int = 1;

INSERT INTO SalesLT.SalesOrderHeader (OrderDate, DueDate, CustomerID, ShipMethod)
VALUES (@OrderDate, @DueDate, @CustomerID, 'CARGO TRANSPORT 5');

PRINT SCOPE_IDENTITY();
--------------------------------------------------------------------------------------------------------------

Slightly adapted code from the previous exercise is available; it defines the OrderID with SCOPE_IDENTITY().
Complete the IF-ELSE block:
The test should check to see if there is a SalesOrderDetail with a SalesOrderID that is equal to the OrderID 
exists in the SalesLT.SalesOrderHeader table.Finish the statement to insert a record in the SalesOrderDetail 
table when this is the case. Print out 'The order does not exist' when this is not the case.

DECLARE @OrderDate datetime = GETDATE();
DECLARE @DueDate datetime = DATEADD(dd, 7, GETDATE());
DECLARE @CustomerID int = 1;
INSERT INTO SalesLT.SalesOrderHeader (OrderDate, DueDate, CustomerID, ShipMethod)
VALUES (@OrderDate, @DueDate, @CustomerID, 'CARGO TRANSPORT 5');
DECLARE @OrderID int = SCOPE_IDENTITY();


DECLARE @ProductID int = 760;
DECLARE @Quantity int = 1;
DECLARE @UnitPrice money = 782.99;

IF EXISTS (SELECT * FROM SalesLT.SalesOrderHeader  WHERE SalesOrderID  = SalesOrderID)
BEGIN
	INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID, OrderQty, ProductID, UnitPrice)
	VALUES (@OrderID, @Quantity, @ProductID, @UnitPrice)
END
---------------------------------------------------------------------------------------------

The loop should execute only if the average list price of a product in the 'Bikes' parent category is less than the market average.
Update all products that are in the 'Bikes' parent category, increasing the list price by 10%.
Determine the new average and maximum selling price for products that are in the 'Bikes' parent category.
If the new maximum price is greater than or equal to the maximum acceptable price, exit the loop; otherwise continue.
DECLARE @MarketAverage money = 2000;
DECLARE @MarketMax money = 5000;
DECLARE @AWMax money;
DECLARE @AWAverage money;

SELECT @AWAverage = AVG(ListPrice), @AWMax = MAX(ListPrice)
FROM SalesLT.Product
WHERE ProductCategoryID IN
	(SELECT DISTINCT ProductCategoryID
	 FROM SalesLT.vGetAllCategories
	 WHERE ParentProductCategoryName = 'Bikes');

WHILE @AWAverage < @MarketAverage
BEGIN
   UPDATE SalesLT.Product
   SET ListPrice = ListPrice * 1.1
   WHERE ProductCategoryID IN
	(SELECT DISTINCT ProductCategoryID
	 FROM SalesLT.vGetAllCategories
	 WHERE ParentProductCategoryName = 'Bikes');

	SELECT @AWAverage = AVG(ListPrice), @AWMax = MAX(ListPrice)
	FROM SalesLT.Product
	WHERE ProductCategoryID IN
	(SELECT DISTINCT ProductCategoryID
	 FROM SalesLT.vGetAllCategories
	 WHERE ParentProductCategoryName = 'Bikes');

   IF @AWMax >= @MarketMax
      Break
   ELSE
      CONTINUE
END

PRINT 'New average bike price:' + CONVERT(VARCHAR, @AWAverage);
PRINT 'New maximum bike price:' + CONVERT(VARCHAR, @AWMax);

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

Modify the code to check for the existence of the specified order ID before attempting to delete it. 
If the order does not exist, your code should throw an error. Otherwise, it should go ahead and 
delete the order data. If you coded the script correctly, your code should generate an error when you run it, 
as the order with ID 0 does not exist.

DECLARE @OrderID int = 0

DECLARE @error VARCHAR(30) = 'Order #' + cast(@OrderID as VARCHAR) + ' does not exist';

IF NOT EXISTS (SELECT * FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @OrderID)
BEGIN
  THROW 50001, @error, 0;
END
ELSE
BEGIN
  DELETE FROM SalesLT.SalesOrderDetail WHERE SalesOrderID = @OrderID;
  DELETE FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @OrderID;
END
-----------------------------------------------------------------------------------------------------

Add a TRY...CATCH to the code:

Include the IF-ELSE block in the TRY part.
In the CATCH part, print the error with ERROR_MESSAGE();


DECLARE @OrderID int = 71774
DECLARE @error VARCHAR(30) = 'Order #' + cast(@OrderID as VARCHAR) + ' does not exist';

-- Wrap IF ELSE in a TRY block
BEGIN TRY
  IF NOT EXISTS (SELECT * FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @OrderID)
  BEGIN
    THROW 50001, @error, 0
  END
  ELSE
  BEGIN
    DELETE FROM SalesLT.SalesOrderDetail WHERE SalesOrderID = @OrderID;
    DELETE FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @OrderID;
  END
END TRY
-- Add a CATCH block to print out the error
BEGIN CATCH
  PRINT ERROR_MESSAGE();
END CATCH

------------------------------------------------------------------------------------------------------
Add BEGIN TRANSACTION and COMMIT TRANSACTION to treat the two DELETE statements as a single transactional unit of work.
In the error handler, modify the code so that if a transaction is in process, it is rolled back. If no transaction is 
in process the error handler should continue to simply print the error message.

DECLARE @OrderID int = 0
DECLARE @error VARCHAR(30) = 'Order #' + cast(@OrderID as VARCHAR) + ' does not exist';

BEGIN TRY
  IF NOT EXISTS (SELECT * FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @OrderID)
  BEGIN
    THROW 50001, @error, 0
  END
  ELSE
  BEGIN
    BEGIN TRANSACTION
    DELETE FROM SalesLT.SalesOrderDetail
    WHERE SalesOrderID = @OrderID;
    DELETE FROM SalesLT.SalesOrderHeader
    WHERE SalesOrderID = @OrderID;
    COMMIT TRANSACTION
  END
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
  BEGIN
     ROLLBACK TRANSACTION;
  END
  ELSE
  BEGIN
    PRINT ERROR_MESSAGE();
  END
END CATCH
---------------------------------------------------------------------------------------------------
Select the quantity per unit for all products in the Products table.


SELECT PRODUCTNAME, QUANTITYPERUNIT FROM DBO.PRODUCTS

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


Select the unique category IDs from the Products table.

SELECT DISTINCT CATEGORYID FROM DBO.PRODUCTS

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

Select the names of products from the Products table which have more than 20 units left in stock.

SELECT PRODUCTNAME FROM DBO.PRODUCTS
WHERE UNITSINSTOCK > 20
----------------------------------------------------------------------------------------------------
Select the product ID, product name, and unit price of the 10 most expensive products from the Products table.


SELECT  TOP 10 PRODUCTID, PRODUCTNAME, UNITPRICE FROM DBO.PRODUCTS 
ORDER BY UNITPRICE DESC
---------------------------------------------------------------------------------------------------------

Select the product ID, product name, and quantity per unit for all 
products in the Products table. Sort your results alphabetically by product name (where A comes first).

SELECT PRODUCTID, PRODUCTNAME,QUANTITYPERUNIT FROM DBO.PRODUCTS
ORDER BY PRODUCTNAME ASC

-----------------------------------------------------------------------------------------------------------
Select the product ID, product name, and unit price of all products in the Products table. Sort your results by 
number of units in stock, from greatest to least.
Skip the first 10 results and get the next 5 after that.

SELECT PRODUCTID, PRODUCTNAME, UNITPRICE FROM PRODUCTS
ORDER BY UNITSINSTOCK DESC 
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
--------------------------------------------------------------------------------------------------------------
Use STR, CONVERT, and NVARCHAR(30) where appropriate to display the first name, 
employee ID and birthdate (as Unicode in ISO 8601 format) for each employee in the Employees table.
Each result should be a single string in the following format, where each <<value>> is replaced by 
the appropriately converted value:

SELECT FirstName +' has an EmployeeID of ' +CAST(EmployeeID AS VarChar(5)) +' and was born '+CONVERT(NVARCHAR(30), BirthDate, 126) AS Employees FROM Employees
-------------------------------------------------------------------------------------------------------------
Select from the Orders table.

The first column of your result should be a single string in exactly the following format:

<<ShipName>> is from <<ShipCity or ShipRegion or ShipCountry>>
If there is no ShipCity, then you should select ShipRegion, and if there is no ShipRegion you should select ShipCountry.

SELECT 
ShipName + ' is from ' + COALESCE(ShipCity,ShipRegion, ShipCountry) AS DESTIONATION 
FROM DBO.ORDERS
------------------------------------------------------------------------------------------------------------

Select the ship name and ship postal code from the Orders table. If the postal code is missing, display 'unknown'.

Select ShipName, isnull(ShipPostalCode, 'unknown')
from dbo.Orders

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

Using the Suppliers table, select the company name, and use a simple CASE expression to display 'outdated' if 
the company has a fax number, or 'modern' if it doesn't. Do this by specifying IS NULL in your conditional statement.
Alias the result of the CASE expression to Status.

select 
CompanyName,
Case 
when fax is null then 'modern' else 'outdated' end as Status
from dbo.Suppliers
--------------------------------------------------------------------------------------------------------

Get the order ID and unit price for each order by joining the Orders table and the Order Details table.
Note that you need to use [Order Details] since the table name contains whitespace.

select a.orderId, b.UnitPrice
from dbo.Orders a
join dbo.[Order Details] b
on a.OrderId = b.OrderId
--------------------------------------------------------------------------------------------------------

Get the order ID and first name of the associated employee by joining the Orders and Employees tables.

select  b.OrderID, a.FirstName
from dbo.Employees a
join dbo.Orders b
on a.EmployeeID = b.EmployeeID
-----------------------------------------------------------------------------------------------------------

Get the employee ID and related territory description for each territory an employee is in, by joining the 
Employees, EmployeeTerritories and Territories tables.

select a.EmployeeID, c.TerritoryDescription
from dbo.Employees a
join dbo.EmployeeTerritories b
on a.EmployeeID = b.EmployeeID
join dbo.Territories c
on b.TerritoryID = c.TerritoryID
-------------------------------------------------------------------------------------------------------

Select all the different countries from the Customers table and the Suppliers table using UNION.

select Country from dbo.Customers
union 
select Country from dbo.Suppliers

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

Select all the countries, including duplicates, from the Customers table and the Suppliers table using UNION ALL.

select Country from dbo.Suppliers
union all 
select Country from dbo.Customers
---------------------------------------------------------------------------------------------------------
Using the Products table, get the unit price of each product, rounded to the nearest dollar.

Select 
ProductName,
UnitPrice,
round(UnitPrice,0)
From dbo.products 
--------------------------------------------------------------------------------------------------------
Using the Products table, get the total number of units in stock across all products.

Select 
sum(UnitsInStock)
from dbo.Products 
-------------------------------------------------------------------------------------------------------

Using the Orders table, get the order ID and year of the order by using YEAR(). Alias the year as OrderYear.

Select 
OrderId,
year(orderdate) as OrderYear
from Orders
------------------------------------------------------------------------------------------------------

Using the Orders table, get the order ID and month of the order by using DATENAME(). Alias the month as OrderMonth.

Select
OrderId,
datename(mm,Orderdate) as OrderMonth
from Orders
-------------------------------------------------------------------------------------------------------

Use LEFT() to get the first two letters of each region description from the Region table.

select 
left(RegionDescription, 2)
from Region
---------------------------------------------------------------------------------------------------------
Using the Suppliers table, select the city and postal code for each supplier, 
using WHERE and ISNUMERIC() to select only those postal codes which have no letters in them.

Select
CompanyName,
City,
PostalCode
from Suppliers
where isnumeric(PostalCode) = 1
-------------------------------------------------------------------------------------------------------
Use LEFT() and UPPER() to get the first letter (capitalized) of each region description from the Region table.

select
upper(left(RegionDescription,1))
from Region
-------------------------------------------------------------------------------------------------------
Use a subquery to get the product name and unit price of products from the Products table which have 
a unit price greater than the average unit price from the Order Details table.

Note that you need to use [Order Details] since the table name contains whitespace.

select 
ProductName,
UnitPrice
from Products
where UnitPrice > (select avg(unitprice) from [order details] )
-----------------------------------------------------------------------------------------------------
Select from the Employees and Orders tables. Use a subquery to get the 
first name and employee ID for employees who were associated with orders which shipped from the USA.


select distinct sec.firstname, sec.employeeid 
from (select firstname,a.employeeid from orders as a join employees as e on a.employeeid = e.employeeid where a.ShipCountry = 'USA'
) sec
----------------------------------------------------------------------------------------------------
Use the # to create a new temporary table called ProductNames which has one field called ProductName (a VARCHAR of max length 40).
Insert into this table the names of every product from the Products table. Note that there are two syntaxes for 
the INSERT INTO statement. Use the syntax that does not specify the column names since the table only has one field.
Select all columns from the ProductNames table you created. Note: you need to specify the Products table as Products, not dbo.Products.


CREATE table #ProductNames(ProductName varchar(40));
INSERT INTO #ProductNames select ProductName from Products;
select * from #ProductNames;
----------------------------------------------------------------------------------------------------

Use CHOOSE() and MONTH() to get the season in which each order was shipped from the Orders table. 
You should select the order ID, shipped date, and then the season aliased as ShippedSeason. You can copy and paste the
below into your query.

select orderid, ShippedDate, choose(month(shippeddate), 'Winter', 'Winter', 'Spring', 'Spring', 'Spring', 
'Summer', 'Summer', 'Summer', 'Autumn', 'Autumn', 'Autumn', 'Winter') as ShippedSeason from orders where 
ShippedDate IS NOT null

Choose ---  
Summary: in this tutorial, you will learn how to use the SQL Server CHOOSE() function to return an item
based on its index in a list of values.
SQL Server CHOOSE() function overview
The CHOOSE() function returns the item from a list of items at a specied index.
The following shows the syntax of the CHOOSE() function:
1 CHOOSE ( index, elem_1, elem_2 [, elem_n ] )
HOME START HERE BASIC ADVANCED  FUNCTIONS 
⤒
In this syntax:
index is an expression integer that species the index of the element to be returned. Note that
the indexes of the elements are 1-based.
elem_1 , elem_2 ,… elem_n is a list of comma-separated values of any type.
If index is 1, the CHOOSE() function returns elem_1 . If index is 2, the CHOOSE() function returns
elem_2 , and so on.
If index is not an integer, it will be cast to an integer. In case the index is out of the boundary of the
list, the CHOOSE() function will return NULL.
SQL Server CHOOSE() function examples
Let’s take some examples of the CHOOSE() function.
A) Using SQL Server CHOOSE() function with literal values example
This example returns the second item from the list of values:
Here is the output:
1
2
SELECT
 CHOOSE(2, 'First', 'Second', 'Third') Result;
1
2
3
4
5
Result
------
Second
(1 row affected)
HOME START HERE BASIC ADVANCED  FUNCTIONS 
⤒
B) Using SQL Server CHOOSE() function for table column example
The following example uses the CHOOSE() function to return the order status based on the value in the
order_status column of the sales.orders table:
The following picture shows the partial result:
C) Using SQL Server CHOOSE() function with the MONTH function
The following example uses the MONTH() function to return the seasons in which the customers buy
products. The result of the MONTH() function is used in the CHOOSE() function to return the
corresponding season:
The following picture shows the partial result set:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT
 order_id,
 order_date,
 customer_id,
 CHOOSE(
 MONTH(order_date),
 'Winter',
 'Winter',
 'Spring',
 'Spring',
 'Spring',
 'Summer',
 'Summer',
 'Summer',
 'Autumn',
 'Autumn',
 'Autumn',
 'Winter') month
FROM
 sales.orders
ORDER BY
 customer_id;
HOME START HERE BASIC ADVANCED  FUNCTIONS 
⤒
In this tutorial, you have learned how to use the SQL Server CHOOSE() function to return an element on
its index in a list of values.
------------------------------------------------------------------------------------------------

DECLARE @region NVARCHAR(25) = 'Space'

IF NOT EXISTS (SELECT * FROM Region where RegionDescription = @region)
BEGIN
  THROW 50001, 'Error!', 0;
END
Else 
Begin 
select * from Region where RegionDescription = @region
end