SQLite

Output

Before we get to see our work, we have some choices as to how it appears.

This is done with a dot command: .mode at the SQLite prompt.

By itself it tells what mode the current setting is:

.mode
current output mode: box --wrap 60 --wordwrap off --noquote

and it looks like this:

select count(title) from books;
┌──────────────┐
│ count(title) │
├──────────────┤
│ 693          │
└──────────────┘

Other modes are

table
line
list
column
csv
html
ascii

Play with each one to see what effect it has, and chose one that makes sense for your purposes.

When you find one you like, you can have SQLite use that mode each time you run it by setting up a configuration file: .sqliterc.

In your Home directory create a text file named '.sqliterc' (note the dot) and enter the following:

.headers on
.mode box

Then quit sqlite and restart it again so that it reads the config file.

Ensure you use a TEXT editor NOT a word processor to edit this file.

select

Now that we've added some material into our database, how do we get it back out?

select is the most powerful and complex command in SQL.

Here is mode set to line. Let's see how it works:

select count(*) from books;
count(*)
--------
    693

The * is a wild card, which may be familiar to you from other computer adventures. In this case, it means ALL the fields for each record matching our requisition.

And count asks - 'How many things are there?', which in this case it asks 'How many records are there in the books table?

Since we didn't specify any specific condition/s to meet, it showed us how many records are in the books table.

The 'specific conditions' mentioned above are important to understand. They will let you zero in on specific things that match the condition/s you specify.

select title,titleid,pubyear from books where pubyear=1984;
         title           titleid  pubyear
-----------------------  -------  -------
Introduction to Go (An)      120     1984
Why Math                     467     1984

We could also ask for a single field, or any combination of them:

select title,titleid,pubyear from books where title like  '%5e%';
                     title                        titleid  pubyear
------------------------------------------------  -------  -------
Fields Virology 5E                                    220     2007
Learning Perl 5E                                      254     2008
UNIX and Linux System Administration Handbook 5E      319     2018
macOS Monterey Tricks and Tips 5E                     578     2022
Professional JavaScript for Web Developers 5E         635     2024
Joe Celko's SQL for Smarties 5E                       683     2015
Introduction to Electrodynamics 5E                    689     2024
Berek and Novak's Gynecology 15E                      834     2012
Laptops For Seniors For Dummies 5E                    840     2017

And it shows us just that.

Note that 5e found 5E - like is case-insensitive.

The 2 percent signs are wild-cards in this case.

You may notice that the titles are not in any particular order. That's because we did NOT specify any order. You have to tell sqlite whether you want your output ordered, and by which field.

select title,titleid,pubyear from books where title like  '%5e' order by title desc;
                     title                        titleid  pubyear
------------------------------------------------  -------  -------
macOS Monterey Tricks and Tips 5E                     578     2022
UNIX and Linux System Administration Handbook 5E      319     2018
Professional JavaScript for Web Developers 5E         635     2024
Learning Perl 5E                                      254     2008
Laptops For Seniors For Dummies 5E                    840     2017
Joe Celko's SQL for Smarties 5E                       683     2015
Introduction to Electrodynamics 5E                    689     2024
Fields Virology 5E                                    220     2007
Berek and Novak's Gynecology 15E                      834     2012

Lower-case letters have a lower ASCII value than upper-case letters. Here we told sqlite to order the titles field in descending order.

Once you have some data to work with, play with ordering your output. You can have secondary ordering if more than 1 field is the same as another:

select without tables

How about doing some math: SQLite has several built-in math functions which may be useful.

Databases with SQLite

Output

select

select without tables