Hi I need help with this mysql question: Display the last name, first name, and
ID: 3914938 • Letter: H
Question
Hi I need help with this mysql question:
Display the last name, first name, and email address of the customers who made the
purchase with order IDs 1, 2, and 3. Use a subquery. */
Display the last name, first name, and email address of customers who have purchased
an item that was supplied by a vendor with a company name that begins with the letter H.
Use a subquery. */
For each product that has more than two items sold within all sales transactions,
retrieve the product id, product name, and product price. Use a subquery. */
Explanation / Answer
If you have any doubts, please give me comment...
1)
SELECT last_name, first_name, email_address
FROM customers
WHERE customer_id IN(
SELECT customer_id FROM orders WHERE order_id=1) AND customer_id IN(
SELECT customer_id FROM orders WHERE order_id=2) AND customer_id IN(
SELECT customer_id FROM orders WHERE order_id=3);
(or)
SELECT last_name, first_name, email_address
FROM customer
WHERE customer_id IN(
SELECT customer_id
FROM orders
WHERE order_id IN(1, 2, 3)
);
2)
SELECT last_name, first_name, email_address
FROM customer
WHERE customer_id IN(
SELECT customer_id
FROM orders
WHERE order_id IN(
SELECT order_id
FROM orderitems
WHERE product_id IN(
SELECT product_id
FROM product
WHERE vendor_id IN(
SELECT VENDOR_id
FROM vendor
WHERE company_name LIKE 'H%'
)
)
)
);
3)
SELECT product_id, product_name, list_price
FROM product
WHERE product_id IN(
SELECT product_id
FROM orderitems
GROUP BY product_id
HAVING COUNT(*)>2
);