Subject: Database Principles

Using CLI of MySQL..1. Create tables A-E found in Figure 4.1 from the CLI of MySQL. 2. Insert the records shown for the tables A-E of figure 4.1 from the CLI of MySQL. 3. Create 5 SQL queries that will demonstrate that you have accomplished steps 1 and 2. 4. What query would find all salespeople whose name has the second letter a? 5. What query would find all customer names headquartered in New York with a customer number less than 2000?

(o) SALESPERSON table SPNAME Baker Adams Dickens SPNUM COMMPERCTYEARHIRE 1995 2001 1998 2001 OFFNUM 1284 1253 1209 1227 15 204 (b) CUSTOMER table CUSTNAME HQCITY CUSTNUM 0121 0839 0933 1047 1525 1700 1826 2198 2267 SPNUM Main St. Hardware Jane's Stores ABC Home Stores Acme Hardware Store Fred's Tool Stores XYZ Stores City Hardware Western Hardware Central Stores 137 186 137 137 361 361 137 New York Chicago Los Angeles Los Angeles Atlanta Washington New York New York New York (c) CUSTOMER EMPLOYEE table EMPNAME Smith Garcia CUSTNUMEMPNUM 0121 0121 0933 0933 0933 1525 2198 2198 2198 2198 27498 30441 25270 30441 48285 33779 27470 30441 33779 35268 Co-Owner Co-Owner VP Sales Sales Manager President Sales Manager President VP Sales VP Personnel Senior Accountant Morton Baker Smith Jones Garcia FIGURE 4.1 The General Hardware Company relational database aplan (Continues)