SQL: Creating Views

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

Pingbacks and trackbacks (1)+

Add comment