week 5 day 1 - SQLite work

SQLite work

sqlite3 classroom.sqlite3

CREATE TABLE students (id INTEGER, first_name TEXT, last_name TEXT)

INSERT INTO students (id, first_name, last_name) VALUES (1,"Ben","Doane"),(2,"Ben","Barnett"),(3,"Matt","Fair"),(4,"Angie","Martaus"),(5,"Lauren","Imhoff"),(6,"Shirley","Grigsby"),(7,"Anna","Lioubimova"),(8,"Michelle","Caudle");

sqlite> SELECT * FROM students;                                               
1|Ben||Doane
2|Ben||Barnett
3|Matt||Fair
4|Angie||Martaus
5|Lauren||Imhoff
6|Shirley||Grigsby
7|Anna||Lioubimova
8|Michelle||Caudle


sqlite> SELECT COUNT(*) FROM students;
8

sqlite> SELECT COUNT(*) FROM students WHERE first_name LIKE "A%";
2

sqlite> CREATE TABLE posts (id INTEGER, title TEXT, body TEXT, summary TEXT, student_id INTEGER);

sqlite> INSERT INTO posts (id,title,body,summary,student_id) VALUES (1,"The Apes and the Two Travelers",'Two men were travelling together, one of whom never spoke the truth, whereas the other never told a lie: and they came in the course of their travels to the land of Apes.
   ...>             The King of the Apes, hearing of their arrival, ordered them to be brought before him; and by way of impressing them with his magnificence, he received them sitting on a throne, while the Apes, his subjects, were ranged in long rows on either side of him.
   ...>             When the Travellers came into his presence he asked them what they thought of him as a King.
   ...>             The lying Traveller said, "Sire, every one must see that you are a most noble and mighty monarch."
   ...>             "And what do you think of my subjects?" continued the King.
   ...>             "They," said the Traveller, "are in every way worthy of their royal master."
   ...>             The Ape was so delighted with his answer that he gave him a very handsome present.
   ...>             The other Traveller thought that if his companion was rewarded so splendidly for telling a lie, he himself would certainly receive a still greater reward for telling the truth; so, when the Ape turned to him and said, "And what, sir, is your opinion?" he replied, "I think you are a very fine Ape, and all your subjects are fine Apes too."
   ...>             The King of the Apes was so enraged at his reply that he ordered him to be taken away and clawed to death.
   ...>           ',"Apes",1);
   
   sqlite> INSERT INTO posts (id,title,body,summary,student_id) VALUES (2,"plymouth rock","This really long thing that I'm not re-typing","Mark Twain",2);
   
   sqlite> INSERT INTO posts (id, title, body, summary, student_id) VALUES (3,"The Lady in Pink","She wore pink","Sir Arthur Conan Doyle",3),(4,"Consider the Lobster","He can be a bit crabby, wouldn't you say?","David Foster Wallace",3),(5,"Dr. Strangelove","He wasn't a real doctor","Stanley Kubrick",1);