Customer_t Customer_ID Customer_name Customer_address City State Postal_Code 001
ID: 3600251 • 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
A. Single Table Queries
Compose a query to list the number of different products for each order ID. In the result table, your query should show the order ID and the number of different products for each order ID. Sort the results by order ID in ascending order. For example, your result table should look similar to the following:
Order_ID
NumberOfDifferentProducts
1001
3
1002
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.
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.
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.
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.
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.
Compose a query to delete all customers who are located in Illinois (IL).
B. Multiple Table Queries
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.
Compose a query to calculate the total cost of raw materials for each finish product. In the result table, we would like to see Product ID, Product Name, and its total cost of raw materials. Sort the results by the total cost of raw materials in descending order.
Compose an SQL statement to generate a list of two least expensive vendors (suppliers) for each raw material. In the result table, show the following columns: material ID, material description, vendor ID, vendor name, and the supplier's unit price. Sort the result table by material ID and supplier’s unit price in ascending order. Note: If a raw material has only one vendor (supplier), that supplier and its unit price for the raw material should also be in the result (output) table [hint: use a correlated subquery].
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
A. Single Table Queries
The above query lists the order id and the no of products each by its order id in the ascending order.
The above query returns the product it with its total quatity ordered by all the orders.
The above query retrieves the customer and the order count for those who have ordered more than 2 in year 2011.
The above query retrieves the product id and the value of the product having more than 350 with the other required information.
As we have the customer table I am giving the some customer id by assuming the value
insert into Customer_t values (016,'Lakeshore Furniture','300 East Lakeshore Drive','Chicago','IL',60602);
The above query will insert the values into the customer table
The above query will delete all the customers info who are located in state Illinois (IL)
update Customer_t set Postal_Code = 60607 where Customer_Name ='Lakeshore Furniture';
The above update query is used to update the attribute Postal_Code
delete * from Customer_t where State='IL';
B. Multiple Table Queries
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.