SQLUSA
FREE TRIAL  CLICK HERE TO ORDER  SEARCH
SQL Server 2008 Training Scripts
SQL Server 2005 Training Scripts
SQL Server Training Scripts
SQL 2008 GRAND SLAM
How to create VIEWs for low and high-priced products?

Execute the following Microsoft SQL Server T-SQL script in Query Analyzer or SSMS Query Editor to create views and SELECT from views:

USE Northwind;

GO

CREATE VIEW [LowPriceProducts]

AS

  SELECT ProductName,

         UnitPrice

  FROM   Products

  WHERE  UnitPrice < 1.0 / 3.0 * (SELECT AVG(UnitPrice)

                                  FROM   Products)

GO

 

SELECT   *

FROM     LowPriceProducts

ORDER BY ProductName

GO

/*

ProductName             UnitPrice

Filo Mix                7.00

Geitost                 2.50

Guaraná Fantástica      4.50

Konbu                   6.00

....

*/

 

CREATE VIEW [HighPriceProducts]

AS

  SELECT ProductName,

         UnitPrice

  FROM   Products

  WHERE  UnitPrice > 2.0 / 3.0 * (SELECT AVG(UnitPrice)

                                  FROM   Products)

GO

 

SELECT   *

FROM     HighPriceProducts

ORDER BY ProductName

GO

/*

ProductName                   UnitPrice

Alice Mutton                  39.00

Camembert Pierrot             34.00

Carnarvon Tigers              62.50

Chef Anton's Cajun Seasoning  22.00

....

*/

 

The World Leader in SQL Server 2008 Training
Order SQL 2008 GRAND SLAM Today!
The Future is just a CLICK away! Your Future!
SQLUSA.com Home Page

Copyright 2005-2010, SMI Corp. All Rights Reserved.

SQL Server 2008 is a program product of Microsoft Corporation.
SQL Server 2005 is a program product of Microsoft Corporation.
SQL Server 2000 is a program product of Microsoft Corporation.