SQL Statements
3. Create a relation showing the employee name (first and last), and ordertbl of employees whose last name starts with J and who have taken orders in January 2017 from WA customers in Seattle. Remove duplicate rows in the result. produ I 5. Create a relation showing the product name, sum of the quantity of products ordered, and total order amount (sum of the product price times the quantity) for orders placed in February 2017. Only include products that have more than three products ordered in February 2017. Sort the result in descending order of the total amount. Ordno Qty 1 1 1 1 1 1 1 1 01241518 01455122 01579999 01579999 01579999 01615141 01615141 01615141 01656777 01656777 02233457 02233457 02334661 02334661 02334661 03252629 03252629 03331222 03331222 03331222 03377543 03377543 04714645 04714645 05511365 05511365 05511365 05511365 05511365 06565656 07847172 07847172 07959898 07959898 07959898 07959898 07989497 07989497 07989497 08979495 08979495 08979495 09919099 09910699 09919699 ProdNo P0036577 P4200344 P1556678 P6677900 P9995676 P0036566 P1445671 P4200344 P1445671 P1556678 PO036577 P1445671 PO036566 P1412138 P1556678 P4200344 P9995676 P1412138 P1556678 P3455443 P1445671 P9995670 POO36566 P9995676 P1412138 P 1445671 P1556678 P3455443 P6677900 PD036566 P1556678 P8677900 P1412138 P1556678 P3455443 P6677900 P1114590 P1412138 P1445671 P1114500 P1412138 P1445671 PD036577 P1114590 P4200344 1 1 1 1 10 1 1 5 5 5 5 5 2 2 3 1 + 1 OrdNo 03252629 03331222 03377543 04714545 05511365 06565656 07847172 07959898 07989497 08979495 09919699 OrdDate CustNo EmpNo OrdName OrdStreet Ord City OrdState Ord Zip 01/23/2017 C9403348 E9954302 Mike Boren 642 Crest Ave Englewood Co 80113-5431 01/13/2017 C1010398 Jim Glussman 1432 E. Ravenna Denver CO 80111-0033 01/15/2017 C9128574 E8843211 Jerry Wyatt 16212 123rd C Denver CO 80222-0022 01/11/2017 C2388597 E1329594 Beth Taylor 2396 Ratter Rd Seattle WA 98103-1121 01/22/2017 C3340959 E9884325 Betty White 4334 153rd NW Seattle WA 98178-3311 01/20/2017 09865874 E8843211 Mr. Jack Sibley 166 E 3441 Ranton WA 98006-5543 01/23/2017 09943201 Harry Sanders 1280 S. Hill Rd File WA 98222-2258 02/19/2017 C8543321 E8544399 Ron Thompson 789 122nd SL Renton WA 98666-1289 01/16/2017 C3499503 E9345771 Bob Mann 1190 Lomaine Cle. Monroe 98013-1095 01/23/2017 C9865874 Heleniley 206 McCaffrey Renton WA 98006-5543 02/11/2017 09857432 E9954302 Homer Wells 123 Main SL Seattle 98105-4322 WA WA Employee EmpCommRate EmpNo E1329594 E8544399 E8843211 E9345771 E9834325 E9954302 E9973110 0.02 0.02 EmpFirstName EmplastName EmpPhone EmpeMail Land Santos (303) 789-1234 LSantos@bigco.com Joe Jenkins (303) 221-9875 Jenkins @bigco.com Army Tang (303) 556-4321 ATang bigco.com Colin White (303) 221-4453 CWhiteogo.com Thomas Johnson (303) 556-9987 Tjohnson@bigco.com Mary Hill (303) 556-9871 Mbigco.com Theresa Beck (720) 320-2234 Teckbigco.com SupEmpNo E8843211 E8843211 E9884325 E9884325 0.04 0.04 0.05 0.02 E8843211 E9884325 Product ProdQOH ProdPrice 12 ProdNextShipDate 2/20/2017 2/20/2017 1/22/2017 5 100 ProdNo PO036566 PO036577 P1114590 P1412138 P1445671 P1556678 P3455443 P4200344 P8677900 P9995676 ProdName ProdMig 17 inch Color Monitor ColorMeg, Inc 19 inch Color Monitor ColoregInc R3000 Color Laser Printer Connex 10 Foot Printer Cable Ehite B-Outlet Surge Protector Intersale CVP Ink Jet Color Printer Connex Color Ink Jet Cartridge Connex 36-Bit Color Scanner UV Components Black Ink Jet Cartridge Connex Battery Back-up System Cybercx 33 5169.00 $319.00 $699.00 $12.00 $14.99 $99.00 $38.00 $199.99 $25.69 $89.00 1/22/2017 1/22/2017 1/29/2017 44 2/1/2017 OrdLine OrdNo ProdNo aty 01116324 01231231 01231231 P1445671 P0036566 P1445671 FIGURE 4.P1 Relationship Window for the Order Entry Database Customer CustNo Custbal C0954327 C1010398 C2388597 C3340959 C3499503 C8543321 C8574932 C8654390 C9128574 C9403348 C9432910 C9543029 C9549302 C9857432 C9865874 C9943201 CustFirstName CustiastName CustStreet CustCity CustState Sheri Gordon 336 HSE Littleton Jim Glussman 1482 E. Ravenna Denver CO Beth Taylor 2396 Ratier Rd Seattle WA Betty Wise 4334 153rd NW Seattle WA Bob Mann 1190 Lorraine Cir. Monroe WA Ron Thompson 789 122nd SL Renton WA Wally Jones 411 Webber Ave. Seattle WA Candy Kendall 456 Pine St Seattle WA Jerry Wyatt 16212 123rd C Denver Mike Boren 642 Crest Ave. Englewood Larry Styles 9:25 S. Crest Lane Bellevue WA Sharon Johnson 1223 Meyer Way Fife WA Todd Hayes 1400 NW 18h Lynnwood WA Homer Wells 123 Main I Seattle WA Mary 206 McCaffrey Littleton co Harry Sanders 1280 S. HAR Fife WA Custzip 80129-5543 80111-0033 98105-1121 98178-3311 38013-1095 98666-1289 98105-1093 98105-3345 B0222-0022 80113-5431 98104-2211 98222-1123 98036-2244 98105-422 80129-5543 98222-2258 5230.00 $200.00 $500.00 $200.00 $0.00 $85.00 $1,500.00 $50.00 $100.00 $0.00 $250.00 $856.00 $0.00 $500.00 $150.00 $1,000.00 Order OrdNo 01116324 01231231 01241518 01455122 01579999 01615141 01656777 02233457 02334661 OrdDate CustNo Emp No OrdName OrdStreet Ord City OrdState OrdZip 01/23/2017 C0954327 E8544399 Shen Gordon 356 Hai St. Leton 80129-5543 01/23/2017 09432910 E9954302 Larry Styles 9825 S. Crest Lane Bellevue WA 98104-2211 02/10/2017 C9549302 Todd Hayes 1400 NW 886h Lynnwood WA 98036-2244 01/09/2017 C8574932 E9345771 Wally Jones 411 Webber Ave Seattle WA 98105-1093 01/05/2017 C9543029 E8544399 Tom Johnson 1632 Ocean De Des Moines WA 98222-1123 01/23/2017 08654390 E8544399 Candy Kendall 456 Pine St Seattle WA 98105-3345 02/11/2017 C8543321 Ron Thompson 789 122nd St. Renton WA 98666-1289 01/12/2017 C2388597 E9B84325 Beth Taylor 2396 Rafter Ra Seattle WA 98103-1121 01/14/2017 C0954327 E1329594 Mrs. Ruth Gordon 233 S. 1666 Seattle WA 98011
