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.

If you get an assignment back without a grade you need to fix it and resubmit to get a grade.

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!
HELP schedule We are starting the semester with this help schedule. We may make changes based on student need, so check back! These are the times that Joe Fletcher, my SI, is available.
Monday 2-7 in K101
Tuesday 2-5 in K104
Wednesday 2-6 in K101
Thursday 2-5 in K101
Jayson Rios from the tutoring center is also available on Monday 11-3 and Wednesday 11-1 in Fall River and Tuesday 11-2 in New Bedford.
Week #3
Week of September 16th
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: 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.
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 September 9th
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. See the help times above.
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!
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 September 2nd
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 download
PuTTY download
Using Putty (copy and paste)
Basic steps to login to putty and get to Oracle:
1) Start putty and put in the hostname as 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
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:
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
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:

Back to the Top
Return to home page