Oracle Academy: Database Design and Programming with SQL (Online) 2020-2021

Online

This 180 hour online course engages you to analyse complex business scenarios and create a data model—a conceptual representation of an organisation’s information. It comprises two equally sized halves, DB design, and DB programming using SQL.

You will implement your database design by creating a physical database using SQL. Basic SQL syntax and the rules for constructing valid SQL statements are reviewed. This course culminates with a project that challenges you to design, implement, and demonstrate a database solution for a business or organisation.

Exam Information

Through Oracle's Certification paths, you demonstrate a professional level of technical skills recognised throughout the information technology industry, this gives you a competitive edge in your career.

Completion of Database Design and Programming with SQL course prepares you close to being ready to sit the optional external exam Oracle Database SQL | 1Z0-071. Read more about this exam.

• All current Cambridge University members (departments and colleges)
• Further details regarding eligibility criteria are available

• Ease with using a computer
• General knowledge of databases and query activity

DB Design

• Introduction
  • Introduction to the Oracle Academy
  • Data vs. Information
  • History of the Database
  • Major Transformations in Computing
• Entities and Attributes
  • Conceptual and Physical Models
  • Entities, Instances, Attributes, and Identifiers
  • Entity Relationship Modeling and ERDs
• Relationship Basics
  • Identifying Relationships
  • ER Diagramming Conventions
  • Speaking ERDish & Drawing Relationships
  • Matrix Diagrams
• Super/Sub Types and Business Rules
  • Supertypes and Subtypes
  • Documenting Business Rules
• Relationship Fundamentals
  • Relationship Transferability
  • Relationship Types
  • Resolving Many-to-Many Relationships
  • Understanding CRUD Requirements
• UIDs and Normalization
  • Artificial, Composite, and Secondary UIDs
  • Normalization and First Normal Form
  • Second Normal Form
  • Third Normal Form
• Arcs, Hierarchies, and Recursive Modeling
  • Arcs
  • Hierarchies and Recursive Relationships
• Changes and Historical Modeling
  • Modeling Historical Data
  • Modeling Change: Time
  • Modeling Change: Price
  • Drawing Conventions for Readability
• Mapping
  • Introduction to Relational Database Concepts
  • Basic Mapping: The Transformation Process
  • Relationship Mapping
  • Subtype Mapping
• Creating Database Projects
  • System Development Life Cycle
  • Project Overview and Getting Started
  • Presentation Project Management
  • Final Presentation Components
• Presenting Database Projects
  • Creating Tables for the Final Presentation
  • Preparing Written Documentation
  • Preparing Visual Materials
  • Final Presentations

Database Programming with SQL

• Introduction
  • Oracle Application Express
  • Relational Database Technology
  • Anatomy of a SQL Statement
• SELECT and WHERE
  • Columns, Characters, and Rows
  • Limit Rows Selected
  • Comparison Operators
• WHERE, ORDER BY, and Intro to Functions
  • Logical Comparisons and Precedence Rules
  • Sorting Rows
  • Introduction to Functions
• Single Row Functions Part I
  • Case and Character Manipulation
  • Number Functions
  • Date Functions
• Single Row Functions Part II
  • Conversion Functions
  • NULL Functions
  • Conditional Expressions
• JOINs
  • Cross Joins and Natural Joins
  • Join Clauses
  • Inner versus Outer Joins
  • Self-Joins and Hierarchical Queries
  • Oracle Equijoin and Cartesian Product
  • Oracle Nonequijoins and Outer Joins
• Group Functions
  • Group Functions
  • Oracle Nonequijoins and Outer Joins
  • Using Group By and Having Clauses
  • Using Rollup and Cube Operations, and Grouping Sets
  • Using Set Operators
• Subqueries
  • Fundamentals of Subqueries
  • Single-Row Subqueries
  • Multiple-Row Subqueries
  • Correlated Subqueries
• Ensuring Quality Queries Part I
  • Ensuring Quality Query Results
• DML
  • INSERT Statements
  • Updating Column Values and Deleting Rows
  • DEFAULT Values, MERGE, and Multi-Table Inserts
• DDL
  • Creating Tables
  • Using Data Types
  • Modifying a Table
• Constraints
  • Intro to Constraints; NOT NULL and UNIQUE Constraints
  • PRIMARY KEY, FOREIGN KEY, and CHECK Constraints
  • Managing Constraints
• Views
  • Creating Views
  • DML Operations and Views
  • Managing Views
• Sequences and Synonyms
  • Working With Sequences
  • Indexes and Synonyms
• Privileges and Regular Expressions
  • Controlling User Access
  • Creating and Revoking Object Privileges
  • Regular Expressions
• TCL
  • Database Transactions
• Final Project and Exam Review
  • Testing
  • Final Project Database Creation
  • Final Exam Review
• Ensuring Quality Queries Part II
  • Ensuring Quality Query Results - Advanced Techniques

What is needed to access Oracle iLearning:

A browser, either:

• IE | Edge | Chrome | FireFox | Safari
• Cookies must be enabled
• Pop-ups must be allowed
• Adobe Acrobat Reader

Each course is approximately 90 hours on a self study basis, however the course should be completed within one year of enrolment.

On-going

• Oracle Academy: Database Foundations (Online) 2023-2024
• Oracle Academy: Database Programming with PL/SQL (Online) 2021-2022
• Oracle Academy: Application Express – Application Development Foundations (Online) 2021-2022

• Databases
• Programming and Scripting