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.
CREATE VIEW vEmployeesWithSales
Sales ON Employees.EmployeeID =Sales.EmployeeID
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.
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
SELECT TOP 10
Name AS ProductName,
SUM(Sales.Quantity) AS TotalQuantity
Products ON Sales.ProductID = Products.ProductID