| 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. | |
| Unit #15 | The final is due by the 22nd of August. You must pass in both the final and the required status sheet. Final Exam Required status sheet |
| Unit #14 | We will go over the notes on procedures, functions, and triggers this week - please check notes and presentations. The 15th unit will be the final. Assignments: Procedure, function, trigger assignment |
| Unit #13 |
Script to create emp table (createemp.sql) Check the data before you use it to make sure I keyed it in correctly. We will continue with cursors. We looked at exceptions this unit and we also looked at procedures. We will move on to functions, more procedures and triggers next unit. 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 exceptions and either table or record. (your choice of which exception type). Extra credit if you use all three: table, record, exception (and multiple exception types). Please include a description of the problem you are solving. |
| Unit #12 |
We will continue with cursors. Assignments: First Problem:Write a PL/SQL program to accomplish the following: Using the sample emp database, give everyone in dept 10 a 5% raise, everyone in dept 20 a 7.5% raise, everyone in department dept 30 a 10% raise. Do this using a simple IF...ELSE and a loop. Second Problem:Modify the problem in #1 to meet the following criteria: IF dept is 10 and the salary is greater than 2000 give a 6% raise otherwise give a 7% raise. IF dept is 20 and the salary is greater than 2500 give a 5% raise otherwise give a 5.5% raise. IF dept is 30 and the salary is greater than 1000 give a 7% raise otherwise give a 6.5% raise. Third Problem: Set up an inventory table and a transaction table that has sales, returns, and purchases (the transaction table should have a code with S for sales, R for returns and P for purchases). Create scripts to create and insert data into these tables. Fourth Problem: Using the tables you created in the fourth problem, process the transactions and determine the impact on inventory. Display information that gives the original inventory and the inventory after the sales, returns and purchases have been processed. |
| Unit #11 | We will continue with PL/SQL, check back. Assignments: Extra credit that must be passed in before class on Thursday, Nov 17th. Go to coursorexampl1.html and take the program that uses two cursors and an inner and outer loop and change the LOOP...EXIT structures to WHILE structures. We will start working on this in class and see how it goes. These scripts will create two tables (two versions of the first one): createscript.txt createscript1.txt createtrans.txt scripts 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. |
| Unit #10 | Look at my notes on lopps in PL/SQL. Assignments: First: Write the progam to find odd numbers using a FOR loop. Find the odd numbers from 1 to the maximum number (use user input to get the maximum number). Display the odd numbers and display them. Then sum the odd numbers and display the total. We worked on this in class. Second: Redo the program using a while loop. Third: Redo the program using a loop and exit. Fourth: Enter three numbers (the rule is that they cannot be the same). Determine which number is biggest. Don't forget to finish this one as well (copied from last week): Oracle experiment to do |
| Unit #9 | We are starting with PL/SQL this week. Using Putty with PL/SQL: Login to the cisweb server with putty, then run 'sqlplus.sh' it will ask for the Oracle username and password and start sqlplus. When you run 'edit scriptname' it will create the script file and open it in the nano editor. The files are saved in the home directory of the cisweb user so you don't lose them when you log out of Oracle or the cisweb server. For those comfortable with the Linux shell, you can also just edit the scripts from the Linux shell with 'nano scriptname.sql'. This week, I am going to look at The Introduction to PL/SQL (the beginning), skip for a while the More examples of input/output procedures (we will go back to them) and work on Introduction to IF statements. If you have problems contact me or contact Eric Oldford in the lab. Which brings me to a question. Are you all working from home or are some of you using the lab. 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 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. Assignments: Oracle experiment Oracle PL/SQL Intro Quiz |
| Unit #8 |
Please read the following, we started to look at indexes a little last unit: 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 Oracle Key Example A script to create the donation table, you can use the same technique to create the drive table if you do not have them. Create donation script Assignments: Oracle keys assignment |
| Unit #7 |
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 especially since there is only one day this week. 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 Next we will start to look at indexes and keys. Check back to see where we are. Assignments: Views and Reports |
| Unit #6 |
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 Look at maintenance in the notes below variables (there are two sets of notes on maintenance there). You should also be checking the text or information at Oracle or other sites regularly to supplement. Assignments: We will make this a catch up unit! |
| Unit #5 |
Be sure you read about using JOIN in your textbook or online and also look at the handout:
Oracle Join Multiple tables using join 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 Assignments: Quiz #3 Multiple tables and subqueries |
| Unit #4 | Again let me stress that you should work at your own pace but you need to finish 15 units by the end of the summer. I am posting the units
every couple of days to give you the flexibility to plan your work. Do not feel pressured to do the units as they are posted. Start with unit #1
and move forward at a pace that works for you. I recommend a minimum of one unit per week and clearly some weeks you will have to do two in
order to finish by the end of the summer semester. This unit we will look at using multiple tables and how to link them and also take another look at database design. We are going to start looking at relating tables: 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. Assignments: Quiz #2 Oil database to design Before you implement, I suggest you send me the design and ask for feedback. Implement the oil design |
| Unit #3 | If you are following the audio and Smartboard, in the last unit I skipped ahead and looked at the first maintain listed below and I also looked at the others with maintain in their title a little further down the list. 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 If you decide to use 11g Express, this shows the steps to get started: Getting started using 11g Express Assignments: Oracle create, maintain and query assignment Oracle quiz 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. |
| Unit #2 | If you have any questions, please be sure to email me! I am putting up the first few units at a fairly fast pace to give you the flexibility to do things on your own schedule. There will be a total of 15 units so you need to schedule yourself to make this work. Be sure you look at the Smartboard notes and listen to the audio under Fall 2011 - this is essentially the same course. These are the notes that we are covered at the beginning of the 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 Note: You can use the committ command to make sure your work gets saved. We will move ahead in the notes at the end of the week. If you decide to use 11g Express, this shows the steps to get started: Getting started using 11g Express 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. I prefer that you copy and paste the actual text rather then taking pictures of screens - they tend to be hard to read. |
| Unit #1 | Since the summer has less weeks than the other semesters, I use units in the summer. You will have the same 15 units that I
give every other semester and you will have to decide how to handle the extras, I will start posting them this week to give
you the flexibility to get through them at a pace and on a schedule that works for both of us. 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. When this course is held in class, I record the lecture and capture the Smartboard notes. I did this in Fall 2011 and I suggest you use those Smartboard notes and audio recordings to see what happened in each class as we went through the material. 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 10g. Note that the host is orcl10g on the machines running 10g. (I am currently checking to make sure that locations and login information has not changed.) I have not worked with 11 but I am assuming as long as you can get access to the SQL prompt it will work as well. Access to the SQL prompt is important. 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. We are going to spend this week looking at the design of a database and the theory of relational databases. Note you should look at the theory and the design. We will implement in a few weeks and then you can revisit and focus on the Oracle. This will give you some time to download. Your password also will give you the ability to login to the Oracle on campus through putty although having it installed at home will certainly be an easier approach. 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. For example: Normalization 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 I have sent you your Oracle login information (to your BCC account). Let me know if you did not get it. How to login into the Oracle at BCC from home I will spend a little time with these handouts and continue with them next 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 Putty: Here is the information about putty that I promised. 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. Assignments: The design assignment below is a check off assignment that I would like sent to me and we can go back and forth on the design. Design assignment Try to either get Oracle installed or use putty to access in at BCC. You want to make sure that you can get access to some version somewhere! |