Zenler Player
Your course is loading. Hang tight.
Writing SQL Queries for DuckDB
Back to curriculum
0% Complete
0% Complete
Who is this course for?
Who is Greg?
What will I learn in this course?
Overview of lab setup
Module introduction
What is DuckDB?
What is DuckDB SQL?
Types of SQL statements
What is in a DuckDB database?
Database files, catalogs, and attached databases
Opening a DuckDB database
What is the DuckDB CLI?
Finding help and exploring DuckDB
Lab 1
Quiz 1
Module introduction
What are tables?
Finding out what's in a table
What are schemas?
What is Popkorn Kraze ?
SELECT statements
Using aliases for columns and tables
Quoting (delimiting) names
ORDER BY clause
Restricting output with LIMIT and OFFSET
Restricting output with FETCH and OFFSET
Removing duplicates with DISTINCT
Querying literals, expressions and functions
Querying files directly
Lab 2
Lab 2 Answers
Quiz 2
Module introduction
Filtering output with the WHERE clause
Finding patterns with LIKE and ILIKE
Logical operations with AND, OR, and NOT
Logical values withe boolean data type
Ranges of values with the BETWEEN operator
Checking lists of values by using the IN operator
Working with NULL values (the lack of data)
What are macros and table macros?
Lab 3
Lab 3 Answers
Quiz 3
Module introduction
Using statement terminators
Working with scripts in DBeaver
Using query history
Copying and saving query results
Commenting code
Formatting your scripts for readability
Quiz 4
Module introduction
What are data types?
Exact whole numbers
Exact decimal numbers
Approximate numbers
Numeric operators
Universally unique identifiers (UUIDs)
UUID operators
Common numeric functions
Lab 5
Lab 5 Answers
Quiz 5
Module introduction
String data types
Literal string values
String operators
Trimming strings with LTRIM, RTRIM, and TRIM
Changing case with UPPER, LOWER
Extracting parts of strings with LEFT, RIGHT, SUBSTRING
Determining the length of strings
Replace substrings
Duplicating strings with REPEAT
Finding substrings with POSITION
Extracting delimited string tokens
Replacing characters with TRANSLATE
Other common string functions
Lab 6
Lab 6 Answers
Quiz 6
Module introduction
Date and time data types
Literal date and time values
Current date and time
Extracting components from date and time values
Adding and subtracting date and time values
Calculating date and time differences
Calculating ages
Calculating beginning and end of month
Constructing dates and times from components
Changing time zone offsets
Formatting date and time values
Lab 7
Lab 7 Answers
Quiz 7
Module introduction
Replacing NULL values with COALESCE
Returning NULL When Equal with NULLIF
Comparing NULL values using IS DISTINCT FROM
Implicit vs explicit conversions
Converting data types with CAST function
Converting data types with CAST operator
Other common data types
Lab 8
Lab 8 Answers
Quiz 8
Module introduction
Counting rows and columns with COUNT
Summarizing data with SUM, AVG, MIN, MAX
Summarizing data in sections with GROUP BY
Filtering returned groups with HAVING
Understanding logical query execution order
Numbering rows with window functions
Partitioning ranking and numbering with PARTITION BY
Retrieving one row from each group with DISTINCT ON
Other interesting aggregates
Lab 9
Lab 9 Answers
Quiz 9
Module introduction
Using conditional expressions with CASE
Simplifying conditionals with GREATEST, LEAST
Defining variables and assigning values
Querying environment information
Conditional logic in expressions with IF
Generating rows instead of using loops
Lab 10
Lab 10 Answers
Quiz 10
Module introduction
Using CROSS JOIN
What is a primary key?
What is a foreign key?
Using INNER JOIN
Using modern join syntax
Using LEFT OUTER JOIN
Other OUTER join types
Joining more than two tables
Joining a table to itself (self join)
Joins without equality (non-equi joins)
ASOF joins for time-series data
Lab 11
Lab 11 Answers
Quiz 11
Module introduction
Using UNION and UNION ALL
Excluding data with EXCEPT
Finding common data with INTERSECT
Selecting from table macros
Repetitively selecting with LATERAL joins
Lab 12
Lab 12 Answers
Quiz 12
Module introduction
Using subqueries
Using scalar subqueries
Using lists from subqueries
Checking for existence with EXISTS subqueries
Using set-returning subqueries
Using VALUES row constructors
Simplifying queries with common table expressions (CTEs)
Using multiple CTEs in a single query
Lab 13
Lab 13 Answers
Quiz 13
Module introduction
What are macros?
Calling table functions
Parameterized and dynamic queries
Querying DuckDB metadata
Lab 14
Lab 14 Answers
Quiz
Module introduction
Inserting data into a table
Inserting multiple rows at once with VALUES
Using default values
Generating keys with sequences
Identifying the last values inserted
What is a check constraint?
What is a unique constraint?
Deleting rows from a table
Inserting rows with data from another table
Creating a table from a query
Updating data in a table
Merging data into a table
Lab 15
Lab 15 Answers
Quiz 15
Summary and further steps
Module 0: Getting started
Who is this course for?
Preview
Who is Greg?
Preview
What will I learn in this course?
Preview
Overview of lab setup
Module 1: Introduction to querying DuckDB
Module introduction
What is DuckDB?
What is DuckDB SQL?
Types of SQL statements
What is in a DuckDB database?
Database files, catalogs, and attached databases
Opening a DuckDB database
What is the DuckDB CLI?
Finding help and exploring DuckDB
Lab 1
Quiz 1
Module 2: Querying a single table
Module introduction
What are tables?
Finding out what's in a table
What are schemas?
What is Popkorn Kraze ?
SELECT statements
Using aliases for columns and tables
Quoting (delimiting) names
ORDER BY clause
Restricting output with LIMIT and OFFSET
Restricting output with FETCH and OFFSET
Removing duplicates with DISTINCT
Querying literals, expressions and functions
Querying files directly
Lab 2
Lab 2 Answers
Quiz 2
Module 3: Filtering the rows to return
Module introduction
Filtering output with the WHERE clause
Finding patterns with LIKE and ILIKE
Logical operations with AND, OR, and NOT
Logical values withe boolean data type
Ranges of values with the BETWEEN operator
Checking lists of values by using the IN operator
Working with NULL values (the lack of data)
What are macros and table macros?
Lab 3
Lab 3 Answers
Quiz 3
Module 4: Working with queries
Module introduction
Using statement terminators
Working with scripts in DBeaver
Using query history
Copying and saving query results
Commenting code
Formatting your scripts for readability
Quiz 4
Module 5: Querying numbers
Module introduction
What are data types?
Exact whole numbers
Exact decimal numbers
Approximate numbers
Numeric operators
Universally unique identifiers (UUIDs)
UUID operators
Common numeric functions
Lab 5
Lab 5 Answers
Quiz 5
Module 6: Querying strings
Module introduction
String data types
Literal string values
String operators
Trimming strings with LTRIM, RTRIM, and TRIM
Changing case with UPPER, LOWER
Extracting parts of strings with LEFT, RIGHT, SUBSTRING
Determining the length of strings
Replace substrings
Duplicating strings with REPEAT
Finding substrings with POSITION
Extracting delimited string tokens
Replacing characters with TRANSLATE
Other common string functions
Lab 6
Lab 6 Answers
Quiz 6
Module 7: Querying dates and times
Module introduction
Date and time data types
Literal date and time values
Current date and time
Extracting components from date and time values
Adding and subtracting date and time values
Calculating date and time differences
Calculating ages
Calculating beginning and end of month
Constructing dates and times from components
Changing time zone offsets
Formatting date and time values
Lab 7
Lab 7 Answers
Quiz 7
Module 8: Converting between data types
Module introduction
Replacing NULL values with COALESCE
Returning NULL When Equal with NULLIF
Comparing NULL values using IS DISTINCT FROM
Implicit vs explicit conversions
Converting data types with CAST function
Converting data types with CAST operator
Other common data types
Lab 8
Lab 8 Answers
Quiz 8
Module 9: Aggregating data
Module introduction
Counting rows and columns with COUNT
Summarizing data with SUM, AVG, MIN, MAX
Summarizing data in sections with GROUP BY
Filtering returned groups with HAVING
Understanding logical query execution order
Numbering rows with window functions
Partitioning ranking and numbering with PARTITION BY
Retrieving one row from each group with DISTINCT ON
Other interesting aggregates
Lab 9
Lab 9 Answers
Quiz 9
Module 10: Implementing logic in scripts
Module introduction
Using conditional expressions with CASE
Simplifying conditionals with GREATEST, LEAST
Defining variables and assigning values
Querying environment information
Conditional logic in expressions with IF
Generating rows instead of using loops
Lab 10
Lab 10 Answers
Quiz 10
Module 11: Querying multiple tables
Module introduction
Using CROSS JOIN
What is a primary key?
What is a foreign key?
Using INNER JOIN
Using modern join syntax
Using LEFT OUTER JOIN
Other OUTER join types
Joining more than two tables
Joining a table to itself (self join)
Joins without equality (non-equi joins)
ASOF joins for time-series data
Lab 11
Lab 11 Answers
Quiz 11
Module 12: Applying set operations to tables
Module introduction
Using UNION and UNION ALL
Excluding data with EXCEPT
Finding common data with INTERSECT
Selecting from table macros
Repetitively selecting with LATERAL joins
Lab 12
Lab 12 Answers
Quiz 12
Module 13: Using subqueries
Module introduction
Using subqueries
Using scalar subqueries
Using lists from subqueries
Checking for existence with EXISTS subqueries
Using set-returning subqueries
Using VALUES row constructors
Simplifying queries with common table expressions (CTEs)
Using multiple CTEs in a single query
Lab 13
Lab 13 Answers
Quiz 13
Module 14: Working with macros, table functions, and metadata
Module introduction
What are macros?
Calling table functions
Parameterized and dynamic queries
Querying DuckDB metadata
Lab 14
Lab 14 Answers
Quiz
Module 15: Modifying data
Module introduction
Inserting data into a table
Inserting multiple rows at once with VALUES
Using default values
Generating keys with sequences
Identifying the last values inserted
What is a check constraint?
What is a unique constraint?
Deleting rows from a table
Inserting rows with data from another table
Creating a table from a query
Updating data in a table
Merging data into a table
Lab 15
Lab 15 Answers
Quiz 15
Module 16: Next steps
Summary and further steps
×
This is an unpublished lesson. This lesson will not be shown for students unless you set it as Public.
Back to Dashboard
No contents are available in this lesson!
No lessons available !
Back to Dashboard
Lesson contents locked
Enroll to unlock this lesson.
Enroll to unlock
Next Lesson