PRAGMA Statements

I don't really know what the deal is with the pragma statement but for some reason it automatically placed it when I used the dump command along with the commit statement. I'm assuming it just removes the foreign key restriction judging from what sqlite.org said.

"As of SQLite version 3.6.19, the default setting for foreign key enforcement is OFF. However, that might change in a future release of SQLite. The default setting for foreign key enforcement can be specified at compile-time using the SQLITE_DEFAULT_FOREIGN_KEYS preprocessor macro. To minimize future problems, applications should set the foreign key enforcement flag as required by the application and not depend on the default setting." http://www.sqlite.org/pragma.html

Querying Data

Instead of adding more values I tried querying data because before the results I was getting had a lot of repeating data. I was surprised and very happy to figure out how to restrict results to just one name and one movie. Here are some scripts I made to query data.

This one restricts the search to a person's first name, Hank, and lists their name along with their ID, Title of the movie and its ID.
https://dl.dropboxusercontent.com/u/8028799/movie_query1.sql

This one restricts the search to a person's last name, Krabs, and lists their full name with only the title of the movie.
https://dl.dropboxusercontent.com/u/8028799/movie_query2.sql

This one was difficult to get working but it lists any results whose name starts with an "A" but only shows the movies they like that have an ID less than 8, but it also shows results for people with a last name that starts with "H" and any movie that ends with an "s".
https://dl.dropboxusercontent.com/u/8028799/movie_query3.sql

Movie Database

I added more values mainly to the likes table so the database is basically done now. Also, when I tried to query info I got more data than I wanted but besides that everything is fine.
https://dl.dropboxusercontent.com/u/8028799/movie_likes_insertscript.sql

Movie Database

I spent most of the day adding more values to the database and I also messed around with selecting different values. This is the updated insert script.
Insert Code

SQL Movie Database

I started on the database for list of movies people like and I got pretty far. I made 2 files, 1 to make the tables and another to insert values. This is it so far.
Tables
Values

When you select the likes table you currently get people's ID's matched with the ID's of movies they like.

sqlite> select * from likes;
3|1
1|2
2|2
3|3



I'll also add some more values later.

SQL Files

Here is the code for the database:
https://dl.dropboxusercontent.com/u/8028799/export_destination.sql
https://dl.dropboxusercontent.com/u/8028799/product.sql
https://dl.dropboxusercontent.com/u/8028799/sales_statement.sql
https://dl.dropboxusercontent.com/u/8028799/sales.sql

I think it is all working so I will start on that new database ASAP

SQL

Pretty sure I figured out how to use the dump command and I made some different tables and practiced queering different data. I am excited to write the book and I will try to write the book similar to how you wrote yours because it was very easy to understand your books. I'll try to learn other things that could be relevant to write in the book and I will continue practicing.

SQL

I think I mostly understand primary keys and a little of foreign keys so the database is complete, I think. I also thought I understood how to use the dump command but I don't so I could use some help with that. I just practiced some SQL in the mean time because you had to teach web design.

Primary and Foreign Keys and Data Dump

I spent most of the day trying to figure out when to use primary and foreign keys and I sort of understand it and I figured out how to dump data. That's basically all I did because I'm a bit confused about primary keys but I almost have the entire fruit database done and I understand most of SQL.

More SQL Practice

I have been trying things stated in the manga like "WHERE" statements and it has helped quite a bit to understand the format of SQL. I'm almost done with the fruit tables made in the manga I just have to add the foreign keys and primary keys but I'm not too sure about those and I will try adding info. Should I try messing with scripts and making my own table with foreign keys next week or do you have something else in mind?

SQL

I spent most of the day changing around the tables so that they are exactly like the ones in the manga but I wasted a lot of time with silly mistakes. I was able to finally get this chunk of code working which was kind of annoying.

sqlite> SELECT sales.report_code, dates, sales.export_destination_code, export_destination_name, sales_statement.product_code, product_name, unit_price
   ...> FROM sales, sales_statement, product, export_destination
   ...> WHERE sales.report_code = sales_statement.report_code
   ...> AND
   ...> sales_statement.product_code = product.product_code
   ...> AND
   ...> export_destination.export_destination_code = sales.export_destination_code;
1101|3/5|12|KINGDOM OF MINANMI|101|MELON|800
1101|3/5|12|KINGDOM OF MINANMI|102|STRAWBERRY|150
1102|3/7|23|ALPHA EMPIRE|103|APPLE|120
1103|3/8|25|THE KINGDOM OF RITOL|104|LEMON|200
1104|3/10|12|KINGDOM OF MINANMI|101|MELON|800
1105|3/12|25|THE KINGDOM OF RITOL|103|APPLE|120
1105|3/12|25|THE KINGDOM OF RITOL|104|LEMON|200

SQL

I finished recreating all the tables that were mentioned in the manga and I think they are all functional. I practiced with some statements and they are actually kind of neat. I messed around with the product table a bit and selected different data by using restrictions like unit_price<800.
I also think it's worth mentioning that my sister is taking SQL classes and she needed help making a table so I became a bit more familiar with scripts and foreign/primary keys with the help of my brother.

Getting Down With SQL

I looked over the Wikipedia page about Normalization and I think I understand it. I don't fully understand 1st, 2nd, and 3rd Normal Form because a lot of terms I do not know are used but I think I know enough to start working with SQL. I started by recreating the tables made in the Manga and I think I finally know how to operate the basics after coming across problems but fortunately you could answer them(like not using periods in my code). Tomorrow I will finish up the tables and try different operations like updating the data.

SQL Practice

I practiced SQL and it was really annoying at first but I think I am getting the hang of it. I started by making a table of presidents but I had trouble inserting values and I didn't quite understand the syntax so then I just made a random table with random values and got everything working. So it is possible to make data tables through vim or through SQL? I made mine using SQL, should I know how to do it through vim too?
 

This is what I did, unfortunately it took me a long time to figure out
sqlite> CREATE TABLE test(
   ...> stuff varchar,
   ...> garbage varchar,
   ...> random varchar);
sqlite> insert into test (stuff, garbage, random) values (blah, spam, woop)
   ...>
Error: no such column: blah
sqlite> INSERT INTO test(stuff, garbage, random) VALUES ("blah", "spam", "woop");
sqlite> select * from test;
blah|spam|woop