SQL
interval data type
Holds a value representing a unit of time expressed in the format quantity unit. It doesn't record the start or end of a time period, only its length, for example 12 days or 8 hours.
CROSS JOIN
Lines up each row in the left table with each row in the right table to present all possible combinations of rows.
Is there a performance advantage gained by using char, varchar, or text?
No, but there is a storage advantage.
char(n ) data type
a fixed-length column where the character length is specified by n. If you insert fewer than n characters, the server will pad the rest of the columns with spaces. It's a good data type to use if you want to signal how many characters the data should contain.
composite primary key
a natural key composed of a combination of columns
wildcard
a stand-in for a value; it doesn't represent anything in particular and instead represents everything that value could possibly be.
text data type
a variable-length column of unlimited length.
what are the three guidelines to consider when dealing with number data types?
1) Use integers when possible. Unless your data uses decimals, stick with integer types. 2) If you're working with decimal data and need calculations to be exact, choose numeric or its equivalent, decimial. Float types will save space, but the inexactness of floating-point math won't pass muster in many applications. Use them only when exactness is not important. 3) Choose a big enough number type. Unless your designing a database to hold millions of rows, err on the side of bigger. When using numeric or decimal, set the precision large enough to accommodate the number of digits on both sides of the decimal point.
delimiter
A character that separates data entries from one another.
What is a database server?
A collection of objects that includes tables, functions, and user roles.
What is a data dictionary?
A document that lists each column; specifies whether it's a number, character, or other type, and explains that column.
one-to-many relationship
A key value in the first table will have multiple matching values in the second table's joined column.
primary key
A primary key is a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.
What is a database management system?
A software package that allows you to define, manage and query databases (PostgreSQL, MySQL are examples)
varchar(n) data type
A variable-length column where the maximum length is specified by n.
What is the keyword to transform values from one type to another?
CAST
delimited text file
Contains rows of data, and each row represents one row in a table. In each row, the column separates, or delimits, each data column.
How many different data types can a column in a table hold?
Each column in a table can hold one and only one data type.
one-to-one relationship
In a JOIN, there is only one match for each key value in each table.
What is a UNIQUE constraint?
It guarantees that values in a common, or a combination of values in more than one column, are unique.
FULL OUTER JOIN
Returns every row from both tables and matches rows; then joins the rows where values in the joined columns match. If there's no match for a value in either the left or right table, the query result contains an empty row from the other table.
how would you return quartiles?
SELECT percentile_cont(array[0.25, 0.5, 0.75]) WITHIN GROUP(ORDER BY col) AS "quartiles"
What is SQL?
SQL stands for Structured Query Language. SQL lets you define and query databases.
What are the constraints enforced for a valid primary key?
The column or collection of columns must have a unique value for each row AND the column or collection of columns can't have missing values.
What are the constraints of a foreign key?
The foreign key constraint requires a value entered in a column to already exist in the primary key of the table it references.
what is the difference between real and double precision floating-point types?
The real type allows precision to six decimal digits, and double precision to 15 decimal points of precision. They are both variable-precision types, meaning it's in scientific notation. real stores 6 decimal digits precision, and double precision stores 15 decimal digits precision.
B-Tree Index
balanced tree index, basically structures the data into a balanced tree. It looks from the top of the tree down through branches until it locates the data you want.
CHECK constraint
evaluates whether data added to a column meets the expected criteria, which we specify with a logical test.
Anything with a floating-point number returns a
floating-point number of type double-precision
two integers return a
integer
many-to-many
multiple rows in the first table will have multiple matching rows in the second table.
A numeric on either side of the operator returns a
numeric
exponentiation, root, and factorial functions always return
numeric and floating-type
Your company delivers fruit and vegetables to local grocery stores, and you need to track the mileage driven by each driver each day to a tenth of a mile. Assuming no driver would ever travel more than 999 miles in a day, what would be an appropriate data type for the mileage column in your table. Why?
numeric(4,1) provides four digits total (the precision) and one digit after the decimal (the scale). That would allow you to store a value as large as 999.9.
how do you calculate the median, rounded if even?
percentile_cont(.5)
how do you calculate the median, discrete if even?
percentile_disc(.5), which returns the last value in the first 50 percent of numbers.
what is the precision and scale of fixed-point numbers?
precision is the total allowable digits, and scale is the allowable digits to the right of the decimal point.
timestamp data type
records date and time.
date data type
records the date
time data type
records the time
auto-incrementing data type
when you add a new row, the column auto-increments by 1. smallserial, serial and bigserial are all auto-incrementing data types.
What is a LEFT or RIGHT JOIN? When would you use a left or right join?
returns every row from the left right) table plus rows that match values in the joined column from the right (left) table. When a left table row doesn't have a match in the right (left) table, the result shows no values from the right (left) table. You would you use a right or left join if (1) you want your query results to contain all the rows from one of the tables. (2) you want to look for missing values in one of the tables; for example, when you're comparing data about an entity representing two different time periods. (3) when you know some rows in a joined table won't have matching values.
JOIN
returns rows from both tables where matching values are found in the joined columns of both tables. Alternate syntax is INNER JOIN
header row
the first row of the file that lists the name of each column.
CAST
transforms one data type to another
fixed-point and floating-point
two formats of fractions of whole numbers
In the table listing each driver in your company, what are appropriate data types for the drivers' first and last names? Why is it a good idea to separate first and last names into two columns rather than having one larger name column?
varchar(50). Separating by first and last name will allow you to order by each separately.
natural v. surrogate keys
when defining a primary key, you can use either a natural or surrogate key. You implement a natural key by using one or more of the table's existing columns rather than creating a column and filling it with artificial values to act as keys. You implement a surrogate key by populating a new column with artificial values, such as sequential number auto-generated by the database.
integer data type
whole numbers, both positive and negative, smallint, integer, and bigint are the three different types.