| Schedule by week | Information 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 have changed office hours to not overlap with my CIS120 SI.
My office hours are 1 on Monday, 10 on Tuesday, 1 on Wednesday, 10 on Thursday.. If it turns out other hours work better
for students these may change, so check back. 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 #13 Week of November 30th |
INFORMATION: Please try your best to come to the alum job/career seminar on December 2nd at 12:00. I have sent you the link - it is the one you use to go to Zoom for help from me.We will look at the last set of topics this week - a few topics we skipped next week. The topics are procedures, functions, triggers and a quick look at an array this week or next week. NOTES/EXAMPLES TO LOOK AT: 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 More on internal procedures and functions Still more on procedures and functions Introduction to triggers More on triggers ACCOMPANYING POWERPOINT PRESENTATIONS Introduction to PL/SQL ProceduresSeparate 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: Introduction to ProceduresIntroduction 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 procedures (11/14/19) Accompanying audioCursors 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 |
| Week #12 Week of November 23rd |
INFORMATION: Next Wednesday, December 2nd at 12:00 noon, 8 alum are going to participate in a Zoom discussion about their careers. What they do, how they got there, what they enjoy and what they could do without. These presentations have, in the past, been in the classroom but this year it will be a Zoom event. I hope you can attend to here the valuable career information they will convey. Students tell me that this event really helps them understand the career potentials in the field. I will send you the address of the event.Please continue to look at the information on cursors. This week we will also introduce exceptions. NOTES/EXAMPLES TO LOOK AT: Introduction to exceptionsACCOMPANYING POWERPOINT PRESENTATIONS Exceptions in PL/SQLSeparate speaker notes for Exceptions in PL/SQL TOPIC VIDEOS: ExceptionsSMARTBOARD AND AUDIO NOTES FROM Spring 2020 and Fall 2019: Cursors and exceptions (11/12/19) Accompanying audioCursors and exceptions (11/12/19) See notes under Smartboard presentation ASSIGNMENTS TO DO: cursor/exceptions Note that each modification should be sent in as a separate program. |
| Week #11 Week of November 16th |
INFORMATION: I really hope you were able to spend some time on cursors last week. We are going to continue with cursors this week as well. I had trouble with my videos, so I am going to have to do some work on them tomorrow and then I will post.Monday office hours at 1:30 to 2:30. NOTES/EXAMPLES TO LOOK AT: Another set of cursor examplesSome 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 exampesSeparate 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 audioStill 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: Use cursors to solve these problems.First Problem: 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. Second 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. Third Problem: Modify the problem in #2 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. Fourth 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. Fifth 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. Sixth Problem: I want you to redo this program so that the EXIT does not come in the middle of code but rather at the bottom. Do the second version of the program on this example sheet: Example |
| Week #10 Week of November 9th |
INFORMATION: Remember that there is a department wide advising session with CIS faculty on Monday Nov 9th from 12:00 to 2:00 and another on Thursday Nov 12th from 6:00 to 8:00. I have sent out the Zoom address, contact me if you do not have it. This is an excellent opportunity to ask CIS faculty questions about the curriculum and your schedule. Since 1:00 is advising on Monday, I will make an office hour on Friday at 5:00.EXTRA CREDIT On Friday, November 13th there is a talk by William Morrison from Bay State College about digital forensics and cyber security. He will talk about his amazing lab and how they use it to effectively solve situations. Anyone who attends and sends me an email with brief comments about the talk will be given EXTRA CREDIT. 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 CursorsExample 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 exampleSeparate speaker notes for Cursors in PL/SQL, cursor example and continuation of first cursor example TOPIC VIDEOS: Introduction to CursorsExample 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 audioData 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. |
| Week #9 Week of November 2nd |
INFORMATION: I will not be available Tuesday at 10 since I have to go to a college wide faculty and staff meeting. I can be available Friday instead. For now, lets say 10.NOTES/EXAMPLES TO LOOK AT: More on PL/SQL IF statementsIntroduction to loops Data manipulation in PL/SQL - introduction ACCOMPANYING POWERPOINT PRESENTATIONS More on PL/SQL IF statementsSeparate 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 IFIntro 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: Oracle Loop AssignmentOracle PL/SQL Intro Quiz Check back! |
| Week #8 Week of October 26th |
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 viewsInformation 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 viewsSeparate speaker notes for More on views PL/SQL - Using IF statements Separate speaker notes for PL/SQL - Using IF statements TOPIC VIDEOS: More viewsSequence 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 audioWrapup 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: IF assignmentCheck back! |
| Week #7 Week of October 19th |
INFORMATION: 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 introductionMore on Primary and Foreign keys Notes on key questions Also please look at the accompanying presentations: ACCOMPANYING POWERPOINT PRESENTATIONS Indexes in Oracle - An IntroductionSeparate 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 constraintsPrimary 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.Keys etc assignment Check back! As always this is a work in progress... |
| Week #6 Week of October 12th |
INFORMATION: Joe has a new programming job - congratulations and our best! However, we will miss him a lot. I am in the process of seeing up tutoring but I am not sure when it will be set. So in the meantime, I will be available a lot this week. Let me know if you need Zoom time. I will definitely do my office hours, but I am willing to do lots more including some evenings. So... Stay in touch!NOTES/EXAMPLES TO LOOK AT: There was a qusestion 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 viewsSeparate 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 viewsIntroduction to reports SMARTBOARD AND AUDIO NOTES FROM Spring 2020 and Fall 2019: Views and reports (10/3/19) Accompanying audioViews and reports (10/3/19) See notes under Smartboard presentation ASSIGNMENTS TO DO: Views and ReportsCheck back - a work in progress! |
| Week #5 Week of October 5th |
INFORMATION: Let me know if you want Zoom during my office hours and suggest other times if my office hours do not work and you need some Zoom time.We are going to continue with Oracle and subqueries and variables and into maintenance and reports. I have a dentist appointment at the time of my office hours on Tuesday so instead I will do an office hour at 1 on Friday. 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 VariablesSeparate 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 variablesMore 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 audioVariables and maintenance (10/1/19)See accompanying pdf ASSIGNMENTS TO DO: Coding assignmentCheck back! |
| Week #4 Week of September 28th |
INFORMATION: This week I am trying having you contact me to meet on Zoom during my office hours if you want to meet. You can also contact me to set up a different time to meet on Zoom. Just a suggestion, not a requirement.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. NOTES/EXAMPLES TO LOOK AT: Using joinJoin 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 tablesAccompany notes on Subqueries Explanation of subqueries described in previous notes on subqueries SMARTBOARD AND AUDIO NOTES FROM Spring 2020 and Fall 2019: Subqueries (9/24/19) Accompanying audioMore 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 AssignmentSubquery quiz I am posting this quiz later in the week because I hoped you would get to the other assignment first. This is not due next Thursday, it is due the 15th. Please note there will be one more assignment next week. Woops, I made a mistake on #4 - hopefully it is fixed. |
| Week #3 Week of September 21st |
INFORMATION: 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. 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. However, if you want to meet during my office hours you need to let me know so I will be there rather than just online.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 Normalization - information on third normal formSeparate 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 FormIntroduction 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 audioOracle 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 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. Database Quiz #1 |
| Week #2 Week of September 14th |
INFORMATION: I will go to my Zoom area during office hours this week.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 OracleCharacter 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 audioWhere, 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 Joe 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 September 7th |
INFORMATION: Welcome to CIS150 Oracle and SQLI 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 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 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 notesFirst 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 OracleSeparate 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 OracleVideo 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 with a cc to the email address I will send you. I like to have backup. 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! |