SELECT Statement:
A Basic Form
c Subqueries
c SELECT Statement: Other
Clauses and Functions
c Temporary Tables
c Join Operator
c Correlated Subqueries
c Table Expressions
Te marrim te dhena nga SQL Server.
Sintaksa e komandes SELECT
SELECT (liste me kollona ose *)
FROM emertabele
WHERE (shprehje llogjike)
.GROUP BY (liste me kollona)
HAVING (shprehje llogjike)
Per shembull per te afishuar te dhenat e punonjesit me kodin 1 do te shkruanim komanden e meposhtme:
SELECT *
FROM employees
WHERE EmployeeId=1
Shprehja llogjike mund te jete e perbere nga disa shprehje llogjike te lidhura nepermjet operatoreve llogjike AND,OR. Ne kete rast eshte e rekomandueshme te perdoren kllapat per te percaktuar prioritetin e veprimeve.
Per shembull komanda me poshte afishon listen e punonjesve qe jane zonja ose zonjusha
SELECT *
FROM employees
WHERE TitleOfCourtesy = 'Ms.' OR TitleOfCourtesy = 'Mrs.'
Operatori BETWEEN
Komanda e meposhtme afishon nje liste me punonjesit qe jane marre ne pune vitin e fundit:
SELECT firstname, lastname, hiredate
FROM employees
WHERE hiredate >= ‘1-Jan-2010’ AND hiredate <=‘31-Dec-2010’
Kjo komande mund te shkruhet ne nje menyre me te pembledhur duke perdorur operatorin BETWEEN
SELECT firstname, lastname, hiredate
FROM employees
WHERE hiredate
between ‘1-Jan-2010’ AND ‘31-Dec-2010’
Operatori NOT
Operatori NOT na mundeson llogaritjen e te kundertes se nje shprehje llogjike.
Shembulli i meposhtem, i cili afishon nje liste te punonjesve qe nuk jane marre ne pune vitin e fundit.
SELECT firstname, lastname, hiredate
FROM employees
WHERE hiredate
NOT between ‘1-Jan-2010’ AND ‘31-Dec-2010’
Operatori IN
Operatori IN krahason nje shprehje kundrejt nej liste vlerash. Lista e vlerave mund te jete statike ose nje liste me vlera e kthyer nga nje komande SELECT. Shembujt e meposhtem ilustrojne te dy keto raste:
Komanda e meposhtme afishon te gjithe punonjesite, mbiemri i te cileve nuk ndodhet ne listen (‘Jones’, ‘Smith’, ‘Keenan’)
SELECT firstname, lastname
FROM employees
WHERE lastname NOT IN (‘Jones’, ‘Smith’, ‘Keenan’)
Ndersa komanda e meposhtme afishon nje liste me punonjesit qe nuk kane realizuar asnje shitje
SELECT firstname, lastname
FROM employees
WHERE EmployeeId
NOT IN
(SELECT EmployeeId from Ordes)
Operatori EXISTS
Komanda e meposhtme afishon nje liste me punonjesit qe kane realizuar te pakten nje shitje
SELECT * from Employees
where
exists
(
select * from Orders where Orders.EmployeeID=Employees.EmployeeID
)
Operatoti LIKE
Operatori LIKE eshte nej operator mbi stringjet, dhe mundeson krahasimin e nje stringu(teksti) kundrejt nje maske , qe permban shkronja, numra , shenja pikesimi si edhe disa simbole te vecante si % dhe _ qe quhen wildcards.
Tabela me poshte sqaron funksionin e wildcards
Simboli % nenkupton nje numer variabel simbolesh nga 0 deri ne n.
Simboli _ nenkupton saktesisht nje simbol
psh [a-f] nenkupton nje shkronje nga a deri f
psh [^a-f] nenkupton nje shkronje cfaredo meperkashtim te intervalit a deri f
[abc] nenkupton nje simbol a ose b ose c
[^abc] nenkupton nje simbol cfaredo me perjashtim te a, b dhe c
Shembujt e meposhtem sqarojne funksionin e operatorit LIKE:
Komanda
SELECT * from employees where Firstname like ‘A%’
afishon nje liste te punonjesve emrat e te cileve fillojne me shkronjen A , ndersa komanda e meposhtme:
SELECT * from employees where homephone like ‘(206) _ _ _-_ _ _ _’
afishon nje liste te punonjesve te cilet e kane numrin e telefonit me prefix (206) , e me pas vazhdon me 3 shifra, vazhdon me simbolin - dhe me pas ka kater shifra.
Operatori IS NULL
Per te testuar nese nje kollone e caktuar ka vleren NULL duhet te perdorim operatorin IS NULL (ose IS NOT NULL kur duam te gjejme vlera jo boshe).
Nuk eshte e mundur te krahasojme per vlera NULL me shenjen e barazimit.
Homephone=NULL konsiderohet gabim , shkrimi korrekt do te ishte Homephone IS NULL
Per shembull, komanda e meposhtme:
SELECT * from Employees where Birthdate IS NULL
afishon nje liste te punonjesve per te cilet nuk e kemi te regjistruar datelindjen, ndersa komanda e meposhtme:
SELECT * from Employees where Birthdate
IS NOT NULL
afishon nje liste te punonjesve per te cilet e kemi te regjistruar datelindjen.
Te rendisim rezultatin nepermjet klauzoles ORDER BY
Klauzola Order By, mundeson te rendisim rezultatin e nje query sipas nje ose disa kollonash.
ASC - ASCENDING, percakton rendin rrites ndersa
DESC - DESCENDING, percakton rendin zbriter
Nese nuk e percaktojme SQL nenkupton qe po kerkojme ti rendisim rreshtat ne rendin rrites ASC.
Per shembull, komanda e meposhtme:
SELECT firstname, lastname
FROM Employees
ORDER BY firstname
afishon nje liste te punonjesve te renditur sipas emrit te tyre ne rendin rrites (A->Z), ndersa komanda e meposhtme:
SELECT firstname, lastname
FROM Employees
ORDER BY firstname DESC ,lastname ASC
afishon nje liste te punonjesve te renditur sipas emrit te tyre ne rendin zbrites(Z->A) dhe me pas sipas mbiemrit ne rendin rrites (A->Z)
Funksionet e Grupit
Ka disa funksione te SQL qe aplikohen mbi grupet e rreshtave. Keto funksione si psh SUM, COUNT etj, shoqerohen me klauzolen GROUP BY.
Tabela me poshte paraqet nje pershkrim te funksioneve te grupit:
AVG - Mesatarja
COUNT - Numeron vlerat
MAX - maksimumi
MIN - minimumi
SUM - shuma
STDEV - Deviacioni standart (statitike)
VAR - Varianca ( statistike )
Ne rastin e SELECT te shoqeruar me Group By, vetem kollonat qe ndodhen ne listen e GROUP BY mund te jene ne listen e kollonave te SELECT.
Rezultatet pas grupimit mund te filtrohen nepermjet HAVING BY, e cila eshte e ngjashme me WHERE, por ne dallim nga kjo e fundit aplikohet pas llogaritjes se rezultateve mbi rreshtat e grupuara.
Per shembull, komanda e meposhtme afishon numrin e shitjeve per cdo kod punonjesi:
SELECT EmployeeId,
Count(*) ‘NoOfOrdersHeDealed’
from Orders
Group by (EmployeeId)
ndersa komanda e meposhtme afishon kodin e nje porosise dhe vleren financiare te saj si total
SELECT OrderId,SUM(Quantity*UnitPrice) 'totalamount' from [Order Details]
Group by OrderID
Ndersa komanda e meposhtme afishon vetem kodet e punonjesve qe kane bere me shume se 10 shitje:
SELECT EmployeeId,
Count(*) ‘NoOfOrdersHeDealed’
from Orders
Group by (EmployeeId)
Having Count(*) >10
SHEMBUJ TE FUNDIT
--USHTRIME
--U1 Afishoni per cdo kod funsitori numrin e produkteve qe blejme nga ai furnitor
-- renditini sipas numrit te produkteve be rendin zbrites
SELECT SupplierID,COUNT(*) 'prods'
From Products
GROUP BY SupplierID
ORDER BY COUNT(*) DESC
--U2 Afishoni per cdo kod funsitori numrin e produkteve qe blejme nga ai furnitor
-- renditini sipas numrit te produkteve be rendin zbrites
-- afishoni vetem furnitoret nga ku blejme me shume se 3 produkte
SELECT SupplierID,COUNT(*) 'prods'
From Products
GROUP BY SupplierID
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC
-- U3 Afishoni sa produkte kemi per cdo kategori
SELECT CategoryID,COUNT(*) 'Prods'
FROM Products
GROUP BY CategoryID
-- U4 Afishoni sa produkte kemi per cdo kategori
-- renditni rezultatin sipas numrit te produkteve ne rendin zbrites
SELECT CategoryID,COUNT(*) 'Prods'
FROM Products
GROUP BY CategoryID
ORDER BY COUNT(*) DESC
-- U5 Afishoni sa produkte kemi per cdo kategori
-- renditni rezultatin sipas numrit te produkteve ne rendin zbrites
-- afishoni vetem kategorite me me shume se 4 produkte
SELECT CategoryID,COUNT(*) 'Prods'
FROM Products
GROUP BY CategoryID
HAVING COUNT(*)>4
ORDER BY COUNT(*) DESC
-- U6 Afishoni sa porosi kemi kryer me sejcilen nga postat (Shippers)
SELECT ShipVia 'Posta',COUNT(*) 'Porosi'
FROM Orders
GROUP BY ShipVia
-- U7 Afishoni sa porosi kemi kryer me sejcilen nga postat (Shippers)
-- renditni rezultatin sipas numrit te porosive ne rendin zbrites
SELECT ShipVia 'Posta',COUNT(*) 'Porosi'
FROM Orders
GROUP BY ShipVia
ORDER BY COUNT(*) DESC
-- U8 Afishoni sa porosi kemi kryer me sejcilen nga postat (Shippers)
-- renditni rezultatin sipas numrit te porosive ne rendin zbrites
-- afishoni vetem postat me me shume se 150 porosi
SELECT ShipVia 'Posta',COUNT(*) 'Porosi'
FROM Orders
GROUP BY ShipVia
HAVING COUNT(*)>150
ORDER BY COUNT(*) DESC
-- U8 Afishoni sa porosi kemi kryer me sejcilen nga postat (Shippers)
--gjate vitit 1998
-- renditni rezultatin sipas numrit te porosive ne rendin zbrites
-- afishoni vetem postat me me shume se 50 porosi
SELECT ShipVia 'Posta',COUNT(*) 'Porosi'
FROM Orders
WHERE OrderDate between '1998-01-01' AND '1998-12-31'
GROUP BY ShipVia
HAVING COUNT(*)>50
ORDER BY COUNT(*) DESC
-- U9 Afishoni klientet me te mire (me shume se 5 porosi) per vitin 1998
SELECT CustomerId 'Klienti',COUNT(*) 'Porosi'
FROM Orders
WHERE OrderDate between '1998-01-01' AND '1998-12-31'
GROUP BY CustomerID
HAVING COUNT(*)>5
ORDER BY COUNT(*) DESC
-- U10 Afishoni sa lloje produktesh kemi gjendje (UnitInStock>0) per sejcilen kategori
SELECT CategoryID,COUNT(*) 'PRODUKTE_STOK'
FROM Products
grouP BY CategoryID
--U11 Afishoni sa eshte vlera financiare e cdo porosie.
--Interesohemi vetem per porosite e vitit 1998.
-- Porosite te renditen sipas vleres financiare nga me e shtrenjta tek me e lira
SELECT OrderID,SUM(UnitPrice*Quantity) 'amount'
FROM [Order Details]
GROUP BY OrderId
-- sa kliente ka kompania ne cdo shtet
select Country,COUNT(*) 'Kliente'
from Customers
Group by Country
Order by Kliente DESC
-- listoni porosite vlera totale e te cilave eshte > 20
Select * from [Order Details]
---
select OrderId, SUM(UnitPrice*Quantity) 'Vlera'
from [Order Details]
group by OrderID
having SUM(UnitPrice*Quantity) > 20
Order by Vlera
-- afishoni kodin e punonjesve dhe numrin e porosive qe ka ndjekur sejcili prej tyre
select EmployeeID, COUNT(*) 'NrPorosi'
from Orders
group by EmployeeID
-- afishoni vetem kodet e punonjesve qe kane trajtuar me shume se 10 porosi
select EmployeeID
from Orders
group by EmployeeID
having COUNT(*)>100
--afishoni emrat e punonjesve qe kane bere me shume se 100 porosi
-- punonjesit e dalluar
select EmployeeID,TitleOfCourtesy+' '+FirstName+' '+LastName AS 'Punonjesi',
Title 'Pozicioni'
from Employees
where EmployeeID IN
(
select EmployeeID
From Orders
Group by EmployeeID
Having COUNT(*)>100
)
-- a
select Orders.EmployeeID, Orders.OrderID,[Order Details].Quantity,[Order Details].UnitPrice
from
Orders Inner Join [Order Details]
On Orders.OrderID=[Order Details].OrderID
select EmployeeID, sum([Order Details].Quantity*[Order Details].UnitPrice) 'vlera'
from
Orders Inner Join [Order Details]
On Orders.OrderID=[Order Details].OrderID
group by EmployeeID
order by EmployeeID
select Orders.EmployeeID, Orders.OrderID,
[Order Details].Quantity,[Order Details].UnitPrice
from Orders INNER JOIN [Order Details]
ON (Orders.OrderID=[Order Details].OrderID)
order by EmployeeID
select Orders.EmployeeID, Orders.OrderID,
SUM([Order Details].Quantity * [Order Details].UnitPrice) 'Vlera'
from Orders INNER JOIN [Order Details]
ON (Orders.OrderID=[Order Details].OrderID)
Group by Orders.EmployeeID, Orders.OrderID
order by Orders.EmployeeID
select Orders.EmployeeID,
SUM([Order Details].Quantity * [Order Details].UnitPrice) 'Vlera'
from Orders INNER JOIN [Order Details]
ON (Orders.OrderID=[Order Details].OrderID)
Group by Orders.EmployeeID
HAVING SUM([Order Details].Quantity * [Order Details].UnitPrice) > 100000
order by Vlera DESC
select Orders.EmployeeID
from Orders INNER JOIN [Order Details]
ON (Orders.OrderID=[Order Details].OrderID)
Group by Orders.EmployeeID
HAVING SUM([Order Details].Quantity * [Order Details].UnitPrice) > 100000
order by SUM([Order Details].Quantity * [Order Details].UnitPrice) DESC
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].Quantity * [Order Details].UnitPrice) > 100000
)
order by EmployeeID