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