Public databases in DbShare can be explored without being logged in.
This tutorial uses a public database at the SciLifeLab
instance of DbShare.
The demo database
contains the following:
- A table of flower measurements for specimens of three species of Iris:
iris_flower_measurements
- A view selecting the measurements of one species from the table:
iris-setosa
- A table of some Dow Jones share index data:
dow_jones_index
A more complicated, real-world data set.
Inspect the iris data
- Go to iris_flower_measurements.
(Use ctrl-click or whatever method your browser has to bring up the table
in a separate window or tab.)
- There are four columns of numerical data for the dimensions of the flower
parts in centimeters. The fifth column shows the species of Iris.
- Inspect the schema for the table; click Schema. This shows the data type
and constraints on the values in each column.
- This dataset was uploaded from a CSV file, so the schema was inferred from
it. The numerical column data is encoded as real (floating point) values,
while the class column contains text values.
- All columns are set as NOT NULL. This means that no rows may contain NULL
values in any column. This is defined when the schema is created
-
Click Statistics. This will compute a number of simple statistics for
the values of each column, such as:
- mean, median and standard deviation for numerical columns,
- whether there are any NULL values in a column,
- the number of unique values in a column,
- list the unique values, if there are less than 8 of them.
Query the iris data
-
Go back to the page for Database demo (in the menu at the top of the
page).
-
Click Query. The page contains input fields for the usual parts of an
ordinary SQL query statement beginning with SELECT
.
-
To the right are panes with information on the columns of each table and view
in the database, to aid formulating a query.
-
Fill in the fields to produce the SQL statement, and click Execute query:
SELECT
sepal_width, sepal_length
FROM
iris_flower_measurements
WHERE
class='Iris-setosa' (NOTE: Sensitive to character case!)
LIMIT
200 (this is default)
-
The result page shows the complete SQL query at the top, with the number
of selected rows, and then the row values below.
-
Click Edit query.
-
Try changing the query in different ways, adding or modifying parts to
the SQL. If the SQL statement becomes invalid, an error message will be
produced.
Explore the view
A view in SQL is a saved query that has been given a name, and
which looks like a table. When it is accessed, it automatically
recomputes the query and produces the result as if it were a table.
On the demo database page,
there is a view iris-setosa defined. Check it out.