SQL, as we have already established, is built on collections of tables. These tables can be linked together by queries to form increasingly complex tables, which allows us to draw conclusions that would otherwise be extraordinarily difficult to obtain.
There are some very powerful (yet weird) things we can do to create more useful queries.
By default, SQL will name columns and/or tables by the requests that have produced them. We haven’t seen evidence of this yet (though we certainly will in a moment), but these can be irritatingly long and useless for us.
However, SQL gives us the power to temporarily rename columns and tables with the alias (AS) command. We can rename any table or column that we want very simply.
For example, we can replace our previous SELECT statement (from the code example) with the following:
SELECT first_name AS "FIRST", last_name AS "LAST", title AS "BOOK TITLE" FROM books AS b, people AS p, checked_out AS co WHERE p.id = co.person_id AND b.book_id = co.book_id AND co.checked_in IS NULL;
In the context of the command, we have renamed the tables with their first letters and we have renamed the columns with all-caps words. If you run the command, you will get output that looks like this:
CASE, COUNT, and SUM
Suppose we wanted to know how many books each user has checked out in total. We would want these results to have their own columns, but how on earth would we do that?
The CASE command allows us to create new columns of data from our tables. For example, if we wanted to print out all the books that are checked out, with the names of those who have them checked out, we could write it like this:
SELECT CASE WHEN checked_out.checked_in IS NULL THEN people.first_name ELSE '' END, CASE WHEN checked_out.checked_in IS NULL THEN books.title ELSE '' END FROM people, books, checked_out WHERE people.id = checked_out.person_id AND books.book_id = checked_out.book_id;
That should produce something like this:
That’s not a very useful result, though. It would make more sense to count how many books are currently checked out, would it not? In that case, we could combine CASE with COUNT to produce something meaningful to us:
SELECT COUNT(CASE WHEN checked_in IS NULL THEN checked_in ELSE 0 END) AS "Checked out" FROM checked_out;
The output will look like this.Suppose we don’t want to count how many books are checked out, but rather calculate how much money we could collect in fines if those books are never returned. For our purposes, we’re going to pretend that “That One Book” costs $15 and “Ender’s Game” costs $10. In this case, we would want to use SUM, which looks like this:
SELECT SUM( CASE WHEN checked_in IS NULL AND title = "Ender's Game" THEN 10 WHEN checked_in IS NULL AND title = "That one book" THEN 15 END ) AS "Fines from Books Currently Out" FROM books, checked_out WHERE books.book_id = checked_out.book_id;
We should get something that looks like this:
We can actually perform the basic mathematic operations (+,-,*,/) in SQL, which opens a lot of doors. If we wanted to find out exactly how many copies of each book were currently on the shelves, we could write something like this:
SELECT books.title, copies - CASE WHEN books.book_id = checked_out.book_id AND checked_out.checked_IN IS NULL THEN 1 ELSE 0 END AS "On Shelf" FROM books, checked_out WHERE books.book_id = checked_out.book_id;
This produces output like this: