Customer_t Customer_ID Customer_name Customer_address City State Postal_Code 001
ID: 3603902 • 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
*I am using Access*
1. Compose a query as follows. For each product that had been ordered, we would like to know the total quantity that had been requested. List the most popular product first and the least popular product last. In the result table, show the product ID and the total quantity that had been requested.
2. Compose a query as follows. For each customer who placed at least two different orders in 2011, we would like to see the customer ID and the total number of orders placed by that customer in 2011. Sort the results by the total number of orders in ascending order.
3. Compose a query to show every product that has a value of $350 or more. The value of each product is defined as the quantity on hand multiplied by the unit price of that product. In the result table, show product ID, unit price, quantity on hand, and the product value. Sort the results by the product value in descending order.
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
1)select productID,count(productId) as product_orders,Quantity as Total_quantity
from Order_line_t
group by productID,Quantity
order by Total_quantity desc
2)select customerID,count(Order_ID) as total _number_of_orders
from Customer_t
where CustomerID in
(select CustomerID from Order_t
where year(Order_Date)=2011
having count(customer)>=2)
group by CustomerID
order by total _number_of_orders asc
3)select ProductID,Unit_Price,On_hand,(On_hand*Unit_Price) as Product_Value
from Product_t
where product_value>='$350'
order by product_value desc
4)select customer_name,city,state,orderId,Orderdate,Product_name
from Customer_t c
inner join Order_t o
on c.Customer_ID=o.Customer_ID
inner join Order_line_t olt
on olt.Order_ID=o.Order_ID
inner join Product_t p
on p.product_ID=olt.Product_ID
where Product_name='Entertainment Center' or Product_name='Dining Table' and
year(OrderDate)=2011
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.