SQL

Select all records from Table A and Table B, where the join condition is met. Intersection.

Select all records from Table B, along with records from Table A for which the join condition is met (if at all).

Select all records from Table A and Table B, regardless of whether the join condition is met or not.

A left outer join will give all rows in A, plus any common rows in B

A right outer join will give all rows in B, plus any common rows in A.

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn’t have a corresponding datum in B, then the B portion is null, and vice versa.

AVG, COUNT, MIN, MAX, SUM

To free the collection of relations from undesirable insertion, update and deletion dependencies; To reduce the need for restructuring the collection of relations, as new types of data are introduced, and thus increase the life span of application programs; To make the relational model more informative to users; To make the collection of relations neutral to the query statistics, where these statistics are liable to change as time goes by.

is a subroutine available to applications that access a RDBMS Uses • data-validation (integrated into the database) • access-control mechanisms. • consolidate and centralize logic that was in applications. - execution of several SQL statements One can use nested stored procedures by executing one stored procedure from within another.

SUBSTRING(‘hello’, 3, 1) index 1 based!! one letter is substring(‘hello’, 2, 2)

CASE WHEN blah THEN blah WHEN blah THEN blah ELSE blah END

joining back with subselect inside join to join columns that match and get id