IPT - A Virtual Approach - Views and Aliases

Aliases

provide alternative (more convenient?) names for tables
allow abbreviated names for long table names
provide explicit references to table copies

select song
from albums as A, tracks as T
where A.serNum = T.serNum
and albName = 'Wrong Way Up'

The above query uses a JOIN to re-connect the songs to the album title. The ALIASES used are 'A' and 'T' - note in the where clause that they have been used instead of the full table names. Notice that the where contains explicit references to particular columns in particular copies of tables - this uses a convention similar to records in Pascal.

It should also be noted that the 'renaming' using an alias is only current for the lifetime of the query. After the query has executed, SQL has no knowledge of the alternative names A and T - they are local. The word "as" is strictly not necessary.

There are many types of queries that REQUIRE you to use aliases, and some that are awkward or long-winded to type if you do not. An alias name can be up to 255 characters long - this is a little crazy though, right, as the alias is supposed to be easier to remember/type than the original table name.

When you HAVE to join a table to itself, you have no choice but to use an alias - without it, SQL will confuse what copy of the table you are referring to.

Data Display In Access (an aside)

In most graphical based DBMSs (like Access), data display is achieved in very different ways to those you have learned so far in mSQL.

Although queries and tables are involved, the data is presented through a FORM or a REPORT.

In the screen display above (part of the Access version of the Music Database), you see data from many sources displayed together in the same window - this is called a FORM. The main form has data originating from the Albums table, the 2 panes near the bottom are data from the Tracks and Performers tables, with joins ensuring the data realtes to it's corresponding album.

Forms are on-screen displays like querys in that they allow you to 'design' how you want the data to appear, and surround it with other information as well that does not originate from base tables. It is often possible to update base tables through a query - the same updatability issues exist here.

Reports are designed to be sent to a printer - the report generation engine offers you many features that you would expect in a word processor/mail merge application.

Each RDBMS offers you differing ability to manipulate and display data - SQL is still at the heart of these products.

We will concentrate on System Development in Access a little later in the course

Destroying things

To destroy container objects (tables and databases) along with names for them (views and synonyms), the DROP command is used.

DROP thingtype thingname

DROP DATABASE databaseName

causes all views for the database to vanish, but base tables still exist

DROP TABLE tableName

all traces of the table are removed, including data, views and aliases. The dictionary entry for the table is also deleted

Altering Table Definitions

We can ADD columns to existing tables

example: add a cost column to the albums table

ALTER TABLE albums
ADD cost dec(4)

note the new column name and data type must be specified. Column uniqueness and key attributes can be added later. An ADDED column cannot be specified as MANDATORY (i.e. NOT NULL) unless the table contains NO data (ie. is a NULL table),or you additionally specify a default value for the new column also - otherwise, the moment you tack on an extra column to an existing table, you automatically generate NULLs for each tuple existing

Deleting Columns

This is generally not supported - since deleting of a column represents a fundamental change in the relationship that bound data in the table in the first place. What would be the effect of being allowed to accidentally delete the primary key column?

If you no longer want a column you have three options:

create a view that excludes that column, and only ever deal with the view
unload the data into a text file, drop the table, re-create the table, re-populate the table. (Dangerous and often time prohibitive)
Use the ALTER TABLE as defined in the dictionary to remove the column - warning, this permanently destroys the data contained in the column.

Access lets you alter your tables as you see fit. If a column is involved in a relationship, you will need to decompose the relationship first.

Sub-String Comparisons

Text (char) data can be used in whole or part for searches. It is therefore possible (although relationally unacceptable) to use repeating fields as string data - as there can be searched using the % operator.

select artist
from albums
where alb_name like 'T%'

will find artists whose album starts with a 'T'

note: the work like is a key word, used with the sub- string operator (%)

like 'T%' = all words starting with a 'T'
like '%T' = all words ending with a 'T'
like '%T%' = words containing a 'T'

The '_' (an underscore) operator, in mySQL stands for any single character.
LIKE '_ _ T' will find any 3 letter words that end in the letter 'T'

With a little imagination, it is possible to search for exact length strings:

field LIKE '_ _

will extract strings that are exactly 4 characters (regardless of what they are) long.

To confuse things, ACCESS uses the asterisk '*' for the "any sequence of characters" operator in a LIKE statement, and the question mark "?" to stand for any zero or single character.

Grouping Data

Column agglomeration is a great way to summarise large numbers of rows - the GROUP BY, [HAVING] clauses of the select command allow you to compress rows into groups of similar values.

Consider:

select artist, count(albname)
from albums
group by artist

The net result is that copies of each artist name are compressed into single values - (15 Brian Eno's Become 1, for example) and the count(*) reports how many rows were compressed into that group. The result is a 2 column answer table with tallies of the number of albums for each artist.

Row compression using a GROUP BY is limited only by the number and type of groups you want to form:

select artist, media, count(*)
from albums
group by artist, media

Will, for example compress media within artists and return 3 coloums representing how many of each media I have recorded for each media.

You can constrain the groups by invoking the HAVING clause - it can be thought of as a WHERE CLAUSE for the group - allowing you to specify a group condition

select artist
from albums
group by artist
having count(*) > 2

will list any artist I have more than 2 albums of

select recco, count(*)
from albums
group by recco
having count(*) > 5

Will show record companies and the number of releases from them so long asthe number is more than 5.

Query Exotica

MATCH (col1,col2,...) AGAINST (expr) can be used in MySQL for FULL TEXT SEARCHES, allowing you to specify what columns to search, and will return the answer table in a sorted form- most similar to the search expression to least similar (this is like a search engine) BUT a FULLTEXT index needs to be added to any column you want to include in this command

more to come here....including