Databases with SQLite

Now What?

Now that we've designed, normalized, and populated our database, what do we do with it?

If this sounds like a dumb question, bear with me. I was once a dummy.

Of course the whole point of building the database is so that we can now pull or extract 'useful' data out of it. Depending on your actual situation, this may become another rabbit hole you need to dig into and understand, or it may be a slight, uphill curve in the road.

As may be expected, there are various ways to do these extractions:

• Compound Operators
• Joins

We also should consider the purpose of a particular database.

• For a business database, you may be in for another 'learning experience' on the best way to design, build, and do these extractions.
• On the other hand, if this is a personal database to manage your own collection of 'stuff' (books, CDs), this may be quite a simple (even enjoyable?) exercise.

Another situation might be what environment you are doing your database management in:

• You are using a scripting language (Perl, Lua, Julia, Python, bash), possibly for web-enabled use, either locally or hosted
• This is done completely through the Terminal window and the sqlite3 command-line application

Compound Operators

There are several ways to pull data out, usually all involving the select statement.

One way is with compound operators, named that because they are typically used with multiple select queries.

They are also known as set operators, and we will use Venn diagrams to represent them visually.

We will be using the following 2 tables:

CREATE TABLE a1 (c1 INT);

INSERT INTO
a1 (c1)
VALUES
(1),
(2),
(3),
(4);

CREATE TABLE a2 (c2 INT);

INSERT INTO
a2 (c2)
VALUES
(2),
(3),
(4),
(6);

SQLite has 4 compound operators availble:

• UNION ALL
  Concatenate results from each SELECT into one large table. Allows duplicate values, and may result in a LARGE number of results.

  select c1 from a1 union select c2 from a2;
  c1
  --
  1
  2
  3
  4
  2
  3
  4
  6
  



• UNION
  Like UNION ALL but list only distinct values

select c1 from a1 union select c2 from a2;
c1
--
1
2
3
4
6



• INTERSECT
  List values contained in both tables
The dataset returned by the INTERSECT statement will be the intersection of the data sets of the two SELECT statements. Only those rows that are common to both of the SELECT statements will be returned.


select c1 from a1 intersect select c2 from a2;
c1
--
2
3
4



• EXCEPT
  Return all values in the first SELECT statement the are NOT found in the second SELECT statement.

  select c1 from a1 except select c2 from a2;
  c1
  --
  1