1. 2. 3. 4. 5. 6. For every sales rep, list the sales rep's last name...

    

Transcribed Image Text:

1. 2. 3. 4. 5. 6. For every sales rep, list the sales rep's last name and the sales rep's first name in lower case letters. For every customer, list the customer's last name and the customer's first name in upper case letters. For every customer, list the customer number, the customer entire zip code, and add two more columns where the next column is the first 2 numbers of the zip code, and the last column is the last 3 numbers of the zip code. For every sales rep, list their street, and determine the length of the characters of the sales rep's Street. For every part description that begins with the letter "b", list the part description, and then pad each part description with a "+"on the left side so that all these part descriptions are 15 characters in length. For every part description that begins with the letter "g", list the part description, and then pad each part description with a "-"on the right side so that all these part descriptions are 15 characters in length. 7. 8. 9. 10. 11. For every part number, list the part number and then right trim of the numbers from the part number that are on the right side of the part number. For every sales rep, list the sales rep's last name and then the sales rep's first name, then concatenate the text Sales rep number: followed by the sales rep number. For every customer, list the customer number, the customer balance, and then round the customer balance up to the next whole dollar amount. For every customer, list the customer number, the customer balance, and then truncate the customer balance to its whole dollar amount. For every order, list the order number and the order date, use the To Char to convert the order date from all numbers to a month name, a day, and a year format. SLSR SLSR SLSR SLSR SALES LAST FIRST STREET REP NUMBE R (PK) 03 06 12 CUST CUST CUSTOMER LAST NUMBER (PK) 124 239 256 311 315 405 412 522 567 587 622 Jones 123 Main Mary Smith William 102 Raymond Diaz Miguel 419 Harper BZ66 CA14 CB03 CX11 CZ81 CUST CUST FIRST Adams Sally (Yours) (Yours) Samuels Ann Charles Don Daniels Tom Williams Al Adams Sally Nelson Mary Dinah Tran Galvin Mara Martins Dan PART Iron Dartboard Basketball Corn Popper Gas Grill SALES REP SLSR_ SLSR SLSR SLSR CITY STATE ZIP TOTAL Washer Griddle Bike Blender Treadmill CUSTOMER CUST STREET CITY PART PART NUMBER PART_DESCRIPTION (PK) AX12 AZ52 BA74 BH22 BT04 481 Oak 239 Drive Grant MI Ada MI Lansing MI 215 Pete 48 College 914 Cherry 519 Watson 16 Elm 108 Pine 808 Ridge 512 Pine 419 Chip 104 20 40 95 11 Grant Ira PART 52 78 44 112 68 MI CU CUST CUST CUST ST ZIP BALAN CREDIT STA COD CE TE _LIMIT Lansing MI 49224 818.75 Edison MI 08818 1000.00 MI Kent MI Grant MI Lansing MI Ada Harper Ada Grant SLSR COMM CODE COMMISSION ISSION_ RATE 49219 49441 49224 E 49219 49034 AP HW SG HW SG 48391 49219 PART UNITS ON ITEM HAND CLASS HW SG SG HW AP MI 49441 PART PART 49224 1817.50 98.75 MI 48421 402.40 MI 49441 114.60 MI 49219 1045.75 2150.00 4912.50 2150.00 3 2 1 3 2 3 21.50 825.75 WAREHOUSE NUMBER 3 1 3 2 770.75 402.75 1000 3000 1500 1000 750 1500 2000 1500 750 1000 1000 CUST SALES REP NUM BER .05 .07 .05 PART UNIT_PRICE 24.95 12.95 29.95 24.95 149.99 399.99 39.99 299.99 22.95 349.95 03 12 06 12 06 12 03 12 06 06 03 12491 12491 12494 12495 12498 12498 12500 ORDR 12504 12507 ORDER_NUMBER (PK) 12489 12491 12494 12495 12498 12500 12504 12507 BT04 BZ66 CB03 CX11 AZ52 BA74 BT04 ORDERS ORLN ORLN ORLN ORLN ORDER NUMBER (PK) PART NUMBER (PK) NUMBER ORDERED QUOTED PRICE 12489 AX12 21.95 149.99 399.99 279.99 22.95 12.95 24.95 149.99 CZ81 CB03 ORDR ORDER_DATE CUSTOMER_NUMBER 09/02/2018 09/02/2018 09/04/2018 09/04/2018 09/05/2018 09/05/2018 09/05/2018 09/22/2018 ORDER LINE 11 1 1 ORDR 4 2 2 4 1 2 1 124 311 315 256 522 124 522 239 325.99 299.99 1. 2. 3. 4. 5. 6. For every sales rep, list the sales rep's last name and the sales rep's first name in lower case letters. For every customer, list the customer's last name and the customer's first name in upper case letters. For every customer, list the customer number, the customer entire zip code, and add two more columns where the next column is the first 2 numbers of the zip code, and the last column is the last 3 numbers of the zip code. For every sales rep, list their street, and determine the length of the characters of the sales rep's Street. For every part description that begins with the letter "b", list the part description, and then pad each part description with a "+"on the left side so that all these part descriptions are 15 characters in length. For every part description that begins with the letter "g", list the part description, and then pad each part description with a "-"on the right side so that all these part descriptions are 15 characters in length. 7. 8. 9. 10. 11. For every part number, list the part number and then right trim of the numbers from the part number that are on the right side of the part number. For every sales rep, list the sales rep's last name and then the sales rep's first name, then concatenate the text Sales rep number: followed by the sales rep number. For every customer, list the customer number, the customer balance, and then round the customer balance up to the next whole dollar amount. For every customer, list the customer number, the customer balance, and then truncate the customer balance to its whole dollar amount. For every order, list the order number and the order date, use the To Char to convert the order date from all numbers to a month name, a day, and a year format. SLSR SLSR SLSR SLSR SALES LAST FIRST STREET REP NUMBE R (PK) 03 06 12 CUST CUST CUSTOMER LAST NUMBER (PK) 124 239 256 311 315 405 412 522 567 587 622 Jones 123 Main Mary Smith William 102 Raymond Diaz Miguel 419 Harper BZ66 CA14 CB03 CX11 CZ81 CUST CUST FIRST Adams Sally (Yours) (Yours) Samuels Ann Charles Don Daniels Tom Williams Al Adams Sally Nelson Mary Dinah Tran Galvin Mara Martins Dan PART Iron Dartboard Basketball Corn Popper Gas Grill SALES REP SLSR_ SLSR SLSR SLSR CITY STATE ZIP TOTAL Washer Griddle Bike Blender Treadmill CUSTOMER CUST STREET CITY PART PART NUMBER PART_DESCRIPTION (PK) AX12 AZ52 BA74 BH22 BT04 481 Oak 239 Drive Grant MI Ada MI Lansing MI 215 Pete 48 College 914 Cherry 519 Watson 16 Elm 108 Pine 808 Ridge 512 Pine 419 Chip 104 20 40 95 11 Grant Ira PART 52 78 44 112 68 MI CU CUST CUST CUST ST ZIP BALAN CREDIT STA COD CE TE _LIMIT Lansing MI 49224 818.75 Edison MI 08818 1000.00 MI Kent MI Grant MI Lansing MI Ada Harper Ada Grant SLSR COMM CODE COMMISSION ISSION_ RATE 49219 49441 49224 E 49219 49034 AP HW SG HW SG 48391 49219 PART UNITS ON ITEM HAND CLASS HW SG SG HW AP MI 49441 PART PART 49224 1817.50 98.75 MI 48421 402.40 MI 49441 114.60 MI 49219 1045.75 2150.00 4912.50 2150.00 3 2 1 3 2 3 21.50 825.75 WAREHOUSE NUMBER 3 1 3 2 770.75 402.75 1000 3000 1500 1000 750 1500 2000 1500 750 1000 1000 CUST SALES REP NUM BER .05 .07 .05 PART UNIT_PRICE 24.95 12.95 29.95 24.95 149.99 399.99 39.99 299.99 22.95 349.95 03 12 06 12 06 12 03 12 06 06 03 12491 12491 12494 12495 12498 12498 12500 ORDR 12504 12507 ORDER_NUMBER (PK) 12489 12491 12494 12495 12498 12500 12504 12507 BT04 BZ66 CB03 CX11 AZ52 BA74 BT04 ORDERS ORLN ORLN ORLN ORLN ORDER NUMBER (PK) PART NUMBER (PK) NUMBER ORDERED QUOTED PRICE 12489 AX12 21.95 149.99 399.99 279.99 22.95 12.95 24.95 149.99 CZ81 CB03 ORDR ORDER_DATE CUSTOMER_NUMBER 09/02/2018 09/02/2018 09/04/2018 09/04/2018 09/05/2018 09/05/2018 09/05/2018 09/22/2018 ORDER LINE 11 1 1 ORDR 4 2 2 4 1 2 1 124 311 315 256 522 124 522 239 325.99 299.99

Answer rating: 100% (QA)

To perform the requested operations lets start by organizing the given data into tables Based on the provided information we can create four tables Sales Rep Customer Part and Order Sales Rep Table SL... View the full answer