Creating a View

The formula to programmatically create a view is:

CREATE VIEW ViewName
AS
SELECT Statement

Here is an example:

USE Exercise;
GO
CREATE SCHEMA Personnel;
GO
CREATE TABLE Personnel.Employees(EmplNbr nchar(10), FirstName nvarchar(20),
	LastName nvarchar(20), Salary money, FullTime bit);
GO
INSERT INTO Personnel.Employees
VALUES(N'29730', N'Philippe', N'Addy', 20.05, 1),
      (N'28084', N'Joan', N'Shepherd', 12.72, 0),
      (N'79272', N'Joshua', N'Anderson', 18.26, 0),
      (N'22803', N'Gregory', N'Swanson', 15.95, 0),
      (N'83084', N'Josephine', N'Anderson', 20.02, 1);
GO

CREATE VIEW Personnel.StaffMembers
AS
SELECT FirstName, LastName, Salary
FROM Personnel.Employees;
GO

Here is an example that includes a condition:

CREATE VIEW Personnel.GoodSalaries
AS
SELECT FirstName, LastName, Salary
FROM Personnel.Employees
WHERE Salary >= 16.00;
GO

Here is an example of a view that uses two tables:

CREATE VIEW People.ListOfMen
AS
SELECT People.Genders.Gender,
       People.Persons.FirstName, People.Persons.LastName
FROM   People.Genders INNER JOIN People.Persons
       ON People.Genders.GenderID = People.Persons.GenderID;
GO

Here is an example of a view that uses two tables:

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

Here is an example of a view with alias names:

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

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:

CREATE FUNCTION Registration.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 Registration.StaffMembers
AS
SELECT Registration.GetFullName(FirstName, LastName) AS [Full Name]
FROM   Registration.Teachers;
GO

SELECT * FROM Registration.StaffMembers;
GO

This would produce: