SQL Tutorial: Queries and Output

First, a plain dump. This is done with a SELECT statement to select all fields ("*") from the specified table. Each table on this page is created with a live 'psql' query, so data here may be different than the data used to create the tables when the previous page was written.

ericj=> SELECT * FROM "stock_data";

Updates over time have changed the order shown here, so we might want to sort the list by specifying that it should "ORDER BY" a certain column or two. It defaults to ascending (small-to-large), but let's list the amount of shares with descening (large-to-small) in the order of purchase. You don't need the quotes around names unless they include spaces, etc., so I'll drop those from here on.

ericj=> SELECT * FROM stock_data ORDER BY purch_date, shares DESC;

What if we only want the symbol and share price with date? Instead of asking for all fields ("*"), we can name the specific fields we need:

ericj=> SELECT symbol, curr_date, curr_price FROM stock_data
ericj->   ORDER BY symbol;

How about a query with calculations? We can find the total current gain (or loss) by calculating a few numbers:

We also need to add all entries for the same symbol together, with a combination of SUM() mathematical functions and the "GROUP BY" feature, to only list one line for each symbol.

Notice that we need to calculate the "Purchase Total" and "Current Total" twice in this example. While SELECT can do the calculation for the first two, it won't know the name we're giving those calculations when it comes time to calculate the third. We could setup extra tables, but I'll leave that optimization as an exercise for the reader.

ericj=> SELECT symbol, SUM(shares) AS tot_shares,
ericj->   SUM(shares * purch_price) AS purch_total,
ericj->   SUM(shares * curr_price) AS curr_total,
ericj->   SUM((shares * curr_price) - (shares * purch_price))
ericj->     AS tot_gain
ericj-> FROM stock_data GROUP BY symbol ORDER BY tot_gain DESC;

We don't really want to have to type all that in to see that list each time, so we can setup a "VIEW", which is a sort of virtual-table that includes a SELECT statement. If you're used to being to save queries in database utilities like Microsoft Access, etc., this will do that in SQL.

ericj=> CREATE VIEW tutor1_gain AS (
ericj(>   SELECT symbol, SUM(shares) AS tot_shares,
ericj(>     SUM(shares * purch_price) AS purch_total,
ericj(>     SUM(shares * curr_price) AS curr_total,
ericj(>     SUM((shares * curr_price) - (shares * purch_price))
ericj(>       AS tot_gain
ericj(>   FROM stock_data GROUP BY symbol
ericj(> );

VIEWs, like TABLEs, are unsorted, so adding the ORDER BY clause here will only result in an error. However, now we only need to type the following command to see the same result as above.

ericj=> SELECT * FROM tutor1_gain ORDER BY tot_gain DESC;

In this main "stock_data" table, we've already noticed a few duplicate entries. Some stocks had two purchase-date rows with the same current-date and current-price info, for example. There's a way to split that up into multiple tables to help reduce duplicates (no need to store -- and update -- the same information twice each time, after all), and that's called normalizing a database.

SQL Tutorial I

Queries and Output