Database Essentials With Oracle
Course Contents Listing

Class 1 Register
  1. Overview
    • Introductions
    • Class Syllabus
    • Homework
    • Grading
    • quizzes
    • mid-term
    • final
    • Class Website
    • Resources
    • Contacts
  2. Hardware (minimum requirements)
    • Pentium
    • 1GB RAM (500MB marginal)
    • CDROM drive or Fast Internet connection
  3. Software
    • Windows, Linux, UNIX
    • Oracle Database 10gR2 Enterprise Edition
  4. Method
    • Source
    • Download- otn.oracle.com
    • Purchase- store.oracle.com
  5. SQL*Plus (no GUIs)
  6. Database Contained Object Types (by Quarter)

    Q1

    • Heap Table
    • Global Temporary Table
    • External Tables
    • Compressed Tables
    • Index Organized Table
    • Partitioned Table
    • Cluster
    • Primary Key Constraint
    • Unique Constraint
    • Foreign Key Constraint
    • Check Constraint
    • B*Tree Indexes
    • Bitmap Indexes
    • Bitmap Join Indexes
    • Clusters
    • Compressed Indexes
    • Descending Indexes
    • Function Based Indexes
    • Global Indexes
    • Local Indexes
    • No Segment (Virtual) Indexes
    • Reverse Key Indexes
    • Sequences
    • Views
    • Materialized Views (Snapshot)
    • Built-in Functions

    Q2

    • Procedure
    • Function
    • Package Specification
    • Package Body
    • Operator
    • Table Trigger
    • Instead-of Trigger
    • DDL Event Trigger
    • System Event Trigger
    • Type
    • Directory
    • Built-in Packages
  7. The two most dangerous commands
    • Drop
    • Truncate
  8. Intro to General Database Concepts
    • Flat File
    • Hierarchical
    • Relational
    • Object
    • Object Relational
    • XML
  9. Languages
    • SQL (DDL, DCL, DML)
    • PL/SQL (based on ADA)
    • SQLJ (deprecated)
    • Java
    • C (OCI)
    • C++ (OCCI)
    • Cobol
    • Fortran
    • XML
    • .NET (ODP.NET)
    • Perl
  10. Intro to General Relational Concepts
    • Codd's 12 Rules
    • Normalization
  11. Platform and operating system
  12. independence
  13. ANSI Standard: The mythology
  14. Oracle Mythology & Dinosaurs
  15. Oracle != SQL Server
  16. SQL Server Oracle
  17. Installation Instance
  18. Database Schema
  19. Tablespace Segment
  20. Derived Table Inline View
  21. Cluster Index Organized Table
  22. No similar concept Database
  23. No similar concept Cluster
  24. Intro to Oracle Architecture
  25. An Oracle Installation
  26. Environment Variables
    • ORACLE_BASE
    • ORACLE_HOME
    • ORACLE_SID
  27. File System Layout
    • /admin
    • bdump
    • pfile
    • udump
    • /bin
    • multiple executables
    • /database/
    • init.ora
    • spfile
    • password file
    • /dbs
    • same as /database/ for *nix
    • /network/admin
    • listeners and listener.ora
    • sqlnet.oa
    • tnsnames.ora
    • /rdbms
    • sql.bsq
    • catalog.sql
    • catproc.sql
  28. Important Executables
    • oracle.exe
    • dbca
    • netca
    • orapwd
    • sqlplus
    • sqlplusw
  29. Database Infrastructure Object Types
    • Physical
      • datafile
      • tempfile
      • control file
      • log file
      • block
    • Logical
      • database
      • instance
      • tablespace
      • tablespace group
      • segment
      • extent
      • user
      • schema
  30. Oracle Data Dictionary Basics
    • user_
    • all_
    • dba_
    • gv$ and v$
    • x$
  31. What makes Oracle Oracle
    • Multiversion Read Consistency
    • Reads don't block writes
    • Writes don't block reads
    • Database Flashback
    • Transaction Flashback
    • Locking
  32. CREATE USER and Security Basics


Class 2
  1. Database Infrastructure Object Types
    • Physical
      • datafile
      • tempfile
      • control file
      • log file
      • block
    • Logical
      • database
      • instance
      • tablespace
      • tablespace group
      • segment
      • extent
      • user
      • schema
  2. Oracle Data Dictionary Basics
    • user_
    • all_
    • dba_
    • gv$ and v$
    • x$
  3. Demo 1: Tablespaces
  4. Demo 2: Data File Locations
  5. Demo 3: Create Tablespace
  6. Demo 4: Create User
  7. Demo 5: Grant Privileges
  8. Demo 6: Heap Tables
  9. Demo 7: Global Temporary Tables
  10. Demo 8: Create Compressed Tablespace & Table


Class 3
  1. Review
    • Pseudo-column Review (ROWID and ROWNUM)
    • Heap Table
    • Global Temporary Table
    • Compressed Table
    • External Table & Directory
    • Partitioned Tables
  2. Tables (New)
  3. Partition Pruning
  4. Index Organized Tables
  5. NULL / NOT NULL
  6. Column Defaults
  7. Clusters (New)
  8. Multi-table Clusters
  9. Sorted Hash Clusters
  10. Constraints (New)
  11. Primary Key Constraints
  12. Unique Constraints


Class 4
  1. Constraints (New)
  2. Foreign Key Constraints
  3. Check Constraints
  4. Data Types (New)
  5. Index Concepts (New)
  6. Index Concepts
  7. Clustering Factor
  8. Local & Global Indexes
  9. Unique & Non-unique Indexes
  10. Index Storage Parameters
  11. Logging and NOLOGGING
  12. MONITORING Keyword
  13. NOSORT Keyword
  14. PARALLEL Keyword
  15. Index REBUILD
  16. Index Types (New)
  17. B*Tree
  18. Bitmap
  19. Bitmap Join Indexes
  20. Compressed Indexes
  21. Descending Indexes
  22. Function Based Indexes
  23. No Segment Indexes
  24. Reverse Indexes


Class 5
  1. Index Types (New)
  2. Compressed Indexes
  3. Descending Indexes
  4. Function Based Indexes
  5. No Segment Indexes
  6. Reverse Indexes
  7. DML Clauses (New)
  8. Insert Statements
  9. ISO & ANSI Syntax Joins
  10. SELECT Clause
  11. FROM Clause
  12. WHERE Clause (filtering)
  13. GROUP BY Clause
  14. HAVING Clause
  15. ORDER BY Clause
  16. Conditions
  17. EXISTS
  18. LIKE
  19. LOGICAL
  20. MEMBERSHIP
  21. RANGE
  22. SINGLE COMPARISON


Class 6
  1. DML Clauses (New)
  2. ISO & ANSI Syntax Joins
  3. SELECT Clause
  4. FROM Clause
  5. WHERE Clause (joins)
  6. WHERE Clause (filtering)
  7. Delete Statement
  8. Update Statement
  9. Merge Statement
  10. Conditions
  11. EXISTS
  12. LIKE
  13. LOGICAL
  14. MEMBERSHIP
  15. RANGE
  16. SINGLE COMPARISON
  17. Wildcards
  18. New Objects
  19. Views and Aliasing


Class 7
  1. Numeric Functions
    • count(owner)
    • count()
    • distinct
    • sum
    • avg
    • round
    • max
    • min
    • floor
    • ceil
    • <
    • >
    • <=
    • >=
    • != OR <>
    • abs
    • sqrt
    • greatest
    • least
    • length
    • mod
    • power
    • trunc
  2. String Functions
    • altering the display of data without altering the data itself (SOX)
    • substr from start and end, returns string
    • instr returns number, iterations
    • upper
    • lower
    • initcap on upper, lower, and mixed
    • ascii
    • char
    • ||
    • lpad and rpad
    • trim
    • reverse
    • distinct/unique
    • concatenate
    • comma delimited parsing
  3. Date Functions
  4. distinct
  5. round
  6. greatest
  7. least
  8. max
  9. min
  10. trunc
  11. time component
  12. -
  13. +
  14. age calculation
  15. Conversion Functions
  16. implicit conversions
    • to_number
    • to_char
    • to_date
    • default date format


Class 8
  1. Index Organized Tables
  2. Inline Views
  3. Sequences
  4. Synonyms (Private & Public)
  5. More Functions
  6. CONNECT BY
  7. TRANSLATE
  8. REPLACE
  9. DECODE
  10. CASE
  11. SET Operators
  12. intersect
  13. minus
  14. union all
  15. union


Class 9
  1. Final
  2. final1_ddl.sql
  3. final1_dml.sql
  4. Materialized Views
  5. Explain Plan and Tuning
  6. Autotrace
  7. Cursor Sharing
  8. More Functions
  9. DECODE
  10. CASE
  11. SET Operators
  12. intersect
  13. minus
  14. union all
  15. union


Class 10
  1. More Functions
  2. SET Operators
  3. intersect
  4. minus
  5. union all
  6. union
  7. Gathering Statistics
  8. Bind Variables
  9. Database Links
  10. Grouping Sets, Rollup, and Cube
  11. Analytic Functions
  12. SQL for Interviews
  13. Q2 Preview

 



    Copyright © SQLUniversity.net