3 Executing SQL Queries

This chapter covers

The different categories of SQL statements and their fundamental structure
Creating tables and structures for ingesting a real world dataset
Laying the fundamentals for analyzing a huge dataset in detail
Exploring DuckDB-specific extensions to SQL

Now that you’ve learned about the DuckDB CLI, it’s time to tickle your SQL brain. We will be using the CLI version of DuckDB throughout this chapter. However, all the examples here can be fully applied from within any of the supported environments, such as the Python client, the Java JDBC driver, or any of the other supported language interfaces.

In this chapter, we will quickly go over some basic and necessary SQL statements and then move on to more advanced querying. In addition to explaining SQL basics, we’ll also be covering more complex topics like common table expressions and window functions. DuckDB supports both of these and this chapter will teach you how to build queries for doing the best possible in-memory Online Analytical Processing (OLAP) with DuckDB.

3.1 A quick SQL recap

3.2 Analyzing energy production

3.2.1 Downloading the dataset

3.2.2 The target schema

3.3 Data definition language (DDL) queries

3.3.1 The CREATE TABLE statement

3.3.2 The ALTER TABLE statement

3.3.3 The CREATE VIEW statement

3.3.4 The DESCRIBE statement

3.4 Data manipulation language (DML) queries

3.4.1 The INSERT statement

3.4.2 The DELETE statement

3.4.3 The SELECT statement

3.5 DuckDB-specific SQL extensions

3.5.1 Dealing with SELECT *

3.5.2 Inserting by name

3.5.3 Accessing aliases everywhere

3.5.4 Grouping and ordering by all relevant columns

3.5.5 Sampling data

3.5.6 Functions with optional parameters

3.6 Summary