Database Design and Programming with SQL

Categories

Oracle Database

Database Design and Programming with SQL

Database Design and Programming with SQL

Students learn to analyze complex business scenarios to develop data models and learn to implement and perform advanced queries on databases using SQL in hands-on, engaging activities.
180 Hours

For students: Who wish to learn techniques beyond SQL to execute procedural logic on a database.

Educator prerequisites: High level knowledge of database design concepts and of programming with SQL.

Target Audiences

Educators

College/university faculty who teach computer programming, information communications technology (ICT), or a related subject

Secondary school teachers who teach computer programming, ICT, or a related subject

Students

Students who wish to learn the techniques and tools to design, guild and extract information from a database

Students who possess basic mathematical, logical, and analytical problem-solving skills

Novice programmers, as well as those at advanced levels, to learning the SQL Programming language to an advanced level

Prerequisites

Required

Ease with using a computer

General knowledge of databases and query activity

Suggested

None

Suggested Next Courses

Database Programming with PL/SQL

 Lesson-by-Lesson Topics

Database 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