For future reference, the way sqlite reads an sql file is with ".read". In this case I used ".read moviedb.sql" and then ".read moviedbinsert.sql" for the data. I reviewed sql queries and I messed around with selecting certain data.
SELECT Fname, Likes.PersonID, Title, Likes.MovieID
FROM Person, Likes, Movies
WHERE Fname = "Rusty"
AND Person.PersonID = Likes.PersonID
AND Movies.MovieID = Likes.MovieID;
Like this would only show the person's first name, then their ID, and the title and ID of
the movie they like. You can try more complicated things with a lot of data using "or" or the "like"
statement which finds data restricted to certain letters or greater than or less than a certain
number.
SELECT Fname, Likes.PersonID, Title, Likes.MovieID
FROM Person, Likes, Movies
WHERE Lname LIKE "%o"
AND Person.PersonID = Likes.PersonID
AND Movies.MovieID = Likes.MovieID;
This displayed a person's first name, their ID, along with the name and ID of the movie they
liked but it sorted it by last names only ending in "o".
I think I understand SQL pretty well again, I think I just need to learn how to apply it better though,
like when using it for a form.
It is important to keep clear in your mind that those command that begin with "." belong to the sqlite3 command shell (http://www.sqlite.org/cli.html), and not to SQLite proper. In fact, while the sqlite library comes bundled with Python (and lots of other languages as well), the command shell does not, so on a fresh install of an ubuntu box you would have access to SQLite, but not to the command shell. How can you access it without the command shell, you may ask? Well, the way it is really used in practice, programmaticlly from your Android app or from you Python program, etc.
ReplyDeleteThis will make so much more sense once we have the database integrated into the ATIC website.