| Schedule by week | Information 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. 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! | |
| Week #15 Week of May 2nd |
Open lab week. I am posting your final. It is due on Thursday, May 12th at noon. You should email to my regular and backup emails and Priscilla.Grocer@bristolcc.edu just to be sure it gets through. Final exam CIS150 Required status sheet Check back! |
| Week #14 Week of April 25th |
I will be around Tuesday afternoon and some on Thursday as well (probably after 3) Try to get caught up this week - check back! |
| Week #13 Week of April 18th |
Erik will be available on Tues from 2 to 5:30 and Thurs from 2 to 6. I will be around at 10 Wednesday and Tues and
Thurs from 2 to 4ish with type out for advising etc.. This week we will look at procedures and functions and maybe even triggers. From a classmate: From the SQL prompt, if you type "host" it drops you out into the bash shell, which is a great way of seeing all of your files. ls - la or it looks like they kludged in a "dir" sumlink for the more Windows based users. To get back to SQL, type "exit". Example: empcode.sql empproc1.sql Here are notes on functions, procedures and triggers. 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 Notes on check digits Assignments: Procedure, function, trigger assignment Write code to take in an id and determine if the check digit is correct - use the notes on check digits to see the logic. |
| Week #12 Week of April 11th |
Catch up week! |
| Week #11 Week of April 4th |
I have three speakers coming to my 11 class who are BCC CIS graduates and are going to talk about careers in IT. You are
welcome to come. Examples we are going over in class: payprog.sql data.txt Data layout for studentchk.sql studentchk.sql createdonor.sql Script to create donor donorcursor1.sql donorcursor2.sql donorcursor3.sql decodecursor1.sql We finished cursors and we will now look at some of the things we skipped and also exceptions. Record and table types Introduction to exceptions There are also presentations for these notes that can help to clarify. Assignments: 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 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. Fourth 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. |
| Week #10 March 28th |
Message I received from Pat Condon: "Meditech will be attending the April 4th Job Fair (they have jobs and are hiring
May 2016 Grads) Recruiters are looking for CIS students." We will start looking at cursors this week. But first please go through Manipulation of data. Manipulation of data We will look at records and table types a little as we do cursors and more after. I have listed a lot here, we will be continuing it in week #11 as well. Introduction to Cursors Example 1 using cursors Continuing example 1 using cursors Another set of cursor examples Script to create emp table (createemp.sql) 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 Covers: Another set of cursor exampes Separate speaker notes for Another set of cursor examples We discussed a way to use the select distinct to bring in the distinct manager id to a cursor and then go through the cursor (see Smartboard notes). And we talked about the possibility of moving the manager ids in the cursor into the table for processing. Assignments: These scripts will create two tables (two versions of the first one): createscript.txt createscript1.txt createtrans.txt scripts Then write the PL/SQL program to read the files using cursors and write out the total hours worked by each employee. Then write a modification to the program that takes salaried workers and divides their salary by 52 and display and take hourly workers and multiply the hours they worked by the pay per hour. Then continuing with modifications, calculate hourly works pay if they make overtime. You should use a base of 40 hours and pay time and a half. |
| Week #9 Week of March 21st |
We are going to look at The Introduction to PL/SQL (the beginning), skip for a while the More examples of input/output
procedures (we will go back to them) and work on Introduction to IF statements. Introduction to PL/SQL Introduction to IF statements Introduction to loops Data manipulation in PL/SQL - introduction With the accompanying PowerPoint presentations: Introduction to PL/SQL Separate speaker notes for Introduction to PL/SQL PL/SQL - Using IF statements Separate speaker notes for PL/SQL - Using 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 Assignments: IF assignment Oracle Loop Assignment Oracle PL/SQL Intro Quiz |
| Week #8 Week of March 7th |
Please look at these handouts - they finish up the coverage of SQL. Information on database objects Script for input data A script to create the donation table, you can use the same technique to create the drive table if you do not have them. Create donation script Sorry, forgot I had this. We will start PL/SQL. Catch up week! |
| Week #7 Week of February 29th |
I am usually available Tues and Thurs at 2 and this week also Mon at 10 and maybe 2. This week we will start looking at indexes and other constraints including keys. Please read the following on indexes, constraints and keys: Indexes in Oracle - An introduction More on Primary and Foreign keys Notes on key questions More on views (we already started to look at this) Be sure you read about indexes, keys etc in your text as well. Also please look at the accompanying 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. More on views Separate speaker notes for More on views Interesting SQL/Oracle Article Assignments: Oracle Key Example - This is practice, it is not a pass in assignment) HOWEVER extra credit if you do it and pass it in Oracle constraint assignment |
| Week #6 Week of February 22nd |
I am available Tuesday and Thursday around 2 in the help sessionin K101 amd Mon at 10. As usual I will try to come as much as I can. Now we are going to move on to views and reports and that more on scripts. Introduction to views Introduction to reports 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. createdonor.sql Script to create donor Insert data into maintain table Insert data into maintain table ver 2 Assignments: Views and Reports |
| Week #5 Week of February 15th |
This is an example that you might find helpful: Examples Solution 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) 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) Assignments: Oil Database explanation Subquiz play computer - do not do the questions in Oracle Multiple tables and subqueries. To do this assignment you need to create the oil database. |
| Week #4 Week of February 8th |
I will be available Tuesday at 2 in K101. We are going to start looking at subqueries. Subqueries More on subqueries You should also look at the presentation on these topics especially the one that accompanies More on subqueries. Subqueries Separate speaker notes for subqueries More on Subqueries Separate speaker notes for More on subqueries Zipped subqueries Assignments: Oracle quiz (third) Subquery work Subquery work Check back! |
| Week #3 Week of February 1st |
I will be available Monday at 10 in my office and Tuesday and Thursday at 2 in either K101
or my office. We are going to finish up my overly optimistic plans from last week and then move on, so check back! 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 Information on join: www.sqlbook.com and then look up the kind of query you want: inner join for example. More information on joins Some interesting examples We are going to start looking at subqueries and continue these next week: Assignments: Here is a script to create the emp table: Unfortunately you need to look at it because two commas are missing - one is on the record for Blake and one is on the record for Scott. 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. You can then write and exit. Back at the SQL prompt is where you run it with the @. 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. We will start this quiz in clas. 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. |
| Week #2 Week of January 25th |
A setting adjustment needs to be made by BCC, so if you are going in through putty to our
server, get the assignment in when you can! 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. I will be available for help Monday at 10:00 in my office K112 and I will have lab time at 2 on Tuesday and Thursday hopefully in K101. If not, I will post a note on my door. These are the notes for this week. As I said, I am following the class from the Fall so I am going with what was posted then. 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! Trips to IT locations Updated Information: Updated information on iRobot and a second change the time they are leaving is now 8:30 Updated information on Microsoft Assignments: Second Oracle 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. |
| Week #1 Week of January 18th |
CIS150 is an online course this semester. We are going to follow the outline from last semester which means
that you can follow the audio and the Smartboard that I posted in the Fall 2015. In fact you really need to
do that since there will be no inclass lecture. This week look at the Smartboard and listen to the audio
for 9/8/15 and 9/10/15. 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 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. Since login information has not been sent, we are going to spend the first day looking briefly 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 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 PuTTY 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 2) At the login screen enter your username and your Linux password. 3) When you see that you are at cisweb, enter sqlplus12c.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. 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: 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 Assignments: 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 - they are really hard to read! |