SHARE
TWEET

Untitled

a guest Feb 17th, 2020 105 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --1.
  2. Declare @OrderCount int, @TotalSales money, @AvgSales money;
  3. Set @OrderCount = (Select Distinct Count(*) From Orders);
  4. Set @TotalSales = (Select Sum((ItemPrice-DiscountAmount)*Quantity) From OrderItems);
  5. Set @AvgSales = (@TotalSales/@OrderCount);
  6. Print 'OrderCount = ' + Convert(varchar,@OrderCount)
  7. Print 'TotalSales = ' + Convert(varchar,@TotalSales)
  8. Print 'AvgSales = ' + Convert(varchar,@AvgSales)
  9.  
  10. --2.
  11. Declare @ProductCount int, @AvgListPrice money;
  12. Set @ProductCount = (Select Count(*) From Products);
  13. Set @AvgListPrice = (Select Avg(ListPrice) From Products);
  14.  
  15. If @ProductCount >= 7
  16. Begin
  17.     Print 'ProductCount = ' + Convert(varchar,@ProductCount)
  18.     Print 'AvgListPrice = ' + Convert(varchar,@AvgListPrice)
  19. End
  20. Else
  21.     Print 'The number of products is less than 7.'
  22.  
  23. --3.
  24. Declare @CustName varchar(50), @LargeOrder money;
  25. Select Top 1 @CustName = FirstName + ' ' + LastName
  26. From Customers Join Orders On Customers.CustomerID = Orders.CustomerID
  27. Join OrderItems On Orders.OrderID = OrderItems.OrderID
  28. Order By (ItemPrice-DiscountAmount)*Quantity desc
  29. Select Top 1 @LargeOrder = (ItemPrice-DiscountAmount)*Quantity
  30. From Customers Join Orders On Customers.CustomerID = Orders.CustomerID
  31. Join OrderItems On Orders.OrderID = OrderItems.OrderID
  32. Order By (ItemPrice-DiscountAmount)*Quantity desc
  33.  
  34. Print 'The largest order of $' + Convert(varchar,@LargeOrder) + ' was made by ' + @CustName + '.'
  35.  
  36. --4.
  37. BEGIN TRY
  38.     INSERT Categories
  39.     VALUES ('Guitars');
  40.     PRINT 'SUCCESS: Record was inserted.';
  41. END TRY
  42.  
  43. BEGIN CATCH
  44.     PRINT 'FAILURE: Record was not inserted.';
  45.     PRINT 'Error '
  46.           + CONVERT(varchar, ERROR_NUMBER())
  47.           + ': '
  48.           + ERROR_MESSAGE();
  49. END CATCH
  50.  
  51. --5.
  52. If Object_ID('findAverageSales') Is Not Null
  53.     Drop Proc findAverageSales
  54. GO
  55. Create Proc findAverageSales
  56.     @AvgSales money Output
  57. AS
  58.     Set @AvgSales = (Select Sum((ItemPrice-DiscountAmount)*Quantity)/Count(Distinct Orders.OrderID) From OrderItems Join Orders On OrderItems.OrderID = Orders.OrderID)
  59. GO
  60.  
  61. Declare @AvgSalesOut money
  62. Exec findAverageSales
  63. @AvgSales = @AvgSalesOut Output
  64. Print @AvgSalesOut
  65.  
  66. --6.
  67. If Object_ID('spInsertCategory') Is Not Null
  68.     Drop Proc spInsertCategory
  69. GO
  70. Create Proc spInsertCategory
  71.     @CatName varchar(50)
  72. AS
  73. BEGIN TRY
  74.     INSERT Categories
  75.     VALUES (@CatName);
  76.     PRINT 'Insert was successful!';
  77. END TRY
  78.  
  79. BEGIN CATCH
  80.     PRINT 'Insert failed!';
  81. END CATCH
  82. Go
  83.  
  84. Exec spInsertCategory @CatName = 'Chimes'
  85. Exec spInsertCategory @CatName = 'Guitars'
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top