Queries in SQL

Simple select queries, where, and distinct

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

We have already shown a simple query that 'pulls out' all the data in a table:

You can see that here the star (*) acts as a wild card, selecting all of the columns in the table. It doesn't take a great leap of imagination to work out how to select individual columns:

.. will select the two columns specified, and present the information for all rows but just those two columns. Also see how we've specified the columns in a different order from the one used in the original data table definition. The information that is extracted will be presented in the new column order:

How do we select individual rows? Twice before, we've already come across the where command - when we talked about deletion of rows (remember the catastrophe that results if you leave out where?), and also in our discussion of the update command. Here's another use of where:

See how the condition (Frequency = 'OD') didn't have to involve one of the columns selected.

Distinct rows

.. do we get two rows or three? Let's see:

.. three rows as you expected. But let's say we only want all the distinct rows, with no duplication. Then we say:

.. and we get:

.. and we would be given a table with dose values divided by two. With more complex expressions, our "usual" precedence rules (BODMAS) apply - when there is any possibility of doubt, use parentheses. There is also a unary minus that gives the negative of a value (e.g. select - DoseMg .. would give the values -30, -10 and -200).

Conditions and Logic

First note that when we said

.. where Frequency = 'OD' ;

we used the "=" operator to test for equality (We've previously mentioned why some seasoned (heh) programmers might find this strange). There is a number of other operators you will come across. Here they are:

This would work for Exclamations such as 'Oh quoit', 'it works', and 'dammit'.
(Although by now, the C programmers are again clutching their foreheads, as they have just deduced that, yes, SQL starts counting the position of substrings at 1, and not zero as would any modern computer language).
The important question you have to ask yourself is "Will this work for the Exclamation 'I hate IT!' ?"

Case Sensitivity

• upper (string) converts string to upper case;
• lower (string) is similar, converting to lower case.

{check that these aren't Oracle pty ????????????}

To ensure that we picked up lower and upper case It's, we'd have to say:

.. where instr ( upper(Exclamation), 'IT' ) > 0 ;

or, the equally valid:

.. where instr ( lower(Exclamation), 'it' ) > 0 ;

What about !*&"@# %_special characters_% ?

1. Choose some infrequently used character as an 'escape character';
2. Insert the escape character in the string just before the special character (_ or %) you want to represent literally, rather than using it as a wildcard;
3. Tell SQL that you have just used an escape character, using the escape command.

An example will clear things up. The phrase:

.. where Whatever like '%50^%' escape '^'

should match values of Whatever such as 'A 50% increase', and '50% of them came' but not 'I turn 50 today'. The explanation is that we chose '^' as the escape character, so like '%50^%' translates as "give me anything at all, followed by the characters five and then zero and then a percent sign" and not "give me anything followed by five and then zero and then anything again".

Boolean logic in SQL

A and B

will succeed (evaluate to true) if and only if both A and B are true;

A or B

will succeed if either A or B is true, or indeed if both are true;

not B

will evaluate to true only if B is not true. Not reverses the sense (truth) of an assertion!

And that's really that. The only other thing you need to know is that you can use parenthesis to group together logical statements into "compound statements" such as:

A and ( C or D )

where (C or D) will be evaluated first, and then the result will be anded together with A. A rather silly example is:

A note on having

Selecting between multiple options - the SQL92 case statement

{Need to explore this in detail over here}

Ordering and grouping data

Sorting results

Practically, we say:

and we would hope to obtain..

One can also sort in descending order, using the desc operator:

(The default is asc for an ascending sort). There is something to be strongly disparaged over here. You can actually specify the column number (eg 1) instead of the column name, but there are two good reasons why you shouldn't:

1. It violates Dr Codd's rule number nine;
2. With most computer languages, we count starting from zero. In SQL, the counting is from one - column number 1 is the leftmost column!! A sad relic.

You can even sort on several columns (first sort by the first column mentioned, then sort by the next one, and so on). All you need do is list the columns after the order by statement..

A further note..

With this under our belt, let's explore groups. The basic idea is:

select Frequency, avg(DoseMg), min(DoseMg), max(DoseMg) from DrugDosing
            group by Frequency ;

If we apply this command to our boring old table:

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

Then we'll get something along the lines of:

See how using group by Frequency allows us to break up the table into sub-groups, and then (using our new-found knowledge of group functions) extract summary information about each group!

Take note that if you have an 'single' column (one that doesn't describe a group statistic) in the list you provide to select (Frequency, in the example above) and you don't include this column name in the group by section, then you'll get an error! Our example above is trivial, but the group by function is extremely powerful. You can even specify several columns, to aggregate subsets of subsets..

Having

Think about the case where we need to make a decision based on an aggregate function (performed on a group). You can only make the decision to select certain records based on the result of the aggregate function once it's been done, so where is useless for this purpose. Enter (taraa!) having. Here's an example:

select Frequency, avg(DoseMg), min(DoseMg), max(DoseMg) from DrugDosing
            group by Frequency having count(1) > 1 ;

Although again trivial, this example illustrates how you can use having to eliminate subgroups - if there is only one item in a subgroup, it is now eliminated (presumably because the average, max, and min of a single item is of little interest to us)! Note how we use count(1) - this is convenient and quick, as discussed above.

You might think that a natural extension of the good old select statement is the following:

and you would be perfectly correct, but what does the above statement give us when we actually use it? Here we go..

Wooops! Every single row of the first table has been joined with each and every row of the second table, not just the rows that we think should correspond! (This is called a Cartesian join or cross join, and can rapidly generate enormous tables - as Ladányi points out, if you perform a cross join on three tables, each with a thousand rows, then - voila - you have 1000 * 1000 * 1000 = one billion rows, enough to bring most databases to their knees).

For our purposes, most of the rows in the above cross join are meaningless, but we can easily reduce the rows to only those we are interested in. We simply use a where statement to join the tables on the Dosing column, thus:

select * from DrugRegimen, DrugDosing
              where DrugRegimen.Dosing = DrugDosing.Dosing;

It's so important to always have a where condition with your Cartesian joins, let's make it into a rule:

Also note that the two tables each had a column with the same name - "Dosing". We easily sidestepped this one by simply talking about DrugRegimen.Dosing = DrugDosing.Dosing, rather than, say, Dosing = Dosing, which would have forced an error! Needless to say, you can select individual columns from the cross join, rather than having to say select *.

Inner versus Outer Join

Needless to say, few vendors have stuck to the SQL-92 standard as regards outer joins. For example, Oracle sneaks three tiny characters into the where statement thus:

select * from DrugRegimen, DrugDosing
              where DrugRegimen.Dosing (+) = DrugDosing.Dosing;

The (+) tells SQL to "join in a NULL row if you can't find anything that matches a problem row in DrugDosing.Dosing " - all very convenient, but not standard SQL-92. Also note that the (+) is on the same side of the equals sign as the table that is 'augmented', not the one that's causing the problem. It should be clear why this is called a left outer join, and

select * from DrugRegimen, DrugDosing
              where DrugRegimen.Dosing = DrugDosing.Dosing (+);

.. is a right outer join.

{Here explore the SQL-92 standard, and other ways it's not been implemented}

Many vendor SQLs also do not implement the SQL standard for a full outer join, that lists all rows (whether matched or not) from all tables. The SQL-92 syntax for the from clause is:

There are other ways of achieving a full outer join in most SQLs. Remember that the way to avoid all this is to meticulously enforce constraints on integrity! Also note that an outer join will NOT help you if there are duplicate entries in one of the tables you are using for the join (which can only occur in a 'relationally challenged' database).

How to combine queries using set operators

{Could here have pix}

The set operators in SQL are based on the same principles, except they don't have a complement, and can determine the 'difference' between two sets. Here are the operators which we apply to combine two queries:

• union - all elements in both queries are returned;
• intersect - elements common to both queries are returned;
• except - elements in the first query are returned excluding any that were returned by the second query.

These are powerful ways of manipulating information, but take note: you can only apply them if the results of the two queries (that are going to be combined) have the same format - that is, the same number of columns, and identical column types! (Although many SQLs try to be helpful by, for example, coercing one data type into another, an idea which is superficially helpful and fraught with potential for errors). The general format of such queries is illustrated by:

Different strokes..

An outer join could be used (with modification for NULLs if these little monstrosities are present) to achieve the same result as except.

Similarly, an inner join (with select distinct) can do what intersect does.

Set operators can be combined (as you would expect when playing around with sets) to achieve results that simply cannot be obtained using a single set operator.

Note that there are some restrictions on using order by with set operators - order by may only be used once, no matter how big the compound statement, and the select list must contain the columns being used for the sort.

Pseudoset operators

We use the pseudoset operator union all to combine the outputs of two queries (all that is done is that the results of the second query are appended to the results of the first). Union all does exactly what we required from a FULL OUTER JOIN, which as we've already mentioned, is not implemented in many nominally "SQL-92 compliant" databases!

Subqueries

select * from tablename
        where value
                  >
                  ( insert select statement here);

Note that in the above query, the inner select statement must return just one value (for example, an average). There are other restrictions - the subquery must be in parenthesis, and it must be on the right side of the conditional operator (here, a greater than sign). You can use such subqueries with =, >, <, >=, <= and <>, but not {to the best of my knowledge?} with between .. and.

Multiple select subqueries can be combined (using logical operators) in the same statement, but avoid complex queries if you possibly can!

Subqueries that return multiple values

select * from tablename
        where value
                  in
                  ( insert select statement here);

The assumption is that the nested select statement returns a list. The outer shell of the statement can then use in to get cracking on the list, looking for a match of value within the list! There is a surprisingly long list of operators that resemble in, and can be used in a similar fashion. Here it is:

(It is even possible in some SQL implementations to retrieve a 'table' (multiple columns) using the inner select statement, and then use in to simultaneously compare multiple values with the rows of the table produced by this inner select. You'll probably never need to use something like this. Several other tricks are possible, including the creation of virtual views by using a subquery [See Ladányi p 409 if you're interested]. Views are discussed in the next section.)

Correlated Subqueries

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

Let's say we wanted (for some obscure reason) all doses that are greater than average dose, for each dosing frequency. [Meaningless, but it serves the purposes of illustration].

select DoseMg, Frequency
        from DrugDosing fred
        where DoseMg >
                    (select avg(DoseMg)
                                    from DrugDosing
                                    where Frequency = fred.Frequency) ;

The sense of this statement should be clear - we use the outer select to choose a row (into which we put DoseMg, and Frequency). We then check whether this row is a candidate (or not) using the where statement. What does the where statement check? Well, it makes sure that DoseMg is greater than a magic number. The magic number is the average dose for a particular Frequency, the frequency associated with the current row. The only real trickery is how we use the label fred to refer to the current line from within the inner select statement. This label fred is called an alias. We'll learn a lot more about aliases later (Some will argue that aliases are so important you should have encountered them long before, but we disagree).

Correlated subqueries are not the only way of doing the above. Using a temporary view is often more efficient, but it's worthwhile knowing both techniques. We discuss views next.

How do we make a view?

create view nameofview as
            select here have details of select statement

A variant that you will probably use rather often is:

create or replace view nameofview as
            select here have details of select statement

(Otherwise you have to explicitly destroy a view - SQL won't simply overwrite a view without the or replace instruction, but will instead give you an irritating error).

Remember that if you alter the view, you alter the underlying table at the same time!

You cannot use an order by statement (or something else called a for update clause) within a view. There is a whole lot of other convenient things you can do to views. Where you include summary statistics (eg count, sum, etc) in a view it is termed an aggregate view. Likewise, using distinct, you can have a view on the possible values of a column or grouping of columns.

You can even create a view that is derived from several tables (A multi-table view). This is extremely sneaky, as you can largely avoid complex join statements in code which pulls data out of several tables! Ladányi puts things rather well:

"Pre-joined and tested views reduce errors .. that subtly or obviously undermine the accuracy of reports, and thus the credibility and subsequent professional well-being of the people creating them".

How do we limit access to a view?

It is also obvious how we limit access to certain rows - we use a where clause that only includes the rows we want in the view. Note that (depending on your selection criterion) it is possible to insert a row into a view (and thus the underlying database) and then not be able to see this row in the view! With (in)appropriate selection criteria for the view, one can also alter the properties of rows visible in the view so that they now become hidden!

More draconic constraints are possible. The option

with read only

.. prevents any modifications to the view (or the underlying database); while with check option prevents you from creating rows in the view that cannot be selected (seen) in the view itself. If you use the "with check option", then you should follow this with a name, otherwise SQL will create an arbitrary and quite meaningless name for the constraint that will only confuse you when an error occurs!

How do we remove a view?

More things we can do with views

{Does SQL92/98 support the "comment on" facility for views??}

An under-utilised but rather attractive use of views is to make them based on set (or pseudo-set) operators, for example the union of two sets.

{ Note: have something on exists and not exists; also need to talk about recursive joins and tree queries (a la SQL92 and also eg Oracle) }