Databases with 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.

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 box. Let's see how it works:

select count(*) from titles;
┌──────────┐
│ count(*) │
├──────────┤
│ 1        │
└──────────┘

Imagine that - the command to show us something is select!

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

Since we didn't specify any specific condition/s to meet, it showed us all the records.

Since there is only 1 item in the titles table, of course it only shows us that one. If there had been 5479 items in the table, it would have shown ALL of them, and ALL their fields. But don't worry, we can specify which ones we want to see. Coming up soon.

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

select title,titleid,pubyear from titles;
┌────────────────────────────────────┬─────────┬─────────┐
│               title                │ titleID │ pubYear │
├────────────────────────────────────┼─────────┼─────────┤
│ Laptops For Seniors For Dummies 5E │ 1001    │ 2017    │
└────────────────────────────────────┴─────────┴─────────┘

And it shows us, in the order we specified.

select format,pubyear,title,author from TITLES;
┌────────┬─────────┬────────────────────────────────────┬───────────────┐
│ format │ pubYear │               title                │    author     │
├────────┼─────────┼────────────────────────────────────┼───────────────┤
│ epub   │ 2017    │ Laptops For Seniors For Dummies 5E │ Muir,Nancy C. │
└────────┴─────────┴────────────────────────────────────┴───────────────┘

Note that TITLES worked just as well as titles.

select without tables

Huh? How do you select something without any tables?

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

Check that link to find out more, but for now play with these.

What is 89³?:

select pow(89,3);
┌───────────┐
│ pow(89,3) │
├───────────┤
│ 704969.0  │
└───────────┘

What's the square root of 468.34?

select sqrt(468.34);
┌──────────────────┐
│   sqrt(468.34)   │
├──────────────────┤
│ 21.6411644788352 │
└──────────────────┘

And Pythagoras?

select sqrt(pow(3,2) + pow(4,2));
┌───────────────────────────┐
│ sqrt(pow(3,2) + pow(4,2)) │
├───────────────────────────┤
│ 5.0                       │
└───────────────────────────┘

What's the area of a 12" pizza?

select (pi() * pow(6,2));
┌───────────────────┐
│ (pi() * pow(6,2)) │
├───────────────────┤
│ 113.097335529233  │
└───────────────────┘

How many radians in 33°?

select radians(33);
┌───────────────────┐
│    radians(33)    │
├───────────────────┤
│ 0.575958653158129 │
└───────────────────┘

That should keep you busy for a while.

↟