7. Subqueries, Cases, and Views

Activity 7.01: Finding the Product Category Name Using a Subquery

Solution:

1. Enter the following query:

   USE packt_online_shop;
   SELECT  PC.ProductCategoryName
   FROM    ProductCategories PC
   WHERE   ProductCategoryID IN
   (SELECT ProductCategoryID FROM Products WHERE   ProductName = 'habanero peppers');

2. Run the query. Your output will be as follows:

Figure 7.10: Category of the habanero peppers food item

Activity 7.02: Categorizing the Shipments Using CASE Statements

Solution:

1. Enter the following query:

   USE packt_online_shop;
   SELECT OrderNumber, ShipmentDate,
   CASE
   WHEN ShipmentDate < ' 2010-12-10' THEN 'Past Shipment Date'
   WHEN ShipmentDate >= ' 2010-12-10' AND
   ShipmentDate < ' 2019-12-18' THEN
   'Recent Shipment Date'
   ELSE 'Future Shipment Date'
   END AS 'Shipment Date Category'
   FROM Orders;

2. Execute the query. You should...