Oracle and SQL (CIS150/50)

Weekly Schedule

Please do not change your linux or Oracle passwords.

Essentially there are three ways for you to be able to work with Oracle:
1) Download the full version of Oracle and install it on your computer and work locally - that is what I have done
2) Work in the BCC labs with the BCC server installed Oracle
3) Access the BCC server installed Oracle from home using putty - there are instructions for that by Eric at my site.

Remember, all assignments including inclass assignments must be done by everyone - that includes online students and students who missed the class.. Play computer means to figure out the results yourself by evaluating the data rather than running the query. When you are asked to write code you should also show the results that the code generated unless specifically told not to.

Some students have encountered problems in putty because something has expired. If you encounter a problem, send me an email and I will ask IT to fix it.

Remember, I am doing the same course I did in the Fall so you can listen to the audio and look at the Smartboards for each class and it will match.
If you get an assignment back without a grade you need to fix it and resubmit to get a grade.

If you contact me and we work out a plan, I will accept a few things over the weekend of May 11th and May 12th.

Check back!
Schedule by weekInformation to cover
  Note that the audio and Smartboard presentations for each class are posted within a couple of days.
Please check the weekly schedule multiple times a week, I sometimes add things during the course of the week. You need to pass in the code you write and the output that is produced unless you are told otherwise. Sometimes this involves showing data that was written to a file/table. Please keep copies of all work you submit until you receive your final grade at the end of the semester.
Comment on how I want 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 do not want you to show the screen itself - they are really hard to read!
Final Exam The final is a take home final due at 12:00 noon on Thursday, May 16th. Late finals will be penalized.
FINAL EXAM: Final Exam
REQUIRED STATUS SHEET: Required status sheet that must be turned in with the final.
Please email me with any questions.
Week #14/#15
Week of April 29th/May 6th
Tuesday will be the last day of new material classes. Thursday will be an open lab. Next week will also have open labs on Monday, Tuesday and Thursday.
Thursday, May 2nd will be an open lab from 9:30 until 6. I will be there most of the time as will Lindsey.
Monday, May 6th will have an open lab from 11:00 until 4:00 with me the first part and Lindsey after 2.
Tuesday, May 7th will have an open lab from 9:30 until 3:00 with me.
Thursday, May 9th will have an open lab from 9:30 until 6:00 with me there until 3:00 and Lindsey until 6:00.
Dave will be available 4:00 until 9:00 on Tuesday, May 7th and Wednesday, May 8th.
Notes on check digits
Assignments:
Write code to take in an id and determine if the check digit is correct - use the notes on check digits posted this week to see the logic.
Check back!
Week #13
Week of April 22nd
Thursday I will not be in class. On Thursday, Lindsey will be available from 11:00 to 12:15 and then again from 2:00 - 6:00. The lab will be open for 8:30 so please use Thursday as a catch up date. I am available Mondayat 10 in K112 and Tuesday (when I am not doing advisement) from 2:00 to 3:30. Lindsey is available 2-4 on Monday and Thursday 2-6. Dave will be available 4:00 - 7:00 on Wed (and he will stay later if someone requests it from him or me) and 10:30 -2:30 on Saturday. Jayson (from the tutoring center) is available 9-12 on Mon and Tues in New Bedford.
Donor example with 1 cursor
More on internal procedures and functions
Still more on procedures and functions
Introduction to triggers
More on triggers
Notes on check digits
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
Assignments:
Assignment on procedures, functions and triggers
Week #12
Week of April 15th
I will be around Tuesday and Thursday after my classes. Lindsey is available 2-4 on Monday and Thursday 2-6. Dave will be available 4:00 - 7:00 on Tues and Wed and 10:30 -2:30 on Saturday.
. Example we will look at:
type examples
We will look at types and then move on to procedures and functions.
Here are notes on functions, procedures.
Introduction to procedures
Introduction to functions
Internal procedures
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
Catch up time! Be sure you see the exception assignment and notes under week #11 - they were late going up.
Week #11
Week of April 8th
I am available at 10 in K112. I will be around Tuesday after class. Lindsey is available 2-4 on Monday and Thursday 2-6. Dave will be available 4:00 - 7:00 on Tues and Wed and 10:30 -2:30 on Saturday. I will be available at 2 on Tuesday (but double check). Jayson (from the tutoring center) is available 9-12 on Mon and Tues in New Bedford.
On Tuesday at 11:00 in K101, a group of CIS graduates who are working in IT will come to class to talk about their work and answer questions. You are invited to attend.
The last day for student withdrawal is April 11th. Let me know if you have questions.
Advisement - please sign up!
More cursors... Then exceptions.
Introduction to exceptions
Exceptions in PL/SQL
      Separate speaker notes for Exceptions in PL/SQL
Assignments:
Cursor/exception assignment
Week #10
Week of April 1st
I am available at 10 in K112. I will be around Tuesday after class. Lindsey is available 2-4 on Monday and Thursday 2-6. Dave will be available 4:00 - 7:00 on Tues and Wed and 10:30 -2:30 on Saturday. I will be available at 2 on Tuesday (but double check). Jayson (from the tutoring center) is available 9-12 on Mon and Tues in New Bedford.
Continuing with cursor discussion we started last week.
We will continue with cursors and then back up to see some the the things under Notes and handouts that we did not cover yet.
Continuing example 1 using cursors
Another set of cursor examples
Covers: Another set of cursor exampes
      Separate speaker notes for Another set of cursor examples
Examples we are going over in class:
payprog.sql
payprog1.sql
payprog2.sql
data.txt Data layout for studentchk.sql
studentchk.sql
createdonor.sql Script to create donor
donorcursor1.sql
donorcursor2.sql
donorcursor3.sql
decodecursor1.sql
Assignments:
In class we will start a cursor program. I want you to write a script that will handle the inven table and the department table. The script should accumulate the onhand for departments and write to a new table where you will store the dept, the department name and the total of the onhand. I would also like you to use the dbms lines to show the processing that happens. Everyone needs to turn in this script including the dbms output and a listing of what is on the new table. Please pass in by Thursday since I am going over it in class.
Next assignment: Use cursors to solve these problems.
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. You need to use cursors.
Week #9
Week of March 25th
I am available at 10 in K112. I will be around Tuesday after class. Lindsey is available 2-4 on Monday and Thursday 2-6. Dave will be available 4:00 - 7:00 on Tues and Wed and 10:30 -2:30 on Saturday. I will be available at 2 on Tuesday (but double check). Jayson (from the tutoring center) is available 9-12 on Mon and Tues in New Bedford.
We will look at the loops and data manipulation in PL/SQL that are listed under last week. Then we will continue on with PL/SQL.
Returned to finish this handout: Introduction to PL/SQL Note the accompanying Powerpoint is listed on week #8.
Then started cursors:
Introduction to Cursors
Example 1 using cursors
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
Assignments:
Oracle Loop Assignment
Oracle PL/SQL Intro Quiz
Week #8
Week of March 18th
Thursday is an open lab day from 9:30 or 10:00 until 6 in K101. Come for as many hours as you can and work to get caught up with Lindsey or myself to answer questions.
I am available at 10 on Monday in my office K112 and Tues at 2:00 in K101/K112. Lindsey is available 2-4 on Monday and 10-12:30 and 2-6 on Thursday. Dave will be available Tues and Wed from 4-7 and on Saturday from 10:30 to 1:00. Jayson (from the tutoring center) is available 9-12 on Mon and Tues in New Bedford.
We will start PL/SQL this week.
This week, I am going to look at The Introduction to PL/SQL (the beginning part), skip for a while the More examples of input/output procedures (we will go back to them) and work on Introduction to IF statements. The things below will probably be partially done and the rest we will look at next week.
Introduction to PL/SQL
Introduction to IF statements
More on PL/SQL IF statements
Introduction to loops
Data manipulation in PL/SQL - introduction
With the accompanying PowerPoint presentations:
Introduction to PL/SQL
      Separate speaker notes for Introduction to PL/SQL
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
Introduction to loops
Introduction to loops
      Separate speaker notes for Introduction to loops
Data manipulation in PL/SQL - introduction
      Separate speaker notes on Data manipulation in PL/SQL - introduction
Assignments:
IF assignment
Vacation
Week of March 11th
Dave is available for help on Tues and Wed from 4-7 and Sat from 10:30 to 2:30. He will probably be in K101 or K118 but if not, the help desk will know where he is.
Week #7
Week of March 4th
I may be available Monday at 10 (depends on snow). Lindsey is available 2-4 on Monday and Thursday 2-6. Dave will be available 4:30 - 7:30 on Tues and Wed and 10:30 -2:30 on Saturday. I will be available at 2 on Tuesday (but double check). Jayson (from the tutoring center) is available 9-12 on Mon and Tues in New Bedford.
My goal is to finish the notes on SQL next week and then start on PL/SQL.
This week we will continue looking at indexes and other constraints including keys.
Please read the following on indexes, constraints and keys:
Indexes in Oracle - An introduction
More on Primary and Foreign keys
Notes on key questions
More on views (we already started to look at this)
Information on database objects
Script for input data
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 Sorry, forgot I had this.
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
Interesting SQL/Oracle Article
We looked at the two very simple and basic designs for the assignment. If you are interested in more informtion about database development, this free text looks good:
Database Development Life Cycle
Assignments:
Key, index, constraint assignment
Check back!
Week #6
Week of February 25th
I am available at 10 on Monday in my office K112 and on Tues and Thurs at 2 in either K101 or K112.. Lindsey is available 2-4 on Monday and 2-6 on Thursday. Dave will try to be available on Saturday from 10:30 to 2:30 on Saturday (he has some family health problems). Jayson (from the tutoring center) is available 9-12 on Mon and Tues in New Bedford.
We did not do reports last week so we will look at those first.
Please read the following on indexes, constraints and keys:
Indexes in Oracle - An introduction
Also please look at the accompanying presentations:
Indexes in Oracle - An Introduction
      Separate speaker notes for Indexes in Oracle - An Introduction
Assignments:
Views and Reports
Oracle Key Example(this is a check off assignment) I want you to go through this example and do what I did so you follow and understand the concepts.
Week #5
Week of February 18th
I am available at 2 on Tues and Thurs. Lindsey is available 2-4 on Wednesday (Monday classes) and 3-6 on Thursday. Dave is available 4-7 on Tues and Wed and 10:30 to 2:30 on Saturday. Jayson (from the tutoring center) is available 9-12 on Mon and Tues in New Bedford.
This is an example that you might find helpful:
Examples Solution
A few more examples
We started looking at variables and got through Using Input Variables and some of more on variables.
Using input variables
More on variables with Oracle's SQL*Plus
Table maintenance revisited (again)
Continuation of table maintenance revisited (again)
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
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)
There was a qusestion about equijoin and looking at my notes, I could have done better. Try these!
Equijoin notes
Nonequijoin notes
Now we are going to move on to views and reports and more on scripts.
Introduction to views
Introduction to reports
Introduction to views
      Separate speaker notes for Introduction to views
Introduction to Reports
      Separate speaker notes for Introduction to reports
Resource on reports
Scripts: To create a script use edit and the name of the script (.sql is added) and to run use @ scriptname.
createdonor.sql Script to create donor
Insert data into maintain table
Insert data into maintain table ver 2
Assignments:
Subquery assignment
Note you will need to create the inventory table shown in the assignment.
Check back!
Week #4
Week of February 11th
I am available at 10 on Monday and 2 on Tues and Thurs. Lindsey is available 2-4 on Monday and 3-6 on Thursday. Dave is available 4-7 on Tues and Wed and 10:30 to 2:30 on Saturday. Jayson (from the tutoring center) is available 9-12 on Mon and Tues I strongly sugggest you set up some study groups and meet when Lindsey or Dave are available to answer questions.
This is Enrollment Verification week and I have to verify you are in the class. If you have not passed in work or the email a week verifying you are in the class, then you need to contact me now to make sure you are not withdrawn from the class.
We will continue looking at relational databases and then we will start looking at subqueries.
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 subqueries
Assignments:
Subquery Quiz Note that the reference in 6 and 7 should have been to the data in problem 5 rather than problem 4
Multi table assignment
Week #3
Week of February 4th
I will be available Monday at 10 in my office K112 and Tuesday at 2 in K101. Lindsey will be available 0n Monday from 2-4 and Thursday from 2-6, Dave is available Tues and Wed from 4-7 and Sat from 10:30 to 2:30. There is a lot of support and you should definitely use it if you have questions or encounter a problem. We are hear to help you succeed! Please take advantage of that.
To repeat: If you need help with something, now would be the time to ask your questions!
We are going to start looking at the design of a database and 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.
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
Next we are going to start looking at relating tables:
Introduction to multiple tables (relational) - joins
Example of non-equijoin: nonequijoin
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
Using join
Join and where
Information on join:
www.sqlbook.com and then look up the kind of query you want: inner join for example.
More information on joins
Some interesting examples
Questions about work with SQL on a home installed version. To highlight, copy and paste SQL commands and output when you are using a window that works like a command prompt window. Right click on the top border around the command window, select edit and then mark to highlight. Do the same right click edit and select copy. Then you can paste in a document.
Assignments:
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.
Function assignment
In problem #1 just to be clear, I want you to take a name that has been written with the slash separator in the format last/first m and flip it so it has first m last using Oracle functions. Specifically it can be done using substr and instr.
Week #2
Week of January 28th
Please do not change any of the passwords we have given you. I need to be able to get to your site as does the IT staff if there is a problem. I will be available for help Monday at 10:30. At 2:00 Tuesday and Thursday there will be a help lab in K101 (we hope - may have to find a different room) with either me or Lindsey (my SI and tutor). Lindsey will also be available 2 -4 on Monday in K101 and on Thursday from 2 - 6 (I need to check the room). Dave will be available from 4 to 7 on Wed and Saturday from 10:30 until 2:30 in K101. Room numbers are not definite, the help lab will know where Lindsey and Dave are.
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
Always check back and see how we are doing!
Assignments:
Here is a script to create the emp table:
Script to create emp table (createemp.sql)
You can enter edit createemp.sql or another name at the SQL prompt. It will go into the editor and ask if you want to create a new script. Enter yes and paste the script in and save it. Return to the prompt and enter @ createemp.sql and it should run and create the table for you.
If you are using putty, remember to have the script text in notepad and highlight it. When you go into the editor the right click will paste it or you can uses shift + ins to paste. You can then write and exit. Back at the SQL prompt is where you run it with the @.
Inclass if statements (must be passed in by everyone) I want you to play computer and show me the output you think would be generated as opposed to running the query and letting Oracle give you the answer.
Second SQL assignment
Quiz 1
I am hoping your have already created a table that you can use for this quiz. If not you need to create one. I would suggest you create one that you can use with assignments from week 1 or week 2. Note that you need to show me the SQL and the results on all of the quiz questions.
Check back!
Week #1
Week of January 21st
I recommend one of the text books in the syllabus, but a textbook 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. I do recommend that you get one of these books or another book dealing with Oracle SQL (we are using version 11g or 12c this semester) as a reference. We will not do chapter by chapter coverage, the text gives you additional information on topics and a reference if you have questions.
ional database information
LOGIN information:
You need to decide if you want to download Oracle, use the BCC labs or login from home using Putty.
Note that there is a reference to help you download and instal under Using Oracle: Installation and login.
Downloading Be very careful when you install, Oracle leaves a lot of footprints that makes reinstalling very difficult.
You should have received the login information you need for this course. If not, let me know.
PuTTY is available in the labs. Two sites that can be used to download PuTTY are:
PuTTY
PuTTY
Using Putty (copy and paste)
Basic steps to login to putty and get to Oracle:
1) Start putty and put in the hostname as cisweb.bristolcc.edu and the port as 220
2) At the login screen enter your username and your Linux password.
3) When you see that you are at cisweb, enter sqlplus12c.sh
4) You then connect to Oracle and need to enter your username and Oracle password
5) You should see the SQL prompt
Now you can create a table. Note that after you create tables, to see existing tables select table_name from user_tables.
Accompanying step-by-step notes
First we will focus on getting into Oracle and then these are the notes that we will start to cover 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
NOTES: Note: You can use the committ command to make sure your work gets saved.
Copy from SQLPlus so you can paste it onto the document you are submitting as your assignent
Assignments:
I strongly urge you to do the COMMIT; after the table is created.
Oracle assignment on introductory material (html)
Oracle assignment on introductory material (doc)
Comment on how I want 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 do not want you to show the screen itself or pictures of the screen - they are really hard to read!


Send e-mail to Mrs. Grocer:
Priscilla.Grocer@bristolcc.edu

Back to the Top
Return to home page