Remember to

Introduce the Portal Project database

Database Queries

Using SQLite Manager

Selecting columns

SELECT year, month, day
FROM surveys;
SELECT month, day, year
FROM surveys;
SELECT *
FROM surveys;
SELECT DISTINCT year, month, day
FROM surveys;
SELECT species_id, hindfoot_length/1000.0
FROM surveys;
SELECT species_id, ROUND(hindfoot_length/1000.0, 2)
FROM surveys;

Do Exercise 1 - SELECT. Don’t worry if you don’t know how to save it yet, we’ll cover that in a minute.

Saving queries for future use

CREATE VIEW hindfoots_m AS
SELECT species_id, ROUND(hindfoot_length/1000.0, 2)
FROM surveys;

Save the results of Exercise 1 as a new view.

Filtering

SELECT hindfoot_length
FROM surveys
WHERE species_id = 'DS';
SELECT species_id
FROM surveys
WHERE hindfoot_length >= 30;
SELECT year, month, day, species_id, hindfoot_length
FROM surveys
WHERE species_id = 'DS' AND year > 1990;
SELECT year, month, day, species_id, hindfoot_length
FROM surveys
WHERE species_id = 'DS' AND year > 1990 
  AND hindfoot_length IS NOT NULL;

Do Exercise 2 - WHERE.

Style

seLEcT year, MONTH, dAY, WEIght FrOm SURveyS wheRe hindfoot_LENGTH > 30 aND spECIes_ID = 'DM';

Sorting

SELECT genus, species
FROM species
ORDER BY genus;
SELECT genus, species
FROM species
ORDER BY genus DESC;
SELECT genus, species
FROM species
ORDER BY taxa, genus, species;

Do Exercise 3 - ORDER BY.

Comments

-- Get post-2000 weight data on Kangaroo Rats
SELECT year, month, day, species_id, weight
FROM surveys
WHERE year > 2000 AND species_id IN ('DO', 'DM', 'DS');