Sunday, March 18, 2012

Course Identifiers

Some people have asked that I identify which courses the various scripts apply to. So I will label new posts with the course id: sql202, pos410, etc. if applicable. Existing posts will be updated over time.

Saturday, March 17, 2012


There are times when you would like to combine two fields into one. A common example is names. In your database you probably have the first and last names stored in separate fields as shown below from our bookstore database. But what if you want to show the names as just one full name?

To combine the first name and last name into one field use the concatenation operator. In standard SQL this is the || (two of the pipe symbol at the far right of the keyboard). This works in Oracle. In SQL Server, shown in our example below use the + sign. The concatenation operator will create a new calculated field from the results of the operation. You will probably want to give this calculated field its own name. In the example below I called it customer_mail_name.

select field1 + field2 as newname, other columns
from yourtable;

SQL Script and results
The script below performs the concatenation. Note the results are still sorted by the last name even though the mail name starts with the first name. We also put a space between the names so they don't run together.

Tuesday, March 13, 2012

Free Cloud Database

I came across the link below for a free, cloud-based MySQL database from xeround. I plan to give it a try as soon as I have time. But I thought I would post it here now for others. The free version is only 10 MB but that should be enough for a simple sample database. I'll post my findings.

Friday, March 2, 2012

With Rollup

SQL does not normally put a total line in the same result set as the detail like an accounting spreadsheet. By adding with rollup to the group by clause you will get the total line as shown below. For our examples we will use a tiny fact table from the pets database. You can download the database script here. It is a tiny fact table that summarizes animals available by store. We originally obtained the table from the internet, I forget where. At some point we may add dimension tables to get a true star schema.

The result set returned by a group by with rollup is a valid SQL result set. It can be filtered or manipulated like any other result set. The total line or lines returned are not the total of the groups shown. They are the totals in the table. An example below will make this clearer. A NULL is placed in the column where the summary row has been inserted.

SELECT statement...
GROUP BY groupbylist

Example 1
Show the animals available by store with subtotal for each animal type and a grand total.

Example 2
Same above but retain only rows with number available greater than 10. Note that the grand total row still shows 63 animals available.