MS SQL Server Interview Question:

How to Create a View with Data from Multiple Tables?

Submitted by: Administrator

Can You Create a View with Data from Multiple Tables? The answer is yes. A view can be created with a SELECT statement to join data from multiple tables.

It is a common practice to normalize data into multiple tables. Then using a view to de-normalize them into a single output.

The tutorial exercise below shows you how to create a view to normalize data from two tables SalesOrderHeader and Customer in the sample database AdventureWorksLT.

USE AdventureWorksLT;
GO

CREATE VIEW SalesOrderView AS
SELECT o.SalesOrderNumber, o.OrderDate, o.TotalDue,
c.FirstName, c.LastName, c.CompanyName
FROM SalesLT.SalesOrderHeader o, SalesLT.Customer c
WHERE o.CustomerID = c.CustomerID
GO
Submitted by: Administrator

<pre>SELECT TOP 10 SalesOrderNumber, TotalDue, CompanyName
FROM SalesOrderView;
GO
SalesOrderNumber TotalDue CompanyName
---------------- ----------- ------------------------------
SO71915 2361.6403 Aerobic Exercise Company
SO71938 98138.2131 Bulk Discount Store
SO71783 92663.5609 Eastside Department Store
SO71899 2669.3183 Coalition Bike Company
SO71898 70698.9922 Instruments and Parts Company
SO71902 81834.9826 Many Bikes Store
SO71832 39531.6085 Closest Bicycle Store
SO71776 87.0851 West Side Mart
SO71797 86222.8072 Riding Cycles
SO71895 272.6468 Futuristic Bikes</pre>
Submitted by: Administrator