O'Reilly logo
live online training icon Live Online training

Mastering the basics of relational SQL querying

Ami Levin

Structured Query Language—more commonly known as SQL—is an amazing declarative programming language. While easy enough to get started, learning just the syntax and simple use cases will only get you so far. The trickery that you can apply to writing highly efficient and refined logic is mind-blowingly complex.

Join expert Ami Levin to explore the mathematical foundation of the relational model, learn how queries are processed and what they represent in the relational model, and truly master queries in SQL. By the end of this interactive hands-on training, you'll be able to write highly efficient queries to query huge amounts of data and answer complex data questions in seconds.

What you'll learn-and how you can apply it

By the end of this live online course, you’ll understand:

  • What the relational model is and how it's related to SQL
  • The various phases in query processing and how they relate to each other
  • How to use the various SQL clauses and operators efficiently

And you’ll be able to:

  • Write highly efficient, logically correct SQL queries
  • Easily troubleshoot query logical challenges

This training course is for you because...

  • You're a beginner who wants to get started with SQL.
  • You're an experienced SQL programmer who wants to take your skills to the next level, helping you tackle your daily challenges with ease and elegance.

Prerequisites

  • At least one year of experience with a programming or scripting language
  • Familiarity with IT terminology and concepts

Recommended Preparation:

Learning Path: MySQL: High Performance Querying with MySQL 8 (Learning Path)

Time and Relational Theory (Learning Path)

About your instructor

  • Ami Levin is a senior data tier architect, data modeler, database designer and SQL developer with over 20 years of experience. Inventor of intelligent, autonomous performance optimization products. Designed, reviewed and optimized some of the world’s largest, most challenging, mission critical data environments. A seasoned speaker, instructor and mentor. Author of white papers and technical articles.

Schedule

The timeframes are only estimates and may vary according to how the class is progressing

Day 1

Course introduction (5 minutes)

What is SQL? (5 minutes)

  • Lecture: The origins and history of the relational model; how SQL came to be the language of choice for querying it, despite the original inventor’s strong reservations

The basic principles of the relational model (15 minutes)

  • Lecture: Relational model basic principles—set theory, first order predicate logic, relational algebra, relations and tuples, keys, DRI, and constraints

RDM (relational data model) and RDBMS (relational database management systems) design fundamentals (10 minutes)

  • Lecture: Common ERD tools and technologies available for relational model designers, including the common commercially available engines that support SQL, such as Oracle, SQL Server, DB2, MySQL, and Postgres; the differences between the SQL "flavors" that these engines support; the real meaning of the oft-abused term "normalization"

Installing and getting familiar with your development environment (25 minutes)

  • Lecture: How to install, configure, and use the development environment
  • Hands-on exercise: Install and configure your development environment
  • Q&A

Break (10 minutes)

SQL language constructs (10 minutes)

  • Lecture: The three basic constructs that comprise the SQL language—the data definition language (DDL), the data control language (DCL), and the data manipulation language (DML)

The data manipulation language (25 minutes)

  • Lecture: The five basic commands that comprise DML—INSERT, UPDATE, DELETE, MERGE, and SELECT
  • Hands-on exercise: Insert, update, delete, and retrieve data

Introduction to SELECT query clauses (15 minutes)

  • Lecture: The seven basic clauses that comprise the SELECT query—SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and FETCH / OFFSET
  • Q&A

Break (10 minutes)

Query logical processing (10 minutes)

  • Lecture: How SQL engines evaluate and process your queries logically; PDI (physical data independence); the order of execution for the various clauses; how intermediate data is moved between them in fixed order as virtual sets and why this is so important, even when the processing engine can make shortcuts

The FROM clause and JOINS (40 minutes)

  • Lecture: Why every query begins with building an initial source set, comprised of a single table set or by combining multiple sets using all types of JOINS—CROSS JOIN, INNER JOIN, OUTER JOIN, and APPLY; how every type of JOIN is evaluated, using equality and nonequality and single and multiple join predicates; how to change join order and where it matters
  • Hands-on exercise: Use JOINS
  • Q&A

Day 2

The WHERE clause (25 minutes)

  • Lecture: Row filtering in the WHERE clause using predicates; common examples of logical operators; how to join multiple predicates; how to handle missing or inapplicable values using three-valued logic (3VL)
  • Hands-on exercise: Filter rows using WHERE

The GROUP BY and HAVING clauses (35 minutes)

  • Lecture: How to combine multiple rows into a single group; how to use common aggregate functions to come up with higher-level insights about your data; how to filter whole groups from the result set using the HAVING clause; the logical aspects of these aggregations; the common pitfalls many SQL developers fall into when using aggregations
  • Hands-on exercise: Aggregate data

Break (10 minutes)

The ORDER BY and FETCH clauses (30 minutes)

  • Lecture: How to convert your sets to ordered cursors and support presentation ordering and paging to limit the number of returned rows
  • Hands-on exercise: Order and page data for presentation
  • Q&A

Subqueries (35 minutes)

  • Lecture: How to use subqueries; the various clauses where they are applicable; how to construct and use subqueries as scalar operators and derived tables; correlated versus noncorrelated subqueries; the logical implications of using subqueries; common pitfalls that even experienced developers fall into
  • Hands-on exercise: Use subqueries

Break (10 minutes)

Set operators (20 minutes)

  • Lecture: How to combine multiple queries vertically using set operators—UNION and UNION ALL, EXCEPT, and INTERSECT; examples using various techniques to come up with similar results using set operators; the more commonly used query constructs
  • Hands-on exercise: Use set operators

Wrap-up and Q&A (15 minutes)

  • Lecture and discussion: Course evaluations; additional references and reading materials
  • Take-home exercise: Solve a challenging SQL task