Last updated on 2023-10-19 | Edit this page
The definitions below are modified from the Carpentries Glosario (CC-BY-4.0)
- To combine many values into one, e.g., by summing a set of numbers.
- A component of a record containing a single value. Every record in a database table has the same fields.
- To choose a set of records (i.e., rows of a table) based on the values they contain.
- full join
- A join that returns all rows and all columns from two tables A and B. Where the keys of A and B match, values are combined; where they do not, missing values from either table are filled with null, NA, or some other missing value signifier.
- To divide data into subsets according to a set of criteria while leaving records in a single structure.
- inner join
- A join that returns the combination of rows from two tables, A and B, whose keys exist in both tables.
- One of several operations that combine values from two tables.
- A field or combination of fields whose value(s) uniquely identify a record within a table or dataset. Keys are often used to select specific records and in joins.
- left join
- A join that combines data from two tables, A and B, where keys in table A match keys in table B, fields are concatenated. Where a key in table A does not match a key in table B, columns from table B are filled with null, NA, or some other missing value. Keys from table B that do not match keys from table A are excluded for the result.
- missing value
- A special value such as null or NA used to indicate the absence of data. Missing values can signal that data was not collected or that the data did not exist in the first place (e.g., the middle name of someone who does not have one).
- A special value used to represent a missing object.
- A group of related values that are stored together. A record may be represented as a tuple or as a row in a table; in the latter case, every record in the table has the same fields.
- A database that organizes information into tables, each of which has a fixed set of named fields (shown as columns) and a variable number of records (shown as rows).
- right join
- A join that combines data from two tables, A and B. Where keys in table A match keys in table B, fields are concatenated. Where a key in table B does *not* match a key in table A, columns from table A are filled with null, NA, or some other missing value signifier. Keys from table A that do not exist in table B are dropped.
- To choose entire columns or rows from a table by name or location.
- The language used for writing queries for a relational database. The term is an acronym for Structured Query Language.
- A set of records in a relational database or observations in a data frame. Tables are usually displayed as rows (each of which represents one record or observation) and columns (each of which represents a field or variable.)
See this cheat sheet for an list of the commands covered in this lesson.
|Select data from database or table
|Selects the entire dataset
|Selects a particular column
|SELECT 1 + 2
|Performs a calculation
|Indicates the table from which the data is selected or deleted
|Query will display the desired column from the table
|Filter the result set so that only the values satisfying the condition are included
WHERE year == 1990
|Query will display all values from the table for which the year is 1990
|Retrieves the number of records from the table up to the limit value
|Query will will return only the first 5 rows from the table
|Select distinct values
SELECT DISTINCT year
|Query will display the distinct years present on the table
|Used as an alias to rename the column or table
|SELECT 1 + 2 AS calc
|Column will be renamed to “calc”
|Groups the result set
GROUP BY year
|Query will display the max weight per year
|Used to filter grouped rows when using aggregate functions
GROUP BY year HAVING MAX(weight) > 100
|Filter the results by the years that have a maximum weight greater than 100g
ON surveys.species_id = species.species_id
|Query will display all the columns from both tables where the condition is met