Chapter 01 - Retrieving data using SELECT statement



Komanda SELECT


Sintaksa e pergjithshme




Column and Table Alias


SELECT EmployeeID 'Kodi' ,
FirstName [Emri] ,
LastName [Mbiemri],
TitleOfCourtesy AS Titulli ,
Title AS 'Pozicioni',
BirthDate AS [Datelindja],
ReportsTo AS Shefi
FROM employees

ky perdoret vetem ne klauzolen order by. Nuk mund te perdoret ne klauzolen where:

Krijim i alias per tabelen

Duke perdorur fjalen kyce as

SELECT EmployeeID 'Kodi' ,
FirstName [Emri] ,
LastName [Mbiemri],
TitleOfCourtesy AS Titulli ,
Title AS 'Pozicioni',
BirthDate AS [Datelindja],
ReportsTo AS Shefi
FROM employees as punonjesit
order by Emri ASC
Pa e perdorur

SELECT EmployeeID 'Kodi' ,
FirstName [Emri] ,
LastName [Mbiemri],
TitleOfCourtesy AS Titulli ,
Title AS 'Pozicioni',
BirthDate AS [Datelindja],
ReportsTo AS Shefi
FROM employees punonjesit
order by Emri ASC

A vlen emri i tabeles se vjeter? Para krijimit te alias?

Komanda:

SELECT EmployeeID 'Kodi' ,
FirstName [Emri] ,
LastName [Mbiemri],
TitleOfCourtesy AS Titulli ,
Title AS 'Pozicioni',
BirthDate AS [Datelindja],
ReportsTo AS Shefi
FROM employees as punonjesit
where Employees.EmployeeID=1
order by Emri ASC
jep gabim:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Employees.EmployeeID" could not be bound.


Ndersa komanda:

SELECT EmployeeID 'Kodi' ,
FirstName [Emri] ,
LastName [Mbiemri],
TitleOfCourtesy AS Titulli ,
Title AS 'Pozicioni',
BirthDate AS [Datelindja],
ReportsTo AS Shefi
FROM employees as punonjesit
where punonjesit.EmployeeID=1
order by Emri ASC

eshte e sakte pasi tabela tani quhet punonjesit.



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 >= '1994-1-1' AND hiredate <='1995-1-1'


Kjo komande mund te shkruhet ne nje menyre me te pembledhur duke perdorur operatorin BETWEEN


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’


Shembull

Te afishojme listen e punonjesve qe nuk kane shitur ne ShipCountry='Denmark'


SELECT * from Employees
where
not exists
(
select * from Orders
where Orders.EmployeeID=Employees.EmployeeID
and Orders.ShipCountry='Denmark'
)




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.


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’)



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
)



Operatori IN dhe EXISTS

select EmployeeID,FirstName,LastName,TitleOfCourtesy,Title
from Employees
where EmployeeID NOT IN
(
SELECT  DISTINCT EmployeeID FROM Orders
where ShipCountry='SPAIN'
)
select EmployeeID,FirstName,LastName,TitleOfCourtesy,Title
from Employees
where Not  Exists (
SELECT  * FROM Orders
where 
ShipCountry='SPAIN' 
and 
Orders.EmployeeID=Employees.EmployeeID
)
select *
from Customers
where CustomerID NOT IN(
SELECT  DISTINCT CustomerID FROM Orders
where 
OrderDate>'1996-1-1' 
)
select *
from Customers
where Not  Exists (
SELECT  * FROM Orders
where 
OrderDate>'1996-1-1' 
and 
Orders.CustomerID=Customers.CustomerID
)






Klauzola Order By



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)


Ushtrime


Lista e punonjesve sipas gjinise

Te afishojme punonjesit qe jane gjini femrerore ose kane titull shkencor


select * from employees
where TitleOfCourtesy
IN ('Mrs.','Ms.','Dr.')


Lista e punonjesve qe nuk kane realizuar asnje shitje


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 Orders)



Lista e punonjesve qe kane realizuar shitje ne Danimarke

Te afishojme listen e punonjesve qe kane shitur ne ShipCountry='Denmark'



Zgjidhje

SELECT *
FROM employees
WHERE EmployeeId
IN
(
SELECT EmployeeId
from Orders
where ShipCountry='Denmark'
)




Alias-et e kollonave

Afishoni nje liste me porosite e realizuara ne vitin 1996. Te afishohen kollonat e meposhtme

 Kodi     Data Punonjesi Klienti Shteti Qyteti
 ... ... ... ... .... ..


Zgjidhje


select OrderId as Kodi,
Orderdate as Data,
Employeeid as Punonjesi,
CustomerId as Klienti,
ShipCountry as Shteti,
ShipCity as Qyteti
from Orders
where (OrderDate between '1996-1-1' and '1996-12-31')




Shprehje llogjike te perbera ne klauzolen where

Afishoni nje liste me porosite e realizuara nga punonjesi me kodin 1 ne vitin 1996.
Te afishohen kollonat e meposhtme.

 Kodi     Data Punonjesi Klienti Shteti Qyteti
 ... ... ... ... .... ..


Zgjidhje

select OrderId as Kodi,
Orderdate as Data,
Employeeid as Punonjesi,
CustomerId as Klienti,
ShipCountry as Shteti,
ShipCity as Qyteti
from Orders
where (OrderDate between '1996-1-1' and '1996-12-31')
and (EmployeeID=1)

Rezultati te jete i renditur alfabetikisht ne rendin zbrites sipas Qytetit

select OrderId as Kodi,
Orderdate as Data,
Employeeid as Punonjesi,
CustomerId as Klienti,
ShipCountry as Shteti,
ShipCity as Qyteti
from Orders
where (OrderDate between '1996-1-1' and '1996-12-31')
and (EmployeeID=1)
order by Qyteti DESC

Alias-et e kollonave

Afishoni listen e klienteve te kompanise.
a- Do te afishoni kollonat e meposhtme:

 Kodi     Kompania Person Kontakti Telefon Pozicioni i punes Shteti
 ... ... ... ... .... ..


Shenim - Emrat e kollonave kane hapesire boshe midis fjaleve


zgjidhje


b- Interesohemi veten per klientet qe kemi ne USA




Lista e punonjesve qe shiten produkte te shtrenjta (mbi 250 euro)


Me operatorin IN


SELECT * FROM Employees
where EmployeeId IN
(
Select distinct EmployeeID from orders
where OrderID IN
(
select distinct OrderID 
from [Order Details]
where UnitPrice>250
)
)


Me operatorin EXISTS

Zgjidhja me operatorin exists


        select * from employees
        where exists
                (
                select * from orders
                where (Employees.EmployeeID=orders.EmployeeID) AND
                      (
                        exists
                            (
                            select * from [Order Details] od
                            where od.OrderID=Orders.OrderID
                            and od.UnitPrice>250
                            )
                     )
                )


Lista e klienteve qe kane blere produkte me te shtrenjta se 250 euro

Te afishojme listen e klienteve qe kane blere produkte me cmim me te larte se 250 euro

Me operatorin IN


select * from customers
where CustomerID IN
    (
    select CustomerID from orders
    where OrderId IN
        (
        select OrderID
        from [Order Details] od
        where UnitPrice>250
        )
    )



Me operatorin EXISTS


select * from customers
where exists
    (
    select * from orders
    where ( Orders.CustomerID=Customers.CustomerID )
        AND
        (
            exists
            (
            select *
            from [Order Details] od
            where (UnitPrice>250) and
                    (od.OrderID=orders.OrderID)
            )
        )
    )




Lista e produkteve qe ka shitur nje punonjes


Afishoni listen e produkteve qe ka shitur punonjesi me kodin 1


Me operatorin IN


select * from products
where productid IN
    (
    select productid from [Order Details]
    where orderid IN
        (
        select orderId from orders
        where EmployeeID=1
        )
    )




Me operatorin EXISTS


select * from products
where exists
        (
            select *
            from [Order Details]
            where
                (
                [Order Details].ProductID=products.ProductID
                )
                and
                (
                exists
                    (
                    select * from orders
                    where ([Order Details].OrderID=orders.OrderID)
                    and (EmployeeID=1)
                    )
                )
        )





Lista e produkteve qe kane shitur zonjat dhe zonjushat

Afishoni listen e produkteve qe jane shitur nga punonjesit qe e kane titullin  e kortezise Ms. ose Mrs.



Me operatorin IN


select * from products
where productid IN
    (
    select productid from [Order Details]
    where orderid IN
        (
        select orderId from orders
        where EmployeeID IN
            (
            select employeeID from Employees
            where TitleOfCourtesy IN ('Ms.','Mrs.')
            )
        )
    )



Me operatorin EXISTS