| Schedule by week | Information to cover |
|---|---|
|
PowerPoint Presentations:
Free PowerPoint viewer for 97,2000,2002 users Note that the speaker notes are provided on a separate Word document for many of the presentations if you are having trouble accessing them! Please check the weekly schedule multiple times a week, I sometimes add things during the course of the week. Please keep copies of all work you submit until you receive your final grade at the end of the semester. | |
| Week #14 Week of December 14th |
This week we will look at triggers (see postings from last week). I will post your final on Monday
so check back. Final Exam Required status sheet |
| Week #14 Week of December 7th |
I am available at 11 on Tuesday and Thursday and 2 on Tuesday and 3 on Thursday in K101. Friday I am
available at 11 in my office and I am staying late (I will be available from 3 to 7). Please submit your evaluation of me Please read the notes and look at the PowerPoint presentations. We will spend the next few classes on these topics. Introduction to procedures Introduction to functions Internal procedures More on internal procedures and functions Still more on procedures and functions Introduction to triggers More on triggers Introduction to PL/SQL Procedures Separate speaker notes for Introduction to PL/SQL Procedures Introduction to PL/SQL Functions Separate speaker notes for Introduction to PL/SQL Functions More on Procedures - Internal Procedures Separate speaker notes for More on Procedures - Internal Procedures Introduction to Triggers Separate speaker notes for Introduction to Triggers More and Still More on Procedures and Functions Separate speaker notes for More and Still More on Procedures and Functions More on Triggers Separate speaker notes for More on Triggers A site to check out: Tutorials Assignments: Procedure, function, trigger assignment |
| Week #13 Week of November 30th |
I am available at 11 on Tuesday and Thursday and 2 on Tuesday and 3 on Thursday in K101. Friday I am
available at 11 in my office and I am staying late (I will be available from 1 to 7). This week we will continue with cursors and then move on to the other things left to cover. Be sure to look at the notes on cursors and the PowerPoints. Also be sure you look at the information about records and tables. There is both a set of notes and a PowerPoint. These are also posted last week: Example 1 using cursors Continuing example 1 using cursors Another set of cursor examples Covers: Cursors in PL/SQL, cursor example and continuation of first cursor example Separate speaker notes for Cursors in PL/SQL, cursor example and continuation of first cursor example Covers: Another set of cursor exampes Separate speaker notes for Another set of cursor examples I would also like you to look at the information on exceptions: Introduction to exceptions Exceptions in PL/SQL Separate speaker notes for Exceptions in PL/SQL Question about storing multiple fields in a table (array) More notes on table/arrays Assignments: I want you to write a program in Oracle that solves a problem you design. You must use multiple cursors. You also need to include two of the following: table, record, exception (your choice of which exception type). Extra credit if you use all three: table, record,exception. Please include a description of the problem you are solving. |
| Week #12 Week of November 23rd |
This Tuesday, I am not giving any lectures. We will have an open lab in K101 from 9 until 2 for catchup. Please try to get comfortable with cursors. Assignments: No new assignments. |
| Week #11 Week of November 16th |
Next Tuesday (Nov 24th) is an open lab day from 9 to 2 in K101. Students from all classes can attend at
any time for help. If you are behind please plan to come for as long as you can!
I will be available for help Tuesday at 11 and 2 in K101, Thursday at 11 and 3 in K101, and Friday at 11 in
my office. I will post any changes as my week unfolds. We will continue looking at cursors this week. (I plan to look at cursors first and then go back to record and table - that is the way I did it last year and it seemed to work well). Record and table types Introduction to cursors PL/SQL User Defined Types - record and table Separate speaker notes for PL/SQL User Defined Types - record and table Be sure to look at the handouts on cursors. Please note that there are Smartboard explanations on cursor examples and PowerPoint presentations. Please read the following notes and watch the slide presentation. Example 1 using cursors Continuing example 1 using cursors Another set of cursor examples Covers: Cursors in PL/SQL, cursor example and continuation of first cursor example Separate speaker notes for Cursors in PL/SQL, cursor example and continuation of first cursor example Covers: Another set of cursor exampes Separate speaker notes for Another set of cursor examples Covers: Cursors in PL/SQL, cursor example and continuation of first cursor example Separate speaker notes for Cursors in PL/SQL, cursor example and continuation of first cursor example I will continue with cursors so please continue to look at the notes and presentations on this topic. Assignments: This ended up being really an inclass assignment. I want everyone to submit the script with the overtime problem and the output so that I know you participated and got things working. We will work on this assignment in class on Tuesday (if you are not there, you must do it on your own). There are three scripts with a .txt, save them and then bring up the Oracle editor and paste in the code and then rename with .sql. Then run createscript.sql or createscript1.sql and also run createtrans.sql in Oracle. Then write the PL/SQL program to read the files using cursors and write out the total hours worked by each employee. Then write a modification to the program that takes salaried workers and divides their salary by 52 and display and take hourly workers and multiply the hours they worked by the pay per hour. Then continuing with modifications, calculate hourly works pay if they make overtime. You should use a base of 40 hours and pay time and a half. createscript.txt createscript1.txt createtrans.txt scripts Extra Credit: Anyone that gets me the cursor problem dealing with pay and overtime by Friday, Nov 20th gets extra credit. Pass in before I do solution in class or post solution if distance learning. Oracle Cursor Assignment Please note you do not need a cursor for #4 when you are creating tables. My mistake in the assignment. |
| Week #10 Week of November 9th |
I will be available for help Tuesday at 11 and 2 in K101, Thursday at 11 and 3 in K101, and Friday at 11 in
my office. I will post any changes as my week unfolds. On Thursday, a representative from Meditech will be speaking to my 9:00 class and my 10:00 class, if anyone is interested they are welcome to come to either session in K101. We will continue with PL/SQL this week. I started with the into PL/SQL and then went and looked at the if statements and loops. Then I went back and looked at the two intro sets of notes. Please make sure you have now covered the PL/SQL through loops. Introduction to loops Introduction to loops Separate speaker notes for Introduction to loops In class we will work on developing a simple PL/SQL program. After loops, we will go back and look at the first few notes on Introduction to PL/SQL and More examples of inner/outer procedures. If time permits we will then look at Data Manipulation in PL/SQL: Data manipulation in PL/SQL - introduction These topics will probably happen next week, but we might make it. Data manipulation in PL/SQL - introduction Separate speaker notes on Data manipulation in PL/SQL - introduction You should now have looked at the examples and presentations up to Record and table types and cursors. Assignments: IF and LOOP assignment |
| Week #9 Assignments Week of November 2nd |
I am available for help on Tuesday at 11 and 2 in K101, Thursday at 11 and 3 in K101, Friday at 11 in my
office. Check back for changes as my week unfolds. We will start looking at PL/SQL this week. We started to look at the first handout in this group on Thursday and Friday for a few moments. Friday we ended up discussing how to run PL/SQL from home because the scripts are not saved on the server. This is being worked on, but for now if you write a script save it on a flash drive so you can make a copy. Start reading the introductory handout on PL/SQL and look at the PowerPoint presentation! We will temporarily skip the second handout and go to the one on if statements and continue from there. first page - we will come back to this later You should also get started on the following: Introduction to IF statements More on PL/SQL IF statements Also please look at the accompany presentations: PL/SQL - Using IF statements Separate speaker notes for PL/SQL - Using IF statements More on PL/SQL IF statements Separate speaker notes for More on PL/SQL IF statements After loops, we will go back and look at the first few notes on Introduction to PL/SQL and More examples of inner/outer procedures. If time permits we will then look at Data Manipulation in PL/SQL: Data manipulation in PL/SQL - introduction These topics will probably happen next week, but we might make it. Data manipulation in PL/SQL - introduction Separate speaker notes on Data manipulation in PL/SQL - introduction You should now have looked at the examples and presentations up to Record and table types and cursors. Assignments: First PL/SQL Quiz |
| Week #8 Assignments Week of October 26th |
I am available Tuesday at 11 and from 2-2:30, Thursday at 11 and 3, Friday at 11 in my office. Continuing the discussion on scripts, please read: Script for input data Read the information on indexes, keys etc. We will start them this week. Please read the following (again this will hopefully be started this week and will be completed next week): Indexes in Oracle - An introduction More on Primary and Foreign keys Notes on key questions More on views Be sure you read about indexes, keys etc in your text as well. Also please look at the accompanying presentations: Indexes in Oracle - An Introduction Separate speaker notes for Indexes in Oracle - An Introduction Primary and Foreign constraints with a relational database Separate speaker notes to accompany primary and foreign constraints with a relational database More on Primary and Foreign Keys also info from questions Separate speaker notes for More on Primary and Foreign Keys etc. More on views Separate speaker notes for More on views Please look at these handouts - they finish up the coverage of SQL. Information on database objects Script for input data Assignments: Oracle keys assignment |
| Week #7 Assignments Week of October 19th |
Carol has got views working on the school installation so if you log in via putty they will work. I found
that I did not have it on my 10g set up so I had to go and find my administrative login (system and then the
password you set up). When I got there, I granted privileges to my scott/tiger that I use at home by saying:
grant create view to scott; and now I can do views in my 10g. I will be in class on Tuesday and available at 11. Thursday and Friday, I am away at a conference. I strongly suggest you come in to work together, support each other, and get caught up. Those of you who are on target, please review the material by supporting others that are behind. We will start looking at indexes this week. Please look at notes and presentations. Assignments: No new assignment this week! Catch up week! |
| Week #6 Assignments Week of October 12th |
This is an alternative if you do not have Oracle successful installed at home. I have sent you an email about using Oracle from home over the internet. In that email, I told you I would post information on using PuTTy and here it is: PuTTY is available in the labs. Two sites that can be used to download PuTTY are: PuTTY PuTTY Here is an example sheet for use when you are creating databases and tables in putty: Using putty to create a database and table This example is about using PuTTy in another class, you are are simply logging in so only pay attention to the part of this handout that shows the initial login. I will put together a more specific handout that I will email to you, but this should help get you started. I am available Thursday at 11 and 3 in K101 and Friday at 11 in my office and also from 3:30 - 6:30 in my office. Please look at the information on table maintenance and then move on to reports and views. Table maintenance revisited (again) Continuation of table maintenance revisited (again) We are going to start these - may not make it through: Introduction to views Introduction to reports These are the accompanying presentations: Table maintenance revisited (again) Separate speaker notes for Table maintenance revisted (again) Continuation of table maintenance revisited (again) Separate speaker notes for Continuation of table maintenance revisted (again) Introduction to views Separate speaker notes for Introduction to views Introduction to Reports Separate speaker notes for Introduction to reports Assignments: Views and reports The good news is that next week will be light! |
| Week #5 Assignments Week of October 5th |
Help/support lab Tuesday and Thursday at 11:00 in K101. I am available Thursday at 3:00 in K101. I am
usually here until around 7:00 on Friday (free starting at 4ish), so let me know if you would
like to see me then. Keep reading along in the book - picking appropriate chapters to read or using it as a reference - you should make sure you have read the information on all of the topics we cover. If you read about a chapter a week you will be in good shape. If you decided to use the web references instead, make sure you are checking out topics there. Please read the notes and handouts on Oracle Joins. We will start subroutines this week. Please read the following information about subroutines: Subqueries More on subqueries You should also look at the presentation on these topics especially the one that accompanies More on subqueries. Subqueries Separate speaker notes for subqueries More on Subqueries Separate speaker notes for More on subqueries Zipped subqeries and order example from last week This is an example that you might find helpful: Examples Solution Please start looking at input variables: Using input variables More on variables with Oracle's SQL*Plus Using Input Variables Separate speaker notes for Using Input Variables More on variables with Oracle's SQL*Plus Separate speaker notes for More on variables with Oracle's SQL*Plus Assignments: Quiz #3 If you have not done so, please go to the blog and introduce yourself and enter any success or questions about installing Oracle at home. Assignment (mostly on subqueries) At school, I believe you can access the emp, dept, and salgrade tables by using the scott/tiger login. I am not sure they are available under your log in, please check and let me know. At home you should have them under thes scott tiget login. Please use the create table ... as to make a copy with your name. Please do not change or modify the emp table under scott. Example of how to create a copy of the donor table so you do not mess up the data in the original table.
SQL> create table testdonor as
2 select * from donor;
Table created.
|
| Week #4 Week of September 28th |
Help/support lab Tuesday at 11:00 in K101 and also at 2:00 in my office. Thursday I am available at
11 and 3 in K101. If you need to see me and these times don't work contact me and we can try to figure
something out. Be sure to look at this presentation: Relational database notes and examples Goes with notes Introduction to multiple tables (relational) - joins - uses donor, donations and drive tables Be sure you read about using JOIN in your textbook or online and also look at the handout: Oracle Join Multiple tables using join Note that we started to look at subroutines on Friday. Check the first set of notes and the first presentation. Assignments: Quiz #2 (if you are in class, we will devote some time to it on Tuesday) For inclass discussion All students should layout a design and send it in for check off only (I suggest doing this after I have done the discussion in class and posted it). |
| Week #3 Week of September 21st |
If you have the text, I want you to use it as reference - read from the text or from an online resource the
material we are covering in class. I go through the course in the order that it was given at Oracle
so I am not totally in sync with the book. Information on what we have looked at is in the first 6 chapters.
Be sure you do some checking on handling dates either in the text or online. Extra help time is being scheduled. I will be around in K101 on Tuesday and Thursday at 11 and on Thursday at 3 hopefully. help! Friday I will be available for a while after class. We are going to start the week by looking at the theory of relational databases. Please read information about third normal form and relational databases. I have put a variety of links to sources on the Web on the site under links and links to my site are listed below. Please read the handout on definitions: Normalization definitions Please read the two examples - the first two postings under notes. Also view the slide presentation on normalization information about relational databases: Normalization - information on third normal form Separate speaker notes for normalization presentation Relational database information - information on 1 to 1, 1 to M, and M to M Separate speaker notes for relational database information Other examples can be found at my web site under Notes and handouts - Normalization and Relational Database (scroll down to the bottom of the notes and handouts screen to find them). We are going to start looking at relating tables, we will continue this next week: Introduction to multiple tables (relational) - joins Relational database examples Relational database notes and examples Goes with notes Introduction to multiple tables (relational) - joins - uses donor, donations and drive tables Separate speaker notes for relational database presentation Relational database examples - order system Separate speaker notes for relational database examples - order system Student system relational database example Separate speaker notes for student system relational database example Be sure to read about joins in your text book, we will look at them next week. Assignments: In class project. Distance learning students should work with it as well and send me their solution notes. Oil database to design Implement the oil design See the Smartboard notes for Thursday which go over design ideas. I am looking for a very basic structure of three tables for this implementation (customer, house type, delivery (delivery should have a two part key - even though we are not putting in keys right now)). |
| Week #2 Week of September 14th |
Extra help time is being scheduled. I will be around in K101 on Tuesday and Thursday at 11 and on Thursday at 3. (unless the room schedule changes
and then I will be in my office).There is another posting of notes on installation and comments at the blog as well. Hopefully these will
help! I need you to take a look at this handout (there is an accompanying PowerPoint). We will not spend a lot of time on this in class, but it goes into some editing techniques you can use. Working with SQL These are the notes that I will attempt to get through this week. More on maintaining an SQL table SQL functions More SQL functions Group SQL functions I may go further, so do check back. You should also look at the accompanying PowerPoint presentations: More on maintaining an SQL table Separate speaker notes for More on maintaining an SQL table SQL functions - part 1 character Separate speaker notes for SQL functions - part 1 characters SQL functions - part 2 numeric & date Separate speaker notes for SQL functions - part 2 numeric & date More SQL functions (includes DECODE, ways to edit SQL code and nested functions) Separate speaker notes for more SQL functions Group functions Separate speaker notes for group functions Assignments: Oracle create, maintain and query assignment Oracle quiz This will be started in class by inclass students - distance students should do at home - I am interested in having you play computer - I do not want you to use the computer to get the results. You can check your results if you want to, but please do it on your own. |
| Week #1 Week of September 7th |
I recommend the text in the syl, but it is not required. There is enough material on the web and in
my notes that I decided to make the book a recommendation. Please note that many students have found
it useful to have a copy of the text. Read chapter #1 in the text if you have it The topic is database concepts. You should read and
explore the topics that are being covered in class. You should take this time to get a copy of Oracle if you are working on a computer outside the labs. You can use 8, 8i, 9i or 10g. In our labs we have 9i and 10g. 10g is in K101 and the main lab in the middle of K, 9i is in the other K labs. Note that the host is orcl10g on the machines running 10g. I believe I gave you the wrong information in my email. You need to download Oracle if you plan to use it at home. Downloading I have also post information about downloading at the site. This is the version that I downloaded when I downloaded version 10g: Oracle Database 10g Release 2 (10.2.0.1.0) for Microsoft Windows. Be very careful when you install, Oracle leaves a lot of footprints that makes reinstalling very difficult. You will be given a username/password for using Oracle at BCC. The default Oracle username and password is scott/tiger. These are the notes that we are covering this week: Introduction to SQL in Oracle Create a table in SQL SQL Conditions These are the accompanying presentations: Presentations: Introduction to SQL in Oracle Separate speaker notes for Introduction to SQL in Oracle Create a table in SQL Separate speaker notes for Create a table in SQL SQL Conditions Separate speaker notes for SQL Conditions Assignments: Oracle assignment on introductory material (html) Oracle assignment on introductory material (doc) Comment on doing the assignment: You should take the commands you do in SQL and the results and paste them into a word document. It should be similiar to the way I prepared the notes. You then send me the word document. |