Oracle and SQL (CIS150/50)

Weekly Schedule

Things are working. I have emailed you three things (one paper and two videos) about how to access Oracle.

Again my apologies that the some of the extra presentations do not come up - IT is working on a solution. They are extras and are not needed to cover the material.

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.

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

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 11 on Monday, 2 on Tuesday, and 11 on Wednesday. I hold my office hours on Zoom, but let me know you are coming. .
, I can 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.
Milo is the tutor for this course and I have sent you an email with his Zoom address and the times he is available.
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.
DUE DATES Homework is due on May 14th.
I will take a few things after that if we have an email conversation, but everything is due on May 17th.
Because of the technical problems there will be no final unless you personally request one. We would need to talk about dates etc.
Milo has changed his hours to help. On the week of May 15th he will be available at these hours:
Monday 2pm - 5pm , 7pm - 9pm
Tuesday 1:30pm - 5pm , 7pm - 9pm
Wednesday 1:30pm - 5pm
Week #15
Week of May 8th

INFORMATION:

REQUIRED STATUS SHEET
The status sheet must be passed in - I cannot grade until I have checked your work to see if anything is missing!

Check back!
Week #14
Week of May 1st

INFORMATION:

Please continue to look at the information on cursors. This week we will also introduce exceptions.
If you would like me to do a Zoom to go over cursors or something else, just let me know and we can make it happen.
There is information about tables and records under Notes and Handouts and under Presentations that you should look at.
PL/SQL User Defined types - Record and Table
PL/SQL User Defined Types - record and table
      Separate speaker notes for PL/SQL User Defined Types - record and table

NOTES/EXAMPLES TO LOOK AT:

Example we will look at:
Introduction to exceptions
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
More on internal procedures and functions
Still more on procedures and functions
Introduction to triggers
More on triggers

ACCOMPANYING POWERPOINT PRESENTATIONS

Exceptions in PL/SQL
      Separate speaker notes for Exceptions in PL/SQL
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
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

TOPIC VIDEOS:

Exceptions
Introduction to Procedures
Introduction to Functions
More Procedures
Internal Procedures & Functions
More procedures and functions with cursor
Introduction to Triggers
More Triggers

SMARTBOARD AND AUDIO NOTES FROM Spring 2020 and Fall 2019:

Cursors and exceptions (11/12/19) Accompanying audio
Cursors and exceptions (11/12/19) See notes under Smartboard presentation
Cursors and procedures (11/14/19) Accompanying audio
Cursors and procedures (11/14/19) See notes under Smartboard presentation
Cursors and functions (11/19/19) Accompanying audio
Cursors and functions (11/19/19) See notes under Smartboard presentation
Procedures, functions, triggers, check digit (11/26/19) Accompanying audio
Procedures, functions, triggers, check digit (11/26/19) See notes under Smartboard presentation

ASSIGNMENTS TO DO:

Exception assignment: Write new scripts or modify existing scripts to show each of the three kinds of exceptions we went over.
Assignment on procedures, functions and triggers
Extra Credit: Check Digit
Notes on check digits You need to write the routine to calculate a check digit using Mod 11 and to validate a check digit using Mod 11. It can be done in two scripts or one.
Week #13
Week of April 24th

INFORMATION:

If you need help, don't forget to set up an appointment with Milo or myself.
I really hope you were able to spend some time on cursors last week. Again, we can set up a Zoom to go over cursors. Let me know if you want to and some times that work.

NOTES/EXAMPLES TO LOOK AT:

Another set of cursor examples
Some more examples:
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

ACCOMPANYING POWERPOINT PRESENTATIONS

Covers: Another set of cursor exampes
      Separate speaker notes for Another set of cursor examples

TOPIC VIDEOS:

Another set of cursor examples (prob 1)
Another set of cursor examples (prob 2)
Another set of cursor examples (prob 3)
Another set of cursor examples (prob 4)
Cursor examples involving payroll
Cursor examples involving donor DB

SMARTBOARD AND AUDIO NOTES FROM Spring 2020 and Fall 2019:

Still more cursors (11/5/19) Accompanying audio
Still more cursors (11/5/19) See notes under Smartboard presentation
Cursors and exceptions (11/12/19) Accompanying audio
Cursors and exceptions (11/12/19) See notes under Smartboard presentation

ASSIGNMENTS TO DO:

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 which should have an item number, and number on hand and a transaction table that has item number (should match an item number on the inventory table), an amount and a code with S for sales and P for purchases. There is one record per item number on the inventory table and on the transaction table there can be 1 or more transactions for an item number. Create scripts to create the tables and insert data into these tables.
Fourth Problem: Using the tables you created in the fourth problem, process the transactions determine the impact on inventory. Display information that gives the original inventory and the inventory after the sales and purchases have been processed. You need to use cursors.
Please check back as usual!
Week #12
Week of April 17th

INFORMATION:

I have been sick (not Covid) so I got behind. I am feeling better and will try to get caught up this weekend.
Because of the difficult beginning of the semester, I have decided not to have a final this semester. This means that the homework/quizzes etc will count for 90% of the grade instead of 80% to make up for no final. If you truly want a final, you need to contact me and we can make a plan. This will also give you some free time at the end of the semester.
We are going to come back to Records and Table types because I think it will be better to lay the cursor foundation first. So we are going to look at Introduction to Cursors and Example 1 using cursors and its continuation.

NOTES/EXAMPLES TO LOOK AT:

Introduction to Cursors
Example of cursor with while
Example 1 using cursors
Continuing example 1 using cursors

ACCOMPANYING POWERPOINT PRESENTATIONS

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

TOPIC VIDEOS:

Introduction to Cursors
Example 1 using Cursor but I did it with the wrong data The explanation is good though!
Example 1 using Cursor with correct data
Example with While cursor

SMARTBOARD AND AUDIO NOTES FROM Spring 2020 and Fall 2019:

Data manipulation and intro cursors (10/29/19) Accompanying audio
Data manipulation and intro cursors (10/29/19) See notes under Smartboard presentation
More cursors (10/31/19) Accompanying audio
More cursors (10/31/19) See notes under Smartboard presentation

ASSIGNMENTS TO DO:

Work on understanding cursors.
Use cursors to solve this problem:
Note that my example of two cursors using the drive table and the donation table will be very helpful in doing this. Think of drive like dept.
Problem #1: 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. Do it doing the while structure
In response to a question the inven table could have an inven number, a dept number (does not have to be a number) and an onhand amount. The dept table could have a dept number (match to dept on inven table) and a department name. You should assume there are multiple inven records per dept number and only one record per dept number on the department table.
Problem #2: Re do using the loop structure.
Check back as always!
Week #11
Week of April 10th

INFORMATION:

Remember, I will set up a meeting some night to go over this if you want me to.

NOTES/EXAMPLES TO LOOK AT:

More on PL/SQL IF statements
Introduction to loops
Data manipulation in PL/SQL - introduction

ACCOMPANYING POWERPOINT PRESENTATIONS

More on PL/SQL IF statements
      Separate speaker notes for More on PL/SQL IF statements
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

TOPIC VIDEOS:

More on PL/SQL IF
Intro to Loops
Manipulating data
Loops examples with Joe Fletcher

SMARTBOARD AND AUDIO NOTES FROM Spring 2020 and Fall 2019:

Be sure to review the Intro PL/SQL - if and loop Smartboard and Audio under week #8.
Data manipulation and intro cursors (10/29/19) Accompanying audio
Data manipulation and intro cursors (10/29/19) See notes under Smartboard presentation

ASSIGNMENTS TO DO:

Please listen to last weeks recording of the CIS Alum panel if you have not.
CIS Alum Panel Spring 2023
Oracle Loop Assignment
Oracle PL/SQL Intro Quiz

Check back!
Week #10
Week of April 3rd

INFORMATION:

This week we will wrap up the introduction to SQL and move on to look at PL/SQL (Procedual Language/SQL).

NOTES/EXAMPLES TO LOOK AT:

More on views
Information on database objects
Script for input data
Introduction to PL/SQL Just very briefly to look at the structure we will return to this in a week or so.
Introduction to IF statements

ACCOMPANYING POWERPOINT PRESENTATIONS

More on views
      Separate speaker notes for More on views
PL/SQL - Using IF statements
      Separate speaker notes for PL/SQL - Using IF statements

TOPIC VIDEOS:

More views
Sequence views etc
Input Script Example
Introduction to PL/SQL and IF statements

SMARTBOARD AND AUDIO NOTES FROM Spring 2020 and Fall 2019:

Wrapup SQL (10/17/19) Accompanying audio
Wrapup SQL (10/17/19) See notes under Smartboard presentation
Intro PL/SQL - if and loop (10/22/19) Accompanying audio
Intro PL/SQL - if and loops (10/22/19) See notes under Smartboard presentation

ASSIGNMENTS TO DO:

Please listen to todays recording of the CIS Alum panel.
CIS Alum Panel Spring 2023
Finish Basic SQL
IF assignment
Check back!
Week #9
Week of March 27th

INFORMATION:

CIS Alum Panel held on Oct 19th at 12:00 You absolutely should listen to this - it was great!
On April 5th there will be a round table discussion of CIS alum talking about their careers and the paths they took. It is always a great session and students tell me how much they benefit from it. There is a question and answer session. The roundtable runs from 12:00 to about 1:00 to 1:30 depending on the conversation. I will send you the address.
I can go over keys and indexes as well as subqueries and anything else you suggest one night this week around 8 or 9 if I here from you about day and time and that you plan on trying to come.
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 presentations:

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

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:

CIS Alum Panel held on Oct 19th at 12:00 You absolutely should listen to this - it was great!
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.
Check back for another assignment!
Week #8
Week of March 20th

INFORMATION:

Milo will be available 3-5, 7-9 on Friday.
I think we need to setup a time to go over groups, subqueries and linking between tables. If you would like to, 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 presentations:

ACCOMPANYING POWERPOINT PRESENTATIONS

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!
Break/Catch-up
Week of March 13th
In working with the temporary fix using vdi.bristolcc.edu I found that Chrome and Edge worked for me - had trouble with my usual browser Firefox.
Note that what I put in for March 20th was for later in the semester. It has been fixed. I knew trying to keep ahead was going to mess me up at some point and it did. So... If you did the work it will be due - just there were other things before I got to them! Sorry!
Check back!
Week #7
Week of March 6th

INFORMATION:

Remember I am available for a Zoom meeting.
Let me know if you would like some 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

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 and tell what the results would be and why.
Check back!
Week #6
Week of February 27th

INFORMATION:

Would you like a Zoom session to go over some of this? Let me know and let me know days and times that work.

NOTES/EXAMPLES TO LOOK AT:

Using join
Join and where
Subqueries
More on subqueries

ACCOMPANYING POWERPOINT PRESENTATIONS

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 from a previous semester (pre Horizon).
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
Week #5
Week of February 20th or if you do not need the week to deal with catch up move on to week #6

INFORMATION:

If you do not need another catch up week then you can move on to the next week.
I have emailed you information that Janelle developed for using Horizon and then getting in with putty. So you can now start using Oracle. I asked about this week being a figure it all out week or a moving on week and I got mixed feedback. So... I have decided to let you choose in a sense. So... If you feel it would be more beneficial to work on getting things set up then you have a week to do that and finish redoing the assignments in Oracle. If you want to get things working and finish redoing the three assignments and still move on you can finish up and then go to the posting under Week #6. If you did the first three assignments in MariaDB and got them right that works as well.
I will put up next weeks information and you can go to that and I will keep doing that for the rest of the semester. So everyone can just move on doing all of the postings that I put up and pretty much ignoring the week of and date and just making sure that you do them all. Clearly we will have real flexibility in due dates.
Week #4
Week of February 13th

INFORMATION:

It looks like the tech problem may be almost solved so I am waiting until Monday to post the weeks work. Make that Tuesday.
I was overly optimistic it appears... I really think you need to download MariaDB so you can practice the SQL. If you can go to the Bristol labs or have figured out another way to test SQL that works as well. I just want to see the results of actually running SQL.
I am sorry we are in this situation, but I really want to make sure you are comfortable with the basics of SQL. This week, I want you to go over the material covered in the first three weeks and test it on MariaDB. So I want you to do these three assignments in an actual database:
Oracle assignment on introductory material (html)
Second SQL assignment
Function assignment
Turn in each one separately and take pictures of the screen.
Week #3
Week of February 6th

INFORMATION:

There are tech problems and we still do not have access to writing Oracle commands. We are all getting more and more frustrated, but IT is working diligently with experts in the field to overcome the many obstacles. I am looking at MariaDB as a temporary solution. I worked with it a few years ago and found it very similiar to Oracle. Just a thought... Hopefully we will get access to Oracle ths week, but... No promises!
I think it might to good to have a general question session on Zoom, so lets try noon on Monday.
Hopefully I will know about access tomorrow but in the meantime K104 and K130 are available for use with Oracle and putty.
I am posting this weeks work and assignment but at some point we will have some slack time to try some of the previous work in Oracle.
There is a lot of support between Milo and I 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. If you want to meet me in Zoom, I am available during my office hours or we can set up another 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)

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:

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!
Week #2
Week of January 30th

INFORMATION:

Things are going well in the recovery and it looks like we may have access to Oracle early this week, so I am going to put the information I want you to go over up, but not the assignment until we know for sure.

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(not available)

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:(not available)

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 was hoping that we would be back to using Oracle, but maybe by the first of next week.
In the meantime, here is an assignment to do on paper or in Oracle if it becomes available. Show me the data, the code and the results if the code was executed. Second SQL assignment
Week #1
Week of January 25th

INFORMATION:

Welcome to Oracle and SQL - Fall 2022 (youtube 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.
Because of technical problems at Bristol, we do not have access to Oracle yet.
Hopefully we will have access to Oracle in a week or so - if not I am working on an alternative way to start with SQL.
So this week, we are going to start with the basics of SQL - see assignment below.

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

ACCOMPANYING POWERPOINT PRESENTATIONS (These are not currently available)

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 not currently available)

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: For this week I want you to write out the SQL for the assignment and then using the data, I want you to play computer and show the output that the SQL would generate using my sample data. The technology problems at Bristol mean that we cannot do this assignment online this week. I asking you to write the SQL and then analyze the data and determine what your SQL SELECT statement would show. Pass in the SQL and the data that would be displayed.
I am leaving up the instructions for doing Oracle so that you will have them when access is restored.

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