FUNKSIONET TABELE
Problem 1- Ndertoni nje funksion i cili kthen nje liste me emrat e furnitoreve nga te cilet blejme me shume se n(psh 2) produkte
Zgjidhje
-- te ndertojme nje funksion i cili
-- kthen emrat e furnitoreve nga te cilet ne blejme
--me shume se n produkte , psh n=2
CREATE FUNCTION activesuppliers(@n as int)
returns table
as
return
(
SELECT companyName
FROM Suppliers
where SupplierID IN
(
SELECT SupplierID
FROM Products
group by SupplierID
having COUNT(*)>@n
)
)
Po ta perdorim funksionin:
SELECT * from dbo.activesuppliers(3)
do te na kthente emrat e furnitoreve nga te cilet blejme me shume se 3 produkte
-- libri i sshitjeve per nje periiudhe kohore midis dt1 dh dt2
-- te ndertojme nje funksion
CREATE FUNCTION LibriShitjeve(@dt1 as date,@dt2 as date)
RETURNS TABLE
AS
RETURN
(
SELECT OrderId 'FatureNr',OrderDate 'Date',CustomerID 'KodiKlientit',
dbo.CustomerName(CustomerID) 'Klienti',dbo.VlereFature(OrderID) 'Vlera'
FROM Orders
where OrderDate BETWEEN @dt1 AND @dt2
)
-- per librin e shitjeve te muajit korrik 1996
SELECT * FROM LibriSHitjeve('1996-07-01','1996-08-01')
Problem 2
-- ndertoni nje funksion tabele qe kthen listen e klienteve qe kane kryer
-- nje numer transaksionesh me te madh se n
CREATE FUNCTION CustByTrans(@transno as int)
RETURNS TABLE
AS
RETURN
(
SELECT * from customers
where CustomerId IN
(
SELECT CustomerID from Orders
group by CustomerID
HAVING count(*)>@transno
)
)
ose
CREATE FUNCTION CustByTrans2(@transno as int)
RETURNS TABLE
AS
RETURN
(
SELECT CustomerId,dbo.CustomerName(CustomerID) 'Customer' from Orders
group by CustomerID
HAVING count(*)>@transno
)
.....
-- te ndertojme nje funksion qe merr si argument
-- emrin e nje periudhe dhe dy data d1 dhe d2 qe kufizojne periudhen
-- funksioni kthen shitjet ne ate periiudhe
CREATE FUNCTION shitjetperiudhe(@periudha varchar(50),@dt1 as date, @dt2 as date)
RETURNS TABLE
AS
RETURN
(
SELECT @periudha as 'periudha',SUM(dbo.VlereFature(OrderID)) 'shitje'
FROM Orders
WHERE OrderDate BETWEEN @dt1 AND @dt2
)
SELECT * from dbo.shitjetperiudhe('korrik 1996','1996-07-01','1996-07-31')
UNION ALL
SELECT * from dbo.shitjetperiudhe('gusht 1996','1996-08-01','1996-08-31')
UNION ALL
SELECT * from dbo.shitjetperiudhe('shtator 1996','1996-09-01','1996-09-30')
UNION ALL
SELECT * from dbo.shitjetperiudhe('tetor 1996','1996-10-01','1996-10-31')
UNION ALL
SELECT * from dbo.shitjetperiudhe('nentor 1996','1996-11-01','1996-11-30')
UNION ALL
SELECT * from dbo.shitjetperiudhe('dhjetor 1996','1996-12-01','1996-12-31')
-- te ndertojme nje raport mbi shitjet mujore per cdo shtet
CREATE FUNCTION shitjetperiudheshtet(@periudha varchar(50),@dt1 as date, @dt2 as date)
RETURNS TABLE
AS
RETURN
(
SELECT @periudha as 'periudha',shipcountry,SUM(dbo.VlereFature(OrderID)) 'shitje'
FROM Orders
WHERE OrderDate BETWEEN @dt1 AND @dt2
GROUP BY shipcountry
)
SELECT * from dbo.shitjetperiudheshtet('korrik','1996-07-01','1996-07-31')
UNION ALL
SELECT * from dbo.shitjetperiudheshtet('gusht','1996-08-01','1996-08-31')
UNION ALL
SELECT * from dbo.shitjetperiudheshtet('shtator','1996-09-01','1996-09-30')
UNION ALL
SELECT * from dbo.shitjetperiudheshtet('tetor','1996-10-01','1996-10-31')
UNION ALL
SELECT * from dbo.shitjetperiudheshtet('nentor','1996-11-01','1996-11-30')
UNION ALL
SELECT * from dbo.shitjetperiudheshtet('dhjetor','1996-12-01','1996-12-31')
-- ta parametrizojme kete query
-- qe te mos e shkruajme prape
-- te bejme nje funksion tabele
CREATE FUNCTION PunonjesGreat
(
@dt1 as datetime,
@dt2 as datetime,
@amount as float
)
RETURNS TABLE
AS
RETURN
(
SELECT Employees.EmployeeID,Employees.FirstName,Employees.LastName,
SUM([Order Details].UnitPrice*[Order Details].Quantity) 'value'
FROM
Employees inner join Orders ON Employees.EmployeeID=Orders.EmployeeID
inner join [Order Details] ON Orders.OrderID=[Order Details].OrderID
where Orders.OrderDate between @dt1 and @dt2
GROUP by Employees.EmployeeID,Employees.FirstName,Employees.LastName
having SUM([Order Details].UnitPrice*[Order Details].Quantity)>@amount
)
-
CREATE FUNCTION dbo.EmriPunonjesit(@kodi as int)
RETURNS varchar(100)
AS
BEGIN
DECLARE @res as varchar(100)
select @res=(Firstname+' '+Lastname) from Employees
where EmployeeId=@kodi
return @res
END
SHEMBULL ME funksionet
SELECT 1996 as 'viti',SUM(dbo.VlereFature(OrderID)) 'sales'
FROM Orders
WHERE OrderDate BETWEEN '1996-01-01' AND '1996-12-31'
UNION ALL
SELECT 1997 as 'year',SUM(dbo.VlereFature(OrderID)) 'sales'
FROM Orders
WHERE OrderDate BETWEEN '1997-01-01' AND '1997-12-31'
-- te ndertojme nje funksion qe merr si argument
-- emrin e nje periudhe dhe dy data d1 dhe d2 qe kufizojne periudhen
-- funksioni kthen shitjet ne ate periiudhe
CREATE FUNCTION shitjetperiudhe(@periudha varchar(50),@dt1 as date, @dt2 as date)
RETURNS TABLE
AS
RETURN
(
SELECT @periudha as 'periudha',SUM(dbo.VlereFature(OrderID)) 'shitje'
FROM Orders
WHERE OrderDate BETWEEN @dt1 AND @dt2
)
SELECT * from dbo.shitjetperiudhe('korrik 1996','1996-07-01','1996-07-31')
UNION ALL
SELECT * from dbo.shitjetperiudhe('gusht 1996','1996-08-01','1996-08-31')
UNION ALL
SELECT * from dbo.shitjetperiudhe('shtator 1996','1996-09-01','1996-09-30')
UNION ALL
SELECT * from dbo.shitjetperiudhe('tetor 1996','1996-10-01','1996-10-31')
UNION ALL
SELECT * from dbo.shitjetperiudhe('nentor 1996','1996-11-01','1996-11-30')
UNION ALL
SELECT * from dbo.shitjetperiudhe('dhjetor 1996','1996-12-01','1996-12-31')
SELECT 1997 'viti',shipcountry,SUM(dbo.VlereFature(oRDERId)) 'sales'
FROM Orders
WHERE OrderDate BETWEEN '1997-01-01' AND '1997-12-31'
group by ShipCountry
CREATE FUNCTION shitjetperiudheshtet(@periudha varchar(50),@dt1 as date, @dt2 as date)
RETURNS TABLE
AS
RETURN
(
SELECT @periudha as 'periudha',shipcountry,SUM(dbo.VlereFature(OrderID)) 'shitje'
FROM Orders
WHERE OrderDate BETWEEN @dt1 AND @dt2
GROUP BY shipcountry
)
SELECT * from dbo.shitjetperiudheshtet('korrik','1996-07-01','1996-07-31')
UNION ALL
SELECT * from dbo.shitjetperiudheshtet('gusht','1996-08-01','1996-08-31')
UNION ALL
SELECT * from dbo.shitjetperiudheshtet('shtator','1996-09-01','1996-09-30')
UNION ALL
SELECT * from dbo.shitjetperiudheshtet('tetor','1996-10-01','1996-10-31')
UNION ALL
SELECT * from dbo.shitjetperiudheshtet('nentor','1996-11-01','1996-11-30')
UNION ALL
SELECT * from dbo.shitjetperiudheshtet('dhjetor','1996-12-01','1996-12-31')
-- funksion qe merr si argument kodin e punonjesit dhe kthen xhiron qe ka bere
CREATE FUNCTION xhiro(@punonjesi as int)
RETURNS money
AS
BEGIN
DECLARE @res as money
select @res=SUM(([Order Details].UnitPrice*[Order Details].Quantity))
from Orders INNER JOIN
[Order Details] ON Orders.OrderID=[Order Details].OrderID
where EmployeeID=@punonjesi
GROUP BY Orders.EmployeeID
return @res
END
-- libri i sshitjeve per nje periiudhe kohore midis dt1 dh dt2
-- te ndertojme nje funksion
CREATE FUNCTION LibriShitjeve(@dt1 as date,@dt2 as date)
RETURNS TABLE
AS
RETURN
(
SELECT OrderId 'FatureNr',OrderDate 'Date',CustomerID 'KodiKlientit',
dbo.CustomerName(CustomerID) 'Klienti',dbo.VlereFature(OrderID) 'Vlera'
FROM Orders
where OrderDate BETWEEN @dt1 AND @dt2
)
-- per librin e shitjeve te muajit korrik 1996
SELECT * FROM LibriSHitjeve('1996-07-01','1996-08-01')
-- TE NDERTOJME QUERY QE DO ISHTE ZEMRA E FUNKSIONIT E ME PAS DO TA PARAMETRIZOJME
-- ATE BRENDA FUNKSIONIT
SELECT OrderId 'FatureNr',OrderDate 'Date',CustomerID 'KodiKlientit',
dbo.CustomerName(CustomerID) 'Klienti',dbo.VlereFature(OrderID) 'Vlera'
FROM Orders
where OrderDate BETWEEN '1996-07-01' AND '1996-08-01'
CREATE FUNCTION CustomerName(@cid as char(5))
RETURNS varchar(50)
AS
BEGIN
declare @cn as varchar(50)
select @cn=companyname from Customers
where CustomerId=@cid
return @cn
END
-- ndertoni nje funksion tabele qe kthen listen e klienteve qe kane kryer
-- nje numer transaksionesh me te madh se n
CREATE FUNCTION CustByTrans(@transno as int)
RETURNS TABLE
AS
RETURN
(
SELECT * from customers
where CustomerId IN
(
SELECT CustomerID from Orders
group by CustomerID
HAVING count(*)>@transno
)
)
SELECT * from CustByTrans(10)
-- 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
-- Zgjidhja e Ushtrimit 4 me funksione
-- 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
-- tek ushtrimi 3. 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 filtrojme 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
-- USHTRIMI 2 . 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
Shembulli me funksionin tabele kompleks
-- te ndertojme nje funksion qe merr si argument 2 data
--@dt1 dhe @dt2
-- dhe kthen librin e shitjeve te kompannis ene kete periudhe
-- libri i shitjeve perbehet nga:
--kodi i porosisie,data,emri i punonjesit,emri i klientit
CREATE FUNCTION liber(@dt1 DATETIME, @dt2 DATETIME)
RETURNS @L TABLE
(
Kodi INT PRIMARY KEY,
Data DATETIME,
Punonjesi VARCHAR(50),
Klienti VARCHAR(50),
Vlera MONEY
)
AS
BEGIN
INSERT INTO @L(Kodi,Data,Punonjesi,Klienti,Vlera)
SELECT
Orders.OrderID,Orders.OrderDate,
FirstName+' '+LastName,
Customers.CompanyName,
SUM(UnitPrice*Quantity)
FROM Orders inner join [Order Details]
ON Orders.OrderID=[Order Details].OrderID
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
INNER JOIN
Customers
ON Orders.CustomerID=Customers.CustomerID
WHERE OrderDate BETWEEN @dt1 and @dt2
GROUP BY
Orders.OrderID,Orders.OrderDate,
FirstName+' '+LastName,
Customers.CompanyName
RETURN
END
SELECT * FROM dbo.liber('1996-1-1','1997-1-1')
-- ushtrim
-- te afishojme xhiron per cdo shtet
-- ne nje periudhe te caktuar
--po fokusohemi ne dy data psh 1996-1-1 dhe 1997-1-1
-- ku eshte info per shtetet?
select * from Orders
-- per cdo porosi ruhet shteti ne kollonen ship country
-- ku eshte info per xhiron financiare?
select * from [Order Details]
select o.ShipCountry,
SUM(od.Quantity*od.UnitPrice) as xhiro
from Orders o
inner join
[Order Details] od
on o.OrderID=od.OrderID
where o.OrderDate
between '1996-1-1' and '1997-1-1'
group by ShipCountry
-- ta formuloje si nje funksion tabele
CREATE FUNCTION XhiroSipasShtetit
(
@dt1 date,
@dt2 date
)
RETURNS TABLE
AS
RETURN
(
select o.ShipCountry,
SUM(od.Quantity*od.UnitPrice) as xhiro
from Orders o
inner join
[Order Details] od
on o.OrderID=od.OrderID
where o.OrderDate
between @dt1 and @dt2
group by ShipCountry
)
select * from
dbo.XhiroSipasShtetit('1996-6-1','1997-1-1')
-- Ndertoni nje funksion tabele te thjeshte
-- qe merr si argument kodin e nje punonjesi
-- dhe kthen listen e punonjesve vartes te tij
CREATE FUNCTION shefi
(
@eid int
)
returns table
as
return
(
select * from Employees
where ReportsTo=@eid
)
select * from shefi(3)
-- JOIN I TABELES ME VETVHETEN
select e1.EmployeeID,e1.FirstName,e1.LastName,
e2.EmployeeID 'kodishefit',
e2.FirstName 'emrishefit',
e2.LastName 'mbiemrishefit',
COUNT(OrderID) 'Porosi'
from
Employees e1
inner join
Employees e2
on e1.ReportsTo=e2.EmployeeID
inner join
Orders o
on e1.EmployeeID=o.EmployeeID
where OrderDate between '1996-1-1' and '1997-1-1'
group by e1.EmployeeID,e1.FirstName,e1.LastName,
e2.EmployeeID,e2.FirstName,e2.LastName
------------------------------------------
print datepart(yy,'1996-1-1')
print datediff(dd,'1996-1-1','1997-1-1')
-- struktura programimi ne SQL
-- Te ndertojme nje funksionn
-- qe merr si argument nje numer te plote n
-- funksioni gjen dhe kthen shumen e n numrave te pare natyrore
CREATE FUNCTION shuma
(
@n as int
)
returns int
as
begin
declare @i as int -- deklarim i variablave
declare @s as int
set @i=1 -- vleredhenia
set @s=0
while(@i<=@n) -- cikel
begin -- bllok kodi
set @s=@s+@i
set @i=@i+1
end
return @s
end
print dbo.shuma(10)
print datepart(mm,'1996-1-2')
print datediff(mm,'1996-1-1','1997-1-1')
select OrderID,OrderDate from Orders
SHEMBULL - Afishoni lidhjet e nje punonjesi
ALTER FUNCTION lidhjet(@eid INT)
RETURNS @L TABLE
(
Nr INT Primary key identity(1,1),
Emri varchar(50),
Mbiemri varchar(50),
Kompania varchar(250),
Mardhenia varchar(50)
)
AS
BEGIN
INSERT INTO @L(Emri,Mbiemri,Kompania,Mardhenia)
select e1.FirstName,e1.LastName,'Northwind','Shefi Direkt'
from Employees e1
inner join employees e2
on e1.EmployeeID=e2.reportsto
where e2.EmployeeID=@eid
INSERT INTO @L(Emri,Mbiemri,Kompania,Mardhenia)
select e1.FirstName,e1.LastName,'Northwind','Vartes Direkt'
from Employees e1
where e1.ReportsTo=@eid
INSERT INTO @L(Emri,Mbiemri,Kompania,Mardhenia)
select ContactName,ContactName,
CompanyName,'Klient'
from Customers
where CustomerId IN
(
select distinct CustomerID
from Orders where EmployeeID=@eid
)
INSERT INTO @L(Emri,Mbiemri,Kompania,Mardhenia)
select ContactName,ContactName,
CompanyName,'Furnitor' from Suppliers
where SupplierID in
(
Select supplierID from Products
where productID IN
(
select DISTINCT ProductId from [Order Details]
WHERE
OrderId IN
(
SELECT OrderID from Orders Where EmployeeID=@eid
)
)
)
INSERT INTO @L(Emri,Mbiemri,Kompania,Mardhenia)
SELECT 'not available','not available',
CompanyName,'Shoqeri Transporti'
FROM Shippers
WHERE ShipperID IN
(
select DISTINCT ShipVia from orders
where EmployeeID=@eid
)
RETURN
END
Ushtrim - funksionet komplekse
Ndertoni nje funksion qe merr si argument shtetin dhe afishon shitjet qe jane realizuar ne ate shtet per vitet 1996,1997,1998. Shitjet do te jene ne formatin
Nr,Viti,Numri_i_Porosive,Xhiroja