We will use a simple database composed of the following tables: CUSTOMER, INVOICE, LINE, PRODUCT, and VENDOR.

This database model is shown in Figure 1. The database model in Figure 1 reflects the following business rules: - A customer may generate many invoices. Each invoice is generated by one customer. - An invoice contains one or more invoice lines. Each invoice line is associated with one invoice. - Each invoice line references one product. A product may be found in many invoice lines. (You can sell more than one hammer to more than one customer!) - A vendor may supply many products. Some vendors do not (yet?) supply products. (For example, a vendor list may include potential vendors.) - If a product is vendor-supplied, that product is supplied by only a single vendor. - Some products are not supplied by a vendor. (For example, some products may be produced inhouse or may have been bought on the open market.)

1A) Using a single command sequence, write the SQL code that will enter the customers city (CUS_CITY) = Pittsburgh for all customers whose zip code (CUS_ZIP) is 15232.

*

ALTER TABLE CUSTOMER_2

ADD (CUS_ZIP INTEGER,

CUS_RANK CHAR(20),

CUS_CITY CHAR(1)));

1B) Write the SQL code that will enter CUS_RANK of A for those customers whose balance is less than $250.

1C) Write the SQL command sequences required to: a) Create a temporary table name TEMP_1, whose structure is composed of the CUSTOMER_2. b) Copy all rows from CUSTOMER_2 that contain the customers whose balance is in the range from $250 to $500 into the TEMP_1 table.

1D) Write the SQL command that will delete the newly created TEMP_1 table from the database

INVOICE LINE CUSTOMER CUSCODE CUS FNAME CUS AREACODE CUS BALANCE INY NUMBER CUS CODE IV DATE LINE NMEER o P DESCRIPT P ONHAND P MIN VENDOR P DISCOUNT CODE V_AREACODE ORDER Figure 1- The database model CUS 10010 Ramas 0013 olowski 10015 OBrian 10018 Farriss Table name: CUSTOMER 54778-2 1 2238 PD 2 1546-Q02 3 13-02P2 1 54778-2 CUS CODE NV_DATE 10014 16-Jan-04 10011 16-Jen-04 10012 16-Jon-04 1001117 Jan-04 0018 17-Jen-04 10014 17-Jan-04 10015 17-Jen-04 10011 17-Jon-04 1006 1007 1008 1 PVC23DRT Table name: INVOICE Table name: LINE