Chapter 08 - Views

Nje VIEW eshte nje query e emeruar si nje tabele. Nje VIEW perbehet nga nje komande SELECT qe mund te marre te dhena nga nje ose disa tabela.

Te mirat e View-ve

Nje view mund te jete shume e dobishme kur ka disa perdorues me nivele te ndyshme te te drejtave ne database dhe qe kane nevoje per te drejta vetem ne disa pjese te te dhenave. Nepermjet VIEW eshte e mundur

    • te jepen te drejta leximi vetem per disa rreshta te nje tabele

    • Te jepen te drejta leximi vetem per disa kollona te nje tabele

    • Te merren te dhena nepermjet nje JOIN dhe te paraqiten ato si te ishin ne nje tabele

    • Te paraqiten informacione te grupuara

Ku ndodhen View ne Object Explorer

VIew ndodhen nen Tabelat nen Objektin VIEWS:

Si te krijojme nje View

Ka disa menyra per te krijuar nje VIEW. Komanda per krijimin e nje VIEW ne SQL eshte CREATE VIEW e ndjekur nga nje komande SELECTqe do te percaktoje strukturen e VIEW dhe te dhenat qe ajo do te permbaje. Psh

CREATE VIEW ViewName AS SELECT ...

Nje shembull:

CREATE VIEW "Alphabetical_list_of_products" AS SELECT Products.*, Categories.CategoryName FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID WHERE (((Products.Discontinued)=0))

Modifikimi i nje View

Per te modifikuar nje VIEW :

a- mund ta fshijme ate dhe ta rikrijojme me komandat (DROP VIEW emri_i_view dhe CREATE VIEW )

Shenim: Fshirja e VIEW nuk fshin te dhenat e saj, VIEW ne fakt eshte nje strukture, nje query qe afishon te dhenat te tabelave ose VIEW te tjera , nuk ka te dhena te vetat, ndaj fshirja e VIEW nuk shkakton fshirjen e te dhenave te saj

ose

b- Mund ta modifikojme ate nepermjt komande ALTER VIEW

ALTER VIEW "Alphabetical list of products" AS SELECT Products.*, Categories.CategoryName FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID WHERE (((Products.Discontinued)=0))

Perdorimi i nje View

Per te marre te dhena nga nje VIEW perdorimin komanda si te donim te merrnim te dhena nga nje tabele. Psh:

SELECT TOP 1000 * FROM [AdventureWorks2008].[Sales].[vIndividualCustomer]

Rezultati i komandes se mesiperme do te ishte:

Opsioni schemabinding

create view shitjet with schemabinding

as

select Nr,OrderId,OrderDate,Firstname,Lastname,CompanyName,Amount from

(

SELECT ROW_NUMBER()

OVER (

ORDER BY SUM([Order Details].UnitPrice* [Order Details].Quantity)

DESC) as Nr ,

Orders.OrderID, Orders.OrderDate, Employees.FirstName, Employees.LastName,

Customers.CompanyName,

SUM([Order Details].UnitPrice* [Order Details].Quantity) as Amount

FROM dbo.Customers INNER JOIN

dbo.Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN

dbo.Employees ON Orders.EmployeeID = Employees.EmployeeID INNER JOIN

dbo.[Order Details] ON Orders.OrderID = [Order Details].OrderID

GROUP BY Orders.OrderID, Orders.OrderDate, Employees.FirstName, Employees.LastName,

Customers.CompanyName

) t

create view andrewsales with schemabinding

as

select Nr,OrderId,OrderDate,Firstname,Lastname,

CompanyName,Amount from

shitjet

where FirstName='Andrew'

Krijimi i trigerave INSTEAD OF ne View

Krijojme nje view

create view v1

as

select e.EmployeeID,e.FirstName,e.LastName,o.OrderID,o.OrderDate,o.CustomerID

from Employees e join orders o on e.EmployeeID=o.EmployeeID;

Krijojme nje trigger instead of ne view

create trigger v1_insert on v1

instead of insert

as

begin

select * from inserted

end

Mund te bejme insert ne view dhe te kapim ne trigger te dhenat nga tabela inserted

Kodi me poshte

insert into v1(FirstName,LastName,OrderId,OrderDate,CustomerId)

values ('a','b',100,getdate(),'ALFKI');

do te afishonte

Nese do te tentojme te krijojme nje after trigger :

create trigger v1_delete on v1

after delete

as

begin

select * from deleted

end

do te merrnim nje mesazh gabimi:

Msg 8197, Level 16, State 6, Procedure v1_delete, Line 1

The object 'v1' does not exist or is invalid for this operation.

Ushtrime

Ushtrim 1

Te ndertojme nje view SalesUSA qe mban porosite e realizuara ne USA

Zgjidhje

create view SalesUSA

as

SELECT * from Orders

where ShipCountry='USA'

Ushtrim 2

Te ndertojme nje view me emrin shitjet1 qe ruan shitjet e kompanise si ne tabelen me poshte

Zgjidhje

CREATE VIEW shitjet1

as

SELECT Orders.OrderID AS Kodi, Employees.FirstName + N' ' + Employees.LastName AS Punonjesi, Customers.CompanyName AS Klienti, Orders.OrderDate AS Data,

Shippers.CompanyName AS Transpotuesi

FROM Orders INNER JOIN

Employees ON Orders.EmployeeID = Employees.EmployeeID INNER JOIN

Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN

Shippers ON Orders.ShipVia = Shippers.ShipperID

Ushtrim 3

Te ndertojme nje view me emrin shitjet2 qe ruan numrin e porosive te cdo punonjesi si ne formatin me poshte:

Zgjidhje

create view shitjet2

as

SELECT Employees.EmployeeID AS Kodi, Employees.FirstName AS Emri,

Employees.LastName AS Mbiemri, count(Orders.OrderID) as NrPorosi,

Employees.Title AS Pozicioni

FROM Employees INNER JOIN

Orders ON Employees.EmployeeID = Orders.EmployeeID

GROUP BY Employees.EmployeeID,FirstName,LastName,Title

Ushtrim 4

Te ndertojme nje view me emrin shitjet4 qe ruan numrin e porosive te cdo klienti si ne formatin me poshte:

Zgjidhje

create view shitjet4

as

SELECT Customers.CustomerID AS Kodi,

Customers.CompanyName AS Klienti,

count(Orders.OrderID) as NrPorosi,

Customers.Country AS Shteti

FROM Customers INNER JOIN

Orders ON Customers.CustomerID = Orders.CustomerID

GROUP BY Customers.CustomerID,CompanyName,Country

Ushtrim 5

Te ndertojme nje view me emrin shitjet5 qe ruan xhiron cdo punonjesi si ne formatin me poshte:

Zgjidhje

CREATE VIEW shitjet5

as

SELECT Employees.EmployeeID AS Kodi,

Employees.FirstName AS Emri,

Employees.LastName AS Mbiemri,

SUM([Order Details].UnitPrice * [Order Details].Quantity) AS Xhiro,

Employees.Title AS Pozicioni

FROM Employees INNER JOIN

Orders ON Employees.EmployeeID = Orders.EmployeeID INNER JOIN

[Order Details] ON Orders.OrderID = [Order Details].OrderID

GROUP BY Employees.EmployeeID,FirstName,LastName,Title

Ushtrim 6

Te ndertojme nje view me emrin shitjet6 qe ruan xhiron financiare te cdo klienti si ne formatin me poshte:

Zgjidhje

CREATE VIEW shitjet6

as

SELECT

Customers.CustomerID AS Kodi,

Customers.CompanyName AS Klienti,

SUM([Order Details].UnitPrice * [Order Details].Quantity) AS Xhiro,

Customers.Country AS Shteti

FROM Customers INNER JOIN

Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN

[Order Details] ON Orders.OrderID = [Order Details].OrderID

Group by Customers.CustomerID,CompanyName,Country

Ushtrim 7

Te ndertojme nje view me emrin shitjet7 qe ruan shitjet e kompanise si ne tabelen me poshte

Zgjidhje

create view shitjet7

as

SELECT

Orders.OrderID AS Kodi,

Employees.FirstName + N' ' + Employees.LastName AS Punonjesi,

Customers.CompanyName AS Klienti,

Orders.OrderDate AS Data,

SUM([Order Details].UnitPrice * [Order Details].Quantity) AS VleraFinanciare

FROM Employees INNER JOIN

Orders ON Employees.EmployeeID = Orders.EmployeeID INNER JOIN

Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN

[Order Details] ON Orders.OrderID = [Order Details].OrderID

GROUP BY

Orders.OrderID,

Employees.FirstName + N' ' + Employees.LastName,

Customers.CompanyName,

Orders.OrderDate