List the product name along with its discounted list price - list price after applying discount. Order the output by (category_name, product_name). Result has schema (categoryName, productName, discountedPrice) 2. q2: List employees who have empty orders (orders that are not listed in the order items table). Result has schema (employee_id, order_id) 3. q3: 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). 4. q4: For customers who have multiple (> 1) orders, list all their orders EXCEPT their highest order id order. For example, if a customers has orders 1 and 2, list order 1; if a customer has orders 1, 2, 3, 4, list orders 1, 2, 3. Order the output by (first_name) descending. Result has scheme (first_name, customer_id, order_id, order_date) 5. q5: List customers who have ordered products that have not been ordered by any other customer. Result has schema: (order_id, product_id, customer_id, first_name) 6. q6: List customers who either have no orders or exactly one order. Result has schema: (customer_id, first_name)

 

Transcribed Image Text:

addresses address id customer id line 1 line2 city state 1 1 100 East Ridgewood Ave. Paramus NJ zip code 7652 phone 201-653-4472 1 2 1 3 16285 Wendell St. 19270 NW Cornell Rd. Omaha NE 68135 402-896-2576 Beaverton OR 97006 503-654-1291 1 4 186 Vermont St. Apt. 2 San Francisco CA 94110 415-292-6651 1 5 6982 Palm Ave. Fresno CA 93711 559-431-2398 1 6 23 Mountain View St. Denver CO 80208 303-912-3852 1 7 7361 N. 41st St. Apt. B New York NY 10012 212-335-2093 1 8 2381 Buena Vista St. Los Angeles CA 90023 213-772-5033 2 1 21 Rosewood Rd. Woodcliff Lake NJ 7677 201-653-4472 2 4 2 7 2 8 1374 46th Ave. 3829 Broadway Ave. 291 W. Hollywood Blvd. San Francisco CA 94129 415-292-6651 Suite 2 New York Los Angeles NY 10012 212-239-1208 CA 90024 213-391-2938 categories category id category name 2 Basses 3 Drums 1 Guitars 4 Keyboards customers customer id 1 email first name last name allan@yahoo.com Allan 2 barryz@gmail.com Barry Sherwood Zimmer 3 christine@solar.com Christine 4 david@hotmail.com David Brown Goldstein 5 erinv@gmail.com Erin Valentino 6 frank@sbcglobal.net Frank Lee Wilson 7 gary@sun.com Gary 8 heather@mac.com Heather Hernandez Esway emplyee employee id 1 customer id 1 email allan@sun.com first name Allan last name Sherwood 2 2 barryz@sun.com Barry 3 NULL chris@sun.com Chris Zimmer Bani 4 2 julyz@sun.com July Zimmer 5 NULL nina@sun.com Nina 6 7 gary@sun.com Gary Singh Hemandez order items order id product id item price discount amount quantity 1 2 1199 359.7 1 2 4 489.99 186.2 1 3 1 2517 1308.84 1 3 6 415 161.85 1 4 2 1199 359.7 2 5 5 299 0 1 6 5 299 0 1 7 1 699 209.7 1 7 7 799.99 240 1 7 9 699.99 210 1 8 10 799.99 120 1 9 1 699 209.7 1 orders order id address id customer id 1 1 1 2 2 1 order date 3/28/18 9:40 3/28/18 11:23 ship amount 5 5 ship date 3/30/18 15:32 3/29/18 12:52 card type Visa card number card expires 4.11111E+15 Apr-20 Visa 4.01289E+15 Aug-19 3 1 1 3/29/18 9:44 10 3/31/18 9:11 Visa 4.11111E+15 Apr-17 4 1 2 3/30/18 15:22 5 4/3/18 16:32 American Express 3.78282E+14 Apr-16 5 1 3 3/31/18 5:43 5 4/2/18 14:21 6 1 4 3/31/18 18:37 5 NULL Visa Discover 7 2 4 4/1/18 23:11 15 4/3/18 10:21 MasterCard 4.11111E+15 Apr-19 6.01111E+15 Apr-19 5.55556E+15 Apr-19 8 1 5 4/2/18 11:26 5 NULL Visa 4.01289E+15 Apr-19 9 2 8 4/3/18 12:22 5 NULL Visa 4.11111E+15 Apr-19 10 1 7 4/3/18 12:22 5 NULL Visa 4.11111E+15 Apr-19 product id category id 1 1 2 1 3 1 product code strat les paul sg products product name list price discount percent date added Fender Stratocaster 699 30 10/30/17 9:32 Gibson Les Paul Gibson SG 1199 30 12/5/17 16:33 2517 52 2/4/18 11:04 4 1 5 1 fg 700s washburn Yamaha FG700S 489.99 38 6/1/18 11:12 Washburn D10S 299 0 7/30/18 13:58 6 1 rodriguez Rodriguez Caballero 11 415 39 7/30/18 14:12 7 2 precision 8 2 hofner Fender Precision Hofner Icon 799.99 30 6/1/18 11:29 499.99 25 7/30/18 14:18 9 3 ludwig Ludwig 5-piece Drum Set 699.99 30 7/30/18 12:46 10 3 tama Tama 5-Piece Drum Set 799.99 15 7/30/18 13:14 addresses address id customer id line 1 line2 city state 1 1 100 East Ridgewood Ave. Paramus NJ zip code 7652 phone 201-653-4472 1 2 1 3 16285 Wendell St. 19270 NW Cornell Rd. Omaha NE 68135 402-896-2576 Beaverton OR 97006 503-654-1291 1 4 186 Vermont St. Apt. 2 San Francisco CA 94110 415-292-6651 1 5 6982 Palm Ave. Fresno CA 93711 559-431-2398 1 6 23 Mountain View St. Denver CO 80208 303-912-3852 1 7 7361 N. 41st St. Apt. B New York NY 10012 212-335-2093 1 8 2381 Buena Vista St. Los Angeles CA 90023 213-772-5033 2 1 21 Rosewood Rd. Woodcliff Lake NJ 7677 201-653-4472 2 4 2 7 2 8 1374 46th Ave. 3829 Broadway Ave. 291 W. Hollywood Blvd. San Francisco CA 94129 415-292-6651 Suite 2 New York Los Angeles NY 10012 212-239-1208 CA 90024 213-391-2938 categories category id category name 2 Basses 3 Drums 1 Guitars 4 Keyboards customers customer id 1 email first name last name allan@yahoo.com Allan 2 barryz@gmail.com Barry Sherwood Zimmer 3 christine@solar.com Christine 4 david@hotmail.com David Brown Goldstein 5 erinv@gmail.com Erin Valentino 6 frank@sbcglobal.net Frank Lee Wilson 7 gary@sun.com Gary 8 heather@mac.com Heather Hernandez Esway emplyee employee id 1 customer id 1 email allan@sun.com first name Allan last name Sherwood 2 2 barryz@sun.com Barry 3 NULL chris@sun.com Chris Zimmer Bani 4 2 julyz@sun.com July Zimmer 5 NULL nina@sun.com Nina 6 7 gary@sun.com Gary Singh Hemandez order items order id product id item price discount amount quantity 1 2 1199 359.7 1 2 4 489.99 186.2 1 3 1 2517 1308.84 1 3 6 415 161.85 1 4 2 1199 359.7 2 5 5 299 0 1 6 5 299 0 1 7 1 699 209.7 1 7 7 799.99 240 1 7 9 699.99 210 1 8 10 799.99 120 1 9 1 699 209.7 1 orders order id address id customer id 1 1 1 2 2 1 order date 3/28/18 9:40 3/28/18 11:23 ship amount 5 5 ship date 3/30/18 15:32 3/29/18 12:52 card type Visa card number card expires 4.11111E+15 Apr-20 Visa 4.01289E+15 Aug-19 3 1 1 3/29/18 9:44 10 3/31/18 9:11 Visa 4.11111E+15 Apr-17 4 1 2 3/30/18 15:22 5 4/3/18 16:32 American Express 3.78282E+14 Apr-16 5 1 3 3/31/18 5:43 5 4/2/18 14:21 6 1 4 3/31/18 18:37 5 NULL Visa Discover 7 2 4 4/1/18 23:11 15 4/3/18 10:21 MasterCard 4.11111E+15 Apr-19 6.01111E+15 Apr-19 5.55556E+15 Apr-19 8 1 5 4/2/18 11:26 5 NULL Visa 4.01289E+15 Apr-19 9 2 8 4/3/18 12:22 5 NULL Visa 4.11111E+15 Apr-19 10 1 7 4/3/18 12:22 5 NULL Visa 4.11111E+15 Apr-19 product id category id 1 1 2 1 3 1 product code strat les paul sg products product name list price discount percent date added Fender Stratocaster 699 30 10/30/17 9:32 Gibson Les Paul Gibson SG 1199 30 12/5/17 16:33 2517 52 2/4/18 11:04 4 1 5 1 fg 700s washburn Yamaha FG700S 489.99 38 6/1/18 11:12 Washburn D10S 299 0 7/30/18 13:58 6 1 rodriguez Rodriguez Caballero 11 415 39 7/30/18 14:12 7 2 precision 8 2 hofner Fender Precision Hofner Icon 799.99 30 6/1/18 11:29 499.99 25 7/30/18 14:18 9 3 ludwig Ludwig 5-piece Drum Set 699.99 30 7/30/18 12:46 10 3 tama Tama 5-Piece Drum Set 799.99 15 7/30/18 13:14