How to run SQL Queries against the database

How to run SQL Queries against the database?

As most of the databases in the world, our database uses a special language named SQL (Structured Query Language) to execute queries. This language allows different operations, but the most common is to retrieve the data. For this purpose SELECT statement is used.

How SELECT works?

Well, it is quite intuitive once you know the basic things. First it is good to know:

The name of the table that you want to query. The structure of the table (fields, data types...) Let's start with an example: We want to retrieve all the data included in the table: all_info. The list of the tables

available is shown in the drop-down menu Table Info:

Select the option "all_info", and the description of the table will appear:

... and scrolling:

This basically means that:

The table "all_info" has 13 fields (columns) with the names:

cluster_id cluster_name cluster_seq cluster_length P_reads S_reads E_reads pep_seq pep_length score start_nuc stop_nuc

unique cluster identifier (ranges from 1 .. 29608) ong name of the cluster (rA_c02_1 - re-assembly number 02 cluster 1) DNA sequence of the cluster length of the sequence (in nucleotides) number of reads in POLYP stage number of reads in STROBILA stage number of reads in EPHYRA stage predicted peptide sequence (or No Prediction tag) length of the peptide (in amino acids) peptide prediction score (given by EstScan) position of the first nucleotide of the ORF position of the last nucleotide of the ORF

The table also indicates the type of the data: numeric (int or smallint) or text (varchar, mediumtext).

Now we are going to construct and run our first query (retrieve all the data from a given table).

Just type the following on the query field:

SELECT * FROM all_info

Which means: Retrieve (SELECT) all the fields (*) FROM table named: all_info.

NOTE: The syntax is case insensitive, so "SELECT" is the same as "select" or "SElecT". The same happens with the column names and symbols.

Press the button

and a table with the results should appear (If not,

check that you have written the query correctly):

IMPORTANT: The results are limited to 1000 rows by default. Depending of the query, the results can be very large and memory consuming. Type "0" in the field for using no limit.

Saving results as CSV

In order to export the result to a file, the button

can be used. This will save

the results and the filters applied to the table in a CSV file that one can download to the

computer and open in Ms Excel, for example.

Hiding Columns

Question: How can I retrieve only data from columns cluster_id, P_reads, S_reads, E_reads and total_reads, for example? Answer: Just change the * for the field names separated by commas (,) except the last. SELECT cluster_id, P_reads, S_reads, E_reads, total_reads FROM all_info

Ordering Data

Question: How do I order the data ascendant or descendant? Answer: There are two possibilities, but with a slightly difference:

Click on the column's header. This will order the values of this column ascendant or descendant, but only affects to the values displayed. Here we sorted all the rows according to the expression level in strobila (see small arrowhead near "S_reads").

 Use the clause ORDER BY in your query and use ASC or DESC to indicate the order. This affects to all the values in the database table.

SELECT cluster_id, P_reads, S_reads, E_reads, total_reads FROM all_info ORDER BY S_reads DESC

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download