Select from two tables:  More examples

 


SELECT Products.*, Categories.CategoryName


FROM Categories
    INNER JOIN Products
    ON Categories.CategoryID=Products.CategoryID


WHERE (((Products.Discontinued)=No));

 


SELECT

 [Order Details].OrderID, [Order Details].ProductID, Products.ProductName, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount,
CCur([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice

FROM Products
    INNER JOIN [Order Details]
    ON Products.ProductID=[Order Details].ProductID

ORDER BY [Order Details].OrderID;

       Note:  The calculation is not as complex as it may seem.  It is simply unit price * quantity * discount, formatted as currency.



Select from two tables:  Exercises

 

 

 

            City, Country      (e.g.,  Montreal, Canada) 

 

         with a column header such as ShippedTo or Shipped To.  To do this, replace the City and Country columns with one calculated column (comma at the end to separate from the next column if necessary):

  

            City & ", " & Country AS ShippedTo,

           

           or      City & ", " & Country AS [Shipped To],


Note:  Be sure to find the correct names for the City and Country columns - they are different in the two tables.  (Result: 58 rows)


 

            City,  Region PostalCode                        (e.g.:  Newark, DE 19716)

 

         Can you sort on the calculated column in the SQL statement?  (Result:  152 rows)