sql

	--1
	create table EmployeeSalesTotal
	(
	  EmployeeID int PRIMARY KEY,
	  EmployeeName  nvarchar(40),
	  TotalSales  money
	)
	go

	--2
insert into EmployeeSalesTotal
   select  e.EmployeeID,
   CONCAT(e.FirstName,'', e.LastName)as EmployeeFullName,
		   sum(od.quantity *od.UnitPrice ) as SumOfQuantity
		   from NorthWind.dbo.Employees  e
inner join Northwind.dbo.[Order Details] od on e.EmployeeID = od.ProductID
inner join Northwind.dbo.Orders o on od.OrderID = o.EmployeeID
     where YEAR(o.OrderDate)=2008
  group by e.EmployeeID,
           e.FirstName,
		   e.LastName
go

--3
DELETE FROM EmployeeSalesTotal
WHERE TotalSales = '<50000'
go

--4
create view EmployeeView
  as
    select e.EmployeeID,
	       CONCAT(e.FirstName,'', e.LastName)as EmployeeFullName
      from NorthWind.dbo.Employees e

go

--6
create function Sum_Shippers_Sales (@ProductID int)
  returns money
   as
     begin
        declare @result money

        select @result = sum(od.Quantity * od.UnitPrice)
          from NorthWind.dbo.[Order Details] od
         where od.ProductID = @ProductID

        if @result is null
           set @result = 0
        return @result
     end
go


--8
drop view EmployeeView
go

--9
drop table EmployeeSalesTotal
go

--10
drop function Sum_Shippers_Sales
go