CASE expressions

There are times when you want to pick one of several alternatives for a field in your select list. The examples we are going to use are from our bookstore database (click to download at box.com). In our first example we want to look at the order_filled column and depending on its value display a message. To do this we can use the CASE expression. In our second example we will look at the customer table and show the referrer, if any, for each customer. There are two types of CASE expressions: simple or unsearched and searched. Our examples demonstrate both.

Simple Syntax

CASE selector when expr1 then value1 when expr2 then value2 ... when exprN then valueN else value 3

The else clause is optional. The alternative is selected based on the value of the selector.

Searched Syntax

This one has no selector. The alternative expressions are evaluated and if true that choice is used.

CASE when expr1 then value1 when expr2 then value2 ... when exprN then valueN else value 3

Simple Example

Here is an example of this syntax. It expands the order_filled yes/no field.

use bookstore;  select order_numb, order_date,  case order_filled
  when 'Yes' then 'Order status: Filled'  when 'No' then 'Order status: Not filled'  else 'Errorneous order status detected'  end 'Order_Filled'from orders;
Simple Results



Searched CASE example

This example below shows who referred a customer. If the customer was not referred by another customer we print "self". In this case we cannot use a selector since it might evaluate to a null which leads to no defined choice. Instead, this example uses a searched case which separately evaluates each condition. You have to do it this way if the field you might use for a selector can contain nulls.

use bookstore;
select c.customer_first_name
, c.customer_last_name
, case  when c.referred_by is null then 'Self'  else r.customer_last_name
  end as 'Referred By'from customers c left join customers r on c.referred_by = r.customer_numb;
Searched CASE reuslts

Conclusion

The CASE expression can be very helpful when you need to choose between several alternatives. I am sure you will find it useful. Note there is another similar keyword - DECODE - used in Oracle.

 



There is a way to represent the schema of a database in text. Of course a diagram is very helpful in visualing the relationships, but you can also write out the schema if you have to. Suppose we have the following from the Access Relationship Designer. It is a diagram of one of the simple databases we use in class. The purpose of the database is to record hours worked by employees in a factory on various work orders.

We can represent this in text with a few conventions: bold for a required field, italic for foreign key and underline for primary key. List the table name and then the field names in parentheses.This yields the text schema below.

Employees(empno, fname, lname, rate, mgrno)
Work_Orders(wono,descr,std,accum)
Labor(empno, wono, start, end, hours)

Note the composite primary key in the labor table. Each column is also a foreign key.

You might find this technique useful if dashing off a quick Email or you are without your favorite drawing tools.

Left Outer Join with Partial Match
There are some subleties to outer joins involving partial matches. We have looked at joins where we want to show all the rows from the one side of a relationship. Or conversely the rows from the one side that have no matches on the many side. But what if we want to see all the rows from the one side and only a subset from the many side? For eaxmple show all my customers along with their orders for last year. Our first pass at this might be something like the query below.

But this will not work since the filter for 1999 orders effectively masks out the effect of the outer join. We don't want to filter out customers, just orders. The missing values should be replaced by nulls as is normal for outer joins.

Solution
The solution to the above is to prefilter the orders and then join the results of that to the customers using an outer join. We can prefilter the orders in a derived table or inline view using the following syntax.

Syntax

select columns
from table1
left join
(select statement) derived table alias
on table1 key = derived table key

Sample Code
Here is the actual code using the bookstore database (download from Box.net).

Results

Here is an extract of the results of running the above query, Emily Jones and Mary Collins did not place any orders in 1999 but were still listed.

Follow Up
We will expand on this concept in future additions to this post. Hopefully this post will have shown you how to work with outer joins that partially match. It also provided a look at inline views or derived tables which we will cover in more detail in other posts.

Left Outer Join

Introduction
This post shows how to create a left outer join. Outer joins are often used in reporting. It also shows how to modify the outer join to create a "find unmatched" query.

Outer Joins
When you join two tables in SOL the default join type is an inner join. This means that only rows with matching key values from each table are retrieved. For example if I join customers with their orders I will not see customer names with no orders. But it is common in reporting that you want to see all rows from the "one" side of the relationship even if there is no match. In the above example I might want to see all my customer names even if they have not placed an order yet. This is accomplished with an outer join. They come in three flavors: left, right and full.

• Left - all matched rows plus unmatched from the "left" table. Note that left refers to reading order. It means the first table mentioned in the query, the one to the "left".
• Right - all unmatched plus unmatched from the right table. The unmatched from the right table would imply they are orphan records since their foreign key did not refer to an existing parent record.
• Full- not often seen. Adds unmatched from both left and right tables. 

We will present an example of the left outer join, which is the most common, below. This example uses the bookstore database which can be downloaded from box.net.

Left Outer Join Syntax
Here is the basic syntax for a left outer join. We will assume the "one" side is the left table. Note the word outer is optional.

Select columnlist
from table1
left [outer] join table2
on table1.primarykey = table2.foreignkey;

Example
Below is an example using the bookstore database.

Results
Below is a section of the result set. Note that when there is no match the missing values are filled in as nulls by SOL. In the results below the Browns have not placed any orders.

Unmatched (Negative) Left Outer Join
Suppose you want to list all customers who have not placed an order. You can do this by creating a left outer join from the customers table to the orders table. As we have seen this will produce a list of all customers along with their orders. The list will include customers that have not placed any orders since a left join includes all rows from the left table. You can restrict the result set to only those who have not placed orders. Do this by adding a where clause to test for null order numbers. The order number is the primary key in the orders table and wouldn't be in the result set as a null unless it was generated by the left join. This is a common query to test for missing information or missing records.

Syntax
select columnlist
from table1
left [outer] join table2
on table1.primarykey = table2.foreignkey
where table2.primarykey is null;

Example

Results
Below are the results using the bookstore sample database. (In reality you might not want to show the order columns).

More to follow...
There is another type of unmatched join where there is a partial match. For example: list all customers and the titles of books they bought that were written by a certain author. Include all customers even if they did not buy any books from the given author. We will cover this in a subsequent post and in our Intermediate SOL course (SQL302 & 312).

Calculated Fields

It is a simple matter to perform calculations in a query. The result of the expression is placed in a new column in the result set. You can give the calculated field a name, too. These calculated fields are sometines called computed columns. Don't confuse them with aggregate functions which do calculations in a sense but don't create a new column in the result set.

Example
Here is a simple example from the bookstore database. (Remember that the use bookstore statement only applies to SQL Server and MySQL. Oracle and Access don't use it.)

Supplier-Parts Database SQL Script

Below is a DDL script to create Chris Date's supplier-parts database. If Chris Date can explain SQL using this simple database it is worth trying. There is another another version of this database with an additional table that I may post here eventually. A few points of interest in the DDL below:  1) in the Parts table note the use of a unique constraint at the table level to define the uniqueness of the the combination of pname, color and city, 2) in the SupplierParts table there is again a table level constraint as the primary key is the combination of pnum and snum; in the same table definition note that the foreign key references clause does not include a referenced field name because the field names are the same.

CREATE TABLE Suppliers
  (
 SNUM int NOT NULL PRIMARY KEY,
 SNAME varchar(16) NOT NULL UNIQUE,
 STATUS int NOT NULL,
 CITY varchar(20) NOT NULL
  );

CREATE TABLE Parts
  (
 PNUM int NOT NULL PRIMARY KEY,
 PNAME varchar(18) NOT NULL,
 COLOR varchar(10) NOT NULL,
 WEIGHT decimal(4,1) NOT NULL,
 CITY varchar(20) NOT NULL,
 UNIQUE (PNAME, COLOR, CITY)
  );

CREATE TABLE SupplierParts
  (
 SNUM int NOT NULL REFERENCES Suppliers,
 PNUM int NOT NULL REFERENCES Parts,
 QTY int NOT NULL,
 PRIMARY KEY (SNUM, PNUM)
  );

  INSERT INTO Suppliers VALUES (1, 'Smith', 20, 'London');
  INSERT INTO Suppliers VALUES (2, 'Jones', 10, 'Paris');
  INSERT INTO Suppliers VALUES (3, 'Blake', 30, 'Paris');
  INSERT INTO Suppliers VALUES (4, 'Clark', 20, 'London');
  INSERT INTO Suppliers VALUES (5, 'Adams', 30, 'Athens');
  INSERT INTO Parts VALUES (1, 'Nut', 'Red', 12, 'London');
  INSERT INTO Parts VALUES (2, 'Bolt', 'Green', 17, 'Paris');
  INSERT INTO Parts VALUES (3, 'Screw', 'Blue', 17, 'Oslo');
  INSERT INTO Parts VALUES (4, 'Screw', 'Red', 14, 'London');
  INSERT INTO Parts VALUES (5, 'Cam', 'Blue', 12, 'Paris');
  INSERT INTO Parts VALUES (6, 'Cog', 'Red', 19, 'London');
  INSERT INTO SupplierParts VALUES (1, 1, 300);
  INSERT INTO SupplierParts VALUES (1, 2, 200);
  INSERT INTO SupplierParts VALUES (1, 3, 400);
  INSERT INTO SupplierParts VALUES (1, 4, 200);
  INSERT INTO SupplierParts VALUES (1, 5, 100);
  INSERT INTO SupplierParts VALUES (1, 6, 100);
  INSERT INTO SupplierParts VALUES (2, 1, 300);
  INSERT INTO SupplierParts VALUES (2, 2, 400);
  INSERT INTO SupplierParts VALUES (3, 2, 200);
  INSERT INTO SupplierParts VALUES (4, 2, 200);
  INSERT INTO SupplierParts VALUES (4, 4, 300);
  INSERT INTO SupplierParts VALUES (4, 5, 400);

Database scripts

Our scripts can be found at http://tinyurl.com/SQLScripts. This will take you to box.net which has the scripts we have posted to the internet. This will eventually include scripts to create and load most of the databases we uses in our classes or teaching materials.

Xeround

I finally signed up for the Xeround free cloud MySQL database. It was a fairly painless process although it seemed to take a couple of attempts to get the password to take. It uses the standard PhpMyAdmin tool for administering the database. The Xeround account page is shown below. I loaded my standard bookstore script into the PhpMyAdmin SQL window and after a few modifications was able to get the database created and loaded. I will say this is pretty handy. I am sitting in my home office accessing the database which I actually created an hour or two ago at our main office.

Below is a screenshot of the main account page.

With Cube

Background
SQL does not normally put total lines in the same result set as the detail like an accounting spreadsheet. By adding with cube to the group by clause you will get the total lines as shown below. It is similar to with rollup but subtotals each dimension. 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 cube 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 dimension column where a summary row has been inserted.

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

Example 1
Show the animals available by store with subtotals for each store type and a grand total. In addition show the subtotals for each animal type.

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.

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.

Adobe

Just a quick note. We do have a couple of instructors that are very skilled with the Adobe product set. I just wanted to pass this information along as not many people are aware of it. We teach the classes by going to your location. We can teach most of the products such as PhotoShop, Illustrator, InDesign, etc.

Combination Queries

There is a set of queries sometimes referred to as combination queries. There are three set operators used to create them...

• UNION
• INTERSECT
• EXCEPT

We looked at the Union in our basic class. Below we will present the other two. Each is based on the bookstore database which can be downloaded here.

INTERSECT
The intersect operator retrieves all unique rows found in both tables.

Example
The following query will find all cities in common between the sources and customers tables.

Script
use bookstore -- MySQL and SQL Server
select customer_city, customer_state, customer_zip
from customers
intersect
select source_city, source_state, source_zip
from sources;

EXCEPT
The except operator will retrieve all rows found in the first table but not in the second.

Example
Find all cities where the source is located in a city that does not have a customer.

Script
use bookstore -- MySQL and SQL Server
select source_city, source_state, source_zip
from sources
except
select customer_city, customer_state, customer_zip
from customers;

Calculating the Mode of a Dataset

Scenario
SQL script to calculate the mode of a dataset.

Approach
Determine the frequency of occurrence of each value. Sort from largest to smallest. Select the first frequency so long as there is more than one occurrence. Allow for ties in case of bimodal distributions. The script for the parts table can be found in the post for the median.
Script
USE parts
SELECT TOP 1 WITH TIES COUNT(*) occurrences, part_wgt mode
FROM parts
GROUP BY part_wgt
HAVING count(*) > 1
ORDER BY COUNT(*) DESC;

Result

Resources

PowerPoint Presentations of our SQL Classes can be found on SlideShare.

Script files that can be downloaded and run can be found on Box.net. The script files are arranged both by database and by course.

Recirculating Scripts

I often advise students to put the following lines of code at the top of their SQL Server scripts that build and load databases.

use master
drop database yourdatabase;
go
create database yourdatabase;
go
use yourdatabase
--insert your code here to create and load tables
This will first make sure you are in the master database and don't get a database in use error when doing the drop. Then drop the database and recreate it. The GO commands separate the script into batches so if the database doesn't already exist the next batch, the create, will run, even though the drop generated an error. This code works fine but there is a more elegant way.

use master
if db_id('yourdatabase') is not null
drop database yourdatabase;
create database yourdatabase;
use yourdatabase
--insert your code here to create and load the tables
This method tests for the existence of the database first then drops it. Either way is fine. Both make it easy to go in and correct some of the code and then just re-run the script.

---Dan

T-SQL Fundamentals Book Review

I just finished an excellent SQL book...

Ben-Gan, I. (2009). Microsoft SQL Server 2008 T-SQL Fundamentals. Redmond: Microsoft Press.

It is one of the best SQL books I have read. It is written in  a very straightforward style. There are 10 chapters covering all the usual topics from table creation up through triggers and stored procedures. What I especially liked about this book was that it goes into some more advanced topics. It discusses pivot and unpivot, common table expressions, the newer with clause and window functions for example. The author also has some good example exercises for testing transactions. It you are looking for a straightfoward presentation of these more advanced topics, this is your book.

Our intermediate level SQL class will feature some material drawn from this book, although adapted to our sample classroom databases.

SQL Script to calculate the median

Business case
Below is a script that will calculate the median of a dataset. The median is the middle value in an ordered dataset. If the dataset has an even number of values, the median is the average of the innermost two.

Approach
This script works by taking the union of two select top values queries. If there are an odd number of records the same values are returned twice which is fine. The values returned by the union are then averaged. Note the use of derived tables. This example uses the famous parts table from Chris Date. The idea for this script came from a tek-tips SQL forum.

Script
use master
drop database parts;
go
create database parts;
go
use parts
-- create Date's famous parts table
CREATE TABLE Parts (
part_nbr VARCHAR(5) NOT NULL PRIMARY KEY,
part_name VARCHAR(50) NOT NULL,
part_color VARCHAR(50) NOT NULL ,
part_wgt INTEGER NOT NULL ,
city_name VARCHAR(50) NOT NULL);
-- load data (experiment with different numbers of rows, etc.)
INSERT INTO Parts (part_nbr, part_name, part_color, part_wgt, city_name)
VALUES ('p1', 'Nut', 'Red', 12, 'London'),
('p2', 'Bolt', 'Green', 17, 'Paris'),
('p3', 'Cam', 'Blue', 12, 'Paris'),
('p4', 'Screw', 'Red', 14, 'London'),
('p5', 'Cam', 'Blue', 12, 'Paris'),
('p6', 'Cog', 'Red', 19, 'London');
use parts
-- display data
select *from parts
order by part_wgt;

 -- calculate the median
select avg(wgt) as median
from(select max(part_wgt) as wgt
from (select top 50 percent *
from parts
order by part_wgt asc) a
union
select min(part_wgt)
from (select top 50 percent *
from parts
order by part_wgt desc) d) u;

Results

Welcome

Hello All -

This blog will be used as a quick way to post our new SQL Scripts and such. Many of these scripts will eventually be rolled into tutorials and/or courses, perhaps here. Over time, we will also post scripts applicable to existing classes taught at Orange Coast Database Associates or at the University. Most scripts are cross platform. We teach classes in both the Oracle and SQL Server environments, and occaisionally MySQL and Access Jet SQL.

---Dan