Embedded Files
Teknika e NEN -QUERY
Bazohet ne Query te lidhura midis tyre me operatore si IN , EXIST etj
Le ta ilustrojme kete teknike me nje shembull.
Ushtrim
Afishoni nje liste me punonjesit qe kane realizuar me shume se 100 porosi
SELECT Firstname,LastName From Employees
where
EmployeeID IN
(
SELECT EmployeeID from Orders
group by EmployeeID
having COUNT(*)>100
)
Ushtrime
Ushtrim 1
Afishoni klientet me te mire ne numer porosish. Do te konsiderohen kliente te mire ata qe kane kryer me shume se 50 porosi.
Zgjidhje
-- se pari grupojme rreshtat e tabeles orders sipas CustomerId dhe numerojme sa rreshta
-- jane per cdo grup
-- keshtu gjejme per cdo kod klienti sa porosi ka kryer
-- duke filtruar rezulatetin e numerimit afishojme vetem kodet e klienteve
-- me me shume se 50 porosi
SELECT CustomerID
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*)>10
Komanda afishon:
-- se dyti afishojme te dhenat e klienteve nga tabela customers
-- per ato kliente kodi i te cileve gjendet ne listen e CustomerID te gjetura
-- ne piken 1
SELECT * FROM Customers -- problemi i dyte
WHERE CustomerID IN
(
-- problemi i pare
SELECT CustomerID
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*)>10
)
komanda me siper afishon:
Ushtrim 2
Afishoni punonjesit me te mire. Punonjes te mire quhen ata qe kane realizuarnje xhiro financiare mbi 10.000
Ushtrim 3
Afishoni klientet me te mire. Kliente te mire quhen ata qe kane realizuarnje xhiro financiare mbi 10.000
Ushtrim 4
Afishoni te dhenat e furnitoreve tek te cilet blejme me shume se sa 5 produkte te ndryshme
Zgjidhje
-- Problemi 1
-- te gjejme kodet e furnitoreve nga te cilet blejme me shume se 3 produkte
-- Per kete, grupojme rreshtat e tabeles products sipas supplierid dhe i numerojme ato
SELECT SupplierID
from Products
Group by SupplierID
having COUNT(*)>3
komanda afishon:
--Problemi 2
-- te afishojme te dhenat e furnitoreve me keto kode
SELECT * from Suppliers
where SupplierID IN
(
SELECT SupplierID
from Products
Group by SupplierID
having COUNT(*)>3
)
komanda afishon:
Ushtrim 5
Afishoni te dhenat e furnitoreve produktet e te cileve jane shitur ne me shume se 20 porosi.
Ushtrim 6
Afishoni te dhenat e punonjesve qe kane realizuar shitje te medha.
Nje fature konsiderohet e madhe nese vlera e saj eshte me e madhe se 10.000 euro
Zgjidhje
-- Problemi 1
-- te gjejme kodet e faturave me vlere me te madhe se 1000 euro
-- gjejme sa eshte vlera e cdo fature
-- tek order details do grupojme rreshtat sipas kodeve
-- te fatures dhe do gjejme shumen e sasi*cmim
SELECT OrderID
FROM [Order Details]
group BY OrderID
HAVING SUM(Quantity*UnitPrice)>10000
kodi afishon:
-- Problemi 2
-- te gjejme kodet e punonjesve qe kane leshuar ndonje nga keto fatura
SELECT EmployeeID
FROM Orders
where OrderID IN
(
SELECT OrderID
FROM [Order Details]
group BY OrderID
HAVING SUM(Quantity*UnitPrice)>10000
)
Kodi afishon:
-- problemi 3
-- te afishojme te dhenat e punonjesve qe e kane kodin ne listen e gjetur ne piken 2
SELECT * FROM Employees
where EmployeeID IN
(
SELECT DISTINCT EmployeeID
FROM Orders
where OrderID IN
(
SELECT OrderID
FROM [Order Details]
group BY OrderID
HAVING SUM(Quantity*UnitPrice)>10000
)
)
kodi afishon:
Ushtrim 7
Afishoni te dhenat e klienteve qe kane realizuar blerje te medha.
Nje fature konsiderohet e madhe nese vlera e saj eshte me e madhe se 10.000 euro
Zgjidhje
-- Problemi 1
-- te gjejme kodet e faturave me vlere me te madhe se 1000 euro
-- gjejme sa eshte vlera e cdo fature
-- tek order details do grupojme rreshtat sipas kodeve
-- te fatures dhe do gjejme shumen e sasi*cmim
SELECT OrderID
FROM [Order Details]
group BY OrderID
HAVING SUM(Quantity*UnitPrice)>10000
kodi afishon:
-- Problemi 2
-- te gjejme kodet e klienteve qe kane blere ndonje nga keto
SELECT distinct CustomerID
FROM Orders
where OrderID IN
(
SELECT OrderID
FROM [Order Details]
group BY OrderID
HAVING SUM(Quantity*UnitPrice)>10000
)
-- problemi 3
-- te afishojme te dhenat e punonjesve qe e kane kodin ne listen e gjetur ne piken 2
SELECT * FROM Customers
where CustomerID IN
(
SELECT Distinct CustomerId
FROM Orders
where OrderID IN
(
SELECT OrderID
FROM [Order Details]
group BY OrderID
HAVING SUM(Quantity*UnitPrice)>10000
)
)
kodi afishon:
Ushtrimi 8
Afishoni te dhenat e furnitoreve produktet e te cileve jane shitur me me shume se 500 euro copa.
RIEMERIMI I TABELAVE DHE JOIN I NJE TABELE ME VETVEHTEN
Shembull
Te afishohet numri i shitjeve te kompanise sipas cdo shteti per cdo vit 1996, 1997 , 1998
zgjidhje:
SELECT DISTINCT o1.ShipCountry,
(
SELECT COUNT(*) from orders o2 where OrderDate between '1996-01-01' and '1996-12-31' and o2.ShipCountry= o1.ShipCountry
) 'Year1996',
(
SELECT COUNT(*) from orders o3 where OrderDate between '1997-01-01' and '1997-12-31' and o3.ShipCountry= o1.ShipCountry
) 'Year1997',
(
SELECT COUNT(*) from orders o4 where OrderDate between '1998-01-01' and '1998-12-31' and o4.ShipCountry= o1.ShipCountry
) 'Year1998'
from Orders o1
Kodi i mesiperm do te afishonte:
SELECT DISTINCT o1.ShipVia,Shippers.CompanyName,
(
select COUNT(*) from orders o96 where OrderDate between '1996-1-1' and '1996-12-31'
and o96.ShipVia=o1.ShipVia
) '1996',
(
select COUNT(*) from orders o97 where OrderDate between '1997-1-1' and '1997-12-31'
and o97.ShipVia=o1.ShipVia
) '1997',
(
select COUNT(*) from orders o98 where OrderDate between '1998-1-1' and '1998-12-31'
and o98.ShipVia=o1.ShipVia
) '1998'
from Orders o1 INNER JOIN Shippers
ON o1.ShipVia=Shippers.ShipperID
Ndersa nese do te donim vleren financiare qe kemi realizuar me cdo shtet per vitet e mesiperme do te perdornim querin e meposhtem.
Shenim: Ne querin me poshte do te perdorim funksionin dbo.vlerefature qe merr si argument OrderId dhe kthen vleren financiare te saj.
Zgjidhje:
SELECT DISTINCT o1.ShipCountry,
(SELECT SUM(dbo.VlereFature(OrderID)) from orders o2 where OrderDate between '1996-01-01' and '1996-12-31' and o2.ShipCountry= o1.ShipCountry) '1996',
(SELECT SUM(dbo.VlereFature(OrderID)) from orders o3 where OrderDate between '1997-01-01' and '1997-12-31' and o3.ShipCountry= o1.ShipCountry) '1997',
(SELECT SUM(dbo.VlereFature(OrderID)) from orders o4 where OrderDate between '1998-01-01' and '1998-12-31' and o4.ShipCountry= o1.ShipCountry) '1998'
from Orders o1
kodi i mesiperm do te afishonte:
-- punonjesit e dalluar ne nje periudhe te dhene
--kombinimi i where dhe having
-- where vendoset para grupimit
-- having pas
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 '1996-01-01' and '1997-01-01'
GROUP by Employees.EmployeeID,Employees.FirstName,Employees.LastName
having SUM([Order Details].UnitPrice*[Order Details].Quantity)>20000
- te afishojme te dhenat e punonjesve
-- dhe xhiron financiare
--per punonjesit e dalluar (xhiro ja>50000)
-- punonjesit jane tek employees
-- xhiroja ndodhet tek orders por nuk ka cmimet
-- order details shuma(cmim*sasi) te rreshtave te order
SELECT Orders.EmployeeID,Orders.OrderID,[Order Details].UnitPrice,
[Order Details].Quantity
from orders inner join [Order Details]
ON orders.Orderid=[order details].OrderID
order by EmployeeID
--do te afishonte
SELECT Orders.EmployeeID,[Order Details].UnitPrice,
[Order Details].Quantity
from orders inner join [Order Details]
ON orders.Orderid=[order details].OrderID
order by EmployeeID
SELECT Orders.EmployeeID, SUM([Order Details].UnitPrice*[Order Details].Quantity) 'vlera'
from orders inner join [Order Details]
ON orders.Orderid=[order details].OrderID
GROUP BY Orders.EmployeeID
HAVING SUM([Order Details].UnitPrice*[Order Details].Quantity) >200000
SELECT * FROM Employees
WHERE EmployeeID
IN
(
SELECT Orders.EmployeeID
from orders inner join [Order Details]
ON orders.Orderid=[order details].OrderID
GROUP BY Orders.EmployeeID
HAVING SUM([Order Details].UnitPrice*[Order Details].Quantity) >200000
)
-- ti mbledhim bashke dhe ti grupojme ne fund
-- marrim te gjitha kollonat qe na duhen ne llogaritje
SELECT Employees.EmployeeID,Employees.FirstName,Employees.LastName,
[Order Details].UnitPrice,[Order Details].Quantity
FROM
Employees inner join Orders ON Employees.EmployeeID=Orders.EmployeeID
inner join [Order Details] ON Orders.OrderID=[Order Details].OrderID
order by Employees.EmployeeID
-- punonjesit e dalluar menyre tjeter
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
GROUP by Employees.EmployeeID,Employees.FirstName,Employees.LastName
having SUM([Order Details].UnitPrice*[Order Details].Quantity)>200000
-- per cdo kategori produktesh te afishojme volumin financiar
SELECT Categories.CategoryID,Categories.CategoryName,
SUM([Order Details].UnitPrice*[Order Details].Quantity) 'Sales'
FROM Categories
INNER JOIN Products ON Categories.CategoryID=Products.CategoryID
INNER JOIN [Order Details] ON Products.ProductID=[Order Details].ProductID
GROUP BY Categories.CategoryID,Categories.CategoryName
order by Categories.CategoryName
shembuj te tjere
-- AFISHONI SHERBIMIN POSTAR ME TE CILIN ESHTE TRANSPORTUAR
-- POROSIA ME E SHTRENJTE
--SE PARI GJEJME VLEREN FINANCIARE PER CDO PORORI
SELECT OrderId,SUM((Quantity*UnitPrice)) 'value'
from [Order Details]
group by OrderID
-- per lehtesi kodimi kesaj query po i vendosim nje emer(VIEW)
CREATE VIEW shitjet as
SELECT OrderId,SUM((Quantity*UnitPrice)) 'value'
from [Order Details]
group by OrderID
-- provojme view shitjet
SELECT * from Shitjet
-- do gjejme maksimumin e vleres se porosive
--sa ka qene vlera e porosise me te shtrenjte
SELECT MAX(value) FROM Shitjet
-- cili eshte kodi i porosise/ve qe kane kete vlere
SELECT OrderID from SHitjet
where value IN
(
SELECT MAX(value) FROM Shitjet
)
-- te gjejme kodin/et e shippers qe kane trajtuar kete porosi
-- do kerkojme ne tabelen orders
SELECT ShipVia
FROM Orders
where OrderID IN
(
SELECT OrderID from SHitjet
where value IN
(
SELECT MAX(value) FROM Shitjet
)
)
-- afishoj te dhenat e transportuesit
SELECT * from Shippers
where ShipperID IN
(
SELECT ShipVia
FROM Orders
where OrderID IN
(
SELECT OrderID from SHitjet
where value IN
(
SELECT MAX(value) FROM Shitjet
)
)
)
Page updated
Report abuse