Saturday, January 26, 2008

The Database On Which We'll Be Basing Our SQL Guide

I quickly learned SQL from a book named Sams Teach Yourself SQL in 10 Minutes (3rd Edition) (Sams Teach Yourself). It's well written and simply put. If you want to get more information than I'll be putting up, and don't want to spend to much time, I would recommend this book. They based it on a database available on their website. I don't know if they only let you download if you own the book, but it's available here:

www.forta.com/books/0672325675

I will also post the tables below, so that if you're not going through the guide hands-on, at least you'll be able to follow the outcomes.

Table named 'Customers':

cust_idcust_namecust_addresscust_citycust_statecust_zipcust_countrycust_contactcust_email
1000000001Village Toys200 Maple LaneDetroitMI44444USAJohn Smithsales@villagetoys.com
1000000002Kids Place333 South Lake DriveColumbusOH43333USAMichelle Green
1000000003Fun4All1 Sunny PlaceMuncieIN42222USAJim Jonesjjones@fun4all.com
1000000004Fun4All829 Riverside DrivePhoenixAZ88888USADenise L. Stephensdstephens@fun4all.com
1000000005The Toy Store4545 53rd StreetChicagoIL54545USAKim Howard

Table named 'Orders':

order_num

order_date

cust_id

20005

5/1/2004

1000000001

20006

1/12/2004

1000000003

20007

1/30/2004

1000000004

20008

2/3/2004

1000000005

20009

2/8/2004

1000000001

Table named 'OrderItems':

order_num

order_item

prod_id

quantity

item_price

20005

1

BR01

100$5.49

20005

2

BR03

100$10.99

20006

1

BR01

20$5.99

20006

2

BR02

10$8.99

20006

3

BR03

10$11.99

20007

1

BR03

50$11.49

20007

2BNBG01100$2.99

20007

3BNBG02100$2.99

20007

4BNBG03100$2.99

20007

5

RGAN01

50$.4.49

20008

1

RGAN01

5$4.99

20008

2

BR03

5$11.99

20008

3BNBG0110$3.49

20008

4BNBG0210$3.49

20008

5BNBG0310$3.49

20009

1BNBG01250$2.49

20009

2BNBG02250$2.49

20009

3BNBG03250$2.49

Table named 'Products'

prod_id

vend_id

prod_name

prod_price

prod_desc

BNBG01

DLL01

Fish bean bag toy

$3.49

Fish bean bag toy, complete with bean bag worms with which to feed it

BNBG02

DLL01

Bird bean bag toy

$3.49

Bird bean bag toy, eggs are not included

BNBG03

DLL01

Rabbit bean bag toy

$3.49

Rabbit bean bag toy, comes with bean bag carrots

BR01BRS01

8 inch teddy bear

$5.99

8 inch teddy bear, comes with cap and jacket

BR02BRS01

12 inch teddy bear

$8.99

12 inch teddy bear, comes with cap and jacket

BR03BRS01

18 inch teddy bear

$11.99

18 inch teddy bear, comes with cap and jacket

RGAN01DLL01

Raggedy Ann

$4.99

18 inch Raggedy Ann doll

RYL01FNG01

King doll

$9.49

12 inch king doll with royal garments and crown

RYL02FNG01

Queen doll

$9.49

12 inch queen doll with royal garments and crown

Table named 'Vendors'

vend_id

vend_name

vend_address

vend_city

vend_state

vend_zip

vend_country

BRE02

Bear Emporium

500 Park Street

Anytown

OH

44333

USA

BRS01

Bears R Us

123 Main Street

Bear Town

MI

44444

USA

DLL01

Doll House Inc.

555 High Street

Dollsville

CA

99999

USA

FNG01

Fun and Games

42 Galaxy Road

London

N16 6PS

England

FRB01

Furball Inc.

1000 5th Avenue

New York

NY

11111

USA

JTS01

Jouets et ours

1 Rue Amusement

Paris

45678

France

These are the tables we'll be working with as we progress through the lessons. Looking forward.

No comments:

Contact Form