Views

Definition

A view is a list of columns or a series of records retrieved from one or more existing tables, or as a combination of one or more views and one or more tables. Based on this, before creating a view, you must first decide where its columns and records would come from. Obviously the easiest view is one whose columns and records come from one table.

Practical Learning: Introducing Views

• Start Microsoft SQL Server and connect to the server.
  Make sure you have the RealEstate2 database created in the previous lesson. If you didn't create them, do it now

Fundamentals of Creating Views

Visually Creating a View

To create a view, you can use the Object Explorer, a query window, the Command Prompt, or Windows PowerShell. Before starting the view, you would have to specify the table(s) that would be involved. To create a view from the Object Explorer, you can expand the database, right-click Views and click New View... This would open the Add Table dialog box:

The basic functionality is exactly the same as we reviewed in Lesson 13:

• To specify the table that would be used as the source, you can click it in the list box of the Tables property page
• If you would be using another existing view, from the Views property page, you can click the name of the desired view
• If a function would be used to generate the records, you can locate it in the Functions property page. After selecting the source object, you can either double-click it or you can click it once and click Add. In in the previous lesson, we saw that you could add more than one existing table. the same way, you can add more than one view or functions
• After selecting the source(s), you can click Close on the Add Table dialog box

After selecting the objects, as we saw in the previous lesson, they would display in the window. The View Designer toolbar would be added:

If the Query Designer is not visible, on the main menu, you can click View -> Toolbars -> Query Designer. Alternatively, you can right-click any toolbar or the main menu and click Query Designer.

The window from which you create a view displays four sections. This means that, besides the three sections we reviewed in Lesson 13, there is a new section: The Result. Each section is represented on the Query Designer toolbar by a button: the Show Diagram Pane button , the Show Criteria Pane button , the Show SQL Pane button , and the Show Result Pane button .

We reviewed the top three sections already (in Lesson 13). The Results section displays the result produced by the SQL statement when it is executed

If you don't want a particular section or you want to hide some sections, you can right-click anywhere in the table, position the mouse on Pane and click the name of the section. To hide any section, you can right-click anywhere in the window, position the mouse on Show Panes and click one of the selections:

When a section is displaying, its menu option is surrounded.

• As seen in the previous lesson, if you are using more than one table and they are not (yet) related, you can drag a column from one table and drop it on another table to create a JOIN between them
• As we saw in previous lessons, to select a column, you can click its check box in the top list. This would display it in the first empty box under the Column column and would add its name to the SELECT statement. Alternatively, you can click an empty box in the Column column to reveal its combo box, then click the arrow of the combo box and select the desired column from the list
• After selecting the column, its check box would be checked in the top section of the window, its name would be displayed in the Column column, and it would be added to the SELECT statement. If you know the name of the column you want to add, you can manually type it in the SELECT statement.

To structure of a view can be considered complete when the SELECT statement is as complete as possible. At any time, to test the results of a view, you can run it. To do this, you can click the Execute SQL button . This would cause the bottom section of the view to display the results of the query. Here is an example:

As stated already, one of the reasons for creating a view is to be able to use it over and over again. To achieve this, the view must be saved. Like most objects in SQL Server, a view must have a name and it is saved in its own file. To save a view from the view window, you can click the Save button on the toolbar. You can also attempt to close the window. You would then be prompted to save it. When saving a view, you should give it a name that follows the rules and suggestions of SQL.

Practical Learning: Visually Creating a View

1. In the Object Explorer, expand the Databases and the RealEstate2 nodes
2. Right-click Views and click New View
3. In the Add Table dialog box, double-click PropertyTypes, Properties, and Conditions
4. Click Close
5. From the PropertyTypes table, drag PropertyTypeID and drop it on the PropertyTypeID field of the Properties table
6. From the Conditions table, drag ConditionID and drop it on the ConditionID field of the Properties table
7. On the tables, select the following fields: PropertyNumber, PropertyType, Condition, City, ZIPCode, Bedrooms, Bathrooms, FinishedBasement, Stories, YearBuilt, and MarketValue
8. In the Criteria section, click PropertyType and press Tab 6 times. In its Filter field, type Single Family
9. Press Tab and, in its Or field, type Townhouse

The Name of a View

In our lessons, here are the rules we will use to name our views:

• A name will start with a letter. Examples are n, act, or Second
• After the first letter, the name will have combinations of underscores, letters, and digits. Examples are n24, act_52_t
• A name will not include special characters such as !, @, #, $, %, ^, &, or *
• A name will not have spaces
• If the name is a combination of words, each word will start in uppercase. Examples are DateHired, RealSport, or DriversLicenseNumber

After saving a view, it becomes part of the Views node of its database: a node would be created for it and its name would appear in the Views node of its database.

Practical Learning: Saving a View

1. To save the view, on the Standard toolbar, click the Save button
2. Set the Name to Homes and click OK
   
   
3. Close the view window
4. In the Object Explorer, under RealEstate2, right-click Views and click New View
5. In the Add Table dialog box, double-click PropertyTypes, Properties, and Conditions
6. Click Close
7. From the PropertyTypes table, drag PropertyTypeID and drop it on the PropertyTypeID field of the Properties table
8. From the Conditions table, drag ConditionID and drop it on the ConditionID field of the Properties table
9. On the tables, select the following fields: PropertyNumber, PropertyType, Condition, City, ZIPCode, Bedrooms, Bathrooms, and MarketValue
10. In the Criteria section, click PropertyType. Press Tab 3 times and press the Space bar to remove the check mark of its Output field
11. Press Tab 3 times. In its Filter field, type  Condominiums and press Enter
12. On the main menu, click File -> Save
13. Set the Name to Condos and click OK
14. Close the view window

With Transact-SQL

To programmatically create a view, you use the following SQL syntax:

CREATE VIEW ViewName
AS
SELECT Statement

Microsoft SQL Server can generate skeleton code of a view for you. To use it, first create an empty query window. Display the Template Explorer. In the Template Explorer, expand the View node. From the View node, drag Create View and drop it in the query window.

The creation of a view starts with the CREATE VIEW expression followed by a name. The name of a view follows the rules and suggestions we reviewed above. After the name of the view, use the AS keyword to indicate that you are ready to define the view.

Because a view is primarily a SQL statement, it is defined using a SELECT statement, using the same rules we studied for data analysis. Here is an example of a view:

CREATE VIEW dbo.ListOfMen
AS
SELECT dbo.Genders.Gender,
       dbo.Persons.FirstName, dbo.Persons.LastName
FROM   dbo.Genders INNER JOIN dbo.Persons
ON     dbo.Genders.GenderID = dbo.Persons.GenderID
WHERE  (dbo.Genders.Gender = N'Male');
GO

After creating the SQL statement that defines the view, you must execute the statement. If using a query window, you can do this by pressing F5. Once the statement is executed, its name is automatically added to the Views node of its database even if you don't save its code.

Practical Learning: Creating a View

1. Make sure you have the Yugo National Bank database created in the Lesson13. If you didn't create it, do it now.
   In the Object Explorer, right-click YugoNationalBank and click New Query
2. To create a new view, type the following code
    

   Use YugoNationalBank; GO CREATE VIEW PayrollPreparation AS SELECT dbo.Employees.EmployeeNumber, dbo.Employees.LastName + ', N' + dbo.Employees.FirstName AS [Full Name], dbo.Employees.HourlySalary, dbo.TimeSheets.TimeSheetCode, dbo.TimeSheets.Week1Monday, dbo.TimeSheets.Week1Tuesday, dbo.TimeSheets.Week1Wednesday, dbo.TimeSheets.Week1Thursday, dbo.TimeSheets.Week1Friday, dbo.TimeSheets.Week1Saturday, dbo.TimeSheets.Week1Sunday, dbo.TimeSheets.Week2Monday, dbo.TimeSheets.Week2Tuesday, dbo.TimeSheets.Week2Wednesday, dbo.TimeSheets.Week2Thursday, dbo.TimeSheets.Week2Friday, dbo.TimeSheets.Week2Saturday, dbo.TimeSheets.Week2Sunday FROM dbo.Employees INNER JOIN dbo.TimeSheets ON dbo.Employees.EmployeeNumber = dbo.TimeSheets.EmplNumber; GO

3. To execute the code, press F5
4. Delete the content of the whole view window

Opening or Executing a View

Opening a View

As stated already, a view is a technique of selecting records to view or use over an over again. After a view has been created, you can open it. You have two main options.

• To see the structure of a view, such as the table(s) on which it is based and the relationships, if any that compose it, in the Object Explorer, right-click the view and click Design
• To see the SQL code that makes up a view, in the Object Explorer, right-click the view and click Edit

Executing a View

Executing a view consists of seeing its results. To do this, you have various options. To view the results of a view:

• Open an empty query window associated with the database that contains the view. In the query window, write a SELECT statement using the same formulas and rules we saw for tables. Here is an example:
  
  
• From the Object Explorer, expand the database and its Views node. Right-click the name of the view and click Open View

Practical Learning: Executing a View

1. In the Object Explorer, under RealEste2, expand Views if necessary. Right-click Condos and click Open View
   
   
2. Close the view window
3. In the Object Explorer, right-click YugoNationalBank and click New Query
4. To execute the view, in the empty view window, type the following
    

   USE YugoNationalBank; GO SELECT PayrollPreparation.* FROM PayrollPreparation; GO

5. To execute the view, press F5
6. Delete the content of the window

View Maintenance

The Properties of a View

In Transact-SQL, a view is considered an object. As such, it can be viewed, changed, or deleted. Like any regular object, a view has its own characteristics. To see them, you can right-click the view and click Properties. A View Properties dialog box would come up. It can give you information such as the name of the database the view belongs to, the date the view was created, etc.

Practical Learning: Accessing the Properties of a View

1. To create another view, type the following:
    

   -- ============================================= -- Database: YugoNationalBank -- View: TimeSheet -- ============================================= USE YugoNationalBank GO IF object_id(N'TimeSheet', N'V') IS NOT NULL DROP VIEW dbo.TimeSheet GO CREATE VIEW dbo.TimeSheet AS SELECT EmplNumber, StartDate, Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday, Week1Friday, Week1Saturday, Week1Sunday, Week2Monday, Week2Tuesday, Week2Wednesday, Week2Thursday, Week2Friday, Week2Saturday, Week2Sunday, Notes FROM TimeSheets;

2. Press F5 to execute the statement
3. Delete the content of the window
4. In the Object Explorer, under YugoNationalBank, right-click Views and click Refresh
5. Expand Views if necessary. Right-click TimeSheet and click Properties
6. Press Esc to close the Properties dialog box

Modifying a View

After a view has been created, either by you or someone else, you may find out that it has an unnecessary column, it needs a missing column, it includes unnecessary records, or some records are missing. Fortunately, you can change the structure or the code of a view. This is referred to as altering a view. You have two main options:

• To visually change a view, in the Object Explorer, right-click the view and click Design. From the view window, you can add or remove the columns. You can also change any options in one of the sections of the window. After modifying the view, save it and close it
• To change the code of a view, in the Object Explorer, right-click it and view Edit. After editing the view's code, you can save it
• From the Object Explorer, right-click the view, position the mouse on Script View As -> ALTER To -> New Query Editor Window

The basic formula to programmatically modify a view is:

ALTER VIEW ViewName
AS
SELECT Statement

You start the alteration with the ALTER VIEW expression followed by the name of the view. After the name of the view, use the AS keyword to specify that you are ready to show the change. After the AS keyword, you can then define the view as you see fit. For example, you can create a SELECT statement that includes a modification of the existing code or a completely new statement.

In the view we created to show a list of men of a table, we included a column for the gender. This column is useless or redundant because we already know that the list includes only men. Here is an example of altering the view to remove (or rather omit) the Gender column of the Persons table:

ALTER VIEW dbo.ListOfMen
AS
SELECT dbo.Persons.FirstName, dbo.Persons.LastName
FROM   dbo.Genders INNER JOIN dbo.Persons
ON     dbo.Genders.GenderID = dbo.Persons.GenderID
WHERE  (dbo.Genders.Gender = N'Male');

Deleting a View

Instead of modifying a view, if you find it altogether useless, you can remove it from its database. You have various options. To delete a view:

• In the Object Explorer, in a database, right-click the name of the view and click Delete. You would be given the opportunity to confirm your intention or to change your mind
• In the Object Explorer, right-click the view, position the mouse on Script View As -> DROP To New Query Editor Window
• Open an empty query window associated with the database that has the undesired view. From the Template Explorer, in the View node, drag Drop View and drop it in the query window

The formula to programmatically delete a view is:

DROP VIEW ViewName

On the right side of the DROP VIEW expression, enter the name of the undesired view and execute the statement. You will not be warned before the interpreter deletes the view.

Using a View

Data Entry With a View

As seen so far, a view is a selected list of records from a table. As you may suspect, the easiest view is probably one created from one table. Imagine you have a table of employees and you want to create a view that lists only their names. You may create a view as follows:

CREATE VIEW dbo.EmployeesNames
AS
SELECT FirstName,
       LastName,
       LastName + ', N' + FirstName AS FullName FROM Persons;
GO

On such a view that is based on one table, you can perform data entry, using the view, rather than the table. To do this, you follow the same rules we reviewed in Lesson 9. Here is an example:

INSERT INTO dbo.EmployeesNames(FirstName, LastName)
VALUES(N'Peter', N'Justice');

If you perform data entry using a view, the data you provide would be entered on the base table; this means that the table would be updated automatically. Based on this feature, you can create a view purposely intended to update a table so that, in the view, you would include only the columns that need to be updated.

Practical Learning: Performing Data Entry Using a View

1. To create a function we will use, enter the following code:
    

   --================================================== -- Database: YugoNationalBank -- Function: CreateTimeSheetCode -- Purpose: This function takes an employee number -- and the start date of a time sheet. -- Then it creates a unique number -- in the format 0000000000000 -- The first 5 digits represent the -- employee number, -- the second 4 digits represent the year, -- the 2 digits represent the month, -- that last 2 digits represent the day --================================================== USE YugoNationalBank GO IF OBJECT_ID (N'dbo.CreateTimeSheetCode') IS NOT NULL DROP FUNCTION dbo.CreateTimeSheetCode GO CREATE FUNCTION dbo.CreateTimeSheetCode(@EmplNbr nvarchar(6), @dteStart datetime2) RETURNS nvarchar(15) AS BEGIN DECLARE @strMonth AS nvarchar(20); DECLARE @strDay AS nvarchar(20); DECLARE @iMonth AS int; DECLARE @iDay AS int; DECLARE @strTimeSheetCode nvarchar(20); SET @iMonth = CONVERT(nvarchar(20), MONTH(@dteStart)); SET @iDay = CONVERT(nvarchar(20), DAY(@dteStart)); IF @iMonth < 10 SET @strMonth = CONVERT(nvarchar(20), YEAR(@dteStart)) + '0' + CONVERT(nvarchar(20), @iMonth); ELSE SET @strMonth = CONVERT(nvarchar(20), YEAR(@dteStart)) + CONVERT(nvarchar(20), @iMonth); IF @iDay < 10 SET @strDay = @strMonth + '0' + CONVERT(nvarchar(20), @iDay); ELSE SET @strDay = @strMonth + CONVERT(nvarchar(2), @iDay); SET @strTimeSheetCode = @EmplNbr + @strDay; RETURN @strTimeSheetCode; END GO

2. Press F5 to execute
3. To perform data entry using a view, enter the following code:
    

   USE YugoNationalBank GO -- The following code performs data entry using a view INSERT INTO dbo.TimeSheet VALUES(N'46288', N'1/1/2007', 0.00, 8.50, 9.50, 8.50, 9.00, 0.00, 0.00, 10.00, 9.50, 8.50, 10.50, 9.00, 0.00, 0.00, N''); GO INSERT INTO dbo.TimeSheet VALUES(N'66286', N'1/1/2007', 0.00, 8.50, 6.50, 5.50, 6.50, 0.00, 0.00, 4.00, 6.00, 6.50, 6.00, 5.50, 0.00, 0.00, N''); GO INSERT INTO dbo.TimeSheet VALUES(N'92493', N'1/1/2007', 0.00, 8.00, 9.00, 8.50, 9.50, 0.00, 0.00, 5.50, 6.50, 4.50, 6.00, 4.00, 0.00, 0.00, N''); GO INSERT INTO dbo.TimeSheet VALUES(N'27199', N'1/15/2007', 6.00, 8.50, 0.00, 4.00, 6.50, 0.00, 0.00, 4.00, 0.00, 6.00, 4.00, 0.00, 0.00, 0.00, N''); GO INSERT INTO dbo.TimeSheet VALUES(N'39538', N'1/15/2007', 8.00, 8.00, 6.00, 8.50, 6.00, 0.00, 0.00, 9.50, 10.50, 8.00, 8.00, 8.50, 0.00, 0.00, 'There were a few missing times in the time sheet. ' + 'They have been recorded.'); GO INSERT INTO dbo.TimeSheet VALUES(N'40550', N'1/15/2007', 8.50, 8.00, 0.00, 8.50, 0.00, 0.00, 0.00, 6.00, 6.50, 6.50, 0.00, 4.00, 0.00, 0.00, N''); GO INSERT INTO dbo.TimeSheet VALUES(N'66286', N'1/29/2007', 8.00, 6.50, 9.50, 8.00, 7.50, 0.00, 0.00, 10.50, 9.50, 8.50, 8.00, 10.00, 0.00, 0.00, N''); GO INSERT INTO dbo.TimeSheet VALUES(N'90026', N'2/12/2007', 8.50, 6.50, 8.00, 8.00, 9.50, 0.00, 0.00, 9.50, 8.00, 8.50, 8.00, 8.00, 0.00, 0.00, N''); GO INSERT INTO dbo.TimeSheet VALUES(N'92493', N'2/12/2007', 4.00, 6.50, 5.50, 8.00, 6.50, 0.00, 0.00, 8.00, 8.00, 8.00, 6.00, 8.00, 0.00, 0.00, N''); GO -- The following code updates a table using a function UPDATE dbo.TimeSheets SET TimeSheetCode = dbo.CreateTimeSheetCode(N'46288', N'1/1/2007') WHERE (EmplNumber = N'46288') AND (StartDate = N'1/1/2007'); GO UPDATE dbo.TimeSheets SET TimeSheetCode = dbo.CreateTimeSheetCode(N'66286', N'1/1/2007') WHERE (EmplNumber = N'66286') AND (StartDate = N'1/1/2007'); GO UPDATE dbo.TimeSheets SET TimeSheetCode = dbo.CreateTimeSheetCode(N'92493', N'1/1/2007') WHERE (EmplNumber = N'92493') AND (StartDate = N'1/1/2007'); GO UPDATE dbo.TimeSheets SET TimeSheetCode = dbo.CreateTimeSheetCode(N'27199', N'1/15/2007') WHERE (EmplNumber = N'27199') AND (StartDate = N'1/15/2007'); GO UPDATE dbo.TimeSheets SET TimeSheetCode = dbo.CreateTimeSheetCode(N'39538', N'1/15/2007') WHERE (EmplNumber = N'39538') AND (StartDate = N'1/15/2007'); GO UPDATE dbo.TimeSheets SET TimeSheetCode = dbo.CreateTimeSheetCode(N'40550', N'1/15/2007') WHERE (EmplNumber = N'40550') AND (StartDate = N'1/15/2007'); GO UPDATE dbo.TimeSheets SET TimeSheetCode = dbo.CreateTimeSheetCode(N'66286', N'1/29/2007') WHERE (EmplNumber = N'66286') AND (StartDate = N'1/29/2007'); GO UPDATE dbo.TimeSheets SET TimeSheetCode = dbo.CreateTimeSheetCode(N'90026', N'2/12/2007') WHERE (EmplNumber = N'90026') AND (StartDate = N'2/12/2007'); GO UPDATE dbo.TimeSheets SET TimeSheetCode = dbo.CreateTimeSheetCode(N'92493', N'2/12/2007') WHERE (EmplNumber = N'92493') AND (StartDate = N'2/12/2007'); GO

4. Press F5 to execute
5. Delete the content of the window

A View With Alias Names

It is important to know that a view is more of a table type than any other object. This means that a view is not a function but it can use a function. The word argument here only means that some values can be passed to a view but these values can be specified only when creating the view. They are not real arguments.

When structuring a view, you can create placeholders for columns and pass them in the parentheses of the view. This would be done as follows:

CREATE VIEW CarIdentifier([Tag #], Manufacturer, [Type of Car], Available)
. . .

If you use this technique, the names passed in the parentheses of the view are the captions that would be displayed in place of the columns of the view. This technique allows you to specify the strings of your choice for the columns. If you want a column header to display the actual name of the column, write it the same. Otherwise, you can use any string you want for the column. If the name is in one word, you can just type it. If the name includes various words, include them between an opening square bracket "[" and a closing square bracket "]".

After listing the necessary strings as the captions of columns, in your SELECT statement of the view, you must use the exact same number of columns as the number of arguments of the view. In fact, each column of your SELECT statement should correspond to an argument of the same order.

Here is an example:

CREATE VIEW dbo.MenAndWomen([First Name], [Last Name], Gender)
AS
SELECT dbo.Persons.FirstName,
       dbo.Persons.LastName,
       dbo.Genders.Gender
FROM   dbo.Genders INNER JOIN dbo.Persons
ON     dbo.Genders.GenderID = dbo.Persons.GenderID;
GO

Because, as we stated already, a view is not a function and the values passed to the view are not real arguments, when executing the view, don't specify the names of arguments. Simply create a SELECT statement and specify the name of the view as the source. Here is an example:

Views and Conditional Statements

Besides its querying characteristics that allow it to perform data analysis, probably the most important feature of a query is its ability to be as complex as possible by handling conditional statements. This makes it possible to use a view instead of a table in operations and expressions that would complicate the code or structure of a table. When creating a view, in its SELECT statement, you can perform column selections, order them, and set criteria to exclude some records.

Here is an example:

Views and Functions

To create more complex or advanced views, you can involve functions. As always, probably the easiest functions to use are those built-in. 

If there is no built-in function that performs the operation you want, you can create your own. Here is an example:

USE People;
GO
CREATE FUNCTION dbo.GetFullName
(
 @FName nvarchar(20),
 @LName nvarchar(20)
)
RETURNS nvarchar(41)
AS
BEGIN
 RETURN @LName + ', N' + @FName;
END
GO

 Once you have a function you want to use, you can call it in the body of your view as you judge it necessary. Here is an example:

CREATE VIEW dbo.MyPeople
AS
SELECT dbo.GetFullName(FirstName, LastName) AS [Full Name],
       dbo.Genders.Gender
FROM   Genders INNER JOIN dbo.Persons
ON     dbo.Genders.GenderID = dbo.Persons.GenderID;

This would produce:

Practical Learning: Using a Function in a View

1. In the Object Explorer, under YugoNationalBank, expand Views if necessary.
   Right-click PayrollPreparation, position the mouse on Script View As -> ALTER To -> New Query Editor Window
2. Change the code as follows:
    

   USE [YugoNationalBank] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [dbo].[PayrollPreparation] AS SELECT dbo.Employees.EmployeeNumber, dbo.Employees.LastName + ', N' + dbo.Employees.FirstName AS [Full Name], dbo.Employees.HourlySalary, dbo.TimeSheets.TimeSheetCode, dbo.TimeSheets.Week1Monday, dbo.TimeSheets.Week1Tuesday, dbo.TimeSheets.Week1Wednesday, dbo.TimeSheets.Week1Thursday, dbo.TimeSheets.Week1Friday, dbo.TimeSheets.Week1Saturday, dbo.TimeSheets.Week1Sunday, dbo.TimeSheets.Week2Monday, dbo.TimeSheets.Week2Tuesday, dbo.TimeSheets.Week2Wednesday, dbo.TimeSheets.Week2Thursday, dbo.TimeSheets.Week2Friday, dbo.TimeSheets.Week2Saturday, dbo.TimeSheets.Week2Sunday, CAST(dbo.TimeSheets.Week1Monday AS SmallMoney) + CAST(dbo.TimeSheets.Week1Tuesday AS SmallMoney) + CAST(dbo.TimeSheets.Week1Wednesday AS SmallMoney) + CAST(dbo.TimeSheets.Week1Thursday AS SmallMoney) + CAST(dbo.TimeSheets.Week1Friday AS SmallMoney) + CAST(dbo.TimeSheets.Week1Saturday AS SmallMoney) + CAST(dbo.TimeSheets.Week1Sunday AS SmallMoney) AS [Total Week1], CAST(dbo.TimeSheets.Week2Monday AS SmallMoney) + CAST(dbo.TimeSheets.Week2Tuesday AS SmallMoney) + CAST(dbo.TimeSheets.Week2Wednesday AS SmallMoney) + CAST(dbo.TimeSheets.Week2Thursday AS SmallMoney) + CAST(dbo.TimeSheets.Week2Friday AS SmallMoney) + CAST(dbo.TimeSheets.Week2Saturday AS SmallMoney) + CAST(dbo.TimeSheets.Week2Sunday AS SmallMoney) AS [Total Week2], CAST(dbo.TimeSheets.Week1Monday AS SmallMoney) + CAST(dbo.TimeSheets.Week1Tuesday AS SmallMoney) + CAST(dbo.TimeSheets.Week1Wednesday AS SmallMoney) + CAST(dbo.TimeSheets.Week1Thursday AS SmallMoney) + CAST(dbo.TimeSheets.Week1Friday AS SmallMoney) + CAST(dbo.TimeSheets.Week1Saturday AS SmallMoney) + CAST(dbo.TimeSheets.Week1Sunday AS SmallMoney) + CAST(dbo.TimeSheets.Week2Monday AS SmallMoney) + CAST(dbo.TimeSheets.Week2Tuesday AS SmallMoney) + CAST(dbo.TimeSheets.Week2Wednesday AS SmallMoney) + CAST(dbo.TimeSheets.Week2Thursday AS SmallMoney) + CAST(dbo.TimeSheets.Week2Friday AS SmallMoney) + CAST(dbo.TimeSheets.Week2Saturday AS SmallMoney) + CAST(dbo.TimeSheets.Week2Sunday AS SmallMoney) AS [Total Time Sheet] FROM dbo.Employees INNER JOIN dbo.TimeSheets ON dbo.Employees.EmployeeNumber = dbo.TimeSheets.EmplNumber;

3. To update the query, on the main menu, click Query -> Execute
4. Delete the content of the window
5. To see the result of the query, type the following:
    

   Use YugoNationalBank; GO USE YugoNationalBank; GO SELECT PayrollPreparation.* FROM PayrollPreparation; GO

6. Delete the content of the window
   
   

Summary Views

 

Introduction

Consider the following Videos table created in a VideoCollection database:

-- =============================================
-- Database: VideoCollection
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'VideoCollection'
)
DROP DATABASE VideoCollection
GO

CREATE DATABASE VideoCollection
GO

USE VideoCollection;
GO

CREATE TABLE Videos
(
 VideoID int identity(1, 1) not null,
 Title nvarchar(50) not null,
 Director nvarchar(50),
 VideoLength int,
 Rating nchar(10),
 YearReleased int
);
GO

INSERT INTO Videos(Title, Director, VideoLength,
     Rating, YearReleased)
VALUES(N'Two for the Money', N'D.J. Caruso', 123, N'R', 2008),
      (N'Her Alibi', N'Bruce Beresford', 94, N'PG-13', 1998),
      (N'Memoirs of a Geisha', N'Rob Marshall', 145, N'PG-13', 2006),
      (N'Wall Street', N'Oliver Stone', 126, N'R', 2000);
      
GO
INSERT INTO Videos(Title, Director, VideoLength, Rating)
VALUES(N'Distinguished Gentleman (The)', N'Jonathan Lynn', 112, N'R');
GO

INSERT INTO Videos(Title, Director, YearReleased, VideoLength)
VALUES(N'The Silence of the Lambs', N'Jonathan Demme', 1991, 118);
GO

INSERT INTO Videos(Title, Director, YearReleased, VideoLength)
VALUES(N'A Few Good Men', N'Rob Reiner', 1992, N'138');
GO

GO
INSERT INTO Videos(Title, Director, VideoLength,
     Rating, YearReleased)
VALUES(N'Michael Jackson Live in Bucharest',
       N'Andy Morahan', 122, N'N/R', 1992);
GO

INSERT INTO Videos(Title, Director, Rating, VideoLength)
VALUES(N'The Lady Killers', N'Joel Coen & Ethan Coen', N'R', N'104');
GO

INSERT INTO Videos(Title, Director) VALUES(N'Outfoxed', N'Robert Greenwald');
GO

INSERT INTO Videos(Title, Director, VideoLength,
     Rating, YearReleased)
VALUES(N'Sneakers', N'Phil Alden Robinson', 126, N'PG-13', 1992),
      (N'Soldier', N'Paul Anderson', 99, N'R', 1998),
      (N'War of the Roses (The)', N'Dany de Vito', 116, N'R', 2001),
      (N'Last Castle (The)', N'Rod Lurie', 133, N'R', 2001);
GO

INSERT INTO Videos(Title, Director, VideoLength)
VALUES(N'Ghosts of Mississippi', N'Rob Reiner', 130);
GO

There are various types of statistics you may want to get from this table. You may want to know the number of videos in the table. You may want to know the number of videos that are rated R, or the number of videos rated PG-13. You may want to know the average length of the videos. To assist you with getting these statistics, you can create a summary query.

A view is referred to as summary if it provides one or various analytic statistics about the records.

Creating a Summary View

Before creating a summary view, in the Object Explorer, expand the database on which you will work. Right-click Views and click New View... On the Add Table dialog box, select the table(s) (or view(s)) and close it. To start a summary query:

• Right-click somewhere in the middle window and click Add Group By
• On the View Designer toolbar, click the Add Group By button 
• On the main menu, click View Designer, and click Add Group By

This gets the view ready for summary.

The formula to programmatically create a summary view is:

SELECT What
FROM WhatObject(s)
GROUP BY Column(s)

The new expression in this formula is GROUP BY. This indicates that you want to summarize a group of values from one or more columns. Of course, there are rules to follow.

Although you can create a summary view with all the fields or any field(s) of a view, the purpose of the view is to summarize data. For a good summary view, you should select a column where the records hold categories of data. This means that the records in the resulting list have to be grouped by categories. To support this, the SQL provides the GROUP BY expression. It means that where the records display, they would be grouped by their categories. For example, if you want to get the number of videos by rating, you would select the column that holds that information: Rating. You can select other columns also. Here is an example:

When the results come up, they would be grouped by their categories:

As stated already, the purpose of a summary view is to provide some statistics. Therefore, it is normal that you be interested only in the column(s) that hold(s) the desired statistics and avoid the columns that are irrelevant. As a result, if you select (only) the one column that holds the information you want, in the resulting list, each of its categories would display only once:

Summarizing the Values

To get the types of statistics you want, in the Criteria section, add the same column one more time and give it a caption in the Alias section. Then, click the Group By box that corresponds to the duplicate column:

In reality, a summary view uses an aggregate function to display its result:

• Count: The database engine uses the Count() function to count the number of occurrences of the category in the column and produces the total. This function also counts NULL values. The syntax of the Count() function is:
   

  int COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

  This function takes one argument. To get the count of occurrences of a value, in the Criteria section, select COUNT(*). Here is an example:
  
  
  
  Then execute the statement:
  
  
  
  The Count() function returns an int value. If you are working on a large number of records, you can call the Count_Big() function. Its syntax is:

  bigint COUNT_BIG ( { [ ALL | DISTINCT ] expression } | * )

• Consider the following table named:
   

  -- ============================================= -- Database: DepartmentStore -- ============================================= USE master GO -- Drop the database if it already exists IF EXISTS (SELECT name FROM sys.databases WHERE name = N'VideoCollection' ) DROP DATABASE DepartmentStore GO CREATE DATABASE DepartmentStore GO USE DepartmentStore; GO -- ============================================= -- Database: DepartmentStore -- Table: Customers -- Description: This table holds some information -- about the customers who hold a store -- credit card -- ============================================= CREATE TABLE Customers ( CustomerID int identity(1, 1) not null, AccountNumber nchar(10) not null, FullName nvarchar(50) not null ); GO -- ============================================= -- Database: DepartmentStore -- Table: CreditCardPayments -- Description: This table holds the records of payments -- of the customers who hold a store -- credit card -- ============================================= CREATE TABLE CreditCardPayments ( PaymentID int identity(1, 1) not null, CustomerAccount nchar(10) not null, Amount money not null ); GO INSERT INTO Customers(AccountNumber, FullName) VALUES(N'924-074', N'Daniel Caruso'), (N'248-006', N'Annette Greenwald'), (N'570-816', N'Robert Stonewall'), (N'824-692', N'John Stons'); GO INSERT INTO CreditCardPayments(CustomerAccount, Amount) VALUES(N'924-074', 15), (N'248-006', 25), (N'924-074', 50), (N'570-816', 30), (N'248-006', 20), (N'570-816', 120), (N'924-074', 95), (N'924-074', 115), (N'570-816', 15), (N'248-006', 100), (N'824-692', 75), (N'924-074', 50), (N'824-692', 40), (N'924-074', 45), (N'248-006', 125); GO

  
  If the column holds numeric values:
  • Sum: The Sum() function is used to sum up the values in the category. The syntax of the Sum() function is:
     

    Number SUM ( [ ALL | DISTINCT ] expression )

    Here is an example:
    
    
  • Avg: The sum of value in a category would be divided by the number of occurrences in that category to get the average. The syntax of the Avg() function is:
     

    Number AVG ( [ ALL | DISTINCT ] expression )

    Here is an example:
    
    
  • Min: The lowest value of the category would be produced from the Min() function. The syntax of this function is:
    

    DependsOnType MIN ( [ ALL | DISTINCT ] expression )

    Here is an example:
    
    
  • Max: The highest value of the category would be produced using the Max()function. The syntax of this function is:
     

    DependsOnType MAX ( [ ALL | DISTINCT ] expression )

    Here is an example:
    
    
  • StdDev: The StdDev() function is used to calculate the standard deviation of all numeric values of a group. If there is no value or the same value in the considered group, this function returns NULL. This means that there should be at least two different values in the group. The syntax of the StdDev() function is:
     

    float STDEV ( [ ALL | DISTINCT ] expression )

    Here is an example:
    
    
    Notice that the N/R rating produces NULL because only one video uses that rating
  • Var: The Var() function calculates the statistical variance of all numeric values of a group. If there is no value or the same value in the considered group, this function returns NULL. The syntax of the Var() function is:
     

    float VAR ( [ ALL | DISTINCT ] expression )

    Here is an example:
    
    
    The statistical variance of the values in the category would be calculated

• Expression: As its name indicates, the Expression option allows you to write your own expression that will be applied on the column. Here is an example:
  
  
• Where: Consider the following summary view that calls the COUNT(*) function:
  
  
  
  Notice that it includes videos rated NULL, which is not a real rating. Imagine you want to include only videos that have a true rating. To assist you with setting a condition, you can use a Where option. To do this, in the Criteria section, add the column on which the summary should be applied and select Where. Then, in the equivalent Filter box, type the condition, and execute the statement. Here is an example:

Having a Criterion

When we mentioned a Where condition in our summary views, we saw that we had to add a duplicate column to apply it. In some cases, this is not necessary. Consider the following summary of payments for each account:

Imagine you want to see only the accounts whose payments exceed 250.00. To support this, you can add a clause named HAVING to the statement. The formula to use is:

The formula to programmatically create a summary view is:

SELECT What
FROM WhatObject(s)
GROUP BY Column(s)
HAVING Condition

The new operator in this formula is HAVING. It allows you to specify the criterion by which the SELECT statement should produce its results. For example, show only the sums that are higher than 250, you would write:

SELECT   CustomerAccount AS [Account #], SUM(Amount) AS [Total Payments]
FROM     CreditCardPayments
GROUP BY CustomerAccount
HAVING   SUM(Amount) > 250

To visually specify a HAVING criterion, in the Filter corresponding to the column, type the conditional expression, and execute the statement. Here is an example: