CUSTOMER CUSTOMER STREET CITY STATE ZIP BALANCE CREDIT REP NUM LIMIT NUM Fillmor
ID: 3917333 • Letter: C
Question
CUSTOMER CUSTOMER STREET CITY STATE ZIP BALANCE CREDIT REP NUM LIMIT NUM Fillmore FL 33336 $6 550.0O $7,500.00 20 Al's Appliance 2837 and Sport reenway 148 Brookings 3827 Devon Groe FL 33321 $431.5 S10,000,00 35 356 Ferjuson's 382 Wildwood Northfield FL 33146 $5,785.00 $7,500.00 65 1828 Raven Crystal FL33503 $5,285.25 $5,000.00 35 3829 Central Growe FL 33321 $3,412.00 $10,000.00 65 838 Ridgeland Fillmore FL 33336 $12,762.00 $15,000,00 20 Johason's 372 OxfordSheldon FL33553 $2,106.00 $10,000,00 65 Lee's Sport 282 Evengreen Altonville FL 32543 $2,85100 $5,000.00 35 Deerfield's 282 Columbia Sheldon FL 33553 $248.00 $7,500.00 35 Four Seasons All Season 28 Lakeview Growe ORDERS ORDER LINE OADER NUM PART NUM NUM OADERED QUOTED PRICE 21608 21610 NUM DATE AT94 $21.95 21608 $495.00 1220007 356 21613 122007 408 21614 |10W21/20071282 21617 122007 608 23/2007 148 1623 1232007 608 DV11 21613 21614 21617 21617 21619 $329.95 $595.00 $79495 KTOJ 21619 $495.00 1.290.00 FIGURE 2-1 Sample data for Premiere Products (continued) REP REP LAST FIRST STREET CITY STATE ZIP COMMISSION RATE NUM NAME NAME 20 Kaiser Valeric624 Randall Grove FL 35 Hull Richard 532 Jackson Sheldon FL 3332 $20,542.50 0.05 3553$39,216.00 0.07 65 Perez Juan 1626 Taylor Fillmore FL 33336 $23,487.00 005 FIGURE 2-1 Sample data for Premiere Products PART PART NUM DESCRIPTION ON HAND CLASS WAREHOUSE PRICE $24.95 $794.95 $165.00 $129,95 $495.00 $399.99 $159.95 $349.95 $595.00 $1,390.00 IW3 45 8G 2 2 AP 21 HW3 AT94 Home Gym Microwave Oven Cordless Drill Gas Range Washer Stand Mixer D4.71 SIAP DW11 12 AP 12 AP 1 KTO3 81 AP KV29Explanation / Answer
mysql> create table CUSTOMER ( CUSTOMER_NUM varchar(50) , CUSTOMER_NAME varchar (50), STREET varchar(50), CITY varchar(50), STATE varchar(10), ZIP int(igint(20), REP_NUM int(10));
Query OK, 0 rows affected (0.04 sec)
mysql> desc CUSTOMER;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| CUSTOMER_NUM | varchar(50) | YES | | NULL | |
| CUSTOMER_NAME | varchar(50) | YES | | NULL | |
| STREET | varchar(50) | YES | | NULL | |
| CITY | varchar(50) | YES | | NULL | |
| STATE | varchar(10) | YES | | NULL | |
| ZIP | int(10) | YES | | NULL | |
| BALANCE | bigint(20) | YES | | NULL | |
| CREDIT_LIMIT | bigint(20) | YES | | NULL | |
| REP_NUM | int(10) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
Now Insert records above table .. similary we have to create all tables with records,
Answer 4.) select cutomer_name from customer where EXISTS ( select cutomer_num from orders where order_num = 21608);
EXISTS operator returns true . So per requirement we can use this operator.
5.) Joining customer and orders table as below query
select cutomer_num , name from cutomer, orders where customer.customer_num = orders.customer_num and orders.order_date not eq '10/21/2007';
6.) First join the tables orders and order_line
Then resultant records of table be join with part table ..