A view in SQL is a virtual table which is defined by a query. The table can be made up of various tables within a database.
This script will create a view consisting of the Employees table and the Sales table to show “Sales by employees”. It joins the data by the EmpoyeeID column on each table.
USE TechShizz
GO
CREATE VIEW vEmployeesWithSales
AS
SELECT DISTINCT
Employees.*
FROM
Employees
JOIN
Sales ON Employees.EmployeeID =Sales.EmployeeID
GO
If you want to test this view you can highlight everything under the “CREATE VIEW vEmployeesWithSales ‘ AS” statement.
Once the script has been executed, you will now see a virtual table appear in the views node in the object explorer.
Another Example
This script will create a virtual table to show the top 10 products by sales quantity. The column names are aliases (Name as ProductName). The query then adds up all of the sales quantity of on the sales table then puts it into a column called TotalQuantity on the virtual table. The remainder of the script joines the two tables with their common “ProductID” columns the groups and sortes the results.
CREATE VIEW vTop10ProductSalesByQuantity
AS
SELECT TOP 10
Name AS ProductName,
SUM(Sales.Quantity) AS TotalQuantity
FROM
Sales
JOIN
Products ON Sales.ProductID = Products.ProductID
GROUP BY
Name
ORDER BY
SUM(Sales.Quantity) DESC
GO
