On what basics the Queries are made
Answers
Explanation:
Let’s start by using the surveys table. Here we have data on every individual that was captured at the site, including when they were captured, what plot they were captured on, their species ID, sex and weight in grams.
Let’s write an SQL query that selects only the year column from the surveys table. SQL queries can be written in the box located under the “Execute SQL” tab. Click ‘Run SQL’ to execute the query in the box.
SELECT year
FROM surveys;
We have capitalized the words SELECT and FROM because they are SQL keywords. SQL is case insensitive, but it helps for readability, and is good style.
If we want more information, we can just add a new column to the list of fields, right after SELECT:
SELECT year, month, day
FROM surveys;
Or we can select all of the columns in a table using the wildcard *
SELECT *
FROM surveys;
Unique values
If we want only the unique values so that we can quickly see what species have been sampled we use DISTINCT
SELECT DISTINCT species_id
FROM surveys;
If we select more than one column, then the distinct pairs of values are returned
SELECT DISTINCT year, species_id
FROM surveys;
Calculated values
We can also do calculations with the values in a query. For example, if we wanted to look at the mass of each individual on different dates, but we needed it in kg instead of g we would use
SELECT year, month, day, weight/1000.0
FROM surveys;
When we run the query, the expression weight / 1000.0 is evaluated for each row and appended to that row, in a new column. Expressions can use any fields, any arithmetic operators (+, -, *, and /) and a variety of built-in functions. For example, we could round the values to make them easier to read.
SELECT plot_id, species_id, sex, weight, ROUND(weight / 1000.0, 2)
FROM surveys;
Challenge
Write a query that returns the year, month, day, species_id and weight in mg
SOLUTION
SELECT day, month, year, species_id, weight * 1000
FROM surveys;
Filtering
Databases can also filter data – selecting only the data meeting certain criteria. For example, let’s say we only want data for the species Dipodomys merriami, which has a species code of DM. We need to add a WHERE clause to our query:
SELECT *
FROM surveys
WHERE species_id='DM';