ANSWER THE LAST 4 PLEASE !
- Now create a view over the LEDGER table called LEDGER_SALES. The query for this view will provide a summary of data from the LEDGER that is only ACTION = BOUGHT rows, grouped by PERSON and ACTIONDATE, and show the sum of AMOUNT in a new column called TOT_AMT. This view will have 3 columns: PERSON, ACTIONDATE, TOT_AMT. (15) Useful tip: put a DROP VIEW statement before this step so that subsequent runs of your script will not show an error.
DROP VIEW LEDGER_SALES;
CREATE VIEW LEDGER_SALES AS
SELECT PERSON, ACTIONDATE, SUM(AMOUNT) AS TOT_AMOUNT
FROM LEDGER
WHERE ACTION =BOUGHT GROUP BY PERSON, ACTIONDATE;
- Write a query to show the minimum, maximum, and average total amount of a sale (LEDGER_SALES.TOT_AMT). (10)
4. Write a query to show which customers (PERSON) had repeat businessmore than one row appearing in the LEDGER_SALES view. (15)
5. Show the workers who are also customersthey have BOUGHT an item as recorded in the LEDGER table. Show the worker names, the total amount of all items they bought and order the query results by name. Write this query 3 different ways:
5.1. Use a regular join in the WHERE clause. (5)
5.2. Use a subquery and the IN keyword. (5)
5.3. Use a subquery and the EXISTS operator. (5)
6. Same as previous question, but also show all workers (use an outer join) and for those workers who never bought anything, print never bought in place of the total amount column (use the DECODE function and test for NULL). (15)
7. Show all workers name, lodging, and age of those who do not have at least one good, excellent, or average skill in the WORKERSKILL table. Order the query results by name. Write this query 2 different ways:
7.1. Use a NOT IN operator. (5)
7.2. Use an outer join. (5)
8. Using only the ACTION=SOLD rows in LEDGER, write a query that shows PERSON, MONTH (ACTIONDATEs month), and TOT_AMT (SUM(QUANTITY*RATE)). Show subtotals for PERSON, MONTH using ROLLUP. On the subtotal lines use the GROUPING and DECODE to show All persons, All months for their respective subtotal lines. (15)
LEDGER TABLE
come Page Columns Data Model Constraints Grants Statistics | Triggers Flashback Dependencies Details |Partitions | Indexes |SQL W X Sort.. Filter: ACTIONDATE ACTION ITEM QUANTITY QUANTITYTYPE RATE AMOUNT PERSON 1 03-JAN-01 BOUGHT HOMINY 1 BUSHEL 1.25 1.25 GENERAL STORE 2 04-JAN-01 PAID WORK 1 DAY 1 1 GERHARDT KENTGEN 3 09-JAN-01 BOUGHT LICE KILLER 1 BOX 0.5 0.5 GENERAL STORE 4 11-JAN-01 BOUGHT MENDING BRACE 1 EACH 0.15 0.15 GENERAL STORE 5 11-JAN-01 BOUGHT STOVE BLACKING 1 BOX 0.05 0.05 GENERAL STORE 6 12-JAN-01 PAID WORK 1 DAY 1 1 GEORGE OSCAR 7 14-JAN-01 BOUGHT LANTERN GLOBE 1 EACH 0.1 0.1 GENERAL STORE 8 14-JAN-01 SOLD BEEF HIDE 1 EACH 5.46 5.46 GENERAL STORE 9 14-JAN-01 SOLD CHEESE FLAT 13 EACH 3.15 40.95 GENERAL STORE 10 15-JAN-01 BOUGHT STAMP FOR LETTER 1 EACH 0.02 0.02 POST OFFICE 11 15-JAN-01 BOUGHT STOCKING 2 PAIR 0.15 0.3 GENERAL STORE 12 16-JAN-01 BOUGHT OIL 4 GALLON 0.1 0.4 GENERAL STORE 13 16-JAN-01 BOUGHT MOLASSES 1 GALLON 0.6 0.6 GENERAL STORE 14 16-JAN-01 BOUGHT CARD OF THANKS 1 EACH 0.3 0.3 GENERAL STORE 15 16-JAN-01 BOUGHT SUGAR 25 LB 0.07 1.75 GENERAL STORE 16 17-JAN-01 BOUGHT HORSE SHODDING 1 EACH 0.85 0.85 LIVERY 17 17-JAN-01 BOUGHT CORN 230 LB 0.01 2.3 FEED STORE 18 18-JAN-01 BOUGHT CORN MEAL 213 LB 0.01 2.13 FEED STORE 19 18-JAN-01 BOUGHT PAPER 50 SHEETS 0.01 0.5 GENERAL STORE 20 18-JAN-01 BOUGHT COFFEE 1 LB 0.3 0.3 GENERAL STORE 21 18-JAN-01 BOUGHT COTTON STOCKING 3 PAIR 0.08 0.24 GENERAL STORE 22 18-JAN-01 BOUGHT SEEDED RAISINS 1 LB 0.12 0.12 GENERAL STORE 23 19-JAN-01 BOUGHT COTTON STOCKING 3 PAIR 0.08 0.24 GENERAL STORE 24 19-JAN-01 BOUGHT SALT PETER 1 TABLET 0.08 0.08 GENERAL STORE 25 19-JAN-01 BOUGHT CREOAL 2 QUART 0.37 0.74 GENERAL STORE Yes Welcome Page LEDGER X Columns Data Model Constraints Grants Statistics Triggers |Flashback |Dependencies Details |Partitions | Indexes SQL Actions... COLUMN_NAME DATA_TYPE NULLABLE DATA_DEFAULT COLUMN_ID COMMENTS 1 ACTIONDATE DATE Yes (null) 1 (null) 2 ACTION VARCHAR2 (8 BYTE) (null) 2 (null) 3 ITEM VARCHAR2 (30 BYTE) Yes (null) 3 (null) 4 QUANTITY (null) 4 (null) 5 QUANTITYTYPE VARCHAR2(10 BYTE) Yes (null) 5 (null) 6 RATE (null) 6 (null) 7 AMOUNT NUMBER (9,2) (null) 7 (null) 8 PERSON VARCHAR2(25 BYTE) Yes (null) 8 (null) NUMBER Yes NUMBER Yes Yes come Page Columns Data Model Constraints Grants Statistics | Triggers Flashback Dependencies Details |Partitions | Indexes |SQL W X Sort.. Filter: ACTIONDATE ACTION ITEM QUANTITY QUANTITYTYPE RATE AMOUNT PERSON 1 03-JAN-01 BOUGHT HOMINY 1 BUSHEL 1.25 1.25 GENERAL STORE 2 04-JAN-01 PAID WORK 1 DAY 1 1 GERHARDT KENTGEN 3 09-JAN-01 BOUGHT LICE KILLER 1 BOX 0.5 0.5 GENERAL STORE 4 11-JAN-01 BOUGHT MENDING BRACE 1 EACH 0.15 0.15 GENERAL STORE 5 11-JAN-01 BOUGHT STOVE BLACKING 1 BOX 0.05 0.05 GENERAL STORE 6 12-JAN-01 PAID WORK 1 DAY 1 1 GEORGE OSCAR 7 14-JAN-01 BOUGHT LANTERN GLOBE 1 EACH 0.1 0.1 GENERAL STORE 8 14-JAN-01 SOLD BEEF HIDE 1 EACH 5.46 5.46 GENERAL STORE 9 14-JAN-01 SOLD CHEESE FLAT 13 EACH 3.15 40.95 GENERAL STORE 10 15-JAN-01 BOUGHT STAMP FOR LETTER 1 EACH 0.02 0.02 POST OFFICE 11 15-JAN-01 BOUGHT STOCKING 2 PAIR 0.15 0.3 GENERAL STORE 12 16-JAN-01 BOUGHT OIL 4 GALLON 0.1 0.4 GENERAL STORE 13 16-JAN-01 BOUGHT MOLASSES 1 GALLON 0.6 0.6 GENERAL STORE 14 16-JAN-01 BOUGHT CARD OF THANKS 1 EACH 0.3 0.3 GENERAL STORE 15 16-JAN-01 BOUGHT SUGAR 25 LB 0.07 1.75 GENERAL STORE 16 17-JAN-01 BOUGHT HORSE SHODDING 1 EACH 0.85 0.85 LIVERY 17 17-JAN-01 BOUGHT CORN 230 LB 0.01 2.3 FEED STORE 18 18-JAN-01 BOUGHT CORN MEAL 213 LB 0.01 2.13 FEED STORE 19 18-JAN-01 BOUGHT PAPER 50 SHEETS 0.01 0.5 GENERAL STORE 20 18-JAN-01 BOUGHT COFFEE 1 LB 0.3 0.3 GENERAL STORE 21 18-JAN-01 BOUGHT COTTON STOCKING 3 PAIR 0.08 0.24 GENERAL STORE 22 18-JAN-01 BOUGHT SEEDED RAISINS 1 LB 0.12 0.12 GENERAL STORE 23 19-JAN-01 BOUGHT COTTON STOCKING 3 PAIR 0.08 0.24 GENERAL STORE 24 19-JAN-01 BOUGHT SALT PETER 1 TABLET 0.08 0.08 GENERAL STORE 25 19-JAN-01 BOUGHT CREOAL 2 QUART 0.37 0.74 GENERAL STORE Yes Welcome Page LEDGER X Columns Data Model Constraints Grants Statistics Triggers |Flashback |Dependencies Details |Partitions | Indexes SQL Actions... COLUMN_NAME DATA_TYPE NULLABLE DATA_DEFAULT COLUMN_ID COMMENTS 1 ACTIONDATE DATE Yes (null) 1 (null) 2 ACTION VARCHAR2 (8 BYTE) (null) 2 (null) 3 ITEM VARCHAR2 (30 BYTE) Yes (null) 3 (null) 4 QUANTITY (null) 4 (null) 5 QUANTITYTYPE VARCHAR2(10 BYTE) Yes (null) 5 (null) 6 RATE (null) 6 (null) 7 AMOUNT NUMBER (9,2) (null) 7 (null) 8 PERSON VARCHAR2(25 BYTE) Yes (null) 8 (null) NUMBER Yes NUMBER Yes Yes
