SQL-What is a View
A View is a "Virtual Table". It is not like a simple table, but is a virtual table which contains columns and data from different tables (may be one or more tables). A View does not contain any data, it is a set of queries that are applied to one or more tables that is stored within the database as an object. After creating a view from some table(s), it used as a reference of those tables and when executed, it shows only those data which are already mentioned in the query during the creation of the View.
Views are used as security mechanisms in databases. Because it restricts the user from viewing certain column and rows. Views display only those data which are mentioned in the query, so it shows only data which is returned by the query that is defined at the time of creation of the View. The rest of the data is totally abstract from the end user.
Along with security, another advantage of Views is data abstraction because the end user is not aware of all the data in a table.
Uses
We begin with creating 3 tablesPRODUCTS, Customer & BOOKING. These are fictitious tables for our demo. ThePRODUCTS stores data for a retail shop with a flag column IsSalable based on whose value we treat the products as Salable.
CREATE TABLE PRODUCTS
(ProductID INT PRIMARY KEY CLUSTERED,
ProductDesc VARCHAR(50) NOT NULL,
ManufacturingDate DATETIME,
ExpiryDate DATETIME,
IsSalable BIT,--1 Salable/Active FOR 0 For NonSalable/Passive Product
Price MONEY NOT NULL
)
Next, we have a Customer table which stores UserID and Password details for customers.CREATE TABLE Customer
(CustID INT IDENTITY(1002,2)PRIMARY KEY CLUSTERED,
FName VARCHAR(50) NOT NULL,
LNme VARCHAR(50) NOT NULL,
UserID VARCHAR(100) NOT NULL,
Pswd NVARCHAR(100) NOT NULL DEFAULT 'password123'
)
Lastly, I have created a BOOKING table which houses all the bookings from different customers.CREATE TABLE BOOKING
( BookingID INT IDENTITY(10,2) PRIMARY KEY CLUSTERED,
ProductID INT REFERENCES dbo.Products(ProductID),
CustID INT REFERENCES dbo.Customer(CustID),
DateOfBooking DATETIME NOT NULL,
QTY INT
)
Next, insert a few records into these tables:
INSERT INTO PRODUCTS VALUES
(1,'Biscuits','2011-09-01 00:00:00.000','2012-09-01 00:00:00.000',1,20),
(2,'Butter','2010-09-01 00:00:00.000','2011-09-01 00:00:00.000',1,30),
(3,'Milk','2011-10-01 00:00:00.000','2011-11-01 00:00:00.000',1,46)
INSERT INTO Customer (FName,LNme,UserID,Pswd)
VALUES
('Sara','Verma','S.Verma@abc.com','S123'),
('Rick','Singh','G.Singh@xyz.com','G311'),
('Micky','Khera','M.Khera@mno.com','M222')
INSERT INTO BOOKING (ProductID,CustID,DateOfBooking,QTY)
VALUES
(1,1002,'2011-11-01 00:00:00.000',3),
(2,1004,GETDATE(),4),
(3,1006,'2011-10-01 00:00:00.000',2)
Our tables contents look like this. I know the tables are not completely normalized, for now please ignore them, these are simple demo tables.
SELECT * FROM Customer
CustID FName LNme UserID Pswd
--------- -------- ---------- --------------- ---------
1002 Sara Verma S.Verma@abc.com S123
1004 Rick Singh G.Singh@xyz.com G311
1006 Micky Khera M.Khera@mno.com M222
(3 row(s) affected)
Select * from PRODUCTS
ProductID ProductDesc ManufacturingDate ExpiryDate IsSalable Price
---------- ------------ ----------------------- ----------------------- --------- -------
1 Biscuits 2011-09-01 00:00:00.000 2012-09-01 00:00:00.000 1 20.00
2 Butter 2010-09-01 00:00:00.000 2011-09-01 00:00:00.000 1 30.00
3 Milk 2011-10-01 00:00:00.000 2011-11-01 00:00:00.000 1 46.00
(3 row(s) affected)
Select * from BOOKING
BookingID ProductID CustID DateOfBooking QTY
----------- ----------- ----------- ----------------------- -----------
10 1 1002 2011-11-01 00:00:00.000 3
12 2 1004 2011-10-09 17:31:31.790 4
14 3 1006 2011-10-01 00:00:00.000 2
(3 row(s) affected)
A
customer purchases/books a product and the same gets recorded into the BOOKING table now to generate the bill on his name we can uses a VIEW which would help us do away with a physical table. Instead it would enable us to generate the bill based on the information from these 3 tables itself. Let’s see how it’s possible.CREATE VIEW Bill_V
AS
SELECT C.FName
,C.LNme
,P.ProductDesc
,B.DateOfBooking
,P.Price
,B.QTY
,(B.QTY*P.Price) AS TotalAmountPayable
FROM BOOKING B
INNER JOIN PRODUCTS P
ON B.ProductID=P.ProductID
INNER JOIN Customer C
ON B.CustID=C.CustID;
Next if I,
Select * from Bill_V
FName LNme ProductDesc DateOfBooking Price QTY TotalAmountPayable
-------------------------------------------------- ------------------------------------
Sara Verma Biscuits 2011-11-01 00:00:00.000 20.00 3 60.00
Rick Singh Butter 2011-10-09 17:31:31.790 30.00 4 120.00
Micky Khera Milk 2011-10-01 00:00:00.000 46.00 2 92.00
(3 row(s) affected)
We have been able to generate the bill based on the 3 tables hence we have not only optimized the bill generation also we have saved ourselves from hosting a physical table in the database with this information.
- This is the most credible use of a VIEW; it can not only reduce apparent complexity but also prevent redundant hosting of data in the DB.Next say there is some API which enables the
Customercare executives to view thecustomerinformation details. Now exposing the Password might be risky, it’s strictly confidential info.
We create a View which can be exposed to the API:
CREATE VIEW dbo.CustomerInfo_V AS Select CustID ,FNAME AS [FIRST NAME] ,LNME AS [LAST NAME] ,UserID FROM dbo.Customer
We have a created a View which can be used by the API to fetchcustomerdetails –(Minus) the Password Column. - Views can be used to prevent sensitive information from being selected, while still allowing other important data.Views do not have a physical existence, but still they do return a set of record set as a table does, the differences is it is simply an additional layer which calls the underlying code which finally returns the record set.
No comments:
Post a Comment