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 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 2 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 suppliers 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

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 22, 2024

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

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 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.

2. 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 suppliers 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].