Login Access

Here’s two ways the students can access the database from home (in additional to using their browser and connecting via isql+).  These both rely on going to technet.oracle.com, registering, and downloading software.  Our group doesn’t have a public ftp site and in any case you are required to check acceptance statements to get the Oracle software and they don’t want us distributing it.
Go to http://technet.oracle.com, register if necessary, highlight Downloads and click on Database.
Easiest to setup and less disk space using Express Edition
Click on the 10g Express Edition Link for either Linux or Windows depending on their platform.  I’ll assume Windows.
They need to click on Accept License Agreement
Click on the link for Oracle Database 10g Express Client (OracleXEClient.exe) to download and then install it.
From the Start menu -> (All) Programs find the client and click on “Run sql command line”.  They should already have been assigned their Oracle login name.  I assume ora3.  Then at the SQL prompt they log in by
> connect ora3/oracle@216.64.246.11/t3
And they should be in.
Use standard Oracle client as in class
Scroll down and click on the appropriate 10g RELEASE 1 client for your OS.  I assume 32-bit Windows so click on
  Oracle Database 10g Release 1 (10.1.0.2) for Microsoft Windows (32-bit)
Accept (click) the two license agreements.
Scroll down and click on the link for Oracle Database 10g Client Release 1 (10.1.0.2) for Microsoft Windows (32-bit)
Download and unzip it.  There should be a Disk1 folder.  In it, double click setup.exe.
Click on defaults until the Installation Type Screen.  Select Administrator.  To have the same environment as in class also click on Product Languages and click the >> button to add all the languages.  Click ok then click next and install.
Copy the tnsnames.ora file that is in the ORACLE_HOME\network\admin on their computers in class to a flash drive (or they can just recreate the entry for T3.  Copy the file (or create it) in the OracleHome\network\admin on their home computer.  (If they create it using notepad, make sure it doesn’t have a .txt suffix).
They can log on and test it as they did in class.

PLSQL

These instruction are great.
Thanks for putting the time into this.
Mark
503-816-2774 (Cell)
503-460-3336 (Home)
503-627-3008 (Desk)
mark.schofer@gmail.com (Alternative)

-----Original Message-----
From: Rendar, Byron [mailto:brendar@ex.oci.pcc.edu]
Sent: Tuesday, October 31, 2006 10:53 AM
To: Schofer, Mark
Subject: PL/SQL Class

Hi Mark,
Here's more information about the pl/sql class.
I will try to keep the database T3 up and running. If for some reason
it is not, or if isqlplus or the listener isn't running you will need to
log in and start things up.
Use putty to log into sherlock.pcc.edu (216.64.246.11) with login name
ora10103 and password 2gwnhgb4 (star trek saying).
I assume you know how to startup and shutdown the database (using
sqlplus). To start isqlplus type
isqlplusctl start
at a prompt.
To check the status of or start the listener:
lsnrctl status
lsnrctl start
Like the first class, there are two sets of books since the class
consists of two modules, PL/SQL Fundamentals and Develop PL/SQL Program
Units. You can give out just the Fundamentals book to start. IMPORTANT
- please tell the students not to open them if they think that they
might drop the class because they will be charged for the books.
This class, like the previous one has a reset.sql script which needs to
be run when the first module, PL/SQL Fundamentals ends and before you
start the 2nd module, Develop PL/SQL Program Units. The reset.sql
script is in the plsqllabs folder. Below is what the setup guide says.
(Note you may need to uncomment lines in the script, I haven't looked at
it.)
This course is comprised of two modules. The first module is Oracle
Database 10g: PL/SQL Fundamentals. The second module is Oracle Database
10g: Develop PL/SQL Program Units. When the first module ends (at the
end of the second day), the accounts need to be reset to match the
course material for the second module. These are the directions to reset
the course accounts for the second module.
1. On the Instructor machine only: Find the reset.sql file.
2. Drag the reset.sql file to the desktop.
3. The instructor is responsible for running the contents of the
reset.sql file once the first module ends (which should be at the end of
day three).
4. This script requires the instructor to know the sid and the
number of accounts to be reset. The instructor is prompted for this
information.
5. All users must be disconnected from the session in order for the
reset scripts to work.
6. The script is run from SQL*Plus and the working directory in
SQL*Plus must be set to the \labs\reset folder.
7. Once the information is provided, the accounts are automatically
rebuilt for the second module.
8. To test the account, issue the following SQL statement from one
of the reset accounts:
SELECT COUNT(*) FROM employees;
The result should be 107.

Byrons Instruction

Hi Mark,
I’m sending you the portion of the setup instructions for your SQL class for the 2nd part. You need to run the reset script. The database name is T5 and there are 15 student accounts.
This course is comprised of two modules. The first module is Oracle Database 10g: SQL Fundamentals I. The second module is Oracle Database 10g: SQL Fundamentals II. When the first module ends (at the end of the third day), the accounts need to be reset to match the course material for the second module. These are the directions to reset the course accounts for the second module.
1. On the Instructor machine only: In the labs\instr\reset folder resides the reset.sql file.
2. Drag the reset.sql file to the desktop.
3. The instructor is responsible for running the contents of the reset.sql file once the first module ends (which should be at the end of day three).
4. This script requires the instructor to know the sid and the number of accounts to be reset. The instructor is prompted for this information.
5. All users must be disconnected from the session in order for the reset scripts to work.
6. The script is run from SQL*Plus and the working directory in SQL*Plus must be set to the \labs\reset folder.
7. Once the information is provided, the accounts are automatically rebuilt for the second module.
8. To test the account, issue the following SQL statement from one of the reset accounts:
SELECT COUNT(*) FROM employees;
==========================================================
You may have to uncomment lines in the reset script.
==========================================================
Would you also check that underneath the student and instr folders on the C drive there are folders for both parts of the class SQL1 and SQL2. Also have a student check that they have a folder for SQL2 in addition to SQL1. Thanks.
Byron Rendar
PCC Computer Education Program

Certification Exams Info

Hi Mark,

Please pass this information to your class. I believe there are 6 people in your class, right?

Voucher#: WF85374385 Expiration Date: September 08, 2007Voucher#: WF85438925 Expiration Date: September 08, 2007Voucher#: WF85508496 Expiration Date: September 08, 2007Voucher#: WF85541028 Expiration Date: September 08, 2007Voucher#: WF85638934 Expiration Date: September 08, 2007Voucher#: WF85769874 Expiration Date: September 08, 2007

INSTRUCTIONS FOR REDEEMING THE 40% DISCOUNT EVOUCHERS:
1) Take the exam no later than the expiration date specified on the eVoucher. Voucher expiration dates cannot be extended.
2) Provide the voucher number specified at the time of registration.
To register for a test delivered at an Authorized Prometric Testing Center (APTC), go to the OCP website at http://www.oracle.com/education/certification/testreg.html, and click on the link Register for an Exam. Provide the voucher number specified above at the time of registration. If registering by phone, tell the Prometric representative you wish to apply this voucher to your payment. If using Prometric’s online registration, there will be a field provided where you can enter the voucher number specified on the eVoucher. Provide a payment method for the remaining amount due. Prometric accepts Visa, Mastercard, and American Express/Optima.
To take an unproctored test online, log on to http://oracle.prometric.com . Once you complete your registration and are ready to test, enter the voucher number specified on the eVoucher in lieu of payment. Provide a payment method for the remaining amount due. Prometric accepts Visa, Mastercard, and American Express/Optima.
3) Present the eVoucher at the time of testing at the APTC.
The eVoucher (i) can be used by one person, one time, for one exam discount/fee only (ii) is not valid after the expiration date, (iii) is void if altered or revised in any way, (iv) may not be canceled or redeemed for cash, credit or refund, and (v) may be used by the Purchaser only. Neither Prometric, Authorized Prometric Testing Centers, nor Oracle and its partners are responsible for vouchers that are lost or stolen. Prior to taking your exam, you will be presented with an online Oracle Certification Program Candidate Agreement. Upon acceptance of the agreement, you will be able to proceed with the exam. To view a copy of the agreement, go to http://www.oracle.com/education/certification/index.html?canagreemt.html
To register for your exam, visit Prometric website at: www.prometric.com/candidates

SelfTest Software Certification Practice Exam

Some of our courses are associated with a certification exam. If this course is associated with an exam then you are eligible to download a SelfTest Software practice exam to help you prepare for the certification exam associated with this class. Even if you are not interested in certification, the practice exam will help you identify where you can improve your knowledge and move closer to mastering the technology.

To Get Your Self Test Software Practice Exam:

E-mail us the information we need and we will arrange to have a download authorization code e-mailed directly to you. Here's how it works:

1) Before the class end date, e-mail Lois Jurhs (ljurhs@pcc.edu) from the address at which you want to receive your download authorization code and give her:

- Your name
- The class you are in
- CRN for the class
- Your request for a SelfTest Software practice exam download code.
(Something like "Please send me a SelfTest Software practice exam
authorization code. Thanks!")

Make sure to do this before the end of the class.

2) Lois will verify that you are actually enrolled in the class and have not already received your download code, then she will arrange for SelfTest Software to e-mail you a download authorization code.

3) The download authorization code will arrive from VSHOP, unfortunately looking a little bit like spam. Don't delete it! :-)

4) Go to www.SelfTestSoftware.com and create an account for yourself. Once you have created an account, you can then redeem your download authorization code and follow the instructions to obtain your practice exam. You must redeem your code within 90 days of receiving it—if you don't it will expire! Once you have redeemed your code, the practice exam never expires.


NOTE: When you order an Oracle exam, you should know the exam number corresponding to the exam you want to take. This includes (see other side or next page)

1Z0-042 Oracle Database 10g: Administration I
1Z0-043 Oracle Database 10g: Administration II
1Z0-040 Oracle 10g New Features for Administrators
1Z0-007 Introduction to Oracle9i: SQL
1Z0-031 Oracle9i Database: Fundamentals I
1Z0-032 Oracle9i Database: Fundamentals II
1Z0-033 Oracle9i Database: Performance Tuning
1Z0-147 Oracle: Program with PL/SQL

PCC Fall 2006

Mark Schofer







mark.schofer@tek.com
mark.schofer@gmail.com
503-816-2774
503-627-3008



Revised Class Schedule since we can not work from home.
530 -630 HomeWork
630-715 Lecture
715 -810 HomeWork
810-845 Lectures






My Lesson Plan

Lesson 1 - Intro
· Course Objective
· Course Outline
· RDBMS Concept - ER diagram & class data model
· What is Oracle?
· SQL, SQL*Plus, and PL/SQL
· Unix Primer
· Navigation: cd, ls
· File manipulation: cp, rm, mv, more/cat, mkdir, vi
· ****** LAB

Lesson 1 - Basic Query
· Basic Query Block: SELECT...FROM...WHERE...
· Column Aliases
· Operators
· Arithmetic Operators
· Concatenation/Character Operator, Literal Character String
· Managing NULL values: NVL functions
· Preventing the selection of duplicate Rows w/ DISTINCT
· SQL*Plus intro: meta schema, table structures
· ****** LAB1

Chapter 1
Problems 6, 7, 8 , 9 & 10.
---Question for Byron ......How are students getting to the Labs from home

Action Item for mark
(Note Chapter 2 and Chapter 3 were lectured together
Lesson 2
· Datatypes
· Ordering of rows with the ORDER BY clause
· WHERE Condition(s)
· Comparison Operators
· Logical Operators
· ****** LAB

Lesson 3
· Single Row Functions
· Character Functions
· Number Functions
· Date Functions
· Conversion Functions
· Nesting
· Formatting: Number, Dates
· ****** LAB
Click Here for Lab
===========================
Chapter Two HomeWork -5, 7 ,8, 9
Chapter Three HomeWork 5,6,7
Lab Time One Hour
===========================

Lesson 4 - Advanced Query Concepts
Group By Functions
-----------------------------------------------------------
Class Number Three

=============================
Chapter 4 Home Work
4,5,6,8
=============================


Lesson 5
· Joins
· Cartesion Products
· Equijoin
· Self join
· Outer Join
· ****** LAB
· Group Functions
· GROUP BY Clause
· HAVING Clause
· ****** LAB
· Subqueries
· ****** LAB
=====================(Chapter5 HomeWork 4-6-7)
Lesson 4 - SQL*Plus Scripting
· SQL*Plus Commands
· Environment Commands: SET, CLEAR
· Format Command: COLUMN
· File Manipulation: EDIT, GET, SAVE, SPOOL, START
· Interaction: ACCEPT, PROMPT
· ****** LAB
· Specifying variables at Runtime
· Substitution variables
· Single Ampersand
· Double Ampersand
· Defining User Variables
· Passing Values into a Script File
· ****** LAB
Lesson 6 - Complex Queries and Lesson 7 Set Operators
Homework
Chapter 6 - (2,3,4)
Chapter7 - (5)

==========================
Lesson 8 - Data Manipulation Language (DML)
The Insert, Update and Delete (4-15)
============================

Lesson 9 Creating Tables

Lesson 10 Creating Other Schema Objects

· ****** LAB

Lesson 8 - Interacting w/Oracle & Control Flow

===========================

To instructor: 10g: SQL class Fall, 2006

The database is on a Linux machine cp206-13 in the back row, labeled “Acapulco” in 206. When you turn on the machine you will see a boot menu – move the cursor to “Linux root drive” to boot Linux. At the Linux login prompt login with

Name: ora10103

Password: oracle

Then start up the database, the listener, and I*SQL+ by

./oradb start

At the end of each class stop everything by (on the database server)

./oradb stop

To logout and shutdown the database server

There is an icon at the top of the window which is a door with an arrowhead. Click on that to logout. At the login menu that comes up, you will see a shutdown item at the bottom. Click on that to shutdown the computer.

On student and instructor machines log on and click on the “Oracle 10g” partition. That brings up Windows XP.

The course consists of two modules, SQL Fundamentals I and SQL Fundamentals II.

At the end of module I (about the 3^rd or 4^th class), you need to run reset.sql. Look at the file – you need to pass the SID (T5), the number of student accounts (15), and the students’ passwords (15) and the system password (manager).

If you need sysadmin privileges on the Linux server, the root password is password

On the instructor machine desktop are shortcuts to

SQ1.pdf

SQ2.pdf

reset.sql

sqlplusw (this is on student machines also).

Student files are in C:\labs and there is a similar folder on the instructor machine with both student and instructor files.

=============================================

Attendance

Tom Wu xx
Ilene Vu xx
Deb Fay xx
Hung Vu xx
Rian Gilronan xx
Norman Salas xx
Anumeha Gupta x