1 of 1 Chapter 7 How to code subqueries Exercises Write a SELECT statement that

Question

1 of 1 Chapter 7 How to code subqueries Exercises Write a SELECT statement that returns the same result set as this SELECT statement, but don't use a join. Instead, use a subquery in a WHERE clause that uses the IN keyword SELECT DISTINCT category name FROM categories c JOIN products p 1. ON c.category id p.category id ORDER BY category name Write a SELECT statement that answers this question: Which products have a list price that's greater than the average list price for all products? Return the 2. t product name and list i price columns for each product. Sort the results by the list price column in descending sequence. 3. Write a SELECT statement that returns the category name column from the Categories table. Return one row for each category that has never been assigned to any product in the Products table To do that, use a subquery introduced with the NOT EXISTS operator. Write a SELECT statement that returns three columns: email address, order id and the order lotal for cach customer. To do this, you can group the result set by the email address and order id columns. In addition, you must calculate the order total from the columns in the Order Items table. Write a second SELECT statement that uses the first SELECT statement in its FROM clause. The main query should return two columns: the customer's email address and the largest order for that customer. To do this, you can group the result set by the email address. Write a SELECT statement that returns the name and discount percent of cach product that has a unique discount percent. In other words, don't include products that have the same discount percent as another product Sort the results by the product name column. Use a correlated subquery to return one row per customer, representing the customer's oldest order (the one with the earliest date). Each row should include these three columns: email address, order id, and order date. S. 6.

Explanation / Answer

1. select distinct category_name from categories where category_id IN (select category_id from products) order by category_name:

In this first subquery returns category ids then this id's will be matched with categories table id's if those are matched then corresponding name will be printed.

2. select product_name,list_price from products group by product_name having list_price > AVG(list_price) order by list_price;

In this individual list_price is compared with the avg list price calculated on whole list_prices in the table those who are greater than average price will be printed

3. select category_name from categories where category_id NOT EXISTS (select category_id from product );

In this first from sub query category id will be returned from products those are compared with category table id . Those which are not equal to the product table id's in category table those corresponding name will be printed.

4. select c.email_address, o.order_id, ((ot.item_price-discount_amount)*quantity) as Order_total from Customers c,Order o,Order_items ot where c.customer_id=o.customer_id and o.order_id=ot.order_id group by email_address,order_id;

select email_address,max((ot.item_price-discount_amount)*quantity) from (select c.email_address, o.order_id, ((ot.item_price-discount_amount)*quantity) as Order_total from Customers c,Order o,Order_items ot where c.customer_id=o.customer_id and o.order_id=ot.order_id group by email_address,order_id) group by email_address;

5. select product_name,distinct discount_amount from products,order_items where products.product_id=order_items.product_id order by product_name;

6. select c.email_address,o.order_id,o.order_date from Customer c,Order o where c.customer_id=o.customer_id and o.orderdate = (select order_date from Order order by order_date asc limit 1) group by c.email_address;

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.