Managing your Database - SQL

Now that you've designed the perfect database, you need to create and manage it. IBM developed a "Structured English Query Language", which was later shortended to "Structured Query Language" or simply, SQL. The first commercial version was released in 1979. The chances are, that if you have created a database, the best way to interrogate it is through SQL. But SQL is much more than this. SQL allows you to completely manage your database! The major reasons to use SQL are:

• SQL is well-standardised (with ANSI and ISO stamps of approval)
• SQL is extensively supported
• You can (with a bit of training and a lot of learning) do almost anything you want with SQL, repeatably and reliably!

Before we begin, note that instructions in SQL are case-independent - SELECT is regarded as being the same command as select. This doesn't imply that the data in SQL are case-independent. It's good practice to treat everything as if it were case-sensitive, as in any grown-up programming language.

Enough said. Let's get down to brass tacks!

Different Standards?

Fairly recently, ANSI/ISO have released SQL-99. This is an extension of SQL-92, has been widely embraced (at least in concept) but the practical impact of these extensions has still to be felt. You can be sure that most commercial SQL applications are inching in the general direction of SQL3 (The other, 'more correct' name for SQL99).

Note that ANSI/ISO expect to make money out of SQL-92 and -99. Don't expect to find legal copies of the full standard anywhere on the Internet, although if you search around enough, you'll certainly find "draft versions" of SQL-92 that are surprisingly like the final product.

Also, don't expect the unabridged text of SQL-92 to be legible to you as a mere mortal. Rather buy a book that explains the arcana to you, unless you're a confirmed masochist! This tutorial should help with the basics, but you really need a good manual if you're serious about SQL.

Cheats

1. We don't believe you for a minute.
2. Have you thought of applying for canonisation?

All automated tools have their deficiencies. We think it's a rather good idea to learn SQL anyway. Empower yourself, and show off a bit!

Goals - Aim High!

(Okay, you can skip past these for now if you really want to)

Rule Zero

The above list is intimidating, but as you work through SQL, come back again and again to view it. I think you'll find that Codd's rules provide a glimmer of sanity, even in the most gloomy meanderings through the SQL maze. You'll find to your surprise (and perhaps delight) that even the august bodies of ANSI and ISO have occasionally tended to ignore Dr Codd, usually at our (not their) peril!

Making tables in SQL

CREATE

to (surprise, surprise) create a table. NOTE the syntax - the (parenthesis) surrounding the list of columns, and especially, the semicolon after the end of the statement. Leave these out, and watch things break horribly. Okay, often all you get is an obscure error message, but watch out!

There's something hidden in the above statement! What is it? Answer: the format of a a list of columns. To learn this we need to digress a bit - we need to find out about data types in SQL, and how we express them. But before we begin, let's clear up a few trivia:

1. In SQL (and elsewhere) we distinguish between fixed and floating point. Fixed point numbers are stored as they are - you are absolutely sure as to what you are dealing with. Floating point numbers are not - for example, 3.0 might just conceivably be stored as 2.99999999999999 ! (Although when you pull it out of the database, chances are that smart software has turned this back into 3.0). The most important practical consideration is that the answer to "Is this floating point number equal to that one?" may be a surprising "No"! On the one hand we're talking about exact numeric literals, (fixed point, to the uninitiated) on the other, approximate numeric literals (floats).

2. We talk about scale and precision. Precision is the maximum number of digits. Scale is the number of digits after the decimal point. For example, the number 123.45 has a scale of 2, and a precision of 5.

3. In SQL we represent the binary number 11001111 as:
           B'11001111'
   and hexadecimal 3A4F as:
           X'3A4F'
   Note the use of uppercase B and X - only these will do!

4. We all know that "bit" stands for "binary digit". Sometimes you'll come across other terms like "hexit" (yep, you guessed it, "hexadecimal digit"), and octet (a grouping of eight bits, formerly referred to as a byte).

5. Remember that a Kilobyte is 1024 bytes, a megabyte is 1 048 576 bytes, and a gigabyte 1 073 741 824 bytes (or octets).

6. In the following table, where we say [etc..], we are smoothing over a whole lot of optional complexity - what we really mean is that you have the option to add:
   [CHARACTER SET <Character set name> ] [COLLATE <Collation name> ]
   which tells us that SQL can be pretty complex, allowing you to specify character sets other than the default one, and also to collate them in a particular fashion. We won't worry too much about this - consult a good reference such as Gulutzan and Pelzer if you're interested!
   {By collate we mean 'apply a set of rules that determine how individual characters are compared'. For example, we know that normally in computerese, 'A' is considered to come before 'a', as the ASCII code for 'A' is a smaller number than the code for 'a'. But the two might be considered equal under a case insensitive collation. Collation is how you look at characters. Each DBMS has a default collation, and then there are zillions of complex rules that govern SQL use of alternative collations. In addition, each DBMS can do its own merry thing with non-standard collations! }

More agonising. We'll allow a maximum of 999 999 999 drugs in our database - can we ever forsee a billion or more different drugs in the database? No, never. In most SQL systems, decimal(9) is a shorthand for saying decimal(9,0), but we'll be pedantic.

Constraints

Later on we'll explore in detail constraints on data values. For now, just note how the unabridged version of not null has a constraint before it. We'll also just use (without explanation) a constraint that ensures the DrugID is indeed unique, thus:

create table Drug (
            DrugID   decimal(9,0),
                constraint drug_ID_null check ( DrugID is not null ),
                constraint not_unique_drug unique(DrugID),
            DrugName   varchar(32),
            TradeName   varchar(32) default 'Generic' ,
            Formulation   varchar(10),
            SizeMg   decimal(7,3) );

The use of constraint and the unique keyword should be pretty intuitive

But wait a bit, we still haven't created a primary key for our table! In fact, a primary key is always unique and not null, and specified as follows (we can dispense with our unique and not null conditions):

create table Drug (
            DrugID   decimal(9,0),
                constraint bad_drug_prim_key primary key (DrugId),
            DrugName   varchar(32),
            TradeName   varchar(32) default 'Generic' ,
            Formulation   varchar(10),
            SizeMg   decimal(7,3) );

Foreign Keys

But wait a bit! It's clear that the "Dosing" column in DrugRegimen corresponds to the "Dosing" column in DrugDosing. How do we tell SQL to make sure that the two correspond (for example, forbid insertion of a Dosing schedule into DrugRegimen that isn't defined in DrugDosing)? We use a foreign key, thus:

create table DrugRegimen (
            Regimen   varchar(6)
                constraint bad1_regimen primary key(Regimen),
            Drug   varchar(32),
            Dosing   varchar(4),
                constraint regimen_refs_dosing foreign key (Dosing) references DrugDosing );

Here again "regimen_refs_dosing" is just a convenient label that should pop up if the constraint is violated; we reference the table DrugDosing, and the (automatic) implication is that the column we are referring to within that table is the primary key, "Dosing"!

Good practice - attaching comments!

-- this is an SQL comment

If you're going to insert comments into your SQL code (and you should) this is the style you might want to use. In some extensions (and SQL-99) you can use /* C-style comments, of which this is an example */, but this is not a component of core SQL.

Things don't stop here. Because SQL proper says that a comment ends at the end of a line. Sounds simple doesn't it? Well actually, no! SQL insists that a parser must define its own "end of line character". A pity that few agree on what that EOL should be - some use a combination of carriage return and line feed (CR+LF), others LF alone.

Some databases also allow you to, for example, attach comments to formed tables, and even columns, but this is not standard SQL.

{for example, Oracle use the syntax:

comment on table tablename is 'This is a comment';

and

comment on column tablename.colname is 'A column comment'; }

DrugDosing
Dosing	DoseMg	Frequency
D1	30	OD
D2	10	OD
D3	200	TDS

We say:

insert into DrugDosing (Dosing, DoseMg, Frequency) values ('D1', 30, 'OD');
insert into DrugDosing (Dosing, DoseMg, Frequency) values ('D2', 10, 'OD');
insert into DrugDosing (Dosing, DoseMg, Frequency) values ('D3', 200, 'TDS');

See how by specifying the names of the columns and then the corresponding values, we make the insertion independent of position. This is GOOD practice. (In addition, you may leave out values, and defaults will be filled in if they exist, although this practice should be avoided if at all possible).

There are a few things you have to be careful of - see how text strings such as 'D1' are surrounded by 'single quotes'. If you fail to observe this simple rule, you are likely to get a long list of errors! Numbers don't need the quotes, and in well-constructed SQL parsers, must not have them! The simple rule is that the data types submitted must be compatible with the data types of the corresponding columns.

{If you're into long-term pain, you can also leave out the column names and just submit values:

insert into DrugDosing values ('D1', 30, 'OD');
insert into DrugDosing values ('D2', 10, 'OD');
insert into DrugDosing values ('D3', 200, 'TDS');

You must also have exactly the same number of items in the values section as there are in the table, and they must be in the correct order! Although convenient, this approach should be avoided - remember our dear friend Dr Codd and his Rule 9? When we initially defined an RDB, we also said that column position is irrelevant - here we violate that simple rule!}

Q1. How do I put a single quote into a string (for example, the word don't ?

'don''t'

Note that we have put in two single quotes, that is, two of these: '
and not a double quote ", which is what this misleadingly looks like!

Q2. Can I "insert" from one table to another (i.e. copy)?

will retrieve all columns and rows from the stated table, something along the lines of:

There's much, much more - we cover queries in detail on the next page of our tutorial.

Deleting tables, and deletion within tables

- this will wipe out all the data contained in the table. Always include a where statement that qualifies what to delete! For example, if we had the table:

DrugDosing
Dosing	DoseMg	Frequency
D1	30	OD
D2	10	OD
D3	200	TDS

.. and for some peculiar reason we wished to delete the row with the key D2, we could say:

Those of us who are used to C, Perl, C++, JavaScript or Java will be a bit startled by the above, because the condition in these languages would be something along the lines of (Dosing == "D2"). SQL is slightly different, but one soon gets used to it! The bugger is when you are writing in several languages, and jumping from one to the other.

Note that there are certain circumstances where such a deletion will simply not work! Can you think of them? Yes, if other tables contain references to our data item "D2", then deleting it would be wicked, because the database would lose referential integrity! ( Much later on we'll see how we can still delete the item in the face of other references to it).

{Even more vicious is the Oracle-specific command truncate table which irreversibly wipes out every row, without any chance of retrieving the data}.

Deleting the whole table!

Note that, as for deletion of a row, if the table is referenced by other tables, the deletion (as we've put it above) will fail.

1. Adding a column

The data already present in the table won't be affected, but clearly each row will now acquire either a NULL where it intersects the new column, or a default value if you specified one.

2. Changing the data type for a column

Generally, for all changes the correct way to make alterations is to create a NEW table with the column characteristics you require, and then copy from the old to the new table. The bottom line is that most SQLs will generally prevent you from doing something silly like truncating data. Note that if you really DO want to truncate column data, you have to use a specific operator to do so. {More of this later - xref!}

{Even in noncompliant applications like Oracle, there are stringent constraints on changing a column's type if it contains anything other than NULLs. But if the column is unpopulated, then you can:

• Change to any valid data type;
• Change size and precision;
• Alter default values.

The varchar and char column types can be changed (one to the other and back) without any fuss!}

3. Deleting a column!

Unfortunately, in several noncompliant SQLs, there is NO way in SQL to delete a column! You have to go through the rigmarole of making a new table, and then copying over all of the rest of the data (without the column you want to drop) into the new table. Take note that even in the standard, there is a long list of conditions under which such a drop will fail. {? more detail, cf G&P p 373}.

4. Renaming a table

{Others haven't been so smart. For example, in Oracle a table can be renamed using the rename statement thus:

Simple, innit? The big question of course is what happens to all the references to the old table name? }.

5. Changing data in a row

DrugDosing
Dosing	DoseMg	Frequency
D1	30	OD
D2	10	OD
D3	200	TDS

.. and wish to alter the value 30 in the DoseMg column to say 40. How do we do this? One way is:

Note how we chose to use as our selection criterion Dosing = 'D1', rather than say DoseMg = 30. Although the example is rather artificial, it emphasises the need to accurately identify the datum we wish to update. If we had a long list of DoseMg's, we might otherwise end up changing all matching (30mg) doses to 40, rather than just the single row we want! Also remember that the condition Dosing = 'D1' has quotes around the value 'D1' - otherwise SQL will look for some variable called D1, and either fail miserably, or do the wrong thing!

Oops!

This does of course beg the question "What pristine state?" The answer is that everything after your last commit statement is rolled back - be prepared to do a lot of typing. We now know the 'opposite' statement to rollback - the one that prevents a rollback. It's of course:

Unfortunately, it doesn't end there (or should we say, begin here)! In some noncompliant databases, there are certain circumstances where an automatic commit occurs!

{Unfortunate, but true. Here they are for Oracle:

• table truncation;
• logging off usually autocommits;

Some crazy software just autocommits by default! }

You can see that there is a vast potential for balls-ups implicit in all this commit/rollback stuff. What happens if you're sharing an altered table with somebody else, and then you rollback? Or do you not share information until committed? Think about it! Even more complex "intermediate" options exist as a part of the SQL standard, for example, the ability to save information at critical junctures (savepoint xyz; and rollback to savepoint xyz;).