The problems use the tables of the Order Entry database introduced in the Problems section of Chapter 4. When formulating the problems, remember that the EmpNo foreign key in the OrderTbl table allows null values. An order does not have an associated employee if taken over the Internet.
1. Using a Type I nested query, list the customer number, name (first and last), and city of each customer who has a balance greater than $150 and placed an order in February 2013.
2. Using a Type II nested query, list the customer number, name (first and last), and city of each customer who has a balance greater than $150 and placed an order in February 2013.
3. Using two Type I nested queries, list the product number, the name and the price of products with a price greater than $150 that were ordered on January 23, 2013.
4. Using two Type I nested queries and another join style, list the product number, name and price of products with a price greater than $150 that were ordered in January 2013 by customers with balances greater than $400.
5. List the order number, order date, employee number, and employee name (first and last) of orders placed on January 23, 2013. List the order even if there is not an associated employee.
9. Using a Type II nested query, list the customer number and name of Colorado customers who have not placed orders in February 2013.
15. Repeat problem 9 using a one-sided outer join and an IS NULL condition. If the problem cannot be formulated in this manner, provide an explanation indicating the reason.
22. Using a Type I nested query, delete orders placed by customer Betty Wise in January 2013. The CASCADE DELETE action will delete related rows in the OrdLine table.
28. For Colorado customers, compute the number of unique products ordered. If a product is purchased on multiple orders, it should be counted only one time. The result should include the customer number, customer last name, and number or unique products ordered.
3. Identify the result rows in the following SELECT statement:
Access:
SELECT *
FROM Product
WHERE ProdNextShipDate = '22-Jan-2013'
OR ProdPrice <100;
7. Determine the result of the following SELECT statement:
SELECT ProdNextShipDate, ProdMfg, COUNT (*) AS NumRows
From Product
GROUP BY ProdNextShipDate, ProdMfg