Getting Advanced with SQL
If you have completed the lessons previously, you now have a firm handle on SQL: How to create databases, add tables, add and edit entries in a table, delete records, and delete tables. If you are unsure of how well you know these, go back and brush up! It is only going to go uphill from now.
Open up a new terminal window and create a new database in Sqlite3, by typingsqlite3 advanced.db. The Sqlite welcome text will appear; you are editing a new database called advanced.db, which probably will reside in your user folder. But we will be editing it from the command line.
Our new table will contain an address book of sorts, with space for the first and last name, phone number, email, and address. Call the table "addresses" to be consistent. (Go back to the previous lesson if you cannot remember how to do this). If you want to check that you did it correctly, skip down to the lesson log and check the input. Your statements do not have to be exactly the same as the log, however you will have to keep track of your own database.
When you have set up the new table in the database, add a few people into the database, using the insert into command. Go for three for a nice number.
As you know, we could modify, rename, or delete entries from this table. But what about drawing info from our tables, in an easier method? Using a command line it can be rather painstaking, but either way you can make good use of the group by attachement to the select statement. Let us take a look at what such a statement would look like:
select columnx, sum(columny) from "tablex" group by "column-list";
This might be a little confusing, so here is what it would look like in action:
select max(phone) from addresses group by phone;
Used on my table, the above query returns the phone numbers in increasing order descending:
1920129320 7035241067 7035339163
Let us examine what is going on. The Select is the function handle, and the max(phone) bit tells sqlite to fetch the maximum number from the phone column, in the table addresses, as specified in the second line. The third line allows us to group the numbers in a specific way. For instance, if the addresses table had another column, which was called "status", and either had "friend" or "work" or similar in their fields, and we wanted to sort the numbers by which group each number belonged in, we would do this:
repeat the following for each person */ update promlist set paid = 'y' where paid = 'n'; delete from promlist select max(phone), status from addresses group by status;
with the printout being (in the case of my table):
7035241067|friend
Why did it not spit out all the phone numbers this time? Because it was told to look through the maximum number in phone, and grouped it with the status that I added with it -- "friend". If I tell it to look for the smallest number, it returns 1920129320|friend, as the lowest phone number also belongs to a friend.
Of course, phone numbers are not what you would usually be sorting through, but think about what the statement can do and you will realize where it can be helpful -- for instance, if you had a story inventory, you could select the highest priced item in store and see how much of the product is still in stock.
Now lets look back at the first line, specifically max(). Max is one of many aggregate functions, which are used by the select statement. Max, as you guessed, returns the maximum value in the stated column; likewise, MIN returns the smallest value. SUM returns the sum of number-only values; AVG returns the average value of the stated column, COUNT returns the total number of values in a given column, and COUNT(*) returns the number of rows.
Applied SQL: The Prom, Part II
Open up your database promlist, because you are going to have to do some more work to it, using the stuff you have learned.
Task 3: The school administrators have decided they need to revise the table. While all the old fields should stay, they now also want a field for gender. Drop and recreate the old table, (or add a new column) with the same people and the new field as well. In addition, there are two new people who have signed up for the dance; add them to the new table as well.
- Alex Wood(Senior) - paid
- John Matta (Junior) - paid
Task 4: Now that you have recreated the table according to the new specs, the administrators have reached the deadline for payments. Everyone has paid in full, so mark them as such. The exception to this is Will Cox; as he is no longer going to the dance, remove him from the promlist.
Select the area below for one possible way to complete task 4:
/* repeat the following for each person */ update promlist set paid = 'y' where paid = 'n'; /* to delete Will Cox */ delete from promlist where lastname = 'Cox' and firstname = 'Will';
Now, for cataloging purposes, the principal wants to be able to see all the male or female students who are attending by last name.
/* code */ select gender, lastname from promlist where gender='f' group by lastname; /* what the output should look like:*/ f|Clark f|Giobbi f|Matta f|Presley f|Wood f|Zito
Task 5: Satisfied that the table is set up properly, the principal now has a more difficult task: the students will be paired off randomly, one boy, one girl. You must figure out a way to do this.
...confused yet? That is because you haven't been taught a way to do this! Unfortunately, SQlite doesn't come with handy abilities like a shuffle or random selection ability, and the only way to duplicate such abilities would be to use a programming language like python in conjunction with sqlite. That requires downloading and good knowledge of Python, so we are going to try a tricky way around it, by using a mechanic known as table joining.
Table Joins
If the administrators had given me personally this task, I would decide that restructuring the table might be painstaking, but allow me to quickly finish the task with one command. Taking pity on you, the promlist.db file you need is linked here, so you do not have to drop and create and then populate your tables for a second time. We were mean enough. To let you figure out what we did, try the sqlite commands .tables and .schema. The results are below:
sqlite> .tables promlist promlistb sqlite> .schema CREATE TABLE promlist (id int, last varchar (20), first varchar (20), grade number(2), paid char(1), gender char(1)); CREATE TABLE promlistb (id int, last varchar (2), first varchar (20), grade number(2), paid char(1), gender char(1));
As you can see from the above transcript, what we have done is create two tables, one (promlist) with all the boys going to the dance, and the other, promlistb, with all the girls. We have also added a new column, id, which contains a single integer value. For each table, each person has a id value, be it 1,2,3,4, or 5. That means that in both tables, we have the exact same structure, but importantly, the exact same contents in the id column. We are going to use that to our advantage by using the select command on both tables. (Side note: this method makes the gender column somewhat redundant, at least for the purpose of sorting; a stripped-down two-table database with the ids and last names would be enough as well, so if you were to do this on your own, consider just creating another single-use database.)
The select statement for two tables is just like select statements we have used before, however we have to explicitly state which table columns we are selecting come from, and to specify more than one table in the from portion of the query. The command we would use to do our quasi-sorting looks like this:
select promlist.last, promlistb.last from promlist, promlistb where promlist.id = promlistb.id;
In the first line, we are selecting both the last name column from promlist and promlistb, as we want to pair up the last names of boys and girls. Technically, you do not have to state the table in front of the column in the format tablename.columnname, however it is very good form to do so. The second line, states that we want the results to come from both promlist and promlist b. Finally, the last line is the kicker. Since every single person in both tables has an id from 1 to 5 which is unique to their table but has a match in the other table, we say that we want the results where the id from table promlist exactly equals the id from table promlistb. And our results?
sqlite> select promlist.last, promlistb.last from promlist, promlistb where promlist.id = promlistb.id; Donahoe|Clark Lee|Presley Ross|Zito Yue|Giobbi Matta|Wood
As you can see, every boy has been grouped with a girl from the other table. So with a little creativity, we pulled off the task.
The full sqlite transcript for this set of lessons (not tasks) can be found here.
Extensions
- Take the online SQL Course, which comes with a built-in SQL interpreter, and complete the online exercises.
- If SQL interests you deeply, get advanced with the SQL Course 2, which covers all of the above and then some more.