DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to list product details?

Execute the following SQL Server Transact-SQL scripts in Management Studio Query Editor to list the product details for English culture markets.

-- SQL inner join - SQL coalesce - SQL left join - left outer join

-- SQL column alias - SQL money format - currency format

USE AdventureWorks;

SELECT P.[Name]  AS ProductName,

       PM.[Name] AS ProductModel,

       PC.[Name] AS ProductCategory,

       PS.[Name] AS ProductSubCategory,

       PD.DESCRIPTION,

       P.ProductNumber,

       COALESCE(P.Color,'') AS Color,

       COALESCE(P.[Size], '') AS Size,

       COALESCE(CAST(P.Weight AS varchar),'') Weight,

       '$'+convert(varchar,P.StandardCost,1) AS StandardCost,

       COALESCE(P.Style,  '') AS Style,

       COALESCE(P.Class,  '') AS Class,

       '$'+convert(varchar,P.ListPrice,1) AS ListPrice

FROM   Production.Product P

       INNER JOIN Production.ProductSubcategory PS

         ON P.ProductSubcategoryID = PS.ProductSubcategoryID

       INNER JOIN Production.ProductCategory PC

         ON PS.ProductCategoryID = PC.ProductCategoryID

       INNER JOIN Production.ProductProductPhoto PPP

         ON P.ProductID = PPP.ProductID

       INNER JOIN Production.ProductPhoto PP

         ON PPP.ProductPhotoID = PP.ProductPhotoID

       LEFT OUTER JOIN Production.ProductDescription PD

       INNER JOIN Production.ProductModelProductDescriptionCulture PCULT

         ON PD.ProductDescriptionID = PCULT.ProductDescriptionID

       INNER JOIN Production.ProductModel PM 

         ON PM.ProductModelID = PCULT.ProductModelID

         ON P.ProductModelID = PM.ProductModelID

WHERE  (PCULT.CultureID = 'en')

ORDER BY ProductName

GO

/* Partial results

 

ProductName             ProductModel            ProductCategory    ProductSubCategory

All-Purpose Bike Stand  All-Purpose Bike Stand  Accessories             Bike Stands

AWC Logo Cap            Cycling Cap             Clothing                Caps

Bike Wash - Dissolver   Bike Wash               Accessories             Cleaners

Cable Lock              Cable Lock              Accessories             Locks

Chain                   Chain                   Components              Chains

Classic Vest, L         Classic Vest            Clothing                Vests

Classic Vest, M         Classic Vest            Clothing                Vests

*/

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE