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/