Question

These are queries that use the full Red Cat Database as shown in Figure 3.1. You must use the full Red Cat tables of Customer, Sale, SaleItem, Product, Manufacturer, and Employee tables.

For each information request below, formulate a single SQL query to produce the required information. In each case, you should display only the columns requested.
1. Which customer lives in city New York? First name, last name, city, phone number.
2. Show the customer first name, last name, city and state for all in San Francisco and San Diego. Include customers from California where city begins with letter A.
3. Show the customer's first name, customer's last name, customer's city ('CustCity'), product name, list price for all products made by manufacturers in Arizona and purchased by customers in Oklahoma City or Tulsa.

Answer 1

To find the customer who lives in New York, use the 'SELECT' statement with appropriate conditions. To display the customer first name, last name, city, and state for specific cities and states, use the 'OR' operator and 'LIKE' condition.

Step-by-step explanation:

1. To find the customer who lives in New York, you can use the following SQL query:

SELECT first_name, last_name, city, phone_number FROM Customer WHERE city = 'New York';

2. To display the customer first name, last name, city, and state for those in San Francisco and San Diego, as well as customers from California where the city begins with the letter A, you can use this query:

SELECT first_name, last_name, city, state FROM Customer WHERE (city = 'San Francisco' OR city = 'San Diego') OR (state = 'California' AND city LIKE 'A%');

3. To show the customer's first name, last name, city, product name, and list price for all products made by manufacturers in Arizona and purchased by customers in Oklahoma City or Tulsa, you can use this query:

SELECT c.first_name, c.last_name, c.city AS `CustCity`, p.product_name, p.list_price FROM Customer c JOIN Sale s ON c.customer_id = s.customer_id JOIN SaleItem si ON s.sale_id = si.sale_id JOIN Product p ON si.product_id = p.product_id JOIN Manufacturer m ON p.manufacturer_id = m.manufacturer_id WHERE m.state = 'Arizona' AND (c.city = 'Oklahoma City' OR c.city = 'Tulsa');