Description
–Write one statement in the given space beneath each problem description
–to answer the query.
–Partial credits is possible.
–Each clause of SELECT, FROM, WHERE, and ORDER BY is expected to be coded
–on its own line and use proper indentation on each line to make your
–code more readable for grading.
–Do not include statements other than the one for grading.
–Do not change the file name and its extension.
use AP;
/*
1. For items in InvoiceLineItems table whose AccountNo is an even number like
150 or 574, use a CASE function to display their AccountNo,
InvoiceLineItemDescription, and DescriptionLength. Depending on the total
number of characters of the InvoiceLineItemDescription, each DescriptionLength
shows one of the following three strings:
‘Shorter than 10 characters’, or
‘Between 10 and 19 characters’, or
’20 or more characters’
Also sort your output by AccountNo.
Hint: Use one of the operators listed on p.97 to determine if a number is an even
or odd number.
Here is a sample output of first few lines:
AccountNo InvoiceLineItemDescription DescriptionLength
——— ————————– —————————–
150 Supplies Shorter than 10 characters
160 MVS Online Library Between 10 and 19 characters
160 MSDN Shorter than 10 characters
160 Quarterly Maintenance 20 or more characters
170 Network wiring Between 10 and 19 characters
400 CICS Desk Reference Between 10 and 19 characters
……
…
*/
/*
2. Display InvoiceLineItemDescription such that the one with the
longest first word is printed at top and no duplicate rows.
To verify your output, display the length of the first word of
InvoiceLineItemDescription in the second column as FirstWordLength.
Hint: use string functions discussed in lecture.
Here is a sample output of first few lines:
InvoiceLineItemDescription FirstWordLength
————————– ——————
Propane-forklift 16
International pkg. 13
International shipment 13
Publishers Marketing 10
DiCicco’s 9
Quarterly Maintenance 9
Telephone (Line 1) 9
…..
…
*/
/*
3. The manager wants to have a report of detail information of all invoice line items
with the desired column title shown below. She wants the items to be listed based on
the AccountNo (a->z) and item with the highest amount within each account is printed
first (see sample output below). In case of same amount value, items should be printed
by their InvoiceID in ascending order.
Finally, all items should be continuously and uniquely ranked from 1 within each account.
—————————————————————————————-
Item Rank Account No Item Amount Invoice ID Item Description Invoice Sequence
—————————————————————————————-
…..
1 570 75.60 12 Kinko’s 2
2 570 58.40 12 Office Max 3
3 570 41.80 74 Coffee 1
1 572 2433.00 56 Card deck 1
2 572 1575.00 47 Catalog ad 1
3 572 600.00 95 Books for research 1
4 572 579.42 98 Catalog ad 1
5 572 9.95 16 Monthly access fee 1
6 572 9.95 23 Monthly access fee 1
1 574 856.92 15 Property Taxes 1
1 580 50.00 12 DiCicco’s 1
1 582 503.20 105 Bronco lease 1
1 589 7125.34 31 Web site design 1
…..
…
*/
—————END—————
