-
Module 1: Introduction to querying DuckDB
11 Lessons-
StartModule introduction
-
StartWhat is DuckDB?
-
StartWhat is DuckDB SQL?
-
StartTypes of SQL statements
-
StartWhat is in a DuckDB database?
-
StartDatabase files, catalogs, and attached databases
-
StartOpening a DuckDB database
-
StartWhat is the DuckDB CLI?
-
StartFinding help and exploring DuckDB
-
StartLab 1
-
StartQuiz 1
-
-
Module 2: Querying a single table
17 Lessons-
StartModule introduction
-
StartWhat are tables?
-
StartFinding out what's in a table
-
StartWhat are schemas?
-
StartWhat is Popkorn Kraze ?
-
StartSELECT statements
-
StartUsing aliases for columns and tables
-
StartQuoting (delimiting) names
-
StartORDER BY clause
-
StartRestricting output with LIMIT and OFFSET
-
StartRestricting output with FETCH and OFFSET
-
StartRemoving duplicates with DISTINCT
-
StartQuerying literals, expressions and functions
-
StartQuerying files directly
-
StartLab 2
-
StartLab 2 Answers
-
StartQuiz 2
-
-
Module 3: Filtering the rows to return
12 Lessons-
StartModule introduction
-
StartFiltering output with the WHERE clause
-
StartFinding patterns with LIKE and ILIKE
-
StartLogical operations with AND, OR, and NOT
-
StartLogical values withe boolean data type
-
StartRanges of values with the BETWEEN operator
-
StartChecking lists of values by using the IN operator
-
StartWorking with NULL values (the lack of data)
-
StartWhat are macros and table macros?
-
StartLab 3
-
StartLab 3 Answers
-
StartQuiz 3
-
-
Module 5: Querying numbers
12 Lessons -
Module 6: Querying strings
17 Lessons-
StartModule introduction
-
StartString data types
-
StartLiteral string values
-
StartString operators
-
StartTrimming strings with LTRIM, RTRIM, and TRIM
-
StartChanging case with UPPER, LOWER
-
StartExtracting parts of strings with LEFT, RIGHT, SUBSTRING
-
StartDetermining the length of strings
-
StartReplace substrings
-
StartDuplicating strings with REPEAT
-
StartFinding substrings with POSITION
-
StartExtracting delimited string tokens
-
StartReplacing characters with TRANSLATE
-
StartOther common string functions
-
StartLab 6
-
StartLab 6 Answers
-
StartQuiz 6
-
-
Module 7: Querying dates and times
15 Lessons-
StartModule introduction
-
StartDate and time data types
-
StartLiteral date and time values
-
StartCurrent date and time
-
StartExtracting components from date and time values
-
StartAdding and subtracting date and time values
-
StartCalculating date and time differences
-
StartCalculating ages
-
StartCalculating beginning and end of month
-
StartConstructing dates and times from components
-
StartChanging time zone offsets
-
StartFormatting date and time values
-
StartLab 7
-
StartLab 7 Answers
-
StartQuiz 7
-
-
Module 8: Converting between data types
11 Lessons-
StartModule introduction
-
StartReplacing NULL values with COALESCE
-
StartReturning NULL When Equal with NULLIF
-
StartComparing NULL values using IS DISTINCT FROM
-
StartImplicit vs explicit conversions
-
StartConverting data types with CAST function
-
StartConverting data types with CAST operator
-
StartOther common data types
-
StartLab 8
-
StartLab 8 Answers
-
StartQuiz 8
-
-
Module 9: Aggregating data
13 Lessons-
StartModule introduction
-
StartCounting rows and columns with COUNT
-
StartSummarizing data with SUM, AVG, MIN, MAX
-
StartSummarizing data in sections with GROUP BY
-
StartFiltering returned groups with HAVING
-
StartUnderstanding logical query execution order
-
StartNumbering rows with window functions
-
StartPartitioning ranking and numbering with PARTITION BY
-
StartRetrieving one row from each group with DISTINCT ON
-
StartOther interesting aggregates
-
StartLab 9
-
StartLab 9 Answers
-
StartQuiz 9
-
-
Module 10: Implementing logic in scripts
10 Lessons-
StartModule introduction
-
StartUsing conditional expressions with CASE
-
StartSimplifying conditionals with GREATEST, LEAST
-
StartDefining variables and assigning values
-
StartQuerying environment information
-
StartConditional logic in expressions with IF
-
StartGenerating rows instead of using loops
-
StartLab 10
-
StartLab 10 Answers
-
StartQuiz 10
-
-
Module 11: Querying multiple tables
15 Lessons-
StartModule introduction
-
StartUsing CROSS JOIN
-
StartWhat is a primary key?
-
StartWhat is a foreign key?
-
StartUsing INNER JOIN
-
StartUsing modern join syntax
-
StartUsing LEFT OUTER JOIN
-
StartOther OUTER join types
-
StartJoining more than two tables
-
StartJoining a table to itself (self join)
-
StartJoins without equality (non-equi joins)
-
StartASOF joins for time-series data
-
StartLab 11
-
StartLab 11 Answers
-
StartQuiz 11
-
-
Module 12: Applying set operations to tables
9 Lessons -
Module 13: Using subqueries
12 Lessons-
StartModule introduction
-
StartUsing subqueries
-
StartUsing scalar subqueries
-
StartUsing lists from subqueries
-
StartChecking for existence with EXISTS subqueries
-
StartUsing set-returning subqueries
-
StartUsing VALUES row constructors
-
StartSimplifying queries with common table expressions (CTEs)
-
StartUsing multiple CTEs in a single query
-
StartLab 13
-
StartLab 13 Answers
-
StartQuiz 13
-
-
Module 15: Modifying data
16 Lessons-
StartModule introduction
-
StartInserting data into a table
-
StartInserting multiple rows at once with VALUES
-
StartUsing default values
-
StartGenerating keys with sequences
-
StartIdentifying the last values inserted
-
StartWhat is a check constraint?
-
StartWhat is a unique constraint?
-
StartDeleting rows from a table
-
StartInserting rows with data from another table
-
StartCreating a table from a query
-
StartUpdating data in a table
-
StartMerging data into a table
-
StartLab 15
-
StartLab 15 Answers
-
StartQuiz 15
-
Writing SQL Queries for DuckDB
Creating reports, analytics, or applications? And need to get data out of DuckDB? Learn to write SQL queries like a pro !
Course Summary
Do you need to learn how to write SQL queries for DuckDB?
- You know that the information that you need is stored in a DuckDB database
- You need to find some data to extract it
- You need to create reports with reporting tools
- You are using analytic tools like Power BI, Tableau, QlikView, Excel, Looker, or Access and need to get data from DuckDB
- You want to learn to write queries properly, using commercial coding standards
- You are new to writing queries, or you are self-taught and want to make sure you are doing it correctly
We encourage you to complete the practical exercises. We have tried to make this as easy as possible. Installation is very easy.. And there are tools for Windows, Mac, and Linux.
