O'Reilly logo
live online training icon Live Online training

SQL Fundamentals for Data

A hands-on course for beginners

Thomas Nield

Join Thomas Nield for a hands-on introduction to core database and SQL fundamentals—concepts that are critical in today’s business and IT landscape. Businesses are gathering data at exponential rates, and there’s an equally growing demand for people who know how to access it meaningfully. Through a combination of lecture and exercises—and Thomas’s pragmatic approach to teaching—you’ll gain proficiency with SQL fundamentals and database design. Using the simplicity and power of SQLite, you can practice designing and working with databases at home without a database server environment.

Whether you’re a project manager, IT professional, business analyst, programmer, engineer, or simply a person with basic computing skills and an interest in data, you’ll make the leap to data proficiency with this course.

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

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

  • Basic data analysis and writing tasks using SQL
  • Principles for creating resilient database designs
  • The data technology landscape and the role of relational databases

And you’ll be able to:

  • Quickly apply knowledge to most relational database platforms (Oracle, MySQL, etc)
  • Aggregate and join data to get a more complete picture
  • Read, write, and transform data in tables
  • Execute basic data analysis to create aggregations and establish relationships

This training course is for you because...

  • You’re a business analyst who has done some data analysis and now needs to access larger datasets and make sense of them
  • You’re an IT professional who wants to get involved in accessing and managing data, and understand core principles of database design
  • You’re an engineer who wants to collect data and integrate it into your systems, products, or projects
  • You’re a programmer who needs to work with and build solutions around data
  • You’re a project manager who needs to manage a team of data analysts and engineers, so you can fully understand the feasibility of projects as well as quality control

Prerequisites

Experience with basic data analysis using Excel or other spreadsheet tools will be helpful.


Recommended Preparation:

Getting Started with SQL

About your instructor

  • Thomas Nield (author of Getting Started with SQL) has a business analyst background and works at Southwest Airlines in Revenue Management. Early in his career he became fascinated with technology and bought dozens of books to master programming in Java, C#, Kotlin, and database design. He is passionate about sharing what he learns and enabling others with new skillsets, even if they do not work in IT. He enjoys making technical content relatable and relevant to those unfamiliar or intimidated by it.

Schedule

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

Day One

Understanding databases

  • Definition of database
  • Relational databases
  • Normalization
  • Lightweight versus centralized databases

Using SQLite

  • Introduction to SQLite
  • Setting up SQLiteStudio
  • Importing and navigating databases

SELECT

  • Retrieving and viewing data with SELECT
  • Expressions in SELECT statements
  • Text concatenation

WHERE

  • Filtering records with WHERE
  • Using WHERE on numbers
  • AND, OR, and IN statements
  • Using WHERE on text
  • Understanding True/False (boolean) values
  • Handling NULL
  • Grouping conditions

GROUP BY and ORDER BY

  • Grouping records
  • Ordering records
  • Aggregate functions
  • Filtering aggregates with HAVING
  • Getting DISTINCT records

CASE statements

  • The CASE statement
  • Grouping CASE statements
  • The "zero/null" CASE trick

Day Two

JOIN

  • Stitching multiple tables together
  • INNER JOIN
  • LEFT JOIN
  • Other JOIN types
  • Joining multiple tables
  • Using GROUP BY with a JOIN

Database design

  • Decisions in planning a database
  • The SurgeTech conference
  • Turning SurgeTech entities into tables
  • Primary and foreign keys
  • The final schema

Creating and managing a database

  • Using CREATE TABLE to build the SurgeTech database
  • Setting the primary/foreign keys
  • Creating views
  • Adding data with INSERT
  • Changing data with UPDATE
  • Deleting data with DELETE
  • Truncating and dropping tables

Going forward and closing

  • Summarize material covered
  • Discuss possible career paths and opportunities using SQL