First time using coursera

A few days ago, I registered for my first class using coursera.  Coursera provides free online courses from top-notch educators.  The first class I am taking is https://class.coursera.org/db/class, by Jennifer Widom from Stanford University.

I use databases pretty often, so I expect to be familiar with a lot of the material.  Nevertheless, I hope the course will help me fill in gaps of knowledge that I might have missed and organize my understanding of databases in general.

So far, I have only watched the first lecture, which was just introductory material on databases.  Here are my some things that I liked.

• The quality of the video and audio is good.  It is clear and clean.
• The video has subtitles, which I turn on even when I have audio on.  
• The video also lets you change the playback speed. I set it to 1.25x speed.  When I come to a lecture where the material is more unfamiliar or a bit difficult to understand, I can reduce it back to normal speed. 
• Each lecture is relatively short. The longest one goes for 30 minutes, but many are around 15 minutes.
• The ability to pause and rewind is great and much better than live lectures.  If I missed something I can rewind and try again.  Or, I can go on the internet and search for a different explanation.  I can then move on with the lecture when I feel ready. 

Overall, I think coursera is a pretty great service, and I hope that I persevere long enough to benefit from it.  

Testing sql queries

I was working on an Android application, and one of my queries wasn't returning what I expected it to.  

To test my query, I decided to just run sqlite on my computer, which is pretty simple.  Here are the steps:

1. Download sqlite from http://www.sqlite.org/ .
2. Unzip the file into some folder and then you can start a database called test1 with a command like this:
   ~/bin/sqlite3 test1.db
3. Create a table and insert some data.
   create table phrase_table (words char(50));
   insert into phrase_table (words) values ('hello');
4. Run queries like:
   select * from phrase_table;

While that was pretty simple, another tool to quickly test queries is http://sqlfiddle.com/ .  This is a web tool where you can set up a database table and then test queries.  For simple cases where you'd like to share your queries, this is a fantastic tool.

MySQL on Macbook

My wife wanted to set up an environment so that she could practice her sql skills.  After a bit of research, I decided to give MySQL a try.  Here are my notes on how it went.

Step 1. Download the software.
http://dev.mysql.com/downloads/mysql/
I chose the 64 bit DMG archive for Mac version to install.  Before the download started I had to jump through a few hoops.  First, I had to register to get a new oracle account.  This sign up was pretty painful.  I had to provide my name, full address, phone number, and a complicated password.  As usual, I entered a password that I will surely forget by the next time I am asked for it.  I then had to fill out a survey about my job, what I will use the software for and some other questions.  This was a bit off-putting, but in the end, I finally downloaded the file.

Step 2. Installation.
I clicked on the downloaded dmg file and then the pkg file which started the installation process.  I was told that the install would use 680.9 MB of space, which seems a bit high.  The software was installed in less than a minute.  I also installed the MySQLStartupItem.pkg, but I'm not sure what that is.  I finally installed the MySQL.prefPane, which gives a GUI (placed as an icon in system preferences) so that you can easily start the MySQL server.

Step 3. Hello world.
I wanted to do the basic operations with a database to make sure that I had installed everything ok.  In the programming world, when you start working with a new language, you would try to write some code, compile it, and then output the results.  Typically, you make the program say, "hello world".  I think I got my database to say "Hello World" with the following commands:

a. First, I created a database:

mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)

b. I confirmed that my database was created by showing them:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

c. I then told mysql to use the database I just created.

mysql> use testdb;
Database changed

d. I created a table.

mysql> create table phrase_table ( 
    ->   words char(25)
    -> );
Query OK, 0 rows affected (0.02 sec)

e. I added one row. It has one column called words and the phrase "Hello World".

mysql> insert into phrase_table (words) values("Hello World");
Query OK, 1 row affected (0.00 sec)

f. I finally selected all rows from my table to get the program to say "Hello World".

mysql> select * from phrase_table;
+-------------+
| words       |
+-------------+
| Hello World |
+-------------+
1 row in set (0.00 sec)

e. Done. I got the basic operations to work. This link was pretty helpful. http://www.mysqltutorial.org/basic-mysql-tutorial.aspx