07cSelectStatement

Database Management

Mick McQuaid
mcq@utexas.edu

University of Texas at Austin

13 May 2026

Intro

  • The most frequently used SQL statement (for you) is SELECT
  • This is how you get data out of a database (or move it around in a database)
  • Enormously complicated SELECT statements are possible
  • SELECT statements are declarative, meaning they tell the rdbms what you want, not how to get it—how is left up to the system or a database administrator
  • The new default output of the SELECT statement is useful for most purposes

Displaying the output

SQLite has a dot command called .mode that controls the format of the display

If you say help .mode you will get a list of the most common modes. Here are the first few:

  • .mode list — the former default. Columns separated by |, no header. Compact but ugly.
  • .mode column — fixed-width columns aligned in tidy vertical lanes. The most readable mode for interactive use. Pair it with .headers on to label the columns.
  • .mode box — draws Unicode box-drawing characters around the table, like a proper grid. Lovely for screenshots and demos. Available in SQLite 3.33 and later.

More formatting modes

  • .mode table — similar to box but with ASCII characters instead of Unicode, so it works in terminals that mangle Unicode.
  • .mode markdown — produces a Markdown-formatted table you can paste straight into a .md file or a GitHub comment.
  • .mode csv — comma-separated, properly quoted. Good for piping to a file or another tool.
  • .mode tabs — tab-separated. Great for piping into Unix tools like cut, awk, or back into another database.
  • .mode json — each row as a JSON object, all rows wrapped in a JSON array. Excellent for piping to jq or feeding a script.

Still more formatting modes

  • .mode html — wraps the result in <TR> and <TD> tags.
  • .mode insert tablename — emits each row as an INSERT statement, which is invaluable for dumping a small set of rows into another database.
  • .mode quote — values quoted SQL-style, useful for embedding in queries.
  • .mode line — each column on its own line as name = value. Verbose but handy when rows have many columns and the table format wraps awkwardly.

Complicated, isn’t it?

  • It was probably moving too fast to notice, but the documentation page states explicitly that SELECT is the most complicated SQL command.
  • We’ll use a very small part of it.
  • We won’t even use the entire core, shown on the next frame.
  • We’ll restrict ourselves to the FROM, WHERE, JOIN, GROUP BY, ORDER BY, and LIMIT clauses.

The core of the SELECT statement

The “simple” SELECT statement

SELECT examples from pets.db

First download and unzip the sqliteExamples.zip file and say sqlite3 pets.db

select * from pets;
select * from pets where kid_friendly = 'yes';
select kid_friendly, count(*) as total from pets group by kid_friendly;
select * from pets order by name;
select * from pets order by name desc;
select * from pets order by name desc limit 3;

SELECT examples from chinookdb

First download and unzip the sqliteExamples.zip file and say sqlite3 chinook.db

select * from invoices limit 5;
select CustomerId,max(Total) from invoices;
select * from customers where CustomerId=6;
select max(i.Total), i.CustomerId, c.FirstName, c.LastName from invoices i join customers c on c.CustomerId = i.CustomerId;
select count(*) from customers;
select min(i.Total), i.CustomerId, c.FirstName, c.LastName from invoices i join customers c on c.CustomerId = i.CustomerId;
select avg(Total) from invoices;
select printf('$%3.2f',avg(Total)) as average from invoices;

A SQL tradition

Although I have a habit of writing sql in lowercase and run-on lines, the tradition is to do it like this

SELECT min(i.Total), i.CustomerId, c.FirstName, c.LastName
  FROM invoices i
  JOIN customers c
    ON c.CustomerId = i.CustomerId;

END

Colophon

This slideshow was produced using quarto

Fonts are Roboto, Roboto Light, and Victor Mono Nerd Font