Mastering DuckDB: The Hands on Guide

Install duckdb on macOS using Homebrew, clone the repository, build with ninja, and explore the latest version via the command line or a SQL editor.

Install DBeaver on Mac, choose Apple Silicon, and launch from the dmg. Create a DuckDB connection and a .db file, then use the SQL editor and worksheet.

Download the Windows executable for Duckdb, extract the zip, add the extracted path to the environment variables, and verify installation by running Duckdb and show table in the command prompt.

Install DBeaver on Windows and connect to a DuckDB database using the community edition, configuring the database path and viewing objects in the navigator.

Explore DuckDB's single .db file architecture with columnar, zero-copy storage for analytics; learn about WAL, vectorized processing, ACID transactions, schema evolution, and external data integration.

Explore the differences between in-memory and persistent databases in Duckdb, comparing performance, durability, memory management, and practical use cases from data exploration to production.

DuckDB provides lightweight, serverless embedded analytics for SQL over file formats, and pandas/polars integration, with vectorized, parallel execution for OLAP; it isn't suited for OLTP, real-time streaming, or distributed scale.

Create and define an employee table with id, name, and phone columns, then insert single and multiple rows using insert into, and verify results with a select.

Learn to create and drop tables in DuckDB SQL by building an employee table with ID, name, and address columns, then safely remove it with a drop table command.

Explore how Mother Duck extends Duckdb to handle larger data sets and enable collaboration in the cloud. Set up an account, navigate the dashboard, and run a create database query.

Install DuckDB with homebrew on Mac, launch the local ui, attach a database (memory database option), and use the web-based sql editor to run queries.

Create the sales database and product table, set product_id as a primary key with a starting sequence, and use limit and offset to fetch the first five records.

Learn how default values work in SQL tables. Create a sequence and an employees table, showing ID auto generated and default unknown for missing name.

Detach from Mother Duck cloud services to disconnect a database using the detach command, then verify databases in the terminal and UI and view tables in the sales database.

Learn to run select queries in DuckDB to fetch specific columns or all data, and apply upper and lower functions for case-insensitive searches and data standardization.

Master DuckDB string functions by using upper and lower to standardize text and split_part to extract email domains, enabling case-insensitive comparisons and clean data in practice.

Explore distinct in SQL to retrieve unique categories from a product table, remove duplicates, and count distinct records for better data analysis.

Master DuckDB aggregate functions on product data, using select star from product, count star, min price, max price, and average price, and learn about user defined functions.

Explore DuckDB aliases to alias columns and tables, using as to rename product name to name and product id to id, and alias the product table as P.

Learn how the DuckDB concat function merges text from multiple columns, such as product name, category, and price, and how to add separators for readable outputs.

Create an employees table with id, name, phone, salary stored as a string, and email to demonstrate data type casting in SQL. Cast salary to float for math operations.

Explore how the where clause filters data in SQL queries, using stock and price to show equals, not equals, greater than, and less than conditions, with and/or combinations.

Filter string columns with the where clause to retrieve specific records, using equals, not equals, and or conditions across product name and category in DuckDB.

Filter date columns with the where clause in sql, using the orders table and order date 1st january 2024. Apply equals, not equals, greater than, and less than conditions.

Explore how to filter boolean columns with the where clause in sql, using true and false values, and combine conditions with and or to retrieve active products by category.

Master the between operator in DuckDB by filtering product prices from 50 to 200 and orders between January 1, 2024 and January 5, 2024, including endpoints.

Explore how the between keyword filters price values in DuckDB, with examples from 10 to 20, reverse ranges, negative ranges, and multiple columns where is active equals to false.

Learn how to filter by multiple values using the IN keyword in SQL, retrieving products by stock or name from a product table with concise in clauses.

Explore how the where clause in sql filters string columns with the like keyword, using percent and underscore to match starts, ends, and in-between letters.

Explore how null values affect SQL by creating an employee table with not null constraints, inserting and updating records, and using is null and is not null in queries.

Explore the SQL order by clause to sort data, from alphabetical product names to numeric prices, including multi-column sorting in ascending and descending order.

Learn to use the group by clause in DuckDB to group rows by category and apply aggregates like max, min, sum, and count, including grouping by multiple columns.

Master the group by and having clause in DuckDB, using sum(price) and total stock to filter categories with total price over 500 and stock over 20.

Learn to apply the where clause to group by queries, filtering products by price greater than 200 and active status, so only active groups meet both conditions.

Explore the group by SQL concept through experiments with aggregate functions and category-based sums, uncovering common errors and how multiple columns and data types affect grouping.

Master the coalesce function to handle null values in SQL, replacing missing emails with no email and null salaries with zero, shown on an employee table, non-destructively.

Learn to use the null if function in SQL to replace specific values with null and filter out salaries from calculations, demonstrated on an employee table in DuckDB.