12/26/2024 5:02:21 PM	CIS 69.54A Course Outline as of Fall 2003	Changed Course
CATALOG INFORMATION
Discipline and Nbr:  CIS 69.54A	Title:  ORACLE SQL FUNDAMENTALS
Full Title:  Oracle SQL Fundamentals
Last Reviewed:2/10/2003

Units		Course Hours per Week			Nbr of Weeks	Course Hours Total
Maximum	1.50	Lecture Scheduled	2.00		8 max.	Lecture Scheduled	16.00
Minimum	1.50	Lab Scheduled	0		8 min.	Lab Scheduled	0
		Contact DHR	3.50			Contact DHR	28.00
		Contact Total	5.50			Contact Total	44.00
		Non-contact DHR	0			Non-contact DHR Total	0

Total Out of Class Hours:  32.00

Total Student Learning Hours: 76.00

Title 5 Category:  AA Degree Applicable
Grading:  Grade or P/NP
Repeatability:  00 - Two Repeats if Grade was D, F, NC, or NP
Also Listed As: 
Formerly:  CIS 69.54

Catalog Description:

This class will provide students with an introduction to applications development and database administration using Oracle. Topics will include an introduction to Structured Query Language (SQL) and Perl/Structured Query Language (PL/SQL), relational database design, and database administration topics such as permissions, objects, roles, networking, constraints and database integrity.

Prerequisites/Corequisites:
Course Completion or Current Enrollment in CS 81.61 ( or CIS 69.53 or CIS 82.25)

Recommended Preparation:
Eligibility for ENGL 100 or ESL 100

Limits on Enrollment:

Schedule of Classes Information
Description:

This class introduces applications development and database administration using Oracle. Topics include an introduction to Structured Query Language (SQL) and Perl/Structured Query Language (PL/SQL), relational database design and database administration.

(Grade or P/NP)
Prerequisites:Course Completion or Current Enrollment in CS 81.61 ( or CIS 69.53 or CIS 82.25)
Recommended:Eligibility for ENGL 100 or ESL 100
Limits on Enrollment:
Transfer Credit:CSU;
Repeatability:00 - Two Repeats if Grade was D, F, NC, or NP

ARTICULATION, MAJOR, and CERTIFICATION INFORMATION

Associate Degree:		Effective:		Inactive:
Area:
CSU GE:	Transfer Area			Effective:	Inactive:
IGETC:	Transfer Area			Effective:	Inactive:
CSU Transfer:	Transferable	Effective:	Spring 2002	Inactive:	Summer 2010
UC Transfer:		Effective:		Inactive:
C-ID:

Certificate/Major Applicable: Certificate Applicable Course

COURSE CONTENT

Outcomes and Objectives:
At the conclusion of this course, the student should be able to:

Upon completion of the course, students will be able to:

1.  Describe the fundamental principles of entity relationship (ER)

   analysis.

2.  Evaluate and describe steps involved in constructing an entity

   relationship diagram (ERD).

3.  Differentiate between the forms and functions SQL and SQL*Plus.

4.  Summarize the structure and standards for SQL.

5.  Describe the main functions of Development Makeup Language (DML)

   select command, five basic types of predicates, and use of arithmetic

   expressions and logical connectives.

6.  Analyze the sub-query methods for multi-table queries.

7.  Compare the effects and purpose of the SELECT statement and use of the

   WHERE and ORDER BY clause with the select statement.

8.  Distinguish between equijoins and non-equijoins and when to use outer

   joins and self-joins.

9.  Compare and contrast the different group functions available to the

   SQL user.  Describe differences between public and private synonyms.

10. Create, drop, and view indexes and synonyms.

11. Evaluate and describe how to create and use sequences.

12. Describe how to create, modify, and delete simple and complex views,

   include options to protect data integrity.

Topics and Scope

1.  Creation and maintenance of database systems

   a.  System development life cycle

   b.  Data storage on different media

   c.  Relational database concepts and properties

   d.  Data models

   e.  Entity relationship models and conventions

2.  SQL*Plus concepts

   a.  Relationship between SQL and SQL*Plus

   b.  SQL*Plus overview

   c.  Logging in to SQL*Plus

   d.  Displaying table structure with SQL*Plus

   e.  SQL*Plus editing commands

   f.  SQL*Plus file commands

3.  Restricting and sorting data

   a.  Limiting rows using a selection

   b.  Using the WHERE clause

   c.  Character strings and dates

   d.  Comparison operators

   e.  Sorting: descending, by column alias, by multiple columns

4.  Single row functions

   a.  SQL functions

       1)  Character functions

       2)  Number functions

       3)  Date functions

   b.  NVL (check-expression, replace-expression) function

   c.  RR Date format

   d.  Data type conversions

5.  Displaying data from multiple tables

   a.  Join overview

   b.  Type of joins

       1)  Equijoin

       2)  Outer joins

       3)  Inner joins

       4)  Self joins

   c.  Logical operators: AND and OR

   d.  Table aliases

6.  Aggregating data using group functions

   a.  Types of group functions

   b.  Group functions and null values

   c.  Creating groups of data

   d.  Using NVL function with group functions

   e.  Excluding group results

   f.  Nesting group functions

7.  Subqueries

   a.  Guidelines for using subqueries

   b.  Single-row subqueries

   c.  Multi-row subqueries

   d.  Overview of ANY and ALL operators

8.  Manipulating data

   a.  Data Manipulation Language (DML)

   b.  INSERT, UPDATE, and DELETE statements

   c.  COMMIT and ROLLBACK statements

   d.  Read consistency

9.  Creating and managing tables

   a.  Database objects

   b.  CREATE TABLE statement

   c.  Querying the data dictionary

   d.  ALTER TABLE statement

   e.  DEFAULT and SET UNUSED options

   f.  Dropping and truncating a table

   g.  Changing the name of an object

10. Creating and manipulating views

   a.  Simple and complex views

   b.  Creating and retrieving data from a view

   c.  Modifying views

   d.  Rules for using DML operations on views

   e.  Denying DML operations

   f.  Inline views

   g.  Performing "Top-N" analysis

11. Other database objects

   a.  Sequences

       1)  CREATE SEQUENCE statement

       2)  Confirming sequences

       3)  Using, modifying, and removing a sequence

   b.  Indexes

       1)  Creating an index

       2)  Function-based indexes

   c.  Creating and removing synonyms

12. Interacting with the Oracle server

   a.  Overview of PL/SQL

   b.  PL/SQL syntax and guidelines

   c.  SELECT statements

   d.  Inserting, updating, and deleting data w/ PL/SQL

   e.  COMMIT and ROLLBACK statements

13. PL/SQL control structures

   a.  IF/THEN/ELSE statement

   b.  FOR statement

   c.  WHILE statement

14. Working with composite datatypes

   a.  PL/SQL records

   b.  %ROWTYPE attributes

   c.  Creating PL/SQL tables

   d.  Using PL/SQL table methods

Assignments:

1.  Reading approximately 25 pages per week from text book.

2.  Weekly lab assignments practicing the concepts of the week.

3.  Taking quizzes and exams, both ojbective and hands-on.

Methods of Evaluation/Basis of Grade.

Writing: Assessment tools that demonstrate writing skill and/or require students to select, organize and explain ideas in writing.		Writing 0 - 0%
None
This is a degree applicable course but assessment tools based on writing are not included because problem solving assessments and skill demonstrations are more appropriate for this course.
Problem solving: Assessment tools, other than exams, that demonstrate competence in computational or non-computational problem solving skills.		Problem Solving 40 - 60%
Homework problems
Skill Demonstrations: All skill-based and physical demonstrations used for assessment purposes including skill performance exams.		Skill Demonstrations 20 - 30%
Performance exams
Exams: All forms of formal testing, other than skill performance exams.		Exams 20 - 30%
Multiple choice, True/false, Matching items, Completion
Other: Includes any assessment tools that do not logically fit into the above categories.		Other Category 0 - 0%
None

Representative Textbooks and Materials:

1.  "Oracle 8i: A Beginner's Guide", by Abbey, Abramson & Corey -

   McGraw-Hill Professional Book Group. 1999

2.  "Learn Oracle 8i", by Jose A. Ramahlo - Wordware Publishing, Inc.

   2000.

3.  "Enhances Guide to Oracle 8i", by Joline and Mike Morrison -

   Course Technology, 2002.

Print PDF