'SQL' frills!

Having a look at table structure and constraints

Security

Creating a user

create user username identified by password;

This still doesn't allow the user (even though he/she exists) to talk to SQL. You need to do something like:

grant create session to username;

grant must also be used to provide resources that the user can utilise. Details are at present beyond the scope of this tutorial. {to update}. A user can also grant access to other users, allowing them to share tables that he/she owns. Privileges may be the right to simply read the table or view, or perform potentially damaging operations such as updates, deletes, or inserts. (Other privileges that may be granted on tables include things called alter, index and reference privileges).

Deleting all the information about a user

drop user username cascade;

The cascade option is particularly nasty, deleting all the objects in the (now defunct) schema that belonged to the user.

Database administrators

drop table tablename cascade constraints;

Simple and lethal.

Static constraints

alter table tablename modify (
          colname1 NOT NULL,
          colname2 NULL,
          );

where colname1 will acquire the NOT NULL constraint, and colname2 will lose it!

Another static constraint - the CHECK constraint

SQL can also check a whole host of other constraints, under the general heading of check constraints. These are implemented using the check clause, and example of which is:

create table tablename (
          colname1 char(4)
          check (
                    colname1 between 1 and 1234
                    )
          );

In this rather artificial example, we ensure that the character string colname1 is numeric in the range 2 to 1233 inclusive.

check constraints are quite flexible - they can check ranges, equality, or use the in statement to select from a list of options, but cannot use subqueries or aggregate values in their definition. As with null above, alter table statements can apply new check constraints, and check constraints can be given their own names (something you should always apply rigorously). You can also combine individual checks within a constraint using the logical operators and, or and not.

Something to avoid

alter table disable constraint constraint_name

will turn off constraints. The catch is that if you then abuse the data, and try and turn on the constraint again using alter table enable constraint constraint_name, the command will fail if even one data value violates the constraint!

Dynamic Constraints

insert into tableA ( list of columns )
            select list of columns
            from tableB
            ;

It is conventional not to enclose the select statement in parenthesis! You are permitted to (for example) use a where clause in the select statement.

Aliases, constants and the like

Aliases

select ForeName, Surname, IdNumber from tablename;

Now compare it with the following (which uses aliases:

select ForeName aforename, Surname asurn, IdNumber anid from tablename;

Not only is the above statement difficult to read, not only does it provide us with a host of new names that we have to remember and reconcile, but even worse, if we leave out a single comma, the whole select statement will be hideously mucked up!

Our rule is:

Note that it is also possible to create aliases for table names. For example, where we select from several tables, we had two tables with the same column name. Now, the sensible way of referring to the two columns is (for example) DrugRegimen.Dosing and DrugDosing.Dosing, but one could also, in the select statement create an alias for each table name. Guaranteed to confuse if used often enough.

One place where aliases are actually very useful is where you are making a view, and have a whole lot of bolshy users of that view who insist on certain peculiar names for the columns. Aliases make acceding to their unreasonable requests a piece of cake!

Constants

We believe that programs should be used for what they're best at. Programs that manage databases using SQL should do just that. They shouldn't be used as statistical packages, or word processors. Our fervent hope is that sometime in the next several centuries, designers will catch onto this seemingly obvious concept, and stop burdening us with spacetime guzzling monster programs that try and do a bit of everything. Export your tables to a word processor, and do the formatting job properly, rather than messing around inserting laborious separators and rows of
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - es !

We'll even turn it into a rule:

Okay, having got that off our chests, here's an example of how you use constants:

select col1, col2, 'blahblah' from table

This will provide us with an ugly "constant" column entitled 'BLAHBLAH and containing the value blahblah for each and every row. If you need a single quote, render it as two single quotes side by side - the first quote acts as an escape character for the second one. (But we know this)!

Secondary keys

Okay, we promised to tell you what a secondary key is. A secondary key is a datum that identifies records in a set that share the same property, that is, a subset. This is of some importance in hierarchical databases, but not in relational ones. Forget the term!

Concatenation

select col1 || col2 || 'blahblah' from table

The output from this ugly thing will be rendered as lines without the usual column spacing, and every column juxtaposed to the next one. Okay, you can bung in spaces ' ' between ' ' each ' ' column. Hmm.

Null Values

Nulls pose the following problems:

• If you inner join columns and one of the values is NULL, that row will be ignored. Note that fixes exist, but those that use or or in, cannot be combined with an outer join (which cannot be used with or or in).
• NULLs in aggregate functions are ignored; for example, if you're adding up and averaging say, incomes, the person with a null income will be ignored. Well and good if his income was null; not so hot if some silly person forgot to enter the value! Note that there is a useful property of the count function - if you provide it with a constant (eg 1) as an argument, it counts all the rows in the table, but if you provide it with an expression, it looks for all column names within that expression, and then excludes rows containing NULL values for any one of those columns!
• Conversion functions {??? QV xref} provided with null, return null.
• NULLs mess around with Boolean logic (Well, actually, they extend it to something you don't want to know a lot about, Lukasiewiczian logic)!
• All operators (apart from || concatenation) return NULL if you (anywhere, just anywhere) give them a NULL to play around with!

There is only one rule for NULL values, and that is:

If, however, you through some defect in your psychological makeup cannot do so (avoid the NULLs, that is) or you've inherited a database stuffed with NULLs, then you can do a variety of things:

• Curl into the foetal position and whimper quietly to yourself until they come and cart you away;
• Test for the NULLs using something along the lines of
  where columname is NULL
  (You can also use is not null for the opposite condition);
• Use order by on the offending column to list the null columns
• Use a quick fix:

Quick NULL fixes

select avg ( coalesce (columname, 0)) from tablename;

You should only apply the above approach if you are sure that the substitution is appropriate, which is rarely the case! Note that different SQL dialects have different synonyms for coalesce - for example, Oracle uses nvl.

Even more contrary is to replace a known value with NULL ! SQL92 provides the nullif( columname, targetvalue) function to do so. Some SQL implementations lack this function, if not the functionality.

Data coercion, conversion and truncation, including dates

Data coercion

Explicit conversions

• convert non-string value to string, with or without other fancy frills such as formatting (to_char);
• convert string to number (to_number);
• convert character to ASCII code (ascii);
• convert number to character - the reverse of 'ascii' (chr);
• .. and more (including date conversions, and conversions between 'raw data' and hexadecimal data)!

Decode, Stats, Maths and String manipulation

Decode

select
            decode (
            Frequency,
            'OD', 'once daily',
            'BD', 'twice daily',
            'TDS', 'three times a day',
            'QID', 'four times a day',
            'I am confused'
            )
from DrugDosing;

The intent is obvious - go through the table Frequency and replace occurrences of the various codes (OD, BD, etc) with their English equivalents, defaulting to 'I am confused' if there is no match. A useful little function that shouldn't cause trouble if you remember to put in a default value, and only keep two items to a line when you write the code, as we did above.

Statistics and Maths

We have learned how to perform arithmetic on columns and the basic grouping functions that exist in SQL. Alas, not content with these, most vendors of SQL databases have added their own proprietary extensions, playing around with variances, standard deviations and so on.. Our comments above apply!

You will find functions that do:

• absolute values (abs)
• sign tests (sign)
• rounding (floor, ceil, round, trunc ..)
• modulo (mod, remainder)
• exponents and the like (log, ln, exp, power, sqrt)
• trigonometry (sin, cos, tan) and even hyperbolic functions

String handling

• extraction of sub-strings (substr)
• string trimming (ltrim, rtrim)
• string padding (lpad, rpad)
• character translation (translate) and replacement (replace)
• string concatenation (concat)
• string length determination (length, lengthb), as well as searching for a string within a string (instr, instrb);
• Some SQLs even do 'soundex' lookups (eg. Oracle).

Indexes

More silliness (to examine!)

1. ((Hierarchical views (p536) ))

2. Better definition of relational calculus, relational algebra.

3. ((The force option with views (p517)))

4. ((Oracle snapshots (pty)))

5. [decode p87. DONE]

6. exists / not exists. p422. *****

7. ((Oracle (? SQL equiv) uid, sysdate, user, ))

8. Have a tiny note on the all keyword (redundant)

9. (eschew the oracle currval and nextval operators.)

10. ((Trees, Celko and hierarchical subqueries.))

11. SQL generating SQL. NB. ***

12. ((oracle space and storage directives!? (initial, next, minextents, maxextents, pctincrease)))

13. (Sharing using synonyms.)

14. Triggers, transaction logs. !!

15. Data scrubbing. **