--1.
Declare @OrderCount int, @TotalSales money, @AvgSales money;
Set @OrderCount = (Select Distinct Count(*) From Orders);
Set @TotalSales = (Select Sum((ItemPrice-DiscountAmount)*Quantity) From OrderItems);
Set @AvgSales = (@TotalSales/@OrderCount);
Print 'OrderCount = ' + Convert(varchar,@OrderCount)
Print 'TotalSales = ' + Convert(varchar,@TotalSales)
Print 'AvgSales = ' + Convert(varchar,@AvgSales)

--2.
Declare @ProductCount int, @AvgListPrice money;
Set @ProductCount = (Select Count(*) From Products);
Set @AvgListPrice = (Select Avg(ListPrice) From Products);

If @ProductCount >= 7
Begin
	Print 'ProductCount = ' + Convert(varchar,@ProductCount)
	Print 'AvgListPrice = ' + Convert(varchar,@AvgListPrice)
End
Else
	Print 'The number of products is less than 7.'

--3.
Declare @CustName varchar(50), @LargeOrder money;
Select Top 1 @CustName = FirstName + ' ' + LastName
From Customers Join Orders On Customers.CustomerID = Orders.CustomerID
Join OrderItems On Orders.OrderID = OrderItems.OrderID
Order By (ItemPrice-DiscountAmount)*Quantity desc
Select Top 1 @LargeOrder = (ItemPrice-DiscountAmount)*Quantity
From Customers Join Orders On Customers.CustomerID = Orders.CustomerID
Join OrderItems On Orders.OrderID = OrderItems.OrderID
Order By (ItemPrice-DiscountAmount)*Quantity desc

Print 'The largest order of $' + Convert(varchar,@LargeOrder) + ' was made by ' + @CustName + '.'

--4.
BEGIN TRY
    INSERT Categories
    VALUES ('Guitars');
    PRINT 'SUCCESS: Record was inserted.';
END TRY

BEGIN CATCH
    PRINT 'FAILURE: Record was not inserted.';
    PRINT 'Error '
          + CONVERT(varchar, ERROR_NUMBER())
          + ': '
          + ERROR_MESSAGE();
END CATCH

--5.
If Object_ID('findAverageSales') Is Not Null
	Drop Proc findAverageSales
GO
Create Proc findAverageSales
	@AvgSales money Output
AS
	Set @AvgSales = (Select Sum((ItemPrice-DiscountAmount)*Quantity)/Count(Distinct Orders.OrderID) From OrderItems Join Orders On OrderItems.OrderID = Orders.OrderID)
GO

Declare @AvgSalesOut money
Exec findAverageSales
@AvgSales = @AvgSalesOut Output
Print @AvgSalesOut

--6.
If Object_ID('spInsertCategory') Is Not Null
	Drop Proc spInsertCategory
GO
Create Proc spInsertCategory
	@CatName varchar(50)
AS
BEGIN TRY
    INSERT Categories
    VALUES (@CatName);
    PRINT 'Insert was successful!';
END TRY

BEGIN CATCH
    PRINT 'Insert failed!';
END CATCH
Go

Exec spInsertCategory @CatName = 'Chimes'
Exec spInsertCategory @CatName = 'Guitars'