Objectives

After completing this lab you should be able to:

Use the DISTINCT keyword

Create a view.

Write subqueries using the IN or EXISTS.

Use an outer join.

Write queries with GROUP BY and HAVING clauses.

Use ROLLUP and DECODE/GROUPING.

Description:

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)

Submission: