Learn SQL The Hard way - Creating .sql with .db in SQL Lite 3 - Why and How?

Question

As a beginning programmer with +20 hours of Python coding and novice familiarity with the command-line, I opened up Zed Shaw's "Learn SQL The Hard Way" and was quickly stumped.

In exercise 01, Zed has you create your first table with this first command:

sqlite3 ex1.db < ex1.sql

However this fails to run in my command-line, giving the error message, "-bash: ex1.sql: No such file or directory." Initially, I ignored this recommended code and proceeded with:

sqlite3 ex1.db
SQLite version 3.7.15.1 2012-12-19 20:39:10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE person (
   ...>     id INTEGER PRIMARY KEY,
   ...>     first_name TEXT,
   ...>     last_name TEXT,
   ...>     age INTEGER
   ...> );

Running "ls -l" in the command-line shows:

-rw-r--r--  1 thefifth  staff  2048 Feb 15 15:23 ex1.db

But what I want and am failing to get is:

$ ls -l
-rw-r--r--  1 zedshaw  staff  2048 Nov  8 16:18 ex1.db
-rw-r--r--  1 zedshaw  staff    92 Nov  8 16:14 ex1.sql

I Googled around and found this blog that implements the same "name.db < name.sql" syntax, but following along the code here didn't work for me either. This Stack Overflow also has a similar syntax, but in the context of converting .sql to sqlite3.

Specifically, I'm wondering if this is the "<" for use in the native bash terminal, and that I'm failing to meet certain criteria for its correct use. Also, I don't know the purpose of creating both a .sql and .db file, although apparently one is much smaller than the other. Perhaps I've installed sqlite3 incorrectly, but it seems to be working fine.

Thanks for your help!

Answer 1

sqlite3 ex1.db < ex1.sql

For the above to work, ex1.sql should already exist. < is a character used in shells for input redirection. sqlite3 is started here with a new or existing database (it will create the database as needed), and gets SQL statements from ex1.sql, executing them and modifying ex1.db accordingly.

Now let's generate ex1.sql from ex1.db, which you apparently want to do:

sqlite3 ex1.db .dump > ex1.sql

We use shell redirection facility again, now redirecting the output to ex1.sql. The .dump command makes sqlite write out SQL statements that will recreate similar database when they are executed in an empty database: tables are recreated and populated with INSERT, etc.

Now you can go to step 1:

sqlite3 ex1copy.db < ex1.sql

Answer 2

1. Save your code in a file with the extension .sql
2. Then in the terminal: sqlite3 ex1.db < ex1.sql for create a ex1.db
3. Put in the terminal: sqlite3 ex1.db .schema
4. Or put: sqlite3 ex1.db [... and then....] .schema

Answer 3

I ran into same problem today, and like you I was wondering why my command is not working. As mentioned above author assumes that you create the initial command in a text editor and save as a .sql file. And then ex1.db < ex1.sql command converts the file to a .db file.