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


 Kodi    Punonjesi Klienti Data Transportuesi
 10249 Amanda Jones Alfreds Futterkiste .... DHL

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:


 Kodi Emri Mbiemri NrPorosi Pozicioni
     


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:


 Kodi Klienti NrPorosiShteti
    

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:


 Kodi Emri Mbiemri Xhiroja Pozicioni
     


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:


 Kodi Klienti XhiroShteti
    


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


 Kodi    Punonjesi Klienti Data Vlera
 10249 Amanda Jones Alfreds Futterkiste .... 12332.00

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