instruction

Instructions: Write the SQL code to perform the tasks requested in each problem.

The problems use the tables of the Order Entry database introduced in the problems section of Chapter 4. When formulating the problem, 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 2010.

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 2010.

Problem:

3) Using two type I nested queries, list the products number, the name, and the price of products with a price greater than $150 that were ordered on January 23, 2010

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 2010 by customers with balance greater than $400.

5) List the order number, order date, employee number, and employee name, (first and last) of orders placed on January 23, 2010. List the order even if there is not an associated employee.

6) Using a type II nested query, list the customer number and name of Colorado customers who have not placed orders in February 2010.

7) 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.

8) Using a type I nested query, delete orders placed by customer betty wise in January 2010. The CASCADE DELETE action will delete related rows in the OrdLine table.

9) For Colorado customer, 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 of unique products ordered.

Null Value Problems

The following problems are based on the products and Employee tables of the Order Entry database. The tables are repeated below for your convenience. The prodNextShipDate column contains the next expected shipment date for the product. If the value is null, a new shipment has not been arranged. A shipment may not be schedule for a variety of reasons, such as the large quantity on hand or unavailability of the product from the manufacturer. In the Employee table, the commission rate has not been assigned. A null value for SupEmpNo indicates that the employee has no supervisor.

Product

ProdNo	ProdName	ProdMfg	ProdQOH	ProdPrice	ProdNextShipDate
P0036566	17 inch color Monitor	ColorMeg,Inc	12	$169.00	2/20/2010
P0036577		ColorMeg,Inc	10	$319.00	2/20/2010
P1114590		Connex	5	$699.00	1/22/2010
P1412138		Ethlite	100	$12.00
P1445671		Intersafe	33	$14.99
P1556678		Connex	8	$99.00	1/22/2010
P3455443		Connex	24	$38.00	1/22/2010
P4200344		UV Components	16	$199.99	1/29/2010
P6677900		Connex	44	$25.69
P9995676		Cybercx	12	$89.00	2/1/2010

Employee

EmpNo	EmpFirstName	EmpLastName	EmpPhone	EmpEMail	SupEmpNo	EmpCommRate
E1329594	Landi	Santos	(303)789-1234	[email protected]	E8843211	0.02
E8544399	Joe	Jenikins	(303)221-9875	[email protected]	E8843211	0.02
E8843211	Amy	Tang	(303)556-4321	[email protected]	E98884325	0.04
E9345771	Colin	White	(303)221-4453	[email protected]	E9884325	0.04
E9884325	Thomas	Johnson	(303)556-9987	[email protected]		0.05
E9954302	Mary	Hill	(303)5569871	[email protected]	E8843211	0.02
E9973110	Theresa	Beck	(720)320-2234	[email protected]	E9884325

1) Identify the result rows in the following SELECT statement:

Access:

SELECT *

FROM product

Where ProdNextShipDate = # 1/22/2010#

OR ProdPrice <100

Oracle:

SELETE *

FROM product

WHERE ProdNextShipDate =’22-Jan-2010’

OR prodprice <100;

2) Determine the result of the following SELECT statement:

Select prodNextShipDate, prodMfg, COUNT (*) As NumRows

FROM Product

GROUP BY ProdMfg, ProdNextShipDate, ProdMfg