Prerequisites
This is a 2-day workshop delivered using the Oracle and PostgreSQL databases. Attendees should be experienced at writing SQL queries.
Course Description
This workshop enables developers to use SQL as a first class language in their applications. Due to the historic divide between database and programming skills the majority of SQL written by developers suffers from at least one of the following problems:
- It is overly complex and could be greatly simplified via refactoring
- It has poor performance, due to insufficient knowledge of DB internals
- It does not leverage modern and/or vendor specific SQL functions
All of these issues can be fixed by encouraging developers to treat SQL with as much respect as their normal code, educating them about performance implications and informing them about new features available in commercial and open source databases. This masterclass achieves these three goals.
Course Outline
DAY 1 - SQL Functionality
- Introduction to the course databases and revision of core SQL constructs
- 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 cost based optimiser (CBO)
- The inner workings of an index
- Writing performant predicates
- Constraints, or why metadata matters
- Faster writes / data manipulation (DML)
- Antipatterns, myths, and best practices
Technical requirements
You will need your own laptop. Setup instructions will be provided.
Instructor
Lukas Eder
Lukas Eder is the founder and manager of Data Geekery, which is based in Zürich. He is fascinated by the interaction between Java and SQL. He gained most of his experience in the swiss e-banking sector, mostly with JDBC, Hibernate, JOOQ and Oracle.
He shares his knowledge as a Java Champion at different conferences, JUGs, in-house presentations, workshops and on his blog.
Twitter: @lukaseder