GitHub Repo with appropriate files.
I have created a simple, single-table, database for you to work with. It contains all of the words in the scrabble dictionary with some pleasant features (each row is identified by a set of alphabetical letters and contains the scrabble values of those letters and all scrabble-legal words built from exactly those letters).
To achieve the first goal, connecting with sqlite3 in a prompt, we will setup a virtual machine (server even) using cloud9. First create a github account. Next send me your github username and I will add you to cisc437 our github organization. Then use your github account to create a cloud9 account. Create a workspace to play with.
From there we will have a bash shell to interact with our first db. SQLite is a great starter DB because it is just a file, that makes it portable. Use the command sqlite3 scrabble.sqlite to open a prompt interacting with the DB (first make sure you have uploaded the file).
From within the DB session we can ask SQLite for a list of tables using .tables and given a table name (in this case racks) we can see its schema using pragma table_info(racks);.
You can also evaluate SQL queries by just entering them. One of my favorite "bingo" racks is "AENORST" because the words in it tell a story. Find the words in that rack by using the SQL query:
select words from racks where rack="AENORST";
Now try to find all anagrams of "RETINAS".
SQL stores data in "tables" which we can call "relations" which is a math-y way of saying "tuple" which is a CS-y way of describing a vector. If you've used spreadsheets then you have a decent starting model by thinking of a spreadsheet as a table, each row is an entry. It isn't the best model because each column of the table has a specified type and a specified name and we often have many tables which relate to each other.
In this case the racks table has four columns: rack a string, weight an integer, length an integer, and words a string.
To get data out of the table we use a SELECT statement which has the basic structure of:
SELECT {*, (col_1, col_2, ..., col_n)} FROM table_name_here (WHERE condition_1_here (AND condition_2_here)) (ORDER BY {func(), col_1_name {ASC, DESC} (, col_2_name {ASC, DESC})}) (LIMIT (start_number,) number_of_responses)
That is a lot of parts to toss at you, but let's clarify with an example. To generate a single, random, 6 letter rack (containing at least one word) which uses only 1-point tiles (AEIOU RSTLN) I could use the following query:
SELECT * FROM racks WHERE length=6 AND weight=6 ORDER BY random() LIMIT 0, 1
Learn by example. Get each of these four running on your own cloud9 and make an alteration of some sort.