Chapter 23, Problem 12RE

Explanation of Solution

Consider the following:

The below SQL statement that selects all products that have been purchased by a customer in California:

/* Display all products purchased by customer */

SELECT Product.Description

FROM Customer, Product, Invoice, LineItem

WHERE Customer.Customer_Number = Invoice.Customer_Number

AND Invoice.Invoice_Number = LineItem.Invoice_Number

AND LineItem.Product_Code = Product.Product_Code

AND Customer.State = 'CA'

Explanation:

• The above “SELECT” query that fetches all “Product.Description”  from “Customer”, “Product”, “Invoice”, and “LineItem” table where “Customer.Customer_Number” is equal to “Invoice.Customer_Number”, “Invoice.Invoice_Number” is equal to “LineItem.Invoice_Number”, “LineItem.Product_Code” is equal to “Product.Product_Code”, and “Customer.State” is equal to “CA”.
  • “Product.Description” – It refers to the “Description” column in the “Product” table.
  • “Customer”, “Product”, “Invoice”, and “LineItem” are names of the four tables from where the rows will be retrieved...