Advanced SQL Programming

What you will learn

At the end of this course, you will be able to:

Master the concepts of the relational data model underlying the SQL, SQL2 and SQL3 standards (Codd's theorem on relational algebra, Gray's theorem on transactions, OLAP).
Master SQL2 for relational databases (definition, manipulation and control).
Master SQL3 for relational object data models (the Date manifest) with the integration of pointers within data tables.
Retrieve row and column data from tables with the SELECT statement.
Create reports of sorted and restricted data.
Employ SQL functions to generate and retrieve customized data.
Run complex queries to retrieve data from multiple tables.
Run data manipulation language (DML) statements to update data in a database.
Run data definition language (DDL) statements to create and manage schema objects.

Description

Managing data in the past, present and future requires mastery of the SQL standard. SQL is versatile enough to encompass all types of data in Big Data systems: relational (SQL2), object-relational (SQL3, OQL) and non-relational (NoSQL, NewSQL).

Format

This GRADEO is composed of 2 courses:

- 1 ESTIA academic course: SQL relational & object programming
- 1 Oracle professional course: SQL on Oracle Cloud

The Oracle Professional Course is to be taken on the Oracle platform.

To ensure the efficient implementation of this GRADEO, you will be asked to fill in a form after your registration to enable Datum Academy, technical partner of FUN and ESTIA, to provide you with an activation key to access your Oracle course. Once you have the activation key, all you need to do is create your Oracle account to take the course!

The ESTIA academic course lasts an estimated 6 weeks, with one module per week.

Learning the Oracle Professional Course takes an estimated 3 weeks. It should be noted that learners of this program will be issued with an activation key giving them access to the course and exam for a period of 6 months.

Prerequisites

Learners must have a good command of basic mathematics (set theory) and computer science (bachelor 2-3 years level).

Assessment and certification

The learner will take an exam at the end of each course. Upon successful completion of these exams, they will receive a GRADEO certificate issued by ESTIA (corresponding to 3 ECTS), as well as the Oracle certification "Oracle Database SQL Specialist (1Z0-914)".

The ESTIA certificate of success will be delivered by email to the learner. The Oracle professional certificate can be downloaded directly to the Oracle platform upon successful completion of the corresponding exam.

Course plan

- - Introduction
  - Data paradigms
  - Codd's relational data model
  - Conclusion
  - Exercise : Codd's algebra
  - Exercise : Codd's relational data model
  - Self-assessment quiz
- - Introduction
  - Introduction to Codd & Date method for relational schema design
  - Datawarehouse
  - Database storage & access
  - Exercice : Theorem of Casey & Delobel
  - Exercise : Jim Gray's cube
  - Exercise : Dynamic hashing & B=Tree
- - Introduction
  - Schema definition with SQL2
  - DB manipulation with SQL
  - Exercise : Codd's algebra & SQL2
  - DB control with SQL
  - Conclusion
  - Exercise : Car rental agency
  - Exercise : SQL2
  - Self-assessment quiz
- - Introduction
  - Object paradigm & databases
  - Object-oriented data model
  - Object relational data model
  - D language & Thesaurus
  - Conclusion
  - Second DB manifesto & illusta/postgres by Mike Stonebraker
  - Object-middleware approach & universal access with DCOM
  - Covid relational schema
  - Exercise : Date's schema
  - Self-assessment quiz
- - Introduciton
  - OQL
  - Thesaurus
  - Exercise : Covid relational schema
  - Exercise : ODMG schema
- - Introduction
  - Major object features within SQL3
  - Critics of the double paradigm
  - Exercise : SQL3 & ODMG
  - Conclusion
  - Conclusion
  - Exercise : SQL3 schema
  - Self-assessment quiz
- - Introduction
  - Entity relationship model
  - Practice
- - Retrieving Data using the SQL SELECT statement
  - Practice
- - Restricting and sorting Data
  - Practice
- - Using single-row functions to customize output
  - Number functions
  - Practice
- - Using conversion functions and conditional expressions
  - General functions
  - Practice
- - Reporting aggregated data using the group functions
  - Practice
- - Displaying data from mutiple tables using joins
  - Practice
- - Using subqueries to solve queries
  - Practice
- - Using set operators
  - Practice
- - Managing tables using DML statements in Oracle
  - State of data
  - Practice
- - Introduction to data definition language in Oracle
  - Foreign key constraint
  - Practice
- - Introduction to data dictionary views
  - Practice
- - Creating sequences, synonyms, and indexes
  - Sequence information
  - Practice
- - Creating views
  - Practice
- - Managing schema objects
  - DROP TABLE
  - Practice
- - Retrieving data by using subqueries
  - Practice
- - Manipulation data by using subqueries
  - Practice
- - Manipulating data using advanced queries
  - MERGE statement
  - Practice
- - Managing data in different time zones
  - Practice
- - Conclusion
- - Work on your final project

Course team

Serge Miranda

Organizations

ESTIA

In partnership with

Courses included in this GRADEO

SQL Relational & Object Programming

SQL on Oracle Cloud

License

License for the course content

Attribution-NonCommercial-NoDerivatives

You are free to:

Share — copy and redistribute the material in any medium or format

Under the following terms:

Attribution — You must give appropriate credit, provide a link to the license, and indicate if changes were made. You may do so in any reasonable manner, but not in any way that suggests the licensor endorses you or your use.
NonCommercial — You may not use the material for commercial purposes.
NoDerivatives — If you remix, transform, or build upon the material, you may not distribute the modified material.