CISY 286 - Introduction to Oracle: SQL and PL/SQL

Course Syllabus – Fall 2009

Section 51x - CRN# 10936

 

Room:

Monday 06:00 PM-09:50 PM West Building W310

Book:


image001.jpg

Required: Oracle Database 10g SQL, Jason Price, McGraw-Hill Osborne Media, 2004, ISBN: 0072229810

OracleREF.jpgOracleREF

Optional: Oracle 10g: The Complete Reference, Kevin Loney, McGraw-Hill Osborne Media, 2004, ISBN: 0072253517

If you plan on a career in Oracle, then the Complete Reference is strongly suggested.

Instructor:

Mr. John M. Wong
E-Mail: jwong@raritanval.edu

Web Site:

http://www2.raritanval.edu/departments/cis/part-time/jwong
(Or from school’s web site, Pick Academic Departments, then Computer Science, click Faculty then click John Wong)

Office Hours:

 

By appointment

Course Overview:

In this introductory Oracle course, students will be introduced to SQL and PL/SQL functions. They will learn how to create and maintain database objects and how to store, retrieve, and manipulate data. In addition, students will also learn how to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications.

Course Routine:

The class will be separated into a lecture and Lab.

The lecture will be from the Oracle book as well as some other topics.

The in-class lab problems are assigned almost every night. You are strongly encouraged to work on the labs in the lab with the instructor in case you have questions. You may wish to work on your own, either at home, work, or in the Open Lab (W-212).

The instructor would like to grade the lab and provide feedback prior to the next class.

Please check the schedule below for any deviations from this routine (Such as exams).

Working from Home:

The Oracle Server is only available on Campus - so if you want to run Oracle at Home you need to download a copy and install it.

Historically, many students have been able to complete all the work in the lab without having Oracle at home.

Also, historically, many students have found it helpful to have Oracle at home

The choice to install Oracle at home is a personal one.

In-Class Labs

The intention of the in-class labs is to provide you with practice for the material that was covered in class. The hope is that you stay and work on the labs while the instructor is there so that you may ask questions.

The In-Class assignments must be completed either on campus or at home. If you choose to work at home, please e-mail the work as attachments to the instructor by Saturday so that feedback can be provided by Monday.

In-Class labs are listed on the instructor’s web site.

Oracle Project

There is an Oracle Project that will be done in two parts. Please see the instructor's web site for details.

Part 1 is to make sure you are on the right track.

Part 2 will be due at the end of the semester.

Exams

Exams are open note / open book / open computer (e-mail and instant messaging are excluded).

The format of the exam is similar to the in-class labs

Cheating Policy

You must work alone on the Projects and In-Class Assignments. Working with someone is considered cheating. Taking a file that someone else created is considered cheating. Having your neighbor work on your computer is considered cheating. Cheating is not allowed.

Cheating is also not allowed on Exams. If you have questions during an exam ask the instructor.

All parties involved in cheating (including the one who shares) will be dealt with according to the school’s policy on cheating. The penalty can range from 0 on the assignment to F for the course.

Attendance Policy

Attendance will be taken at the beginning of the class. If you are late it is your responsibility to see the instructor and ask to be marked as present. If the instructor is lecturing, please wait until the break.

What everything is worth...

Item

Percent

Date

In-Class Lab Assignments

30

On going

Mid Term Exam

10

10/19/2009

Computer Project Part 1

10

10/26/2009

Computer Project Part 2

30

12/14/2009

Final Exam

20

12/21/2009

 

Grades

% Range

A

90-100

B+

87-89.9

B

80-86.9

C+

77-79.9

C

70-76.9

D

60-69.9

F

0-59.9

Class Schedule (check website for latest changes)


Week #

Week Of
(Monday)

Topic

Readings

Work Due

1

09/14/2009

Course Overview
Chapter 1 Introduction
Chapter 2 Retrieving Information from a Table
SELECT, WHERE, ANY, ALL
Chapter 5 Enough SQL*Plus to get by

Ch 1 pp. 2-26
Ch 2 pp. 28-38


Ch 5 pp. 142-148

 Lab 01

2

9/21/2009

Chapter 2 More Retrieving Information from a table
and Retrieving Information from Two or More Tables
ORDER BY, IN, DISTINCT, LIKE, BETWEEN, IS NULL,
Joins (non-equijoins vs equijoins, inner joins, outer joins, self joins)

Ch 2 pp. 38-60

Lab 02

3

9/28/2009

Chapter 3 Using Simple Functions
NVL( ), NVL2( ), LOWER( ), INITCAP( ), UPPER( ), SUBSTR( ), INSTR( ),
ROUND( ), TRUNC( ), POWER( ), TO_CHAR( ),
AVG( ), MAX( ), MIN( ), COUNT( ), SUM( ), GROUP BY, HAVING

Ch 3 pp. 62-100

Lab 03

4

10/5/2009

Chapter 4 - Storing and Processing Dates and Times

Ch 4 pp. 102-139

Lab 04

5

10/12/2009

Chapter 5 Using SQL*Plus for Reports
Chapter 6 Subqueries

Ch 5 pp. 148-163
Ch 6 pp. 166-179

Lab 05

6

10/19/2009

Midterm Exam (6:00 - 7:00) Chapters 1-4
Some topics from Chapter 7 Advanced Queries
DECODE( )


Ch 7 pp. 182-245

Lab 06

7

10/26/2009

Chapter 8 Changing Table Contents
INSERT, UPDATE, DELETE, COMMIT, ROLLBACK

Ch 8 248-270

Project Part 1 Due

8

11/2/2009

Chapter 10 - Creating/Altering Tables, Constraints, Keys
CREATE TABLE, ALTER TABLE,

Ch 10 pp. 294-309

Lab 07

9

11/9/2009

Chapter 10 - Sequences, Indexes, Views
CREATE SEQUENCE, .NEXTVAL, .CURRVAL, CREATE INDEX, CREATE VIEW

Ch 10 pp. 309-329

Lab 08

10

11/16/2009

Chapter 11 - Anonymous PL/SQL Blocks
PL/SQL: Blocks, Variables, Conditional Logic, Loops, Cursors, Exceptions

Ch 11 pp. 332-347

 Lab 09

11

11/23/2009

Chapter 11 - Named PL/SQL Blocks
PL
/SQL: CREATE PROCEDURE, CREATE FUNCTION, CREATE PACKAGE, CREATE PACKAGE BODY, CREATE TRIGGER

Ch 11 pp. 347-365

Lab 10

12

11/30/2009

Chapter 9 Security
CREATE USER, CREATE ROLE, GRANT, REVOKE
Chapter 16 High Performance SQL Tuning

Ch 9 pp. 272-292
Ch 16 pp. 530-550

Lab 11

13

12/7/2009

Lab Time for Project

 


Lab 12

 

14

12/14/2009

Lab Time for Project

 

Project Part 2 Due

Final

12/21/2009

Final Exam (6:00 - 8:00)