JOIN - Also known as a
SIMPLE JOIN or
INNER JOIN? An SQL INNER JOIN returns all rows from multiple tables where the join condition is met.
Inner JOIN*- See
Self JOIN - When two instances of the same table are joined. The instances must be distinguishable from one another so their attributes are not ambiguous.
Aggregate Functions - The aggregate functions MAX(), MIN(), AVG(), SUM(), COUNT() perform operations over all rows in a group. If these are mixed with non-aggregate columns in a query, then there must be explicit grouping. I think the non-aggregate columns are the ones that need to be in the GROUP BY clause.
GROUP BY - In a GROUP BY statement only distinct values are shown for the column in the GROUP BY.
CASE - CASE allows you to return different values under different conditions.
SELECT name, population ,CASE WHEN population<1000000 THEN 'small' WHEN population<10000000 THEN 'medium' ELSE 'large' END AS size FROM bbc
NULL - NULL or Null (sometimes represented by the symbol ω) is a special marker used in SQL to show that a data value does not exist in the database. Since Null is not a member of any data domain, it is not considered a “value”, but rather a marker (or placeholder) indicating the absence of value. Because of this, comparisons with Null can never result in either True or False, but always in a third logical result, Unknown. The logical result of the expression below, which compares the value 10 to Null, is Unknown:
SELECT 10 = NULL -- Results in Unknown
Primary Key - The unique id that is associated with the record being described. For example, in a table of order records
orders_table, the Primary Key for any entry will be in the
order_id column. The Foreign Key will be in the
customer_id column in that
orders_table. However, in the
customer_id values will be Primary Keys for that table.
N + 1 Query Problem - Each query to the DB has a bit of overhead. It is more efficient to send 1 query to the DB which returns 100 results than 100 queries which return 1 result each. If the DB is on a different machine on the network which has a latency of 1 - 2 ms, this can really add up.
Index - All values of a certain key (or keys) stored in a tree structure in the database. Indexing makes lookup times much faster at the expense of space.
DBMS - DataBase Management System
CRUD - Stands for Create, Read, Update, Destroy are four basic functions of persistent storage as it relates to the databases and how information is passed to websites.
|Create||INSERT||PUT / POST||write|
|Read (Retrieve)||SELECT||GET||read / take|
|Update (Modify)||UPDATE||POST / PUT / PATCH||write|