Skip to content

Instantly share code, notes, and snippets.

@josh1812

josh1812/code_division_solution_Database_Drill:_Intro_to_SQLite.rb

Last active August 29, 2015 14:25
Show Gist options
  • Save josh1812/a67a6be7b991092ca028 to your computer and use it in GitHub Desktop.
Save josh1812/a67a6be7b991092ca028 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
code_division_solution_Database_Drill:_Intro_to_SQLite.rb
~ sqlite3 dummy.db
-- Loading resources from /home/codedivision/.sqliterc
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
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
, nicknames VARCHAR(64), estelle VARCHAR(64) DEFAULT ' ' NOT NULL, nickname VARCHAR(64) DEFAULT '' NOT NULL);
CREATE TABLE "NamesOfFriends"(Id INTEGER, Name TEXT, Email TEXT);
sqlite> SELECT * FROM users;
id first_name last_name email created_at updated_at nicknames estelle nickname
---------- ---------- ---------- ---------------------- ------------------- ------------------- ----------- ---------- ----------
1 Jeremy Teng jeremy.ongws@gmail.com 2015-05-19 01:30:41 2015-05-19 01:52:26 Ninja Coder
2 Paul Scholes paul@scholes.com 2015-05-19 01:31:55 2015-05-19 01:31:55
3 Joshua Teng josh@codedivision.com 2015-07-21 01:55:17 2015-07-21 01:55:17
4 Josh Yong joshjunhou@gmail.com 2015-07-21 02:03:26 2015-07-21 02:03:26
sqlite> SELECT * FROM users;
id first_name last_name email created_at updated_at nicknames estelle nickname
---------- ---------- ---------- ---------------------- ------------------- ------------------- ----------- ---------- ----------
1 Jeremy Teng jeremy.ongws@gmail.com 2015-05-19 01:30:41 2015-05-19 01:52:26 Ninja Coder
2 Paul Scholes paul@scholes.com 2015-05-19 01:31:55 2015-05-19 01:31:55
3 Joshua Teng josh@codedivision.com 2015-07-21 01:55:17 2015-07-21 01:55:17
4 Josh Yong joshjunhou@gmail.com 2015-07-21 02:03:26 2015-07-21 02:03:26
sqlite> UPDATE users SET nickname='Pookie Butt' WHERE id=3;
sqlite> SELECT * FROM users;
id first_name last_name email created_at updated_at nicknames estelle nickname
---------- ---------- ---------- ---------------------- ------------------- ------------------- ----------- ---------- ----------
1 Jeremy Teng jeremy.ongws@gmail.com 2015-05-19 01:30:41 2015-05-19 01:52:26 Ninja Coder
2 Paul Scholes paul@scholes.com 2015-05-19 01:31:55 2015-05-19 01:31:55
3 Joshua Teng josh@codedivision.com 2015-07-21 01:55:17 2015-07-21 01:55:17 Pookie But
4 Josh Yong joshjunhou@gmail.com 2015-07-21 02:03:26 2015-07-21 02:03:26
sqlite> UPDATE users SET nickname='Josh is awesome' WHERE id=4;
sqlite> SELECT * FROM users;
id first_name last_name email created_at updated_at nicknames estelle nickname
---------- ---------- ---------- ---------------------- ------------------- ------------------- ----------- ---------- ----------
1 Jeremy Teng jeremy.ongws@gmail.com 2015-05-19 01:30:41 2015-05-19 01:52:26 Ninja Coder
2 Paul Scholes paul@scholes.com 2015-05-19 01:31:55 2015-05-19 01:31:55
3 Joshua Teng josh@codedivision.com 2015-07-21 01:55:17 2015-07-21 01:55:17 Pookie But
4 Josh Yong joshjunhou@gmail.com 2015-07-21 02:03:26 2015-07-21 02:03:26 Josh is aw
sqlite> UPDATE users SET first_name='Jawsh', nickname 'Ninja Cookie Butt' WHERE ID=3;
Error: near "'Ninja Cookie Butt'": syntax error
sqlite> UPDATE users SET first_name='Jawsh', nickname= 'Ninja Cookie Butt' WHERE ID=3;
sqlite> SELECT * FROM users;
id first_name last_name email created_at updated_at nicknames estelle nickname
---------- ---------- ---------- ---------------------- ------------------- ------------------- ----------- ---------- ----------
1 Jeremy Teng jeremy.ongws@gmail.com 2015-05-19 01:30:41 2015-05-19 01:52:26 Ninja Coder
2 Paul Scholes paul@scholes.com 2015-05-19 01:31:55 2015-05-19 01:31:55
3 Jawsh Teng josh@codedivision.com 2015-07-21 01:55:17 2015-07-21 01:55:17 Ninja Cook
4 Josh Yong joshjunhou@gmail.com 2015-07-21 02:03:26 2015-07-21 02:03:26 Josh is aw
sqlite> UPDATE users
...> SET updated_at = CURRENT_TIMESTAMP
...> where id = 3
...> ;
sqlite> SELECT *
...> FROM users
...>
...> ;
id first_name last_name email created_at updated_at nicknames estelle nickname
---------- ---------- ---------- ---------------------- ------------------- ------------------- ----------- ---------- ----------
1 Jeremy Teng jeremy.ongws@gmail.com 2015-05-19 01:30:41 2015-05-19 01:52:26 Ninja Coder
2 Paul Scholes paul@scholes.com 2015-05-19 01:31:55 2015-05-19 01:31:55
3 Jawsh Teng josh@codedivision.com 2015-07-21 01:55:17 2015-07-21 03:10:28 Ninja Cook
4 Josh Yong joshjunhou@gmail.com 2015-07-21 02:03:26 2015-07-21 02:03:26 Josh is aw
sqlite>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment