EXERCISE: 7 SQL

Let’s work on some SQL commands:
mysql> select version();
+---------------------+
| version() |
+---------------------+
| 5.0.67-community-nt |
+---------------------+
1 row in set (0.02 sec)

Creating a SARS Database

mysql> create database sars;
Query OK, 1 row affected (0.00 sec)
mysql> use sars;
Database changed
Create table sarsproteins with the following specifications
• Entryname
• protein name
• function
• length
mysql>CREATE TABLE SARSAPRO(ENTRYNAME varchar(90),PRONAME varchar(100),FUNCTION text,LENGTH int);
Query OK, 0 rows affected (0.05 sec)

mysql> DESC SARSAPRO;

+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| ENTRYNAME | varchar(90) | YES | | NULL | |
| PRONAME | varchar(100) | YES | | NULL | |
| FUNCTION | text | YES | | NULL | |
| LENGTH | int | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
Insert the vales into the sars database.
mysql> INSERT INTO SARSAPRO (ENTRYNAME , PRONAME , FUNCTION , LENGTH ) VALUES ('Cytochromeb','ubiquinol-cytochromem', 'reductase complex',380);
mysql> INSERT INTO SARSAPRO (ENTRYNAME , PRONAME , FUNCTION , LENGTH ) VALUES('MYSP_SARSC',' Paramyosin',' thick filaments',870);
mysql> INSERT INTO SARSAPRO (ENTRYNAME , PRONAME , FUNCTION , LENGTH ) VALUES('Q5HJ07_STAAC','Hypothetical protein ',' transcriptional regulater ',60);
mysql> INSERT INTO SARSAPRO (ENTRYNAME , PRONAME , FUNCTION , LENGTH ) VALUES('CAP_CVHSA',' Nucleocapsid protein',' structural component of virions ',420);
mysql> INSERT INTO SARSAPRO (ENTRYNAME , PRONAME , FUNCTION , LENGTH ) VALUES('OPSD_SARSP',' Rhodopsin ',' light-absorbing molecule ',340);
mysql> INSERT INTO SARSAPRO (ENTRYNAME , PRONAME , FUNCTION , LENGTH ) VALUES('Q315J6_DESDG ',' Hypothetical protein ','transcriptional regulater',60);
mysql> INSERT INTO SARSAPRO (ENTRYNAME , PRONAME , FUNCTION , LENGTH ) VALUES('R1AB_CVHSA ',' Replicase polyprotein 1ab',' The papain-like proteinase',7070);
mysql> INSERT INTO SARSAPRO (ENTRYNAME , PRONAME , FUNCTION , LENGTH ) VALUES('SARS_STAAN ',' HTH-type transcriptional regulator sarS ',' Transcriptional regulator',250);
mysql> INSERT INTO SARSAPRO (ENTRYNAME , PRONAME , FUNCTION , LENGTH ) VALUES('SARS_STAAR ',' HTH-type transcriptional regulator sarS ',' Transcriptional regulator',250);
mysql> INSERT INTO SARSAPRO (ENTRYNAME , PRONAME , FUNCTION , LENGTH ) VALUES('SARS_STAAU',' HTH-type transcriptional regulator sarS ',' Transcriptional regulator',250);
List all the sars proteins.
mysql>SELECT * FROM SARSAPRO;

ENTRYNAME PRONAME FUNCTION LENGTH
CYB_SARSA Cytochrome b ubiquinol-cytochrome reductase complex 380
MYSP_SARSC Paramyosin thick filaments 870
Q5HJ07_STAAC Hypothetical protein transcriptional regulater 60
CAP_CVHSA Nucleocapsid protein structural component of virions 420
OPSD_SARSP Rhodopsin light-absorbing molecule 340
Q315J6_DESDG Hypothetical protein transcriptional regulater 60
R1AB_CVHSA Replicase polyprotein 1ab The papain-like proteinase 7070
SARS_STAAN HTH-type ranscriptional regulator sarS Transcriptional regulator 250
SARS_STAAR HTH-type ranscriptional regulator sarS Transcriptional regulator 250
SARS_STAAU HTH-type ranscriptional regulator sarS alpha-hemolysin 250
SYSM_BOVIN Seryl-tRNA synthetase catalytic activity 510
SARS_STAAW HTH-type ranscriptional regulator Transcriptional regulator in cell density-dependent 250
SASA_SYNP7 Adaptive-response sensory-kinase sasA signal transduction 380
SPIKE_CVHSA Spike glycoprotein cell membrane 1250
R1AB_CVHSA Replicase polyprotein 1ab The papain-like roteinase 7070

Find the List of proteins and their length informations whose name is "Cytochrome b".
mysql>SELECT * FROM SARSAPRO WHERE PRONAME='cytochrome b';
+----------+-------------+-----------------------+----+-|ENTRYNAME | PRONAME | FUNCTION |LEN |
+----------+-------------+-----------------------+----+
|CYB_SARSA |Cytochrome b | ubiquinol-cytochrome | | | | | reductase complex |380 |
| | | | |
| | | | |
+----------+-------------+-----------------------+----+
Find the number of sars proteins .
mysql> SELECT COUNT(*) FROM SARSAPRO;
+----------+
| count(*) |
+----------+
| 15 |
+----------+
Find the Entryname and functions of sars proteins whose length is 250.
mysql> SELECT ENTRYNAME,FUNCTION FROM SARSAPRO WHERE LENGTH=250;
+------------+-------------------------------------------+
| ENTRYNAME | FUNCTION |
+------------+-------------------------------------------+
| SARS_STAAN | Transcriptional regulator |
| SARS_STAAR | Transcriptional regulator |
| SARS_STAAU | alpha-hemolysin |
| SARS_STAAW | Transcriptional regulator in cell density|
| | dependent |
+------------+--------------------------------------------
Find the minimum and maximum length of the sars proteins.
mysql> SELECT MIN(LENGTH),MAX(LENGTH) FROM SARSAPRO;
+-------------+-------------+
| MIN(LENGTH) | MAX(LENGTH) |
+-------------+-------------+
| 60 | 7070 |
+-------------+-------------+
List the name of the sars protein having length >300 .
mysql>SELECT PRONAME FROM SARSAPRO WHERE LENGTH>300;
+------------+
| PRONAME |
+------------+
| CYB_SARSA |
| MYSP_SARSC |
| CAP_CVHSA |
| OPSD_SARSP |
| R1AB_CVHSA |
| SYSM_BOVIN |
| SASA_SYNP7 |
| SPIKE_CVHSA|
| R1AB_CVHSA |
+------------+

Limit the sars protein information upto 5;
mysql> SELECT ENTRYNAME ,PRONAME, LENGTH FROM SARSAPRO LIMIT 5;

+--------------+----------------------+--------+
| ENTRYNAME | PRONAME | LENGTH |
+--------------+----------------------+--------+
| CYB_SARSA | Cytochrome b | 380 |
| MYSP_SARSC | Paramyosin | 870 |
| Q5HJ07_STAAC | Hypothetical protein | 60 |
| CAP_CVHSA | Nucleocapsid protein | 420 |
| OPSD_SARSP | Rhodopsin | 340 |
+--------------+----------------------+--------+

Find the protein names and function of sars disease whose length is greater than 5000 and less then 8000;


mysql>SELECT PRONAME,FUNCTION FROM SARSAPRO WHERE LENGTH > 5000 AND LENGTH <8000; +---------------------------+---------------------------+ | PRONAME | FUNCTION | +---------------------------+---------------------------+ | Replicase polyprotein 1ab | Replicase polyprotein 1ab | | Replicase polyprotein 1ab | The papain-like roteinase | +---------------------------+---------------------------+ Display the protein name and length in the ascending order Of the sars database and limit the records to 7. mysql> SELECT PRONAME ,LENGTH FROM SARSAPRO ORDER BY LENGTH ASC LIMIT 7;
+-----------------------------------------+--------+
| proname | length |
+-----------------------------------------+--------+
| Hypothetical protein | 60 |
| Hypothetical protein | 60 |
| HTH-type transcriptional regulator sarS | 250 |
| HTH-type transcriptional regulator | 250 |
| HTH-type transcriptional regulator sarS | 250 |
| HTH-type transcriptional regulator sarS | 250 |
| Rhodopsin | 340 |
+-----------------------------------------+--------+
7 rows in set (0.00 sec)
mysql> UPDATE SARSAPRO SET ENTRYNAME='SARSS_STAAUU' WHERE FUNCTION="Transcriptional regulator";
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> DELETE FROM SARSAPRO WHERE FUNCTION="Transcriptional regulator";
Query OK, 0 rows affected (0.00 sec)