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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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