List employee pairs who share customer id. Result has schema: (first_name1, first_name2) Note that you should not print both (July, Barry) and (Barry, July). q2: List the highest address_id for customers who have addresses. (For customers with a single address_id, list it; for customers with multiple addresses, list the highest.) (Do NOT use any aggregate functions or built-in functions to solve the query.) Result has schema: (customer_id, first_name, address_id, city, state) q3: List customers who have ordered products that have not been ordered by any other customer. Result has schema: (customer_id, product_id) The primary key for table order items is item_id; a candidate key is order_id + product_id.

 

Transcribed Image Text:

Addresses address_id customer id line1 line2 city state 1 1 2 1 234 345678 100 East Ridgewood Ave. 21 Rosewood Rd. 16285 Wendell St. 19270 NW Cornell Rd. Paramus NJ zip_code 7652 phone 201-653-4472 disabled 0 Woodcliff Lake NJ 7677 201-653-4472 0 Omaha NE 68135 402-896-2576 0 Beaverton OR 97006 503-654-1291 0 186 Vermont St. Apt. 2 San Francisco CA 94110 415-292-6651 0 4 1374 46th Ave. San Francisco CA 94129 415-292-6651 0 5 6982 Palm Ave. Fresno CA 93711 559-431-2398 0 9 10 11 12 619 00 00 23 Mountain View St. Denver CO 80208 303-912-3852 0 7 7361 N. 41st St. Apt. B New York NY 10012 212-335-2093 0 7 3829 Broadway Ave. Suite 2 New York NY 10012 212-239-1208 0 8 2381 Buena Vista St. Los Angeles CA 90023 8 291 W. Hollywood Blvd.. Los Angeles CA 90024 213-772-5033 213-391-2938 0 0 Categories category_id category_name customers customer_id 1 2 3 4 Guitars Basses 1 2 email_address allan@yahoo.com barryz@gmail.com Drums 3 Keyboards 4 5 6 password 650215acec 3f563468d42a christine@solar.com ed19f5c083 david@hotmail.com b444ac06613fc erinv@gmail.com 1d7b0df729d299b frank@sbcglobal.net 3ebfa301dc59 first_name Allan Barry Christine David last_name Sherwood Zimmer Brown Goldstein Erin Valentino Frank Lee Wilson 7 8 9 gary@sun.com heather@mac.com heath@mac.com 1ff2b3704aedeb 911ddc3b8f9a13 911ddc3b8f9a13 Gary Heather Heath Hernandez Esway Easy Employee employee_id customer_id email_address first_name 1 1 allan@sun.com Allan last_name Sherwood 2 2 barryz@sun.com Barry Zimmer 3 None chris@sun.com Chris Bani 4 2 julyz@sun.com July Zimmer 5 None nina@sun.com Nina Singh 6 7 gary@sun.com Gary Hernandez 7 2 aprilz@sun.com April Zimmer Order_Items item_id order_id product_id 8 9 10 11 12 9 13 123 2436255172214 1234567 3456227 884 item_price 1199 489.99 discount_amount quantity 359.7 1 186.2 1 2517 1308.84 1 415 161.85 1 1199 359.7 2 299 0 1 299 0 1 699 209.7 1 799.99 240 1 699.99 210 1 10 799.99 120 1 699 2517 209.7 1 1308.84 1 Orders order_id address_id customer_id order_date ship_amount ship_date card_type 1 7 8 9 121345694 234569% o 1 2018-03-28 09:40:28 5 2018-03-30 15:32:51 Visa card number card_expires 4.11111E+15 04/2020 1 2018-03-28 11:23:20 5 2018-03-29 12:52:14 Visa 1 2018-03-29 09:44:58 10 2 2018-03-30 15:22:31 5 3 2018-03-31 05:43:11 5 2018-03-31 9:11:41 2018-04-03 16:32:21 2018-04-02 14:21:12 Visa American Express 4 2018-03-31 18:37:22 5 None Visa Discover 4 2018-04-01 23:11:12 15 2018-04-03 10:21:35 5 2018-04-02 11:26:38 5 3 2018-04-03 12:22:31 5 None None MasterCard Visa Visa 4.01289E+15 08/2019 4.11111E+15 04/2017 3.78282E+14 04/2016 4.11111E+15 04/2019 6.01111E+15 04/2019 5.55556E+15 04/2019 4.01289E+15 04/2019 4.11111E+15 04/2019 Products product_id category_id product_code product_name list_price discount_percent date_added 1 1 strat Fender Stratocaster 699 30 2017-10-30 09:32:40 2 1 les_paul Gibson Les Paul 1199 30 2017-12-05 16:33:13 3 1 sg Gibson SG 2517 52 2018-02-04 11:04:31 4 1 fg700s Yamaha FG700S 489.99 38 2018-06-01 11:12:59 5 1 washburn Washburn D10S 299 0 2018-07-30 13:58:35 6 1 rodriguez Rodriguez Caballero 11 299 39 2018-07-30 14:12:41 7 2 precision Fender Precision 799.99 30 2018-06-01 11:29:35 8 2 hofner Hofner Icon 499.99 25 2018-07-30 14:18:33 9 3 ludwig Ludwig 5-piece Drum Set 699.99 30 2018-07-30 12:46:40 10 3 tama Tama 5-Piece Drum Set 799.99 15 2018-07-30 13:14:15 Addresses address_id customer id line1 line2 city state 1 1 2 1 234 345678 100 East Ridgewood Ave. 21 Rosewood Rd. 16285 Wendell St. 19270 NW Cornell Rd. Paramus NJ zip_code 7652 phone 201-653-4472 disabled 0 Woodcliff Lake NJ 7677 201-653-4472 0 Omaha NE 68135 402-896-2576 0 Beaverton OR 97006 503-654-1291 0 186 Vermont St. Apt. 2 San Francisco CA 94110 415-292-6651 0 4 1374 46th Ave. San Francisco CA 94129 415-292-6651 0 5 6982 Palm Ave. Fresno CA 93711 559-431-2398 0 9 10 11 12 619 00 00 23 Mountain View St. Denver CO 80208 303-912-3852 0 7 7361 N. 41st St. Apt. B New York NY 10012 212-335-2093 0 7 3829 Broadway Ave. Suite 2 New York NY 10012 212-239-1208 0 8 2381 Buena Vista St. Los Angeles CA 90023 8 291 W. Hollywood Blvd.. Los Angeles CA 90024 213-772-5033 213-391-2938 0 0 Categories category_id category_name customers customer_id 1 2 3 4 Guitars Basses 1 2 email_address allan@yahoo.com barryz@gmail.com Drums 3 Keyboards 4 5 6 password 650215acec 3f563468d42a christine@solar.com ed19f5c083 david@hotmail.com b444ac06613fc erinv@gmail.com 1d7b0df729d299b frank@sbcglobal.net 3ebfa301dc59 first_name Allan Barry Christine David last_name Sherwood Zimmer Brown Goldstein Erin Valentino Frank Lee Wilson 7 8 9 gary@sun.com heather@mac.com heath@mac.com 1ff2b3704aedeb 911ddc3b8f9a13 911ddc3b8f9a13 Gary Heather Heath Hernandez Esway Easy Employee employee_id customer_id email_address first_name 1 1 allan@sun.com Allan last_name Sherwood 2 2 barryz@sun.com Barry Zimmer 3 None chris@sun.com Chris Bani 4 2 julyz@sun.com July Zimmer 5 None nina@sun.com Nina Singh 6 7 gary@sun.com Gary Hernandez 7 2 aprilz@sun.com April Zimmer Order_Items item_id order_id product_id 8 9 10 11 12 9 13 123 2436255172214 1234567 3456227 884 item_price 1199 489.99 discount_amount quantity 359.7 1 186.2 1 2517 1308.84 1 415 161.85 1 1199 359.7 2 299 0 1 299 0 1 699 209.7 1 799.99 240 1 699.99 210 1 10 799.99 120 1 699 2517 209.7 1 1308.84 1 Orders order_id address_id customer_id order_date ship_amount ship_date card_type 1 7 8 9 121345694 234569% o 1 2018-03-28 09:40:28 5 2018-03-30 15:32:51 Visa card number card_expires 4.11111E+15 04/2020 1 2018-03-28 11:23:20 5 2018-03-29 12:52:14 Visa 1 2018-03-29 09:44:58 10 2 2018-03-30 15:22:31 5 3 2018-03-31 05:43:11 5 2018-03-31 9:11:41 2018-04-03 16:32:21 2018-04-02 14:21:12 Visa American Express 4 2018-03-31 18:37:22 5 None Visa Discover 4 2018-04-01 23:11:12 15 2018-04-03 10:21:35 5 2018-04-02 11:26:38 5 3 2018-04-03 12:22:31 5 None None MasterCard Visa Visa 4.01289E+15 08/2019 4.11111E+15 04/2017 3.78282E+14 04/2016 4.11111E+15 04/2019 6.01111E+15 04/2019 5.55556E+15 04/2019 4.01289E+15 04/2019 4.11111E+15 04/2019 Products product_id category_id product_code product_name list_price discount_percent date_added 1 1 strat Fender Stratocaster 699 30 2017-10-30 09:32:40 2 1 les_paul Gibson Les Paul 1199 30 2017-12-05 16:33:13 3 1 sg Gibson SG 2517 52 2018-02-04 11:04:31 4 1 fg700s Yamaha FG700S 489.99 38 2018-06-01 11:12:59 5 1 washburn Washburn D10S 299 0 2018-07-30 13:58:35 6 1 rodriguez Rodriguez Caballero 11 299 39 2018-07-30 14:12:41 7 2 precision Fender Precision 799.99 30 2018-06-01 11:29:35 8 2 hofner Hofner Icon 499.99 25 2018-07-30 14:18:33 9 3 ludwig Ludwig 5-piece Drum Set 699.99 30 2018-07-30 12:46:40 10 3 tama Tama 5-Piece Drum Set 799.99 15 2018-07-30 13:14:15