| 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. Sometimes this involves showing data that was written to a file/table. 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 7th |
Tues and Thurs will be an open lab to get caught up from 9:30AM to 3:30PMish. Please come if you need help because Lindsey and
I will both be available. Note that homework is due on May 10th. I will take a few things late (over the weekend). You have a week to do the final. It is due by Fri, May 18 by midnight and you are required to also pass in the status sheet. Please send to the regular email address you use for homework and to priscilla.grocer@bristolcc.edu for backup. Final Exam Required status sheet |
| Week #14 Week of April 30th |
Time for us to all get caught up! Lindsey is available at 2 on Tues and Thurs. I would like you to do some research on arrays. Basic Array |
| Week #12/#13 Week of April 16th/April 23rd |
Lindsey is available at 2 on Tues and Thurs. I will be in and out with advisement and a meeting. Looking at procedures, funtions and triggers. 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 I was sick this weekend so I am not caught up! Assignments: This is a two week assignment since there is a lot being covered. 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 #11 Week of April 9th |
We will continue with Cursors and look at Exceptions this week. Assignments: Cursor/exception assignment Check back! |
| Week #10 Week of April 2nd |
This week there will be help labs with Lindsey available on Tues and Thurs (April 3rd and 5th) in K101 from 9:30 to 3:00. If you are
behind, this would be a nice time to get caught up. We will continue with cursors and then back up to see some the the things under Notes and handouts that we did not cover yet. Introduction to Cursors Example 1 using cursors Continuing example 1 using cursors Another set of cursor examples Introduction to exceptions 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 Exceptions in PL/SQL Separate speaker notes for Exceptions in PL/SQL If we get through these, we will go and check on some of the things we skilled over. Woops... I pasted under week #11 rather than here under week #10. It is correct now! 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 Assignments: 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 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 third 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. Check back! |
| Week #9 Week of March 26th |
Lindsey is available at 2 on Tues and Thurs in K101. David is available if you contact him via email. When I taught this in the fall we did not get through everything from week #8 so we continued on processing those things and you should do the same. I would also like you to start reading about cursors. Assignments: Oracle Loop Assignment Oracle PL/SQL Intro Quiz |
| Week #8 Week of March 19th |
We will wind up SQL this week and start PL/SQL, so please check back! This week, I am going to look at The Introduction to PL/SQL (the beginning part), skip for a while the More examples of input/output procedures (we will go back to them) and work on Introduction to IF statements. The things below will probably be partially done and the rest we will look at next week. I go in a slightly different order. Introduction to PL/SQL Introduction to IF statements More on PL/SQL 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 More on PL/SQL IF statements Separate speaker notes for More on PL/SQL IF statements Introduction to loops 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 |
Week #7 Week of March 5th |
My goal is to finish the notes on SQL this week and then start on PL/SQL after break. 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) 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. 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: Key contraints and index assignment |
| Week #6 Week of February 26th |
Catch up week! Dave is available for help but he needs you to contact him and set up a time. His email is Dbourgeois321@gmail.com. Please read the following on indexes, constraints and keys: Indexes in Oracle - An introduction Also please look at the accompanying presentations: Indexes in Oracle - An Introduction Separate speaker notes for Indexes in Oracle - An Introduction Assignments: Catch up week! Check back! |
| Week #5 Week of February 19th |
Dave will be available for help in K118 from 4 to 6:30 on Wednesday. This is an example that you might find helpful: Examples Solution A few more examples 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) There was a qusestion about equijoin and looking at my notes, I could have done better. Try these! Equijoin notes Nonequijoin notes Now we are going to move on to views and reports and 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 #4 Week of February 12th |
Dave will be available for help in K118 from 4 to 6:30 on Wednesday. I have the flu so I will not be on campus on Tuesday the 13th. This is Student Verification Week so it is important that you contact me to verify you are in the class and will not be withdrawn for lack of participation. 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: Subquery Quiz Week 4 assignment |
| Week #3 Week of February 5th |
Help: Dave will be available Wed from 4 to 6:30 in K118 or check at the help desk. I have an office hour at 10 on Mon in K101 and I will be around Tues and Thurs after 2 mostly in K101 or my office K112. 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. 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 Next 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 Join and where 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 Questions about work with SQL on a home installed version. To highlight, copy and paste SQL commands and output when you are using a window that works like a command prompt window. Right click on the top border around the command window, select edit and then mark to highlight. Do the same right click edit and select copy. Then you can paste in a document. Assignments: 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. 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. The goal here is to look at the SQL and analyze the logic and figure out what you thing the results will be. |
| Week #2 Week of January 29th |
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 also be around for a
while Thurs afternoon after my 12:30 class. If you want help, please let me know. These are the notes for 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: 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 and I suggest selecting copy if the other does not work. 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 @. Notes on creating the emp table in putty in response to a question Inclass if statements (since online this semester it is an assignment.) 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. This helps you to analyze the problem and understand what is happening - again - please do not run these in Oracle. Second SQL 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. Note that you need to show me the SQL and the results on all of the quiz questions. Check back! |
| Week #1 Week of January 22nd |
I am available after 2 on Tuesday or Thursday at 1:45 if you have questions. Check my office K-112 or the classroom K-101 or the central K Buildign lab. 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 or 12c 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. ional 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 or pictures of the screen - they are really hard to read! |