Originally published by Robert Beisert at fortcollinsprogram.robert-beisert.com

Weird Things in SQL

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.

Aliases

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:

Alias OutputIt has more value as we discuss…

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:

See the long names in the header? That's why we use aliases
See the long names in the header? That’s why we use aliases

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.COUNT outputSuppose 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:

SUM outputMathy math

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:

SQL Math

 

photo by: