(The following is adapted from a chapter of the upcoming book, Self-Guided SQL: Build Your SQL Skills with SQLite. You can download the sample databases referenced on the Github project page.)

Database software often comes with different types of interfaces including graphical, menu-based browsers and text-only console programs. While browser programs are easier to navigate, it’s important to understand how to work with a text-based command line program, sometimes referred to as the console. It can even be faster for certain tasks, once you’re familiar with the commands and have some practice. They also tend to be more standardized in operation, as opposed to the graphical programs that can vary in layout and features. For this reason, we’ll start out with an overview of basic operations in the SQLite console.

Read more …: How To Navigate the SQLite Command Line

Opening a database

• If you are running Linux or you added your SQLite directory to your Windows path statement, you should be able to open the Linux Terminal or the Command window in Windows and simply type SQLite3 at the prompt.

The program will load with some introductory text, including the version information and instructions for a couple of commands. See the next page for an example.

• Type “.help” to get a list of all console commands. Don’t forget the period at the beginning. Then type .help open to get specific help on the .open command.

Take a few moments to look through the commands and their descriptions but don’t worry if you don’t understand many of them at this point. Some of them perform immediate actions while others change settings within the console. You can get additional help on any of these commands by typing .help followed by the command name.

The first task is to open one of the sample databases that you downloaded and installed. Since you’re working in the console, you can’t just point and click to the file location. You need to enter the proper command with the location of the database. You can type in the full path and name of the database or you can navigate to the right directory and open the file there.

Let’s look at some navigation commands. The .shell command gives you access to the terminal commands for whatever operating system you’re using.

• In the console, type .shell cd for Windows or .shell pwd for Linux to see the current working directory. This should be the directory where your main SQLite files are stored.
• Use the .cd command to change the current working directory to the location of your sample files, i.e.:
  (Windows) .cd c:\SQLite\Samples
(Linux) .cd /home/user/Documents/Databases
• Use the .open command to open a sample database.
  
  .open chinook.db

It’s not strictly necessary to change the working directory; you could include the entire path in the .open command. Still, it’s good to know how to move around between directories.

If there are any spaces in the directory names involved (i.e. /Documents/Sample Databases), you will need to add quotes around the path name. On Windows, you’ll need to use double backslashes between the directory names. (i.e. “c:\SQLite\Sample Databases”. Also, remember that Linux path statements are case-sensitive.

Examining the tables

• Use the .tables command to show a list of the tables in the database.

Every database has one or more tables in it that holds information on specific subjects. Many of these tables are also related to each other within the database so that, for example, you can retrieve lists of all albums for a specific artist or all tracks on a specific album. The list of artists and the list of albums are stored in separate tables that have the specific fields those subjects need.

These tables are related by common fields so that, as you’ll see later, you can issue queries on both of them at the same time and get the necessary information.

• Enter the following statements to get information on a couple of the tables. Don’t forget the semi-colon at the end.
  
  pragma table_info(‘artists’);
pragma table_info(‘albums’);

These are not console commands like .table or .open. The pragma keyword is an extension of SQL unique to SQLite so it doesn’t start with the period and the statements do require the semi-colon at the end which tells the console that the statement is complete and can be run. If you leave the semi-colon off, the console will simply move down a line and let you keep typing until it sees a semi-colon.

The statements give you information about the fields in the tables and their settings. In this case, you’ll notice that both tables have an ArtistID field. This is the common field on which these two tables are linked. You can get the same information with the following console commands.

.schema artists
.schema albums

These commands will actually show you the SQL statements used to create the tables.

---

Practice Exercise:
Try both the pragma statements and .schema commands on different tables within the database to see the results and help you remember the syntax. You can always use the .tables command to see the list of tables again.

---

Attaching another database

You can have multiple databases open at the same time within the SQLite console. This can be handy if you have related databases or simply want to combine information from more than one database. In future chapters, you’ll see examples from all three of the sample databases so you’ll need to have one open and the other two attached.

• Enter the following SQL statement in the console. I’m using the Sakila database as an example.
  
  ATTACH DATABASE ‘sqlite-sakila.db’ AS sakila;

• List the tables again with the .tables command.

You now have access to all the tables in both databases with the second database using the alias ‘sakila’ before the table names. This helps if both databases have tables with common names. The alias itself can be anything you want it to be.

• If you have all three sample databases downloaded, repeat the above procedure with the Northwind database.
  
  ATTACH DATABASE ‘northwind.db’ AS northwind;

You can use the UP arrow key to recall previous commands that you entered into the console. In the above example, you could recall the ATTACH command for the Sakila database and simply edit it to attach the Northwind database.

Use the .databases command to see the list of databases that are currently attached to the console.
When you’re done with an attached database, you can detach it using the DETACH statement with the alias that you’ve assigned to the database.

DETACH DATABASE sakila;

Querying a table

Once you have your tables loaded, you’ll want to be able to get the information out of them. Most of this book will be about the SQL statements that you use to query exactly the data you need but we can start with a basic statement just to see how they look in the console.

With the Chinook database loaded, enter the following command and statement.

.mode column
SELECT FirstName, LastName, Company, Phone
FROM Customers LIMIT 10;

If you’re querying an attached database, you will need to put the database alias before the table name.

SELECT first_name, last_name
FROM sakila.customer LIMIT 10;

In SQLite, the alias for the database that you open without specifying an alias is “main”. You do not need to specify it and, if your main database has a table with the same name as an attached database, a SQL query will default to it. In this book, I will specify the database in the instructions rather than the queries themselves.

Remember that you can get the information on table column names using the .schema command.

The first SELECT statement above instructs the program to display information from four of the fields from the Customers table. The LIMIT 10 clause limits the output to the first 10 records it finds. This is a helpful instruction when you just want to get a sense of what the data looks like without dumping the entire table to your screen. Some tables get really big. You’ll see a lot more helpful keywords in future chapters.

The .mode column command forces the console to display results of SQL statements in columns for easier reading and you should probably experiment with it at this point. This command actually has 14 different options including .mode table and .mode html which will generate HTML that can be pasted into a web page to display the query results. You can get additional information about the options by typing:
.help .mode

As a side note, SQL is usually not case-sensitive but the statements can get very long and complex and it’s a common convention to capitalize the keywords such as SELECT and FROM to make it more readable. SQLite and other database software also doesn’t care about whitespace or line breaks so SQL statements can (and should) be formatted for better readability by humans.

Quitting the console

When you want to close the console, either the .quit or .exit command will close everything down and exit.

There’s a lot more that you can do within the console. Throughout the book, I’ll show you some examples alongside the instructions for the graphical browser environment.

Takeaways

In just these past few pages, you’ve seen the basics of working in the SQLite console and are off to a great start!

You should be able to:

• Open the SQLite console from the Windows or Linux command line.
• Get help within the console using the .help command.
• See and change the current working directory within the console.
• Open a database using the .open command.
• Use the .tables and .databases commands to get information on currently accessible objects.
• Use pragma and .schema to get design information on specific tables.
• Attach and detach multiple databases within the same console session.
• Issue a basic SQL statement in the console.

You have also learned about:

• Basic relational database theory concerning the use and linking of tables.
• The use of aliases to refer to databases in SQLite.
• The difference between console commands and SQL statements.

If any of these things seem unfamiliar, take some time to look back through the section and work through some of the tasks again. Experiment with different databases, tables and options so you can commit these things to memory.

If you’re still a little fuzzy on some of it, don’t worry – you’ll gain more experience in these and other concepts in future lessons. You can also find more information online at https://sqlite.org/ and on many other websites by copying any of these items into your favorite search engine. console programs.