Lab 5: 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.

Due for Lab 5

Turn in the SQL file saved after the steps of this exercise in a script file and the output produced by this script. Upload these two files to an Oncourse Drop Box by 11:55 PM on its due date.

 

Lab Assignment Steps

1.      Write a query to show the distinct occurences of ACTION in LEDGER.

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.

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

 

4.      Write a query to show which customers (PERSON) had “repeat business”—more than one row appearing in the LEDGER_SALES view.

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 an equi-join (regular join on common columns in the WHERE clause.)

5.2.            Use a subquery and the IN keyword.

5.3.            Use a subquery and the EXISTS operator.

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).  Write this query in two ways:

6.1.            Use an outer join implemented with the (+) operator in the WHERE clause.

6.2.            Use an outer join implemented in the FROM clause.

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.

7.2.            Use an outer join.

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. Use SQLPLUS commands to format TOT_AMT column as 990.00 and ensure that pagesize is at least 46 lines.