Please enable JavaScript.
Coggle requires JavaScript to display documents.
SQL (LEFT JOIN: returns rows from first table matching the second table,…
SQL
LEFT JOIN: returns rows from first table matching the second table, but ignoring rows from second table which doesn't have correspondence with the ON joining key.
RIGHT JOING: it's the mirroring of the LEFT JOIN. Rows from second table are always returned, independently of having a matching correspondent on the first table.
INNER JOIN: Return rows from both tables since they always match. It means that the key used to match exists on both tables.
-
FULL JOIN: returns rows from both tables independently if the key used for comparison has a matching or not.
UNION: concatenates both tables, bringing all rows from both tables without repetition, i.e. identical rows are not duplicated in the result
UNION ALL: concatenate both tables fully, not applying a DISTINCT to the result rows. Duplications might happen
-
-
-
DELETE: performed in a table to delete rows, and it might be conditional. Rollback and Commit might be performed
-
STRUCTURE OF A DB
INDEX:
Performance tuning method
Speed up search queries
Don't have to go to all records before returning
Are stored in B-tree making the search much faster
-
UNIQUE: a column with UNIQUE contraint will have not repeated values. Many UNIQUE constraints are allowed per table. Only one PRIMARY KEY though.
-
NORMALIZATION: reduction of redundancy, trying to define tables per single purposes, limitin its columns per related common relationship. Ex, replacing table Students_Teachers for one table Students and another Teachers
-
-
PROCEDURES: a function that may have inputs and output. It's used to make changes on database structure, format data.
Output is optional
-
TRIGGER: is a mechanism that fires some code execution whenever an event occurs.
Could be a trigger that is fired for every time a ticket is submitted to support tickets table, in a way to send an alert to support noticing that a new ticket is available
CONSTRAINT: mechanism to limit/define data types in a table: UNIQUE / FOREIGN KEY / PRIMARY KEY / NOT NULL / CHECK
DB Relations
-
Many to Many: sales_id X product_id
A sale can contain multiple products, and a product can be sold in multiple sales
A table with sales_product with two columns, sales_id and product_id
A Student x Subject. A student can study many subjects simultaneously, and a subject is studied by multiples students.
One to Many: one Customer can have multiple Sale, but a Sale belongs to only one Customer
It can be defined as a table of Sales and for each sale there is one column named costumer_id