CIS 336 iLab 6 of 7: Oracle Single Row and Group Functions

L A B O V E R V I E W

Scenario/Summary

Lab #6 will introduce the various aspects of the Single-Row and Group Functions available in the Oracle Database. Most functions can be used in either the SELECT statement or the WHERE clause, but more commonly are used in the SELECT. Chapters 5 and 6 cover most all of the normal processing you use in these functions. In some cases, you will be asked to used more than one function within a single SELECT statement.

General Lab Information and Considerations

This lab will utilize the same set of tables found in the script file (LeeBooks.SQL) that were used for Labs 4 and 5.
Each query in the script file you will create must be numbered (use --1 comments for numbering) and in order. The SQL for the following exercises should be written using notepad and run in SQL*Plus. Read each problem carefully and follow the directions as stated.
A Clean Script File:
A script file is meant to be like a program. The file can be run every time the code needs to be executed without having to retype the code again each time. For this reason, it is important that there are no errors in the code inside the file. You can go back and forth between notepad and Oracle when creating your script file to check your queries and verify if they work or not. However, you do not want to create your final output file until after you have verified that everything in your script is correct by running it, in its entirety at least once and viewing the output. Once this has been done, you can create your final output file, with echo to create the document, you can turn in with your lab. Remember in using a spool session, you must type "SPOOL OFF" at the SQL> prompt after your script stops spooling to capture all of your data!
Lab Do's and Don’t's

• Do Not include the LEEBOOKS.SQL as part of your lab script.

• Do use Notepad to write your query script file.

Do Notwrite your queries in Word.

• Do test each query before moving on to the next.

Do Notinclude extra queries for a problem unless the problem explicitly asks for more than one query.

• Do test your queries before creating your final output file.

Do Notturn in a script file that has queries with errors.

• Do number each query using - -1 comment notation.

Do Notstart your query on the same line as the comment.

• Do remember to check your final output and script file for accuracy.

Do Notturn in your lab without first checking your output file to verify that it is correct. Things to keep in mind:
If you are not sure of the table names in your user schema, you can use the following select statement to list them.
SELECT * FROM TAB;
If you want to know the name of the columns in a particular table you can use the following command to list them.
DESC
Making a script file containing a series of describe statements for each table and then spooling the output will give you a listing of all the tables with column names.
Be sure to review and verify your final output when you are finished. Do Not assume anything.
Write queries for each of the stated problems in the steps below that will return a result set of data to satisfy the requirements. When finished, your script file should have a total of 13 queries, and your resulting output file should show both the query and result set for each.


follow the link to get the answer!!!