今天晚上的杰作……

login as: c2003032462
c2003032462@166.111.114.24's password: ********
Last login: Wed Sep 21 21:32:32 2005 from 166.111.6.102
[c2003032462@class-bioinfor c2003032462]$ more 2003032462
Logging to file '2003032462'
mysql> use db2003032462;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table Gene(
    -> Gene_ID int(255),
    -> Gene_Name varchar(30),
    -> Chromosome_Name varchar(10),
    -> Start_Position int(255),
    -> End_Position int(255),
    -> Orientation varchar(10)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into Gene values(
    -> 13456, 'gene1', 'X', 34765, 33176, 'Reverse');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Gene values(
    -> 34245, 'gene5', '21', 2356753, 2376453, 'Forward');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Gene values(
    -> 65431, 'gene78', 'X', 6688732, 6689976, 'Forward');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Gene values(
    -> 78741, 'gene89', 'Y', 456789,498076, 'Forward');
Query OK, 1 row affected (0.00 sec)

mysql> select * from Gene;
+---------+-----------+-----------------+----------------+--------------+-------
------+
| Gene_ID | Gene_Name | Chromosome_Name | Start_Position | End_Position | Orient
ation |
+---------+-----------+-----------------+----------------+--------------+-------
------+
|   13456 | gene1     | X               |          34765 |        33176 | Revers
e     |
|   34245 | gene5     | 21              |        2356753 |      2376453 | Forwar
d     |
|   65431 | gene78    | X               |        6688732 |      6689976 | Forwar
d     |
|   78741 | gene89    | Y               |         456789 |       498076 | Forwar
d     |
+---------+-----------+-----------------+----------------+--------------+-------
------+
4 rows in set (0.00 sec)

mysql> create table Transcript(
    -> Transcript_ID int(255),
    -> Gene_ID int(255),
    -> Transcript_Name varchar(30),
    -> Transcript_Seq varchar(30)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into Transcript values(
    -> 543781, 13456, 'tr198', 'ATGCCTGTACCGGCA');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Transcript values(
    -> 567890, 13456, 'tr654', 'CCGTCATGCTAGCTA');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Transcript values(
    -> 654389, 34245, 'tr890', 'CCGTAGCTAGCTAGAC');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Transcript values(
    -> 765421, 65431, 'tr899', 'CGCTAGCTAGCTACGA');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Transcript values(
    -> 987651, 78741, 'tr964', 'CGTGCTAGAGCTAGCA');
Query OK, 1 row affected (0.00 sec)

mysql> select * from Transcript;
+---------------+---------+-----------------+------------------+
| Transcript_ID | Gene_ID | Transcript_Name | Transcript_Seq   |
+---------------+---------+-----------------+------------------+
|        543781 |   13456 | tr198           | ATGCCTGTACCGGCA  |
|        567890 |   13456 | tr654           | CCGTCATGCTAGCTA  |
|        654389 |   34245 | tr890           | CCGTAGCTAGCTAGAC |
|        765421 |   65431 | tr899           | CGCTAGCTAGCTACGA |
|        987651 |   78741 | tr964           | CGTGCTAGAGCTAGCA |
+---------------+---------+-----------------+------------------+
5 rows in set (0.00 sec)

mysql> select Gene_ID, Chromosome_Name, Start_Position, End_Position from Gene;
+---------+-----------------+----------------+--------------+
| Gene_ID | Chromosome_Name | Start_Position | End_Position |
+---------+-----------------+----------------+--------------+
|   13456 | X               |          34765 |        33176 |
|   34245 | 21              |        2356753 |      2376453 |
|   65431 | X               |        6688732 |      6689976 |
|   78741 | Y               |         456789 |       498076 |
+---------+-----------------+----------------+--------------+
4 rows in set (0.00 sec)

mysql> create table temp
    -> select Gene_ID, count(*) as count from Transcript group by Gene_ID;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from temp;
+---------+-------+
| Gene_ID | count |
+---------+-------+
|   13456 |     2 |
|   34245 |     1 |
|   65431 |     1 |
|   78741 |     1 |
+---------+-------+
4 rows in set (0.00 sec)

mysql> select Gene_Name, count from temp, Gene
    -> where Gene.Gene_ID = temp.Gene_ID having count = max(count);
+-----------+-------+
| Gene_Name | count |
+-----------+-------+
| gene1     |     2 |
+-----------+-------+
1 row in set (0.00 sec)

mysql> select Gene_Name from Gene
    -> where Start_Position-End_Position > 10000 OR Start_Position-End_Position
< -10000;
+-----------+
| Gene_Name |
+-----------+
| gene5     |
| gene89    |
+-----------+
2 rows in set (0.00 sec)

mysql> select Chromosome_Name, Transcript_Name, Transcript_Seq from Transcript,
Gene
    -> where Transcript.Gene_ID = Gene.Gene_ID and (Gene.Chromosome_Name = 'X' o
r Gene.Chromosome_Name = 'Y');
+-----------------+-----------------+------------------+
| Chromosome_Name | Transcript_Name | Transcript_Seq   |
+-----------------+-----------------+------------------+
| X               | tr198           | ATGCCTGTACCGGCA  |
| X               | tr654           | CCGTCATGCTAGCTA  |
| X               | tr899           | CGCTAGCTAGCTACGA |
| Y               | tr964           | CGTGCTAGAGCTAGCA |
+-----------------+-----------------+------------------+
4 rows in set (0.00 sec)

mysql> select distinct Chromosome_Name, Gene_Name, count(*) from Gene group by C
hromosome_Name;
+-----------------+-----------+----------+
| Chromosome_Name | Gene_Name | count(*) |
+-----------------+-----------+----------+
| 21              | gene5     |        1 |
| X               | gene1     |        2 |
| Y               | gene89    |        1 |
+-----------------+-----------+----------+
3 rows in set (0.00 sec)

mysql> quit

[c2003032462@class-bioinfor c2003032462]$