Oracle and SQL (CIS150/50)

Weekly Schedule

Please do not change your linux or Oracle passwords.

Essentially there are two 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 takes a lot of room and leaves a lot of footprints.
2) 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.

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

Remember HELP is available on Zoom - see HELP section below.

The CIS pool is now available for students that need to access licensed software that they cannot use from home.
Remote access virtual desktop
Instructional video for using Remote Access Virtual Desktop - HTML

Schedule by weekInformation to cover
  Note that the audio and Smartboard presentations for each class are posted within a couple of days. This semester, please use 2019 audio and Smartboard since we do not meet in class.
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.
Play computer means to figure out the results yourself by evaluating the data rather than running the query.
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 I will set up some optional class Zoom meetings that you can attend. Check the week of information to see the times and dates.
My office hours are 1 on Monday, 10 on Tuesday, 1 on Wednesday, 10 on Thursday. I hold my office hours on Zoom. If it turns out other hours work better for students these may change, so check back. I am also available by request (give me a few time options) especially while we get other support in place. I have been meeting with a lot of students that request it, maybe something you should consider doing. You can email me with a couple of times you can meet and usually one of those will work.
I will send out the information to contact me on Zoom. I will post availability but you can also set something up with me.
Downloading WinSCP on your PC by SI Joe Fletcher
There is access to WiFi from your car under the solar canopies on the Fall River Campus.
Week #2
Week of September 13th

INFORMATION:

I will go to my Zoom area during office hours this week.
Wed I have a doctor's appointment and need to cancel.
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.

NOTES/EXAMPLES TO LOOK AT:

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

ACCOMPANYING POWERPOINT PRESENTATIONS

These PowerPoint presentations were made to accompany the notes.
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

TOPIC VIDEOS:

Maintain Oracle
Character Functions in Oracle
Other kinds of Oracle Functions
Decode Function in Oracle
Group Functions in Oracle

SMARTBOARD AND AUDIO NOTES FROM Spring 2020 and Fall 2019:

Where, maintenace, functions in SQL (9/10/19) Check accompanying audio
Where, maintain, functions in SQL (9/10/19) See notes under Smartboard presentation
Functions and groups (9/12/19) Check accompanying audio
Functions and groups (9/12/19) See notes under Smartboard presentation

ASSIGNMENTS TO DO:

I am putting this up now because I wanted to give people time to sort out Oracle, but I can't wait any longer. I have not heard from some of you and I am getting concerned. Please contact Kevin or myself if you are having trouble getting into Oracle or executing the SQL.
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 @.
Second SQL assignment
Quiz on where with two parts:
First part 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. We will do a lot of play computer with the goal of making sure you understand the logic and how Oracle handles it.
Second part Note that you need to show me the SQL and the results on all of the part 2 quiz questions.
Week #1
Week of Sptember 6th

INFORMATION:

Welcome to CIS150 Oracle and SQL For Fall 2021
Welcome to CIS150 Oracle and SQL For Fall 2020
I recommend one of the text books in the syllabus if you like having a textbook, 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. These books or another book dealing with Oracle SQL (we are using version 19c this semester) can be helpful as a reference.

LOGIN INFORMATION:

You can download Oracle, but that would not be my recommendation unless you are very experienced in complex downloads. Downloading - enterprise edition Be very careful when you install, Oracle leaves a lot of footprints that makes reinstalling very difficult.

The first approach to running Oracle will involve using putty to access Oracle.
You should have received the login information you need for this course. If not, let me know.
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 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 sqlplus19c.sh (some handouts have an older version, be sure to use sqlplus19c.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.

NOTES/EXAMPLES TO LOOK AT:

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
Note that in many of the notes, the dates are quite old. SQL is SQL is SQL. Changes tend to be at the high end and not at the beginning. These notes were written after my first Oracle training, I have been back but have not seen the need to change the examples. When things are different, I will enter additional notes.

ACCOMPANYING POWERPOINT 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

TOPIC VIDEOS:

Video to accompany Introduction to SQL in Oracle
Video to accompany Create a table in SQL
Video of logging on using putty, establishing a table and testing it
Video to accompany SQL Conditions

SMARTBOARD AND AUDIO NOTES FROM Spring 2020 and Fall 2019:

These are lectures from last Spring and the previous Fall that I recorded. The accompanying Smartboard presentations show what was on the Smartboard and the audio includes my explanation.
Intro to Oracle (9/3/19) Smartboard notes with accompanying audio
Intro to Oracle (9/3/19) See notes under Smartboard presentation to accompany this audio
Putty and more SQL (9/5/19) Smartboard notes with accompanying audio
Putty and more SQL (9/5/19) See notes under Smartboard presentation to accompany this audio

ASSIGNMENTS TO DO:

NOTES: Note: You can use the commit 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
All assignments must be turned in using the email addresses priscilla.grocer@bristolcc.edu.
Your goal should be to get homework in on the Thursday after the week it was assigned.
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!
To repeat: For this semesters assignments, I need the code you write and the results unless there are other specifications in the assignment.


Priscilla.Grocer@bristolcc.edu

Back to the Top
Return to home page