SQL fundamentals for data

Beginner

A hands-on course for beginners

Join Thomas Nield for a hands-on introduction to core database and SQL fundamentals—concepts that are critical in today’s business and technology 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:

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 versus NoSQL 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 live event 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 a data science professional who wants to tap into data sources directly rather than rely on text files.
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.

Required materials and setup:

Download class resources from GitHub
Download and launch SQLite client preferably SQLiteStudio, or just use SQLiteOnline.com

Recommended follow-up:

Advanced SQL for Data Analysis (Online Training)
Getting Started with SQL (book)

Schedule

The time frames are only estimates and may vary according to how the class is progressing.

DAY ONE

Understanding databases (20 minutes)

Definition of database
Relational databases
Normalization
SQL versus NoSQL
Lightweight versus centralized databases
EXERCISES: Identifying lightweight vs centralized databases

Using SQLite (10 minutes)

Introduction to SQLite
Setting up SQLiteStudio or SQLiteOnline
Importing and navigating databases
10 minute break

SELECT (20 minutes)

Retrieving and viewing data with SELECT
Expressions in SELECT statements
Text concatenation
EXERCISES: Selecting columns and expressions

WHERE (40 minutes)

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
EXERCISES: Filtering weather records with WHERE
10 minute break

GROUP BY and ORDER BY (40 minutes)

Grouping records
Ordering records
Aggregate functions
Filtering aggregates with HAVING
Getting DISTINCT records
EXERCISES: Aggregating and summarizing weather data

CASE statements (30 minutes)

The CASE statement
Grouping CASE statements
The "zero/null" CASE trick
EXERCISES: Aggregating weather data by quarter

DAY TWO

JOIN (50 minutes)

Stitching multiple tables together
INNER JOIN
LEFT JOIN
Other JOIN types
Joining multiple tables
Using GROUP BY with a JOIN
EXERCISES: Joining and aggregating customers and customer orders
10 minute break

Database design and Creation (50 minutes)

Decisions in planning a database
Understanding and Preventing SQL Injection
The SurgeTech conference
Turning SurgeTech entities into tables
Primary and foreign keys
The final schema
Using CREATE TABLE to build the SurgeTech database
Setting the primary/foreign keys
Creating views
10 minute break

Managing and Writing a database (40 minutes)

Adding data with INSERT
Changing data with UPDATE
Deleting data with DELETE
Truncating and dropping tables
Indexes and transactions
Specialized data types (dates and times)
EXERCISES: Writing and modifying conference attendees

Your Instructor

Thomas Nield
Thomas Nield is the founder of Nield Consulting Group and an instructor at O’Reilly Media and the University of Southern California, teaching classes on data analysis, machine learning, mathematical optimization, AI system safety, and practical artificial intelligence. He’s authored multiple books including Getting Started with SQL and Essential Math for Data Science, both for O’Reilly. He’s also the founder and inventor of Yawman Flight, a company that develops universal handheld controls for flight simulation and unmanned aerial vehicles. Thomas enjoys making technical content relatable and relevant to those unfamiliar with or intimidated by it.

link search