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, including 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 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 queries

3.4.1 The INSERT statement

3.4.2 Merging data

3.4.3 The DELETE statement

3.4.4 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

Summary