Customer_t Customer_ID Customer_name Customer_address City State Postal_Code 001
ID: 3601877 • Letter: C
Question
Customer_t
Customer_ID
Customer_name
Customer_address
City
State
Postal_Code
001
Contemporary Casuals
1355 S. Hines Blvd.
Gainsville
FL
32601
002
Value Furniture
15145 S.W. 17th St.
Plano
TX
75094
003
Home Furnishings
1900 Allard Ave.
Albany
NY
12209
004
Eastern Furniture
1925 Beltline Rd.
Carteret
NJ
7008
005
Impressions
5585 Westcott Ct.
Sacramento
CA
94206
006
Furniture Gallery
325 Flatiron Dr.
Boulder
CO
80514
007
Period Furnishings
394 Rainbow Dr.
Seattle
WA
97954
008
California Classics
816 Peach Rd.
Santa Clara
CA
96915
009
M & H Casual Furniture
3709 First Street
Clearwater
FL
34620
010
Seminole Interiors
2400 Rocky Point Dr.
Seminole
FL
34646
011
American Euro Lifestyles
2424 Missouri Ave. N.
Prospect Park
NJ
7508
012
Battle Creek Furniture
345 Capitol Ave. SW
Battle Creek
MI
49015
013
Heritage Furnishings
66789 College Ave.
Carlisle
PA
17013
014
Kaneohe Homes
112 Kiowai St.
Kaneohe
HI
96744
015
Mountain Scenes
4132 Main Street
Ogden
UT
84403
Does_Business_in_t
Territory_ID
Customer_ID
1
5
1
6
1
7
2
2
2
5
2
7
2
8
3
1
3
2
3
12
4
1
4
9
4
10
5
6
5
15
6
3
6
4
6
11
6
13
8
14
Employee Skills_t
Skill_id
Employee_id
1
2
1
3
2
4
2
7
3
4
3
7
4
8
5
9
6
1
6
5
7
6
Employee_t
Employee_ID
Employee_name
Employee_Address
City
State
Postal_Code
Supervisor_ID
Hired_Date
1
Paquette
2224 Del Web Blvd
Centralia
WA
99154
6
1/15/1995
2
Johnson
410 Deer Park Ave
Centralia
WA
99155
1
2/1/1996
3
Delgado
5701 Mariner West
Chehalis
WA
99148
1
5/10/1997
4
Bradish
512 Plaza Place
Chehalis
WA
99148
5
10/1/1997
5
Braak
112 N Lincoln
Centralia
WA
99154
6
4/1/1998
6
Shuster
5003 Country Aire
Centralia
WA
99155
0
8/15/1999
7
Tate
603 Pinecrest Rd
Chehalis
WA
99148
5
12/1/2000
8
Van Horn
182 Amberwood Dr
Chehalis
WA
99148
5
4/15/1997
9
Moore
1441 Audobon Trace
Centralia
WA
99155
5
6/1/2001
Order_line_t
Order_ID
Product_ID
Quantity
1001
1
2
1001
2
2
1001
4
1
1002
3
5
1003
3
1
1004
6
2
1004
8
2
1005
4
4
1006
4
1
1006
5
2
1006
7
2
1007
1
3
1007
2
2
1008
3
2
1008
8
3
1009
4
2
1009
7
3
1010
8
10
1011
6
1
1012
4
2
1012
7
1
Order_t
Order_ID
Order_Date
Customer_ID
1001
10/21/2011
1
1002
10/25/2011
8
1003
10/26/2011
15
1004
10/27/2011
5
1005
11/24/2011
3
1006
11/27/2011
2
1007
11/28/2011
11
1008
12/3/2011
12
1009
12/5/2011
1
1010
1/16/2012
4
1011
2/14/2012
1
1012
3/20/2012
8
Produced_in_t
Product_ID
Work_Center_ID
1
2
2
2
3
1
3
2
4
1
5
1
5
2
6
1
6
2
7
2
8
2
Product_Line_t
Product_Line_ID
Product_Line_Name
1
Living Room
2
Family Room
3
Bedroom
4
Office
5
Dining Room
Product_t
Product_ID
Product_Name
Product_Finish
Unit_Price
On_hand
Product_Description
Product_Line_ID
Work_Center_ID
1
End Table
Cherry
$175.00
1
1
1
2
Coffee Table
Natural Ash
$200.00
4
1
1
3
Computer Desk
Natural Ash
$375.00
5
Computer Desk 48"
4
2
4
Entertainment Center
Natural Maple
$650.00
3
2
2
5
Writer's Desk
Cherry
$325.00
0
4
2
6
8-Drawer Desk
White Ash
$750.00
5
4
2
7
Dining Table
Natural Ash
$800.00
2
5
1
8
Computer Desk
Walnut
$250.00
4
Computer Desk 64"
4
2
Raw_Materials_t
Material_ID
Thickness
Size
Grade
Material_description
Footage_on_hand
Unit_price
1
1/4
8x4
B-4
Red Oak
166
$7.89
2
1/2
4x8
A-2
Red Oak
257
$12.05
3
3/4
4x8
A-1
Red Oak
33
$13.67
4
3/4
4x8
C-4
Red Oak
4
$7.66
5
1/4
4x8
A-4
White Oak
72
$7.23
6
3/4
4x8
A-1
White Oak
54
$15.19
7
1/2
4x8
B-4
Walnut
9
$13.02
8
3/4
4x8
A-1
Walnut
58
$15.45
9
3/4
4x8
Shop
Walnut
2
$10.88
10
3/4
4x8
C-2
Natural Ash
11
$15.55
11
1/4
4x8
A-4
White Ash
33
$8.79
12
3/4
4x8
A-1
White Ash
66
$14.26
13
1/2
4x8
A-2
Cherry
2
$15.82
14
1/2
4x8
B-2
Cherry
1
$13.75
15
3/4
4x8
A-1
Cherry
68
$16.72
16
1/4
4x8
A-4
Natural Maple
84
$5.70
17
1/2
4x8
D-3
Natural Maple
165
$7.95
18
3/4
4x8
A-1
Natural Maple
212
$11.13
19
3/4
4x8
B-2
Natural Maple
306
$10.47
20
3/4
4x8
D-3
Natural Maple
302
$9.18
Sales_territory_t
Territory_ID
Territory description
1
Northwest
2
Southwest
3
Midwest
4
South
5
Intermountain
6
Northeast
8
Hawaii
Salesperson_t
Salesperson_ID
Salesperson_name
Salesperson_telephone
Salesperson_fax
Territory_ID
1
Johnson
206-774-5589
206-774-5570
1
2
Haverty
541-443-8934
541-443-8900
1
3
Rodriguez
415-663-5454
415-664-7823
2
4
Chan
714-964-6296
714-885-3498
2
5
Swensen
612-338-9090
612-338-5409
3
6
Kotlowski
219-289-2641
219-653-4444
3
7
Beauclair
504-770-9503
504-770-9898
4
8
Overstreet
813-962-6926
813-974-4598
4
9
Rader
303-556-7463
303-556-8989
5
10
Himenez
505-774-6549
505-774-8934
5
11
O'Neill
215-666-5734
215-666-5700
6
12
Majeska
207-885-4983
207-885-4999
6
Skills_t
Skill ID
Skill Description
Low pay/hour
High pay/hour
1
Office
7
18
2
Carpenter
13
25
3
Finisher
12
25
4
Warehouse
7
12
5
Driver
9
18
6
Supervisor
15
25
7
Manager
25
40
Supplies_t
Vendor_ID
Material_ID
Unit_price
1
1
$7.89
1
2
$12.05
1
3
$13.67
1
4
$7.66
2
16
$5.70
2
17
$7.95
2
18
$11.13
2
19
$10.47
2
20
$9.18
4
5
$7.23
4
6
$15.19
4
7
$13.02
4
8
$15.45
4
9
$10.88
4
10
$15.55
4
11
$8.79
5
10
$16.00
5
12
$14.26
5
13
$15.82
5
14
$13.75
5
15
$16.72
6
1
$8.50
6
2
$12.50
6
3
$15.00
6
4
$8.24
Uses_t
Product_ID
Material_ID
Footage
1
14
4
2
10
6
3
10
15
4
16
20
5
15
13
6
12
16
7
10
16
8
7
15
Vendor_t
Vendor_ID
Vendor_name
Vendor_address
City
State
Postal_Code
1
L & M Hardwoods
7708 W Broadway
Norman
OK
73072
2
Building Blocks
5545 Rolling Hills
Columbia
SC
29212
3
McKnight Hardware
136 Pearl Avenue
Canton
OH
44705
4
Seattle Forest Products
5672 E. Druid Road
Seattle
WA
97960
5
Axley Lumber Co.
718 Moss Creek Dr
Vista
CA
97501
6
McEwen Lumber Co.
2620 36th St. N
Tampa
FL
33617
Work_Center_t
Work_Center_ID
Location
1
Centralia
2
Seattle
Works_in_t
Work_Center_ID
Employee_ID
1
2
1
4
1
5
1
7
1
8
1
9
2
1
2
2
2
3
2
4
2
6
2
9
1.Compose a query to insert a new customer with the following values: Customer Name: Lakeshore Furniture; Customer Address: 300 East Lakeshore Drive; City: Chicago; State: IL; Postal Code: 60602.
2. Compose a query to update the postal code of Lakeshore Furniture from 60602 to 60607. Assume that you know the customer name is Lakeshore Furniture but you do not know its Customer ID.
3. Compose a query to delete all customers who are located in Illinois (IL).
B. Multiple Table Queries
4. Compose an SQL statement that will show all customers who purchased an Entertainment Center or a Dining Table in 2011. In the result table, show the customer name, city, state, order ID, order date, and product name.
Customer_t
Customer_ID
Customer_name
Customer_address
City
State
Postal_Code
001
Contemporary Casuals
1355 S. Hines Blvd.
Gainsville
FL
32601
002
Value Furniture
15145 S.W. 17th St.
Plano
TX
75094
003
Home Furnishings
1900 Allard Ave.
Albany
NY
12209
004
Eastern Furniture
1925 Beltline Rd.
Carteret
NJ
7008
005
Impressions
5585 Westcott Ct.
Sacramento
CA
94206
006
Furniture Gallery
325 Flatiron Dr.
Boulder
CO
80514
007
Period Furnishings
394 Rainbow Dr.
Seattle
WA
97954
008
California Classics
816 Peach Rd.
Santa Clara
CA
96915
009
M & H Casual Furniture
3709 First Street
Clearwater
FL
34620
010
Seminole Interiors
2400 Rocky Point Dr.
Seminole
FL
34646
011
American Euro Lifestyles
2424 Missouri Ave. N.
Prospect Park
NJ
7508
012
Battle Creek Furniture
345 Capitol Ave. SW
Battle Creek
MI
49015
013
Heritage Furnishings
66789 College Ave.
Carlisle
PA
17013
014
Kaneohe Homes
112 Kiowai St.
Kaneohe
HI
96744
015
Mountain Scenes
4132 Main Street
Ogden
UT
84403
Explanation / Answer
Sample Query:
1.Compose a query to insert a new customer with the following values: Customer Name: Lakeshore Furniture; Customer Address: 300 East Lakeshore Drive; City: Chicago; State: IL; Postal Code: 60602.
INSERT INTO Customer_t (Customer_name, Customer_address, City, State, Postal_Code)
VALUES ('Lakeshore Furniture', '300 East Lakeshore Drive', 'Chicago', 'IL', '60602');
2. Compose a query to update the postal code of Lakeshore Furniture from 60602 to 60607. Assume that you know the customer name is Lakeshore Furniture but you do not know its Customer ID.
UPDATE Customer_t
SET Postal_Code = '60607'
WHERE Customer_name = 'Lakeshore Furniture';
3. Compose a query to delete all customers who are located in Illinois (IL).
DELETE FROM Customer_t
WHERE State='IL';
The above query will delete/remove all customer from Customer_t table who are matched by the state is IL
4. Compose an SQL statement that will show all customers who purchased an Entertainment Center or a Dining Table in 2011. In the result table, show the customer name, city, state, order ID, order date, and product name.
SELECT c.Customer_name, c.City, o.Order_ID, o.Order_Date, p.Product_Name FROM Customer_t c
INNER JOIN Order_t o ON o.Customer_ID = c.Customer_ID
INNER JOIN Order_line_t ol ON ol.Order_ID = o.Order_ID
INNER JOIN Product_t p ON p.Product_ID = ol.Product_ID
WHERE (p.Product_Name = 'Entertainment Center' OR p.Dining Table) AND YEAR(o.Order_Date) = '2011';
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.