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.

Concatenation

Scenario
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?

Approach
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.

Syntax
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.

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.

http://xeround.com/lp/free-cloud-database-3i/

With Rollup

Background
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.

Notes
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.

Syntax
SELECT statement...
...
GROUP BY groupbylist
WITH ROLLUP

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.

Using a Union to Add a Grand Total

It is possible to add a grand total line to a query in a couple of ways: using the WITH ROLLUP clause and using a UNION. I will cover the latter here and the former in another post.

Example
Show the total quantity ordered and the total cost for the orders in the bookstore database. Add to this a total line showing the total quantities and costs for the entire orderlines table. In other words present the detail and the summary.

Approach
Take the subtotal as you normally would using a group by with aggregate functions in the select list. Then union the results with another select without a group by clause. This latter select will need an additional column to make it union compatible with the first select. The number of columns has to be the same. You can use a null which is what the rollup does.

Script
use bookstore; -- MySQL and SQL Server
select order_numb
, sum(quantity) as "Quantity"
, sum(cost_line)as "Total Cost"
from orderlines
group by order_numb
union
select NULL, sum(quantity), sum(cost_line)
from orderlines;
Results

The following is part of the result set showing the total line with a null in the first column.

Blog Roll

I added a couple blogs to the blog list on this blog. The SQL half of the Oracle SQL & PL/SQL one seems to be sometimes based on SQL Server but it nevertheless features a handy index to all the SQL commands. The introductory portions on DDL/DCL/DML need work, though. And then I added another blog that features extracts from Joe Celko's writings and posts. His comments can be colorful but are worth reading as he is one of the leading SQL authors out there.

I'll update the list overtime. My current intention is to confine the list to blogs only and just those here on blogger.

Bookstore database

Below is a screenshot from the Microsoft Access relationship designer of the bookstore database. This database is one of the main ones used in our classes. It comes from SQL Clearly Explained by Jan Harrington. You can download the scripts to build this database at SQL Scripts. There are versions for both Oracle and SQL Server.