#Paula Noone
#SQL, Summer, 2009
#P58 Assignment


#1. Display all tables

Your SQL query has been executed successfully

SHOW TABLES 


Tables_in_DBpnoone0
Customer
Order_line
Orders
Part
Rep


#2. Display # of customers

SELECT count( * ) customer_name
FROM Customer  


customer_name
10


#3.  List contents of Customer table

SELECT *
FROM Customer
LIMIT 0 , 30  


CUSTOMER_NUM  CUSTOMER_NAME  STREET  CITY  STATE  ZIP  BALANCE  CREDIT_LIMIT  REP_NUM


148 Al's Appliance and Sport 2837 Greenway Fillmore FL 33336 6550.00 7500.00 20


282 Brooking's Direct 3827 Devon Grove FL 33321 431.50 10000.00 35


356 Ferguson's 382 Wildwood Northfield FL 33146 5785.00 7500.00 65

408 The Everything Shop 1828 Raven Crystal FL 33503 5285.00 5000.00 35

462 Bargain's Galore 3829 Central Grove FL 33321 3412.00 10000.00 65

524 Kline's 838 Ridgeland Fillmore FL 33336 12762.00 15000.00 20

608 Johnson's Dept. Store 372 Oxford Sheldon FL 33553 2106.00 10000.00 65

687 Lee's Sport and Appliance 282 Evergreen Altonville FL 32543 2851.00 5000.00 35

725 Deerfield's Four Seasons 282 Columbia Sheldon FL 33553 248.00 7500.00 35

842 All Season 28 Lakeview Grove FL 33321 8221.00 7500.00 20

 

#4.  Describe the Rep table

DESCRIBE Rep



 Field  Type  Null  Key  Default  Extra
      REP_NUM char(2) NO PRI NULL
      LAST_NAME char(15) NO   NULL
      FIRST_NAME char(15) NO   NULL
      STREET char(15) NO   NULL
      CITY char(15) NO   NULL
      STATE char(2) NO   NULL
      ZIP char(5) NO   NULL
 


#5.  List contents of the Orders table

Showing rows 0 - 6 (7 total, Query took 0.0005 sec)
SELECT *
FROM Orders
LIMIT 0 , 30  


 ORDER_NUM  ORDER_DATE  CUSTOMER_NUM

      21608 2007-10-20 148
      21614 2007-10-21 282
      21610 2007-10-20 356
      21613 2007-10-21 408
      21617 2007-10-23 608
      21619 2007-10-23 148
      21623 2007-10-23 608

 
#6.  List contents of the Rep table sorted by highest commission (I sorted by highest Rep ID since there wasn't any commission column(?)

SELECT *
FROM Rep
GROUP BY Rep_num DESC
LIMIT 0 , 30  


  REP_NUM  LAST_NAME  FIRST_NAME  STREET  CITY  STATE  ZIP
      65 Perez Juan 1626 Taylor Fillmore FL 33336
      35 Hull Richard 532 Jackson Sheldon FL 33553
      20 Kaiser Valerie 624 Randall Grove FL 33321



#7.  Display customer name with highest credit limit

SELECT customer_name
FROM Customer
ORDER BY credit_limit DESC
LIMIT 1   


customer_name

      Kline's