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