Embedded Files
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
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
Ku ndodhen View ne Object Explorer
VIew ndodhen nen Tabelat nen Objektin VIEWS:
Si te krijojme nje View
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 shembukk:
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
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
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:
Ushtrime
Ushtrime
Ushtrim 1
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
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
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
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
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
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
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
Page updated
Report abuse