02 SQL Language Intro

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’

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

Shembull

1 -Te afishojme punonjesit qe jane gjini femrerore ose kane titull shkencor

select * from employees

where TitleOfCourtesy

IN ('Mrs.','Ms.','Dr.')

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

Ushtrim

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'

)

Ushtrim

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

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

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

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)

c- 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

USHTRIM

Afishoni listen e klienteve te kompanise.

a- Do te afishoni kollonat e meposhtme:

Shenim - Emrat e kollonave kane hapesire boshe midis fjaleve

zgjidhje

b- Interesohemi veten per klientet qe kemi ne USA

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

)

Ushtrim

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.

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)

Ushtrim

Te afishojme te renditura porosite sipas ShipCountry (rrites) dhe me pas sipas dates se porosies ne rendin zbrites.

select OrderID,EmployeeId,

CustomerID,OrderDate,ShipCountry

from Orders

order by ShipCountry ASC, OrderDate DESC

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

SELECT * FROM Employees

where EmployeeId IN

(

Select distinct EmployeeID from orders

where OrderID IN

(

select distinct OrderID

from [Order Details]

where UnitPrice>250

)


)

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

)