On a Mac, SQLite is already installed. You can run sqlite3 and play at the interactive console. Here is the session where I play with Rails application development database in sqlite3 interactive console:
$cd ~/projects/lafon/db $sqlite3 development.sqlite3 SQLite version 3.7.13 2012-07-17 17:46:21 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select count(*) from articles; 25 sqlite> select id from articles; 15 20 22 26 ... more output snipped
Selecting a Record
sqlite> select * from articles where id=15; 15|TDD Basics : Writing your First Test|Pre-requisites ========= - Ruby 2 or above must be installed - Your favorite text editor - RSpec gem 2 or above. Testing Objective ========== ... more output snipped
We don't know the database fields from the output. Let's use the shell-specific command .headers:
sqlite> .headers on sqlite> select * from articles where id=15; id|title|body|created_at|updated_at 15|TDD Basics : Writing your First Test|Pre-requisites ========= - Ruby 2 or above must be installed - Your favorite text editor - RSpec gem 2 or above. Testing Objective ========== ... more output snipped
To view the help you can run the .help dot-command:
To view all tables:
sqlite> .tables articles users schema_migrations
Using the column mode, we see the results are displayed as a table:
sqlite> .mode column sqlite> select id, name, email from users; id name email ---------- ---------- ---------- 1 Duck duck@examp 2 Bugs bunny@disn 3 Carrot lover@disn
The email column is snipped off after 10 characters.
Declarative vs Imperative
Jay A Kreibich in Using SQLite book says:
In a declarative language, you state what you want the results to be and allow the language processor to figure out how to deliver the desired results.
In imperative languages, each step of an operation must be explicitly written out, leaving it up to the programmer to lead the program, step by step, to the correct conclusion.
SQL is a declarative language whereas languages like C, C++, Perl, Java are imperative languages. Most SQL commands has the verb-subject form such as CREATE TABLE, DROP INDEX etc. The English-like, declarative nature of SQL makes it easy to understand.
In this article you learned the SQLite basics. You can learn more by experimenting in the SQLite interactive console. Read the documentation for the details of all the available commands.
Using SQLite by Jay A. Kreibich
Ace the Technical Interview
- Easily find the gaps in your knowledge
- Get customized lessons based on where you are
- Take consistent action everyday
- Builtin accountability to keep you on track
- You will solve bigger problems over time
- Get the job of your dreams
Take the 30 Day Coding Skills Challenge
Gain confidence to attend the interview