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.

Assignments are individual projects. 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.

The college wants you to use the Bristol email account when communicating with me.

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!

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
  This semester, please use 2019/2020 audio and Smartboard as an extra resource 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, but I need to know some times and days that work for you.
, I am available by request/appointment (give me a few time day 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 #7
Week of February 26th

INFORMATION:

As always, I am available for individual or group Zoom meetings - get in touch if you want one.
Remember that if you get something returned with errors that means you do not have a grade and you need to fix and resubmit.
This week we will focus on indexes, constraints and keys. As always, the most important thing are the Notes that essentially make the textbook for this class.

NOTES/EXAMPLES TO LOOK AT:

Indexes in Oracle - An introduction
More on Primary and Foreign keys
Notes on key questions
Also please look at the accompanying videos:

ACCOMPANYING POWERPOINT PRESENTATIONS (Not available since last falls problems, trying to recover)

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.

TOPIC VIDEOS:

Indexes and constraints
Primary keys and foreign keys
Key and index notes

SMARTBOARD AND AUDIO NOTES FROM Spring 2020 and Fall 2019:

Review groups, relational, subqueries (10/8/19)
Keys and constraints (10/10/19) Accompanying audio
Keys and constraints (10/10/19) See notes under Smartboard

ASSIGNMENTS TO DO:

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. Pass in your duplicate.
Key and index assignment
Check back as always.
Week #6
Week of February 19th

INFORMATION:

If you would like to set up a meeting to go over material, please let me know.

NOTES/EXAMPLES TO LOOK AT:

There was a question about equijoin and looking at my notes, I could have done better. Try these!
Equijoin notes
Nonequijoin notes
Some subquery examples to look at to make sure you understand subqueries:
A few more examples
And still more examples
Now we are going to move on to views and reports and more on scripts.
Introduction to views
Introduction to reports
Also please look at the accompanying videos:

ACCOMPANYING POWERPOINT PRESENTATIONS (Not available since last falls problems, trying to recover)

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 or a slash.
createdonor.sql Script to create donor
Insert data into maintain table
Insert data into maintain table ver 2

TOPIC VIDEOS:

Introduction to views
Introduction to reports

SMARTBOARD AND AUDIO NOTES FROM Spring 2020 and Fall 2019:

Views and reports (10/3/19) Accompanying audio
Views and reports (10/3/19) See notes under Smartboard presentation

ASSIGNMENTS TO DO:

Views and Reports
Check back as always!
Week #5
Week of February 12th

INFORMATION:

Let me know if you would like some Zoom time to go over things and what days and times work!
We are going to continue with Oracle and subqueries and variables and into maintenance and reports.

REVIEW EXAMPLES:

This is an example that you might find helpful dealing with subqueries:
Examples Solution
A few more examples

NOTES/EXAMPLES TO LOOK AT:

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)

ACCOMPANYING POWERPOINT PRESENTATIONS (Not available since last falls problems, trying to recover)

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)

TOPIC VIDEOS:

Introduction to variables
More on variables
Maintain again
Maintain revisit again

SMARTBOARD AND AUDIO NOTES FROM Spring 2020 and Fall 2019:

Variables and maintenance (10/1/19)See accompanying audio
Variables and maintenance (10/1/19)See accompanying pdf

ASSIGNMENTS TO DO:

Coding assignment I want you to play computer (as opposed to running things) and tell what the results would be and why.
Check valid code Quiz Cannot be resubmitted but can have an extra week
Check back again!!!
Week #4
Week of February 5th

INFORMATION:

I am going to be away for a long weekend, still checking my email periodically, but I decided to load the week of early.
We are still trying to line up a tutor.
Would you like a Zoom session to go over some of this?

NOTES/EXAMPLES TO LOOK AT:

Using join
Join and where
Subqueries
More on subqueries

ACCOMPANYING POWERPOINT PRESENTATIONS (Not available since last years problems, trying to recover)

Zipped zersion of subqueries if you find a problem with version online.
Subqueries
      Separate speaker notes for subqueries
More on Subqueries
      Separate speaker notes for More on subqueries

TOPIC VIDEOS:

Using join to link tables
Accompany notes on Subqueries
Explanation of subqueries described in previous notes on subqueries
Presentation on a previous Feb 14th on intro materialThis was an introduction for students who were behind.
Password: ^1yTykS%

SMARTBOARD AND AUDIO NOTES FROM Spring 2020 and Fall 2019:

Subqueries (9/24/19) Accompanying audio
More Subqueries (9/26/19) Accompanying audio
Subqueries (9/24/19) See notes under Smartboard presentation
More Subqueries (9/26/19) See notes under Smartboard presentation

ASSIGNMENTS TO DO:

Subquery Assignment
Subquery quiz
Check back as always!
Week #3
Week of January 29th

INFORMATION:

Check with me if you have questions or encounter a problem. I am hear to help you succeed! Please take advantage of that. If you want to meet me in Zoom, we can set up a time if that works better and you think it would be helpful
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. Please read the handout on definitions: Normalization definitions
Normalization

NOTES/EXAMPLES TO LOOK AT:

Please read the two examples - the first two postings under notes.
Introduction to multiple tables (relational) - joins
Relational database examples
Example of non-equijoin: nonequijoin example to accompany notes
Also view the slide presentation on normalization information about relational databases:

ACCOMPANYING POWERPOINT PRESENTATIONS (Not available since last falls problems, trying to recover)

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

TOPIC VIDEOS:

Third Normal Form
Introduction to multiple tables (relational) - joins (accompanying notes)
Relational database examples (accompanying notes)

SMARTBOARD AND AUDIO NOTES FROM Spring 2020 and Fall 2019:

Oracle quiz and relational databases (9/17/19) Accompanying audio
Oracle quiz and relational databases (9/17/19) See notes under Smartboard presentation
Relational databases and intro subqueries (9/19/19) Accompanying audio
Relational databases and intro subqueries (9/19/19) See notes under Smartboard presentation

ASSIGNMENTS TO DO:

Oracle quiz - week 3 The problems should be done without the computer. In the first five tell me the output you think would happen and in the last five show me the code you would write.
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.
Check back as always!
Week #2
Week of January 22nd

INFORMATION:

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

TOPIC VIDEOS:

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

TOPIC VIDEOS using Horizon:

A step by step video where I upload a file to cisweb using WinSCP which is available through Horizon.
Upload to cisweb
and another... Loading to cisweb
Finally when you have it uploaded onto cisweb, you need to figure out the address to give to others to see the file or files you upload. There is a very short video on that.
See page in browser
Now we will look at downloading and using an SQL file in Oracle. Specifically we will use at the createemp.sql script which I provided in the assignments.
Downloading SQL in Oracle
Step-by-step notes on downloading and using an SQL file in Oracle
You can do other things as well because you now have access to the files you have set up through the virtual environment.

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:

Please copy and paste onto a document rather than taking a picture. I am finding the black background a problem on some assignments.
Here is a script to create the emp table:
Virtual makes it a little more complicated - I will put together a video and notes - see above. 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. If it does not ask you can just paste (different versions ask and don't ask). WriteOut and return to the prompt by using the exit 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 @.
Query Quiz
Second SQL assignment
Week #1
Week of January 15th

INFORMATION:

Intro to Fall 2023 courses
Welcome to Oracle and SQL - Fall 2022 (youtube version)
Older introductions:
Welcome to CIS150 Oracle and SQL For Fall 2021/Spring 2022
Welcome to CIS150 Oracle and SQL For Fall 2021/Spring 2022 (you tube version)
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.

You will access putty through Horizon so the first step is to connect.
Horizon (Janelle Arruda)
We used to download putty to access Oracle, but now you should use putty in horizon.
You should have received the login information you need for this course. If not, let me know.
Using Putty (copy and paste)
Once you are in Horizon, you should go to software and find putty. 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.
Some notes to help doing the assignment thru putty in Horizon.

NOTES/EXAMPLES TO LOOK AT:

Accompanying step-by-step notes Remember the port has changed to 220.
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.
The accompanying PowerPoint presentations got lost in the technical problems last year. They were a nice supplement but not necessary. I did the topic videos to take their place.

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: Please copy and paste onto a document rather than taking a picture. I am finding the black background a problem on some assignments.
Note: You should use the COMMIT command to make sure your work gets saved.
Copy from SQLPlus (but I actually prefer copy and paste and not having the black background)
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!
So 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