Solution for Database Drill: Intro to SQLite

Database_Drill:_Intro_to_SQLite_solution.sql

-- Solution for Challenge: Database Drill: Intro to SQLite. Started 2014-02-06T18:06:49+00:00

$ sqlite3 dummy.db
-- Loading resources from /Users/carolineartz/.sqliterc

SQLite version 3.7.13 2012-07-17 17:46:21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> CREATE TABLE users (
   ...>   id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...>   first_name VARCHAR(64) NOT NULL,
   ...>   last_name  VARCHAR(64) NOT NULL,
   ...>   email VARCHAR(128) UNIQUE NOT NULL,
   ...>   created_at DATETIME NOT NULL,
   ...>   updated_at DATETIME NOT NULL
   ...> );
   
sqlite> .schema
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name VARCHAR(64) NOT NULL,
  last_name  VARCHAR(64) NOT NULL,
  email VARCHAR(128) UNIQUE NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);

sqlite> INSERT INTO users
   ...> (first_name, last_name, email, created_at, updated_at)
   ...> VALUES
   ...> ('Jessie', 'Farmers', '[email protected]', DATETIME('now'), DATETIME('now'));
   
sqlite> SELECT * FROM users;

id          first_name  last_name   email                  created_at           updated_at         
----------  ----------  ----------  ---------------------  -------------------  -------------------
1           Jessie      Farmers     [email protected]  2014-02-06 17:42:03  2014-02-06 17:42:03

sqlite> INSERT INTO users
   ...> (first_name, last_name, email, created_at, updated_at)
   ...> VALUES
   ...> ('Caroline', 'Artz', '[email protected]', DATETIME('now'), DATETIME('now'));
   
sqlite> SELECT * FROM users;

id          first_name  last_name   email                  created_at           updated_at         
----------  ----------  ----------  ---------------------  -------------------  -------------------
1           Jessie      Farmers     [email protected]  2014-02-06 17:42:03  2014-02-06 17:42:03
2           Caroline    Artz        [email protected]       2014-02-06 17:42:47  2014-02-06 17:42:47

sqlite> INSERT INTO users
   ...> (first_name, last_name, email, created_at, updated_at)
   ...> VALUES
   ...> ('Jessie', 'Farmers', '[email protected]', DATETIME('now'), DATETIME('now'));
Error: column email is not unique

sqlite> ALTER TABLE users ADD COLUMN nickname VARCHAR(64) NOT NULL DEFAULT '';

sqlite> .schema
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name VARCHAR(64) NOT NULL,
  last_name  VARCHAR(64) NOT NULL,
  email VARCHAR(128) UNIQUE NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
, nickname VARCHAR(64) NOT NULL DEFAULT '');

sqlite> UPDATE users SET 
   ...> nickname = 'pookie butt',
   ...> updated_at = DATETIME('now')
   ...> WHERE id = 1;
sqlite> UPDATE users SET 
   ...> nickname = 'cara',
   ...> updated_at = DATETIME('now')
   ...> WHERE id = 2;
   
sqlite> SELECT * FROM users;

id          first_name  last_name   email                  created_at           updated_at           nickname  
----------  ----------  ----------  ---------------------  -------------------  -------------------  -----------
1           Jessie      Farmers     [email protected]  2014-02-06 17:42:03  2014-02-06 17:49:22  pookie butt
2           Caroline    Artz        [email protected]       2014-02-06 17:42:47  2014-02-06 17:49:54  cara       

sqlite> UPDATE users SET 
   ...> last_name = 'Farmer', 
   ...> nickname = 'ninja coder',
   ...> updated_at = DATETIME('now')
   ...> WHERE id = 1;
   
sqlite> SELECT * FROM users;

id          first_name  last_name   email                  created_at           updated_at           nickname  
----------  ----------  ----------  ---------------------  -------------------  -------------------  -----------
1           Jessie      Farmer      [email protected]  2014-02-06 17:42:03  2014-02-06 17:53:03  ninja coder
2           Caroline    Artz        [email protected]       2014-02-06 17:42:47  2014-02-06 17:49:54  cara       
sqlite>