Explain Indexing, Mining algorithms, Joins, SQL

indexing - database index can speed up a query by hundreds or thosands of times. This data structure(indexes) are created by using one or more columns in the table, providing a basis for both rapid random lookups and efficient access of ordered records. in a rational db an index is a copy of one part of a table. Code to create an index:

CREATE INDEX table_column1 ON table(column1) - this allows the database to very quickly match records from column1.

SQL - structured query language - a database language designed for managing data in a relational database management system RDBMS

Joins - a SQL clause that combines records from two or more tables in a database. An SQL join creates a set that can be saved as a table or used as is.
different types of joins:
INNER- create a set that has a similar set of column values
ex. SELECT * FROM table a, table b INNER JOIN
WHERE a.column1 = b.column2

OUTER - basically the opposite of an INNER JOIN, it will return all/some the records that do not match of the joined tables. outer hoins subdivide further into different types of joins LEFT, RIGHT, FULL

LEFT OUTER- aka left join - returns all the records on the left table and only the records on the right table that match. as for the sql below - all the records in table 1 and only the records that match for table 2

SELECT * FROM table1 a, table2 b LEFT JOIN ON column1
WHERE a.column1 = b.column2

RIGHT OUTER JOIN - aka RIGHT JOIN - is opposite of left join - return all the records from right table and only the records that match on left table

FULL OUTER JOIN - combines the records of both left and right joins and fill in NULLs for the missing records matches on either side. you need to use caution with FULL OUTER JOINs becuase not all database systems support this but they do support UNIONS so you will have to write a left and right join with a union.

Mining Algorithms:
Apriori - an algorithm for association rules - aka association rule mining, given a set of itemsets (for instance, sets of retail transactions, each listing individual items purchased), the algorithm attempts to find subsets which are common to at least a minimum number C of the itemsets. Apriori uses a "bottom up" approach, where frequent subsets are extended one item at a time (a step known as candidate generation), and groups of candidates are tested against the data. The algorithm terminates when no further successful extensions are found.