Chapter 6: How to code subqueries Exercises 1. Write a SELECT statement that ret
ID: 3790375 • Letter: C
Question
Chapter 6: How to code subqueries Exercises
1. 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 ON c.category_id = p.category_id ORDER BY category_name
2. 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 product_name and list_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.
4. Write a SELECT statement that returns three columns: email_address, order_id, and the order total for each 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.
5. Write a SELECT statement that returns the name and discount percent of each 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.
6. 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.
Chapter 7 How to insert, update, and delete data Exercises To test whether a table has been modified correctly as you do these exercises, you can write and run an appropriate SELECT statement.
1. Write an INSERT statement that adds this row to the Categories table: category_id: 5 category_name: Brass Code the INSERT statement as part of a script so Oracle commits the change.
2. Write an UPDATE statement that modifies the row you just added to the Categories table. This statement should change the product_name column to “Woodwinds”, and it should use the category_id column to identify the row.
3. Write a DELETE statement that deletes the row you added to the Categories table in exercise 1. This statement should use the category_id column to identify the row.
Explanation / Answer
1. 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 ON c.category_id = p.category_id ORDER BY category_name
SELECT DISTINCT category_name FROM categories c WHERE category_id IN (select category_id from products p) ORDER BY category_name
2. 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 product_name and list_price columns for each product. Sort the results by the list_price column in descending sequence.
SELECT product_name,list_price from products p where list_price > (select avg(list_price) from products) ORDER BY list_price DESC
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.
SELECT DISTINCT category_name FROM categories c WHERE NOT EXISTS (select category_id from products p WHERE c.category_id=p.category_id)
4. Write a SELECT statement that returns three columns: email_address, order_id, and the order total for each 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.
select email_address,max(order_total) as largest_order
from (select email_address,order_id,sum(order) as order_total from order_items group by email_address,order_id)
group by email_address
5. Write a SELECT statement that returns the name and discount percent of each 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.
select Product_name, discount_percent from Products p
where not exists (select ip.product_name from products ip where ip.discount_percent=p.discount_percent and ip.product_name<>p.product_name)
order by product_name;
6. 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.
select email_address,order_id,order_date from order_items s where
exists (select ins.email_address from order_items ins where ins.email_address=s.email_address
group by ins.email_address having min(ins.order_date)=s.order_date)
Chapter 7 How to insert, update, and delete data Exercises To test whether a table has been modified correctly as you do these exercises, you can write and run an appropriate SELECT statement.
1. Write an INSERT statement that adds this row to the Categories table: category_id: 5 category_name: Brass Code the INSERT statement as part of a script so Oracle commits the change.
INSERT INTO categories (category_id,category_name) values (5,'Brass Code');
2. Write an UPDATE statement that modifies the row you just added to the Categories table. This statement should change the product_name column to “Woodwinds”, and it should use the category_id column to identify the row.
update products set product_name='Woodwinds'
where category_id=5
3. Write a DELETE statement that deletes the row you added to the Categories table in exercise 1. This statement should use the category_id column to identify the row.
DELETE FROM categories where category_id=5;