Class Sixteen; Text (SUBQUERIES AND CASE STATEMENT )

Subqueries are a powerful tool that allow you to use the results of one query as part of another query. Subqueries are sometimes called “nested queries” because they’re nested within another query. In the example we just looked at, the subquery is nested within the main query.

1). SELECT  [Stock Item Key], [Supplier Key],  [Ordered Quantity]

FROM [Fact].[Purchase]

WHERE [Stock Item Key] IN (SELECT [Stock Item Key] FROM [Fact].[Order]  WHERE [Unit Price]< 150)

2). SELECT [Customer Key],[Bill To Customer] ,[Primary Contact]

 FROM [Dimension].[Customer]

 WHERE [Customer Key] IN (SELECT [Customer Key] FROM [Fact].[Sale]

 WHERE Profit =800 )

3). SELECT CustomerID, CustomerName,Phone

FROM [dbo].[Customers]

WHERE CustomerID IN( SELECT CustomerID FROM [dbo].[Orders]

WHERE OrderTotal <500)

 CASE STATEMENT                         

The CASE statement is SQL’s way of handling if/then logic. The CASE statement is followed by at least one pair of WHEN and THEN statements—SQL’s equivalent of IF/THEN in Excel. Because of this pairing, you might be tempted to call this SQL CASE WHEN , but CASE is the accepted term.

EXAMPLE

1). SELECT [City Key],Description,[Unit Price],

 CASE

       WHEN [Unit Price] > 200 THEN ‘GOOD’

               WHEN [Unit Price] Between 18 and 35 THEN ‘FAIR’

               ELSE ‘POOR’

               END

 FROM [Fact].[Sale]

 WHERE [Unit Price] IS NOT NULL

2). SELECT [City Key], Description,[Unit Price],Quantity, Profit,

 CASE

      WHEN Profit >= 800 THEN ‘Better sales’

              WHEN Profit >=500 THEN ‘Good sales’

              ELSE ‘Fair sals’

              END

 FROM [Fact].[Sale]

 JOIN [Dimension].[Customer]

 ON [Dimension].[Customer].[Customer Key]=[Fact].[Sale].[Customer Key]