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 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 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)
About your instructor
-
Thomas Nield (author of Getting Started with SQL) is a business consultant for Southwest Airlines in Schedule Initiatives. Early in his career, he became fascinated with technology and its role in business analytics. After becoming proficient in Java, Kotlin, Python, SQL, and reactive programming, he became an open-source contributor as well as an author/trainer for O’Reilly Media. He is passionate about sharing what he learns and enabling others with new skill sets. He enjoys making technical content relatable and relevant to those unfamiliar with or intimidated by it.
Schedule
The timeframes 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