Advertisement
ilianrusev

sql

Nov 15th, 2021
38
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --1
  2. create table EmployeeSalesTotal
  3. (
  4. EmployeeID int PRIMARY KEY,
  5. EmployeeName nvarchar(40),
  6. TotalSales money
  7. )
  8. go
  9.  
  10. --2
  11. insert into EmployeeSalesTotal
  12. select e.EmployeeID,
  13. CONCAT(e.FirstName,'', e.LastName)as EmployeeFullName,
  14. sum(od.quantity *od.UnitPrice ) as SumOfQuantity
  15. from NorthWind.dbo.Employees e
  16. inner join Northwind.dbo.[Order Details] od on e.EmployeeID = od.ProductID
  17. inner join Northwind.dbo.Orders o on od.OrderID = o.EmployeeID
  18. where YEAR(o.OrderDate)=2008
  19. group by e.EmployeeID,
  20. e.FirstName,
  21. e.LastName
  22. go
  23.  
  24. --3
  25. DELETE FROM EmployeeSalesTotal
  26. WHERE TotalSales = '<50000'
  27. go
  28.  
  29. --4
  30. create view EmployeeView
  31. as
  32. select e.EmployeeID,
  33. CONCAT(e.FirstName,'', e.LastName)as EmployeeFullName
  34. from NorthWind.dbo.Employees e
  35.  
  36. go
  37.  
  38. --6
  39. create function Sum_Shippers_Sales (@ProductID int)
  40. returns money
  41. as
  42. begin
  43. declare @result money
  44.  
  45. select @result = sum(od.Quantity * od.UnitPrice)
  46. from NorthWind.dbo.[Order Details] od
  47. where od.ProductID = @ProductID
  48.  
  49. if @result is null
  50. set @result = 0
  51. return @result
  52. end
  53. go
  54.  
  55.  
  56. --8
  57. drop view EmployeeView
  58. go
  59.  
  60. --9
  61. drop table EmployeeSalesTotal
  62. go
  63.  
  64. --10
  65. drop function Sum_Shippers_Sales
  66. go
  67.  
Advertisement
RAW Paste Data Copied
Advertisement