FUNKSIONET SKALARE
-- te ndertojme nje funksion
-- qe merr si argument kodin e nje produkti
-- dhe kthen emrin e kategorise se ciles
-- i perket ky produkt
-- menyra e pare
CREATE FUNCTION catname1(@pid as INT)
returns varchar(50)
as
begin
declare @res as varchar(50)
select @res=CategoryName from
products inner join categories
on products.CategoryID=Categories.CategoryID
where ProductID=@pid
return @res
end
--prova
select ProductID,dbo.catname1(ProductID) from Products
--menyra e dyte
create function catname2(@pid as int)
returns varchar(50)
as
begin
Declare @catid INT, @catname varchar(50)
select @catid=CategoryId from Products
where ProductID=@pid
select @catname=CategoryName from Categories
where CategoryID=@catid
return @catname
end
-- te ndertojme nje funksion qe kthen emrin,mbiemrin e punonjesit,
-- te paraprire nga titulli i kortezise
CREATE FUNCTION dbo.EmployeeName(@eid as INT)
RETURNS Varchar(100)
AS
BEGIN
DECLARE @emri as varchar(100)
SELECT @emri=(TitleOfCourtesy+' '+Firstname+' '+Lastname)
from Employees where EmployeeId=@eid
return @emri
END
-- te ndertojme nje funksion qe merr kodin e furnitorit dhe
-- kthen emrin e kompanise furnitore
CREATE FUNCTION dbo.SupplierName(@sid as INT)
RETURNS Varchar(100)
AS
BEGIN
DECLARE @emri as varchar(100)
SELECT @emri=CompanyName
from Suppliers where SupplierID=@sid
return @emri
END
SELECT dbo.Suppliername(2)
-- te krijojme nje funksion qe merr si argument kodin e produktit dhe kthen
-- emrin e produktit
CREATE FUNCTION emriproduktit(@kodi as int) returns varchar(100)
AS
BEGIN
Declare @emri as varchar(100)
SELECT @emri=productname from products
where productid=@kodi
return @emri
END
SELECT dbo.emriproduktit(7)
-- te krijojme nje funksion qe merr si argument kodin e kategorise dhe kthen
-- emrin e kategorise
CREATE FUNCTION emrikategorise(@kodi as int) returns varchar(100)
AS
BEGIN
Declare @emri as varchar(100)
SELECT @emri=categoryname from categories
where categoryid=@kodi
return @emri
END
-- te krijojme nje funksion qe merr si argument kodin e shippers dhe kthen
-- emrin e shippers
CREATE FUNCTION ShippersName(@kodi as int) returns varchar(100)
AS
BEGIN
Declare @posta as varchar(100)
SELECT @posta=CompanyName from Shippers
where ShipperID=@kodi
return @posta
END
-- te ndertojme nje funksion qe merr si argument kodin e furnitorit dhe kthen
-- sa produkte blejme nga ai furnitor
-- te dhenat i kemi tek tabela products
SELECT * FROM Products
--te gjejme sa produkte blejme nga nje supplier psh supplierid=1
Select * from Products where SupplierID=1
-- ti numerojme keto rreshta me count(*)
Select COUNT(*) from
Products where SupplierID=1
-- ky query do te jete "ZEMRA" e funksionit
-- ku 1 do zevendesohet nga kodi i furnitorit @SUPID
-- dhe vlera qe kthen count(*) do te vendoset ne nje variabel INT @produkte
CREATE FUNCTION dbo.ProductsPerSupplier(@supid INT) RETURNS INT
AS
BEGIN
-- te deklarojme nje variabel ku do vendosim numrin e produkteve per furnitor
DECLARE @produkte AS INT
Select @produkte=COUNT(*) from
Products where SupplierID=@supid
RETURN @produkte
END
-- tani kthehemi tek u4
SELECT * from Suppliers
where dbo.ProductsPerSupplier(SupplierID)>2
-- Afishoni listen e klienteve me te mire , me xhiro pertej nje vlere te caktuar
--te ndertojme nje funksion xhiroklienti qe merr si argument kodin e klientit dhe kthen
-- xhironi financiare qe ai ka realizuar
-- perpara te arsyetoojme
-- informacioni per xhiron ndodhet ne Orders(customerid)
--dhe Order Details(vlera e faturave)
SELECT * from [Order Details]
select * from Orders
--na duhet te bejme JOIN midis dy tabelave per ti kombinuar
SELECT Orders.OrderID,Orders.CustomerID,UnitPrice,QUantity
FROM
ORDERS INNER JOIN [Order Details]
ON Orders.OrderID=[Order Details].OrderID
-- ta filtojme kete rezultat vetem per nje klient psh VINET
SELECT Orders.OrderID,Orders.CustomerID,UnitPrice,QUantity
FROM
ORDERS INNER JOIN [Order Details]
ON Orders.OrderID=[Order Details].OrderID
where CustomerID='VINET'
-- gjejme vleren finaciare per VINET
SELECT SUM(UnitPrice*Quantity)
FROM
ORDERS INNER JOIN [Order Details]
ON Orders.OrderID=[Order Details].OrderID
where CustomerID='VINET'
-- ky query do jete "ZEMRA" e funksionit
-- me ndryshimin qe VINET do zevendesohet me @cid char(5) qe funksion e merr si argument
-- dhe vlera qe kthen SUM(UnitPrice*Quantity) do te vendoset ne nje variable
-- te tipit float psh @xhiro e cila do te ktheht nga funksioni
CREATE FUNCTIOn dbo.xhiroklienti(@cid as CHAR(5)) RETURNS float
AS
BEGIN
DECLARE @xhiro as float
SELECT @xhiro=SUM(UnitPrice*Quantity)
FROM
ORDERS INNER JOIN [Order Details]
ON Orders.OrderID=[Order Details].OrderID
where CustomerID=@cid
RETURN @xhiro
END
-- ta provojme
SELECT dbo.xhiroklienti('VINET')
-- te zgjidhim problemin tone kliente me te mire me xhiro pertej nje vlere
SELECT dbo.xhiroklienti(customerid),*
FROM Customers
where dbo.xhiroklienti(customerid)>100000
order by dbo.xhiroklienti(customerid) DESC
-- te afishojme punonjesit me te dalluar, mbi bazen e zhiros financiare
--te ndertojme nje funksion qe i japim kodin e punonjesit dhe na
--kthen xhiron financiare te punonjesit
-- informacioni mbi xhiron per nje kod punonjesi ndodhet ne dy tabelat order dhe order details
-- po bejme nje JOIN midis ketyre dy tabelabe
SELECT Orders.OrderID,Orders.EmployeeID,UnitPrice,Quantity
FROM ORDERS INNER JOIN [Order Details]
ON Orders.OrderID=[Order Details].OrderID
-- perqendrohemi tek nje punonjes psh punonjesi me kodin 5
SELECT Orders.OrderID,Orders.EmployeeID,UnitPrice,Quantity
FROM ORDERS INNER JOIN [Order Details]
ON Orders.OrderID=[Order Details].OrderID
where EmployeeID=5
-- per te gjetur xhiron e punonjesit duhet te gjejme shumen e quantity*price per keta rreshta
SELECT SUM(UnitPrice*Quantity)
FROM ORDERS INNER JOIN [Order Details]
ON Orders.OrderID=[Order Details].OrderID
where EmployeeID=5
-- kjo do te jete "ZEMRA" e funksionit
-- vetem se 5 do zevendesohet me @eid int, qe funksioni e merr si argument
-- dhe SUM(UnitPrice*Quantity) do te vendoset ne nje variabel te tipit floar
-- qe do te ktheje funksioni si vlere te xhiros se klientit
CREATE FUNCTION dbo.xhiropunonjesi(@eid AS INT) RETURNS float
AS
BEGIN
DECLARE @xhiro as float
SELECT @xhiro=SUM(UnitPrice*Quantity)
FROM ORDERS INNER JOIN [Order Details]
ON Orders.OrderID=[Order Details].OrderID
where EmployeeID=@eid
RETURN @xhiro
END
--ta provojme
SELECT dbo.xhiropunonjesi(1)
--OK
--tani te afishojme punonjesit me te mire
SELECT dbo.xhiropunonjesi(EmployeeID),*
FROM Employees
where dbo.xhiropunonjesi(EmployeeID)>10000
ORDER BY dbo.xhiropunonjesi(EmployeeID) DESC
CREATE FUNCTION <emri i funksionit>
(<lista e argumenave te ndare me presje>)
RETURNS <tipi i te dhenave qe kthen funksioni>
AS
BEGIN
< kodi SQL qe perben llogjiken e funksionit>
RETURN <vlera qe kthen funksioni>
END
-- te ndertojme nje funksion skalar i cili merr si argument
-- kodin e nje punonjesi dhe kthen Emri Mbiemri
CREATE FUNCTION EmriPunonjesit
(
@kodi INT
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @e as varchar(100)
select @e=(FirstName+' '+LastName) from Employees
where EmployeeID=@kodi
RETURN @e
END
-- KU PERDOREN FUNKSIONET SKALARE?
-- KUDO KU NEVOJITET NJE SHPREHJE SKALARE
--SHEMBUJ
print dbo.EmriPunonjesit(3)
select dbo.EmriPunonjesit(1)
SELECT
EmployeeId,
dbo.EmriPunonjesit(EmployeeID) 'Punonjesi'
FROM Employees
select
OrderID,
dbo.EmriPunonjesit(EmployeeID) 'Punonjesi'
from Orders
-- te ndertojme nje funksion skalar
-- qe merr si argument kodin e punonjesit
--dhe kthen numrin e porosive
-- qe ai ka realizuar
CREATE FUNCTION NrPorosi
(
@eid INT
)
RETURNS INT
AS
BEGIN
DECLARE @nr INT
select @nr=COUNT(OrderId)
from Orders
where EmployeeID=@eid
group by EmployeeID
RETURN @nr
END
SELECT
EmployeeID,
dbo.EmriPunonjesit(EmployeeID)'Punonjesi',
dbo.NrPorosi(EmployeeID) 'Numri_Porosive',
5000 'Xhiro_Financiare'
from Employees
-- te ndertojme funksionin per xhiron financiare te nje punonjes
-- te ndertojme nje here query qe
--gjen xhiron financiare te punonjesit me kodin 1 si shembull
select SUM((UnitPrice*Quantity)*(1-Discount))
from Orders inner join [Order Details]
on orders.OrderID=[Order Details].OrderID
WHERE EmployeeID=1
GROUP BY EmployeeID
-- te ndertojme funksionin
ALTER FUNCTION XhiroPunonjesi
(
@eid INT
)
RETURNS money
AS
BEGIN
DECLARE @xhiro MONEY
select @xhiro=SUM((UnitPrice*Quantity)*(1-Discount))
from Orders inner join [Order Details]
on orders.OrderID=[Order Details].OrderID
WHERE EmployeeID=@eid
GROUP BY EmployeeID
RETURN @xhiro
END
SELECT
EmployeeID,
dbo.EmriPunonjesit(EmployeeID)'Punonjesi',
dbo.NrPorosi(EmployeeID) 'Numri_Porosive',
dbo.XhiroPunonjesi(EmployeeID) 'Xhiro_Financiare',
dbo.XhiroPunonjesi(EmployeeID)/dbo.NrPorosi(EmployeeID)
AS 'PorosiaMesatare'
from Employees
-- TE NDERTOJME TE NJEJTIN REZULTAT PER KLIENTET
-- CustomerId eshte CHAR(5)
-- Emri i klientit eshte CompanyName
--Detyre shtepie
-- funksionet tabele
CREATE FUNCTION <emri i funksionit>
(<lista e argumenave te ndare me presje>)
RETURNS TABLE
AS
RETURN
(
< komanda SELECT qe perben llogjiken e funksionit>
)
-- te ndertojme librin e shitjeve
-- te bejme query se pari
SELECT Orders.OrderID as Kodi,
Orders.OrderDate as Data,
Customers.CompanyName as klienti,
TitleOfCourtesy+' '+FirstName+' '+LastName as Punonjesi,
SUM((UnitPrice*(1-Discount))*Quantity) as 'Vlera'
FROM
Employees inner join Orders
on Employees.EmployeeID=Orders.EmployeeID
inner join Customers
on Orders.CustomerID=Customers.CustomerID
inner join [Order Details]
on orders.OrderID=[Order Details].OrderID
where OrderDate between '1996-1-1' and '1997-1-1'
group by
Orders.OrderID,
Orders.OrderDate ,
Customers.CompanyName,
TitleOfCourtesy+' '+FirstName+' '+LastName
-- tani te bejme funksionin libri i shitjeve
CREATE FUNCTION liber_shitje
(
@dt1 as datetime,
@dt2 as datetime
)
RETURNS TABLE
AS
RETURN
(
SELECT Orders.OrderID as Kodi,
Orders.OrderDate as Data,
Customers.CompanyName as klienti,
TitleOfCourtesy+' '+FirstName+' '+LastName as Punonjesi,
SUM((UnitPrice*(1-Discount))*Quantity) as 'Vlera'
FROM
Employees inner join Orders
on Employees.EmployeeID=Orders.EmployeeID
inner join Customers
on Orders.CustomerID=Customers.CustomerID
inner join [Order Details]
on orders.OrderID=[Order Details].OrderID
where OrderDate between @dt1 and @dt2
group by
Orders.OrderID,
Orders.OrderDate ,
Customers.CompanyName,
TitleOfCourtesy+' '+FirstName+' '+LastName
)
-- ku perdoret nje funksion tabele?
-- kudo ku duhet nje tabele
SELECT * FROM dbo.liber_shitje('1996-8-1','1996-9-1')
-- te ndertojme nje funksion qe merr si argument
--kodin e nje punonjesi
-- kthen listen e porosive
--qe ka kryer ai punonjes
CREATE FUNCTION shitje_punonjes
(
@kodipunonjesit INT
)
RETURNS TABLE
AS
RETURN
(
SELECT * FROM Orders
WHERE EmployeeID=@kodipunonjesit
)
-- te therrasim
SELECT * FROM dbo.shitje_punonjes(1)
-- detyre shtepie
-- ndertoni nje funksion tabele te thjeshte
--qe merr si argument kodin e nej klienti
-- dhe kthen porosite qe ai ka kryer
-- ndertoni nje funksion tabele qe merr si argument
--nje vlere financiare
--dhe kthen listen e punonjesve qe kane realizuar
--nje xhiro me te madhe se kjo vlere