COMP 364 Lecture 38
April 9, 2008
[Relevant program code is included on web page, also reproduced here.  Also see program runs!]

Today:

- Extra lab hour was held after this class by Perkins
- Pass-in sheet (Perkins will post an online sheet to ensure everything is given in for each student—assignments, quizzess, etc...—A security measure due to the TA strike)
- Grade sheets to be posted

Today's Material:

- More on multi-table queries (several more relevant examples)
- Variables
- Views
- HTML (?)
- Free money shall be given… (not a joke—for additional attendance—cash value prizes at the scale of pennies)

Multi-Table Queries to be explored :

1) More complicated gene queries (more complicated queries (w/ SELECT)
2) Freezer-samples (biologically-related) 
3) Dating service (with lesbians too!!!)

(1) More complicated gene queries:

- Last time, we were doing GENEINFO table with geneIDs and FUNCTIONS.  Also last time, GENEEXPRESSED was introduced.
-  Today, we will work on gene regulation (a THIRD table will be introduced).

à A question:
Find genes regulated by a gene that is expressed?  (We have already mentioned some regulatory genes, also affecting and being affected by others...)
 
Given:
GeneInfo: GeneID, Location, Func,
GeneExpts: ExpID, GeneID, Expr?
GeneReg: Regulator, Regulatee

What are we asked to report at the end?  (Some GeneID's.)  What tables must we use? (GeneExpts—need to know if something is expressed or not; and GeneReg.)

We want to:
SELECT GeneID FROM GeneExpts,
    GeneReg

So we type in as code:
mysql> SELECT * FROM GeneExpts, GeneReg;

We want:
WHERE GeneExpts.Expressed = 'Yes'

So we type:
mysql> SELECT * FROM GeneExpts, GeneReg WHERE GeneExpts.Expressed = 'YES'
AND GeneReg.Regulatee = GeneExpts.GeneID;
....
…
…

mysql> SELECT * FROM GeneExpts, GeneReg WHERE GeneExpts.Expressed = 'Yes' AND GeneReg.Regulator=GeneExpts.GeneID;

mysql> SELECT DISTINCT GeneReg.Regulatee FROM GeneExpts, GeneReg WHERE GeneExpts.Expressed = 'Yes' AND GeneReg.Regulator=GeneExpts.GeneID;

(2) Freezer-Samples Example:

- Perkins shows 3 prepared tables (refer to WebCT)
- But scraps that example (his tables don’t seem to work)

(3) Dating Service:

Perkins types in:
mysql> SELECT * FROM Dating;

[A table appears (refer to WebCT) with headings]:
memberID	Name		Gender		Age

We have only one query, so we need to make a copy of the table:

Type in:
mysql> CREATE TABLE DatingCopy; 
mysql> SELECT * FROM DatingCopy;

If you do: 
mysql> SELECT * FROM Dating, Dating2;
it translates to the following in this example:
mysql> SELECT * FROM Dating, DatingCopy;
Result: Columns are essentially duplicated across.  But we don't want certain columns.

Type in:
mysql> CREATE TABLE DatingF SELECT * FROM Dating WHERE Gender='F';
mysql> SELECT * FROM DaingF;

mysql> SELECT * FROM DatingM, DatingF;

mysql> SELECT DatingM.Name, DatingF.Name FROM DatingM, DatingF;

[Can pair according to other criteria, i.e. age, lesbianism, etc.]

à Question: List all possible female-female pairs:

Type in:
mysql> CREATE TABLE DatingF2 SELECT * FROM DatingF;
mysql> SELECT DatingF.Name, DatingF2.Name FROM DatingF, DatingF2;

mysql> SELECT DatingF.Name, DatingF2.Name FROM DatingF, DatingF2 WHERE DatingF.MemberID<>DatingF2.MemberID;

NEXT TOPIC: Variables:

- Can save results of queries in 2 different ways:
- If want to compute in single number, can store it in single variable.
- Variables: Can be used to store single values (from queries).

à Q: Above average students? Find students?

[Perkins pulls up table]: 
mysql> SELECT * FROM Students2;

He types in:
mysql> /* Above average score on final... */
mysql> SELECT AVG (Final) FROM Students2;
mysql> SELECT * FROM Students2 WHERE Final>84.125;

Therefore, this reads the number 84.125 and puts it in subsequent query.  [This method may be prone to human (typing) error; difficult in getting a script to “re-take” this value.]

However, a better way is to reassign this value to a varible:

Type in:
mysql> /* Assigning average to a variable */
SELECT @FinalAvg:=AVG(Final) FROM Students2;

To sort it, type in:
SELECT @FinalAvg;
SELECT * FROM Students2 WHERE Final>@FinalAvg; ‡ 

‡ [In a SELECT query, the WHERE clause (condition) is tested /applied to each row.  AVG(Final) depends on the values in the whole column.  Compute values first (because they apply to all rows), then clause is applied.]

Note: variables "disappear" at end of session.

NEXT TOPIC: Views:

- "Display" the result of a query (when it's a table):

Type in:
mysql> SELECT * FROM Students2 WHERE Final>80;

[Can essentially save this query as a view]:
mysql> CREATE VIEW GoodStudents2 AS SELECT * FROM Students2 WHERE Final>80;
mysql> SELECT * FROM GoodStudents2;

[Therefore the general command is: CREATE VIEW [viewname] SELECT ... ]

This is similar to creating tables, but also different because we’re not creating a table and storing data in it, but just remembering what query was.  Really "repeats" the table.  In short:
à Does not explicitly create table
à Remembers query

à If change the [...] to Students, then SELECT will be radically affected for a VIEW, but not for a TABLE.  This will update the values in a view and reapply them.
Updates in tables (the SELECT) depends on reflect in view.

Type in:
mysql> UPDATE Students2 SET Final=78 WHERE Name = 'Carla';
mysql> SELECT * FROM GoodStudents2;
[Carla's name and entry disappear.]

- This can be useful under other circumstances.  A better way to store data, is to create a data table with raw scores, and create a view that will also display scores, and whatever changes made with data in TABLE will appear in VIEW.

- Finally, note that views stay between sessions.