Prerequisites and Logistics

This is a 2-day day workshop delivered using the Oracle and PostgreSQL databases. Delegates should be experienced at writing SQL queries. A basic understanding of Java programming would also be useful.

Delegates can bring their own laptop, in which case setup instructions will be provided. Alternatively we can also provide hardware and software on request.

Outline

DAY 1 - SQL Functionality

• Introduction to the course databases and revision of core SQL constructs
• Techniques for writing SQL to maximise performance
• Advanced constructs, such as row value expressions and hierarchical SQL
• Applying window functions for complex calculations within the database
• Modern use of DML (e.g. using MERGE instead of INSERT and UPDATE)
• Using esoteric data types ( json, hstore, arrays, composite types, ranges etc…)

DAY 2 - SQL Performance

• The inner workings of an index
• The cost-based optimizer
• Why constraints are so important for performance
• Writing performant predicates
• Faster DML
• Antipatterns, myths, and best practices