Skip to content
Tech Shizz Logo

An Engineers Blog

  • TechShizz
  • blog

SQL: Creating Views

Posted on February 23, 2021 By rich No Comments on 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


Post navigation

❮ Previous Post: SQL: Information on SQL Server Editions and Features
Next Post: SQL: Create a Database ❯

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Generic selectors
Exact matches only
Search in title
Search in content
Post Type Selectors

Subscribe to our newsletter!

Recent Posts

  • How to implement a lightning-fast ransomware playbook
  • How to achieve defence in depth in your business
  • How to implement a SecOps team phishing response plan
  • How to block an Office 365 Sign-in correctly
  • Microsoft finally patched serious Exchange 0-day over a month old!

Recent Comments

    Archives

    • November 2022
    • July 2021
    • March 2021
    • February 2021

    Categories

    • Cyber Security
    • Uncategorized

    Meta

    • Log in
    • Entries feed
    • Comments feed
    • WordPress.org

    Copyright © 2023 .

    Theme: Oceanly News Dark by ScriptsTown