26. January 2016
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 TechShizzGOCREATE VIEW vEmployeesWithSalesAS SELECT DISTINCT Employees.* FROM Employees JOIN Sales ON Employees.EmployeeID =Sales.EmployeeIDGO
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 vTop10ProductSalesByQuantityAS SELECT TOP
10 Name AS ProductName
) AS TotalQuantity FROM Sales JOIN Products ON Sales.ProductID
= Products.ProductID GROUP BY Name ORDER BY SUM