After Completing This Lab You Should Be Able To

CSCI N311

Project 4: SQL Group Expressions and Functions

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:

1. Write a query to show the distinct occurrences of ACTION in LEDGER. (5’)

2. 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.

3. 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 business”—more than one row appearing in the LEDGER_SALES view. (15’)

5. Show the workers who are also customers—they 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 worker’s 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 (ACTIONDATE’s 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:

Save your scripts into a .sql file and paste the screen shot into a Word document

Submit the script file and the Word document through Canvas.