Lesson 18

The information in this module is key to creating reports and is central to completing the course project.
You will use functions in many, many of your reports and data warehousing queries.
In the next module, you will pull all this information together to complete the course project. You will see how all these techniques can be applied to query and retrieve more meaningful information from your database, information that can quickly be turned into reports which provide good analysis tools for you and your users.

NEXT_DAY finds the name of the first day of the week that is equal to or later than another specified date. For example, to send a report on the Friday following the first day of each event, you would type

SQL> SELECT STARTDATE,
2 NEXT_DAY(STARTDATE, 'FRIDAY')
3 FROM PROJECT;

STARTDATE NEXT_DAY(
--------- ---------
01-APR-2015 07-APR-2015
02-APR-2015 07-APR-2015
15-MAY-2015 19-MAY-2015
01-JUN-2015 02-JUN-2015
01-JUL-2015 07-JUL-2015
03-SEP-2015 08-SEP-2015

Now think about most of the database users all trying to update records, add new records, delete records, and report on the last month's data at once, and you can see that a small handful of records is often used many times by many users at any given time. Which records are being used changes as the data ages and new claims or orders are processed, but the fact remains that with all of this emphasis on a small percentage of the data, collisions are virtually inevitable. It is critical, therefore, that the DBMS be capable of ensuring that such collisions are handled so that data loss does not occur. For exactly this reason, you use transactions.
The information that follows comes with a huge caveat: Virtually everything that follows varies depending on your specific implementation. Transaction and locking implementation is very specific to a particular vendor's product and the details vary, sometimes immensely. The best you can expect from this book is to get a feeling for what is possible, and then you should read the documentation for the DBMS you are working with for the exact details of how that product works.