| 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 December 14th |
Classes end on the 15th (which is an open lab) - I want all homework by that date. Your final is due on Tuesday, December 22nd at 9:00AM along with the required status sheet. I am posting it early so hopefully you can get it in early and move on with the holidays/vacation. They should be emailed to me at the usual account and the usual backup account and also to my priscilla.grocer@bristolcc.edu account just to be safe. Final exam Required status sheet |
| Week #14 Week of December 7th |
This week there will be an open lab instead of classes. The labs will run on Tues and Thurs from 9:30 until 3:30
and you can come when you can and stay as long as you can. Carolyn and I will be there. Your exam will be posted so that you have more than a week to take it - check back. It is an open note, open book, no help from other people exam. The exam is due at 9:30 on Tuesday the 22nd and should be emailed to me prior to the due date and time. Late points will be taken! Apparently there was a misunderstanding with a student about the due date, so I will take a couple of things from CIS150 up to the final - but you should aim to get most things in by the 15th for all of our sakes (and I would prefer the 16th, 17th, 18th etc for late things - in other words the sooner the better)! |
| Week #13 Week of November 30th |
I will be available Monday at 10 in my office. Carolyn will be available 2-3:30 on Tues and 2-4 on Thursday and I will
be available at least some of that time. Don't forget advisement! This week we will really look at procedures and functions and maybe even triggers. From Matt (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 November 23rd |
This week I am available Mon at 10 in my office and Carolyn is available Tues from 9:30 to 3:30 in K101. This week we will look at procedures and functions. No homework this week. From Carolyn: In putty, if you go into the editor and press Control-R (Read File), and then press Control-T (To Files), get a list of all the scripts you've made! Here's the output I got.... I had to make my putty window narrow to get the list to come out in a single column, which then made it format better when I copied and pasted. Then if you just move the cursor to a file and press enter, it brings it up in the editor to use. |
| Week #11 Week of November 16th |
This week I am available Mon at 10 in my office and Carolyn is available Tues at 2 and Thurs from 2-4 in K101. I will
be there some on Tues and Thurs but advisement is also happening. We will continue with Cursors this week.Pat McDonough who had Carolyn's job last year is coming in to speak to my 11 and 12:30 classes - you are invited to attend as well. 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 Week of November 9th |
This week I am available Mon at 10 in my office and Carolyn is available Tues at 2 and Thurs from 2-4 in K101. I will
be there quite a lot on Thurs and some on Tues. I hope you can go to the IT Roundtable! Thursday there will be a guest speaker from Meditech in my CIS120 classes. He will speak at around 11:30 and around 12:30 for about a half an hour. You are welcome to come and listen and ask questions! We will continue working on PL/SQL specifically on cursors. Then we will go back and look at some of the things I skipped. 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 Think about a way to use the select distinct to bring in the distinct manager id to a cursor and then go through the cursor. 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. Check back! |
| Week #9 Week of November 2nd |
Monday I am available at 10 in my office and Tues (2-3) and Thurs (2-4) Carolyn will run labs and I will drop in. This week, I am 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 STEM events that start November 2nd: Interesting workshops through STEM Assignments: IF assignment Oracle Loop Assignment Oracle PL/SQL Intro Quiz - Start in class |
| Week #8 Week of October 26th |
Monday I hope to be in my office at 10:30 I have an early meeting that may run late. Tues Carolyn will be
available for help in K101 at 2 and I will try to join her. On Thursday we will have an open lab from 9:30
to 3 in K101. I will not be there but Carolyn will be there to help. 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. No assignments this week. |
| Week #7 Week of October 19th |
I am available Monday at 10 in K112. Carolyn is available Tues from 2-3 and Thurs from 2-4 in K101 and I will
drop in when I can. October 29th will be an open lab day in K101 from 9:30 to 3. Carolyn will be here to help. You can come for as much of this time as you want. If you do not need help, then you have the day off. I am taking a personal day so I will not be present. November 10th we are having a discussion with IT professionals at 5:00. The participants at the round table will be people who started their own IT company as well as people who work in the field. If you attend and give me a short write-up, you can get extra credit. 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 - we will work on this in class (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 October 12th |
Carolyn is available Tuesday and Thursday from 2 - 4 in K101. As usual I will try to come as much as I can. Tour of Lockheed Martin arranged as a STEM initiative 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 October 5th |
I will be available for help Monday at 10:00 in my office, Carolyn will be available Tuesday at 2 in K101 and Thursday at 2 in K101.
Thursday she is available until 4. I will be back and forth on Tues and Thurs. 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) Oil Database explanation We will work on developing this in class Assignments: Subquiz play computer - do not do the questions in Oracle We will start this in class if you are there. All students must do the assignment. Multiple tables and subqueries Note that the ones related to the oil tables can be just written. If you want to make the tables and run it you will get extra credit. |
| Week #4 Week of September 28th |
I put up a script you can use to create the emp table. It is under assignments in week 3. Carolyn will be available Tues 2 - 3 and Thurs 2 - 4, I will come and go. I have an office hours Mon at 10 in my office. 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. 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) In class subquery work All students need to do in class or at home. Check back! |
| Week #3 Week of September 21st |
Help: Carolyn will be available Tues at 2 and Thurs at 2 until 4 in K101. I will be joining her when I can. If you need help with something, now would be the time to ask your questions! We are going to finish up my overly optimistic plas 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: 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 September 14th |
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 Carolyn will have lab time at 2 on
Tuesday and Thursday in K101. 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 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! 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 September 7th |
I am available after 2 on Tuesday or Thursday at 1:45 if you have questions. Where is the question? Check my
office K-112. 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: We will see if we get enough done on Thursday since we could not do SQL on Tuesday due to some login problems. 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! |