What is a Database Management System

A database management system (DBMS) is a collection of interrelated data and a set of programs to access those data. The collection of data, usually referred to as database, contains information relevant to an enterprise.

Database system versus File System

Keeping organizational information in file-processing system has a number of major disadvantages:

Data Redundancy and inconsistency:

Since different programmers create the files and application programs over a long period, the various files are likely to have different formats and the programs may be written in several programming languages. Moreover, the same information may be duplicated several places (files).For example, the address and telephone no. of a particular customer may appear in a file that consists of saving-account records and in a file that consists of checking-account records. This redundancy leads to higher storage and access cost.

In addition, it may lead to inconsistency; that is, the various copies of the same data may no longer agree. For example, a changed customer address may be reflected in saving-account records but not elsewhere in the system.

Difficulty in accessing data:

Suppose that one of the bank officers needs to find out the names of all customers who live within particular postal-code area. The officer asks the data-processing department to generate such a list. Because the designers of the original system did not anticipate this request, there is no application program on hand to meet it. There is, however, an application program to generate the list of all customers. The bank officer has now two choices: either obtain the list of all customers and extract the needed information manually or ask a system programmer to write the necessary application program.

The point here is that conventional file-processing environments do not allow needed data to be retrieved in a convenient and efficient manner.

Data isolation:

Because data are scattered in various files, and files may be in different formats, writing new application programs to retrieve the appropriate data is difficult.

Integrity problems:

The data values stored in the database must satisfy certain type of consistency constraints. For example, the balance of a bank account may never fall below a prescribed amount. Developers enforce these constraints in the system by adding appropriate code in the various application programs. However, when new constraints added, it is difficult to change the programs to enforce them.

Atomicity problems:

A computer system, like any other mechanical or electrical device, is subjected to failure. In many applications, it is crucial that, if a failure occurs, the data be restored to the consistent state that existed prior to the failure. Consider a program to transfer $50 from account A to account B. If a system failure occurs during the execution of the program it is possible that the $50 was removed from the account A but was not credited to account B, resulting in an inconsistent database state. The funds transfer must be atomic- it must happen in its entirely or not all. It is difficult to ensure atomicity in a conventional file-processing system.

Concurrent-access anomalies:

For the sake of overall performance of the system and faster response, many systems allow multiple users to update the data simultaneously. In such environment, interaction of concurrent updates may result in inconsistent data. Consider bank account A, containing $500. If two customers withdraw funds (say $50 and $100 respectively) from account A at the about same time, the result of the concurrent executions may leave incorrect state. To guard against this possibility, the system must maintain some form of supervision. But supervision is difficult to provide because data may be accessed by many different application programs that have not been coordinated previously.

Security Problems:

Not every user of the database system should be able to access all the data. For example, in a banking system, payroll personnel need to see only that part of the database that has information about the various bank employees. They do not need access to information about customer accounts. But since application programs are added to the system in an ad hoc manner, enforcing such security constraints is difficult.

SQL

The history of SQL begins in an IBM laboratory in San Jose, California, where SQL was developed in the late 1970s.
SQL stands for Structured Query Language, and the language itself is often referred to as "sequel."

It is a standard and programming language.
SQL lets you access and manipulate databases.
It was originally developed for IBM's DB2 product.
American National Standard Institute is primary organization for fostering of technology standard. Long established computer standard includes the American code for information interchange (ANSI).
SQL is a nonprocedural language, in contrast to the procedural or third generation languages (3GLs) such as COBOL and C that had been created up to that time.
Nonprocedural means what rather than how. For example, SQL describes what data to retrieve, delete, or insert, rather than how to perform the operation.
Some common RDBMS that used SQL are ORACLE, CYBASE, and Microsoft SQL SERVER.

What Can SQL do?

SQL can execute queries against a database.
SQL can retrieve data from a database.
SQL can insert records in a database.
SQL can update records in a database.
SQL can delete records from a database.
SQL can create new databases.
SQL can create new tables in a database.
SQL can create stored procedures in a database.

SQL can create views in a database.
SQL can set permissions on tables, procedures, and views.

SQL Statements

SELECT

Data retrieval

INSERT

UPDATE

DELETE

MERGE

Data manipulating language(DML)

CREATE

ALTER

DROP

RENAME

TRUNCATE

Data definition language(DDL)

COMMIT

ROLLBACK

SAVEPOINT

Transaction control

GRANT

REVOKE

Data control language(DCL)

Statement	Description
SELECT	Retrieves data from database
INSERT UPDATE DELETE MERGE	Enter new rows, changes existing rows and removes unwanted rows from tables in the database, respectively.
CREATE ALTER DROP RENAME TRUNCATE	Set up changes and removes data structures from the tables.
COMMIT ROLLBACK SAVEPOINT	Manages the changes made by DML statements. Changes to the data can be grouped together into logical transactions.
GRANT REVOKE	Gives or remove access rights to both the Oracle database and the structure with in it.

SELECT Statement

Syntax:

SELECT [ALL|DISTINCT] columns/expr [AS new name]

FROM table_name | view_name

[WHERE conditions]

[GROUP BY fields]

[HAVING conditions]

[ORDER BY column/integer [ASC|DESC]]

SELECT	Specifies the columns or fields to be included in the result set.
DISTINCT	Use to avoid duplicates.
FROM	Identifies the tables or views from which the data will be retrieved.
WHERE	The predicate(s) that determine which rows will be retrieved.
GROUP BY	Use to group output by the field with duplicate values and apply group functions to the grouped data.
HAVING	Use to place a condition on results of group function calculations.
ORDER BY	Determines the sequence of the rows (Default order is ascending).

Example:

SELECT ename, age, add as “Address”, sal “Salary”

FROM emp

WHERE sal>10000

ORDER BY sal DESC;

INSERT Statement

The INSERT statement enables you to enter data into the database.
It can be broken down into two statements:

INSERT...VALUES and INSERT...SELECT

INSERT...VALUES Statement:

The INSERT...VALUES statement enters data into a table one record at a time.
It is useful for small operations that deal with just a few records.

Syntax:

INSERT INTO table_name

(col1, col2...)

VALUES (value1, value2...)

The basic format of the INSERT...VALUES statement adds a record to a table using the columns you give it and the corresponding values you instruct it to add.

You must follow three rules when inserting data into a table with the INSERT...VALUES statement:

The values used must be the same data type as the fields they are being added to.
The data's size must be within the column's size. For instance, you cannot add an 80-character string to a 40-character column.
The data's location in the VALUES list must correspond to the location in the column list of the column it is being added to. (That is, the first value must be entered into the first column, the second value into the second column, and so on).

Examples:

INSERT INTO COLLECTION (ITEM, WORTH, REMARKS) VALUES('SUPERMANS CAPE', 250.00, 'TUGGED ON IT');

INSERT INTO COLLECTION VALUES('STRING',1000.00,'SOME DAY IT WILL BE VALUABLE');

INSERT INTO COLLECTION VALUES ('SPORES MILDEW FUNGUS', 50.00, ' ');

INSERT...SELECT Statement:

It enables the programmer to copy information from a table or group of tables into another table.
The INSERT...SELECT statement can take the output of a SELECT statement and insert these values into a temporary table.

Syntax:

INSERT INTO table_name

(col1, col2...)

SELECT col1, col2...

FROM tablename

WHERE search_condition

The INSERT...SELECT statement requires you to follow several new rules:

The SELECT statement cannot select rows from the table that is being inserted into.
The number of columns in the INSERT INTO statement must equal the number of columns returned from the SELECT statement.

The data types of the columns in the INSERT INTO statement must be the same as the data types of the columns returned from the SELECT statement.

Example:

INSERT INTO INVENTORY (ITEM, COST, REMARKS)

SELECT ITEM, WORTH, REMARKS

FROM COLLECTION;

UPDATE Statement

The purpose of the UPDATE statement is to change the values of existing records.
Syntax:

UPDATE table_name

SET columnname1 = value1

[, columname2 = value2]...

WHERE search_condition

Example:

Update collection

Set worth = 900, item = ball

Where item = 'STRING';

DELETE Statement

Syntax:

DELETE FROM table_name

WHERE condition;

Here are several points to remember when using the DELETE statement:

The DELETE statement cannot delete an individual field's values (use UPDATE instead). The DELETE statement deletes entire records from a single table.
Like INSERT and UPDATE, deleting records from one table can cause referential integrity problems within other tables. Keep this potential problem area in mind when modifying data within a database.
Using the DELETE statement deletes only records, not the table itself. Use the DROP TABLE statement to remove an entire table.

Example:

DELETE FROM COLLECTION

WHERE WORTH < 275;

Table

It is a basic unit of storage composed of rows & columns.
We don’t need to specify size of the table at the time of creation.
Table names & column names must begin with an alphabet.
If a table does not belong to a user, the owner name must be prefix to the table.

Creating a Table

Syntax:

CREATE TABLE [Schema].table_name

(

column_name datatype [DEFAULT expr][column_constraint]

……….

[table_constraint]

);

Example:

CREATE TABLE emp

(rollno number(5) CONSTRAINT emp_rollno_pk PRIMARY KEY,

name varchar2(20) CONSTRAINT emp_name_nn NOT NULL,

age number(2),

CONSTRAINT emp_age_chk CHECK(age>20));

To create a table, a user must have CREATE TABLE privilage.
DEFAULT option prevents NULL values from entering the column.

Modifying a table

It enables the database administrator or designer to change the structure of a table after it has been created.

The ALTER TABLE command enables you to do two things:

Add a column to an existing table.
Modify a column that already exists.

Syntax:

ALTER TABLE table_name

<ADD column_name data_type; |

MODIFY column_name data_type;>

Examples:

ALTER TABLE BILLS

MODIFY NAME CHAR (40);

ALTER TABLE NEW_BILLS

ADD COMMENTS CHAR (80);

Removing a table

Syntax:

DROP TABLE table_name;

Example:

DROP TABLE team;

Generic data types

Char(x):

A column of characters, where x is a number designating the maximum number of characters allowed (maximum length) in the column.

Integer:

A column of whole numbers, positive or negative.

Decimal(x, y):

A column of decimal numbers, where x is the maximum length in digits of the decimal numbers in this column, and y is the maximum number of digits allowed after the decimal point. The maximum (4, 2) number would be 99.99.

Date:

A date column in a DBMS-specific format.

Logical:

A column that can hold only two values: TRUE or FALSE.

Operators in SQL

Arithmetic operators:

Used for arithmetic operations.
-, +, *, /,%.
Examples:

SELECT ITEM, WHOLESALE, WHOLESALE + 0.15 FROM PRICE;

SELECT ITEM, WHOLESALE, WHOLESALE *2

FROM PRICE;

SELECT ITEM, WHOLESALE, (WHOLESALE/2) “SALEPRICE” FROM PRICE;

SELECT NUMERATOR, DENOMINATOR, MOD(NUMERATOR,DENOMINATOR) “REMAINDER” FROM REMAINS;

Concatenation operator(||):

It combines columns or character string to other columns.
Examples:

SELECT FIRSTNAME || LASTNAME “ENTIRENAME” FROM FRIENDS;

Comparison operators:

<,<= ,>, >=, (<>, !=, ^=).
Examples:

SELECT * FROM FRIENDS

WHERE AREACODE > 300;

SELECT * FROM FRIENDS

WHERE AREACODE >= 300;

SELECT * FROM FRIENDS

WHERE STATE! = 'CA';

Other comparison operators:
LIKE:

We use LIKE operator to perform wildcard search using % and _.
% represents any sequence of zero or more characters.
_ represents any single character.
Examples:

SELECT * FROM PARTS

WHERE LOCATION LIKE '%BACK%';

SELECT * FROM FRIENDS

WHERE STATE LIKE 'C_';

Determine if a value is a member of a specific search set.
Example:

SELECT * FROM FRIENDS

WHERE STATE IN ('CA', 'CO', 'LA');

IS NULL:

Determine if a value is null.
Example:

SELECT *

FROM TEAM

WHERE WINWORLDCUP IS NULL;

BETWEEN…AND..:

Use to display rows based on a range of values.
Example:

SELECT *

FROM PRICE

WHERE WHOLESALE BETWEEN 0.25 AND 0.75;

Logical Operators:

AND, OR, NOT.
We can use NOT with various operators such as NOT IN, NOT BETWEEN, NOT LIKE etc.
Examples:

SELECT LASTNAME,YEARS * 12 - LEAVETAKEN “REMAINING”

FROM VACATION

WHERE LASTNAME LIKE 'B%'

AND

YEARS * 12 - LEAVETAKEN > 50;

SELECT LASTNAME “WORKAHOLICS”

FROM VACATION

WHERE YEARS >= 5

((YEARS *12)-LEAVETAKEN)/(YEARS * 12) >= 0.50;

SELECT * FROM VACATION

WHERE LASTNAME NOT LIKE 'B%';

Set Operators:

Set operators are used to combine results of two or more queries.
UNION:

It returns the results of two queries minus the duplicate rows.
Examples:

SELECT NAME

FROM SOFTBALL

UNION

SELECT NAME FROM FOOTBALL;

Returns distinct names from the two lists.

UNION ALL:

UNION ALL works just like UNION except it does not eliminate duplicates.
Example:

SELECT NAME FROM SOFTBALL

UNION ALL

SELECT NAME FROM FOOTBALL;

Returns names (including duplicates) from the two lists.

INTERSECT:

It returns only the rows found by both queries.
Example:

SELECT * FROM SOFTBALL

INTERSECT

SELECT * FROM FOOTBALL;

In this example INTERSECT finds the short list of players who are on both teams.

MINUS:

It returns the rows from the first query that was not present in the second.
Example:

SELECT * FROM SOFTBALL

MINUS

SELECT * FROM FOOTBALL;

Shows the three softball players who are not on the football team.

SQL Functions

Number Functions:

Number functions accept numeric input and return numeric values.

Examples:

ABS (n):-

Returns the absolute value of the number you point to.
Example:

SELECT ABS(A) “ABSOLUTE_VALUE”

FROM NUMBERS;

ROUND (n [, m]):-

It rounds the value to n decimal places or if n is omitted , no decimal places. If n is negative, numbers of left of decimal point are rounded.

TRUNC (n [, m]):-

It truncates the column expr or values to ‘n’ decimal places or if ‘n’ is omitted the defaults to 0.

MOD(M,N):-

Returns the modulus of M/N.
Example:

SELECT A, B, MOD(A,B)

FROM NUMBERS;

LN or LOG(n):-

LN returns the natural logarithm of its argument.
Example:

SELECT A, LN(A)

FROM NUMBERS;

POWER(M,N):-

In this function the first argument is raised to the power of the second.
Example:

SELECT A, B, POWER(A,B)

FROM NUMBERS;

SQRT(n):-

It returns the square root of an argument.
Example:

SELECT A, SQRT(A)

FROM NUMBERS;

Character Functions:

Character functions accept character input and return either character or numeric values.
Examples:

CONCAT (column1/expr1, column2/expr2):-

It combines first character value to the second character value.
Example:

SELECT CONCAT (FIRSTNAME, LASTNAME) "FIRST AND LAST NAMES"

FROM CHARACTERS;

INITCAP (column/ expr):-

Convert the string to title case.
Example:

SELECT FIRSTNAME “BEFORE”, INITCAP(FIRSTNAME) “AFTER”

FROM CHARACTERS;

LOWER (column/ expr):-

Convert the string to lower case.
Example:

SELECT FIRSTNAME, LOWER(FIRSTNAME)

FROM CHARACTERS;

UPPER (column/ expr):-

Convert the string to upper case.
Example:

SELECT FIRSTNAME, UPPER(FIRSTNAME)

FROM CHARACTERS;

SUBSTR(column/ expr, M[,N]):-

Returns specified characters from the character value starting at position ‘M’ and ‘N’ characters long. If M is negative the count starts from the end of the character value. If N is omitted all characters to the end of string are return.
Example:

SELECT FIRSTNAME, SUBSTR(FIRSTNAME,2,3)

FROM CHARACTERS;

LENGTH (column/ expr):-

Returns the number of characters in the expression.
Example:

SELECT FIRSTNAME, LENGTH(FIRSTNAME)

FROM CHARACTERS;

INSTR (column/ expr, ‘string’ [, M][,N] ):-

It returns the numeric position of the search string. Optionally we can provide a position ‘M’ to start the search and the occurrence ‘N’ of the string.
Example:

SELECT LASTNAME, INSTR(LASTNAME, 'O', 2, 1)

FROM CHARACTERS;

LPAD (column/ expr, N, ‘string’):-

Pads the character value right justified to a total width of N characters position.
Example:

SELECT LASTNAME, LPAD(LASTNAME,20,'*') FROM CHARACTERS;

RPAD (column/ expr, N, ‘string’):-

Pads the character value left justified to a total width of N characters position.
Example:

SELECT LASTNAME, RPAD(LASTNAME,20,'*') FROM CHARACTERS;

REPLACE (column / expr, search_str, replacement_str):-

Searches a text expression for a character string and if found replaces it with a specify replacement string.
Example:

SELECT LASTNAME, REPLACE(LASTNAME, 'ST','**') “REPLACEMENT”

FROM CHARACTERS;

TRIM (leading / trailing / both trim_character FROM trim_source):-

Enables us to trim leading or trailing characters or both from a character string.

CHR (n):-

Returns the character equivalent of the number it uses as an argument. The character it returns depends on the character set of the database.
Example:

SELECT CODE, CHR(CODE)

FROM CHARACTERS;

Group Functions:

Default is to include all the candidate rows, but using DISTINCT causes the group functions to use only the distinct values of the argument expression.
Group function does not work in the WHERE clause.
Examples:

AVG ([DISTINCT | ALL] n):-

Computes the average of a column.
Example:

SELECT AVG(HITS/AB) “TEAM_AVERAGE”

FROM TEAMSTATS;

SUM ([DISTINCT | ALL] n):-

It returns the total of a column’s values. The columns must be numeric for this function to work properly.
Example:

SELECT SUM(SINGLES) “TOTAL_SINGLES”

FROM TEAMSTATS;

MAX ([DISTINCT | ALL] expr):-

This function returns the maximum value for a column in a table or set of rows returned in a query.
Examples:

SELECT MAX(HITS)

FROM TEAMSTATS;

SELECT NAME

FROM TEAMSTATS

WHERE HITS = MAX (HITS);

ERROR at line 3: ORA-00934: group function is not allowed here.

MIN ([DISTINCT | ALL] expr):-

This function returns the maximum value for a column in a table or set of rows returned in a query.
Example:

SELECT MIN(NAME)

FROM TEAMSTATS;

COUNT (* | [DISTINCT | ALL] expr):-

It returns the number of rows that satisfy the condition in the WHERE clause.
Example:

SELECT COUNT(*)

FROM TEAMSTATS

WHERE HITS/AB < .35;

STDDEV ([DISTINCT | ALL] n):-

It finds the standard deviation of a column of numbers.
Example:

SELECT STDDEV(HITS)

FROM TEAMSTATS;

VARIANCE ([DISTINCT | ALL] n):-

It produces the square of the standard deviation, a number vital to many statistical calculations.
Example:

SELECT VARIANCE(HITS)

FROM TEAMSTATS;

Date Functions and formats:

Date functions operate on values of the DATE data type.
All date functions return a value of DATE data type, except the MONTHS_BETWEEN function, which returns a number.
Examples:

TRUNC (date [, fmt]):-

Returns date truncated to the unit specified by the format model. If format model is omitted, date is truncated to the nearest day.

SYSDATE:-

Returns the current database server date and time.
Example:

SELECT * FROM PROJECT

WHERE STARTDATE > SYSDATE;

ROUND (date [, fmt]):-

Returns date rounded to the unit specified by the format model. If format model is omitted, date is truncated to the nearest day.

NEXT_DAY (date, ‘char’):-

It finds the date of the next specified day of the week.
Example:

SELECT STARTDATE, NEXT_DAY(STARTDATE, 'FRIDAY')

FROM PROJECT;

MONTHS_BETWEEN (date1,date2):-

It finds the no. of months between date1 and date2.
Example:

SELECT TASK, STARTDATE, ENDDATE, MONTHS_BETWEEN (STARTDATE, ENDDATE) “DURATION”

FROM PROJECT;

ADD_MONTHS(date, n):-

It adds a ‘n’ number of months to a specified date.
Example:

SELECT TASK, STARTDATE, ENDDATE “ORIGINAL_END”, ADD_MONTHS(ENDDATE,2) FROM PROJECT;

LAST_DAY (date):-

It returns the last day of a specified month.
Example:

SELECT ENDDATE, LAST_DAY(ENDDATE)

FROM PROJECT;

Conversion Functions:

Conversion functions convert a value from one data type to another.
Generally, the form of the function names follows the convention data type TO data type, the first data type is the input data type; the last data type is the output data type.
Examples:

TO_CHAR (number / date, ‘format model’):-

It converts a number or date value to a character string with specified format model.
Example:

SELECT TESTNUM, TO_CHAR(TESTNUM)

FROM CONVERT;

TO_NUMBER (‘char’, ‘format_model’):-

It converts a string into a number.
Example:

SELECT NAME, TESTNUM, TESTNUM*TO_NUMBER(NAME)

FROM CONVERT;

Miscellaneous Functions:

NVL(expr1, expr2):-

It converts a null value to actual value.

NVL2(expr1, expr2, expr3):-

The function examines the first expression if the 1st expr is not null then the function returns the 2nd expr, otherwise the 3rd expr is returned.

USER:-

It returns the character name of the current user of the database.

Constraint

Unique constraint:

Ensures that no two rows of a table can have duplicate values in a specific column or set of columns.

Primary key constraint:

Only one primary key can be created for a table.
The primary key is column or set of columns that uniquely identifies each row in the table.
The primary key constraint ensures that the columns cannot contain null values.

Foreign key constraint:

The FOREIGN key REFRENCE integrity constraint designates a column or a combination of columns as a foreign key and establishes a relationship between primary key and unique key in the same table or different table.
A foreign key value must match an existing value in the parent table or be null.
ON DELETE CASCADE:

It deletes the dependent rows in the child table when a row in the parent table is deleted.

ON DELETE SET NULL:

It converts foreign key values to null when a row in parent table is removed.

Without these two options, the row in the parent table can’t be deleted if it referenced in the child table.

Check constraint:

It defines a condition that each row must satisfy.
The condition can use the same construct as query conditions with the following exception:

References to CURRVAL, NEXTVAL and ROWNUM pseudo columns.
Calls to SYSDATE, UID, USER, functions.

Joining Tables

Having a common column in two tables implies a relationship between two tables, the nature of relationship is determined by which table uses the column as the primary key.
Sometimes we have to select data from two or more tables to make our result complete, we have to perform a join.
The common column appearing in the child table is referred to as foreign key.

Types of joins

Inner Joins:

Equijoins and Non-equijoins:

An equijoin is simply an inner join where the join clause’s condition specifies that a field in one table must equal a field in the other table.
Syntax:

one_table INNER JOIN another_table

ON one_table.some_field = another_table.another_field

A non-equijoin is a join where the condition is not equal, which means that the condition uses operators such as less than (<), greater than (>), less than or equal to (<=), and greater than or equal to (>=).

Examples:

SELECT FilmName, FirstName, LastName, State, YearReleased, YEAR (DateOfBirth) AS YearOfBirth

FROM MemberDetails INNER JOIN Films ON Films.YearReleased >= YEAR (MemberDetails.DateOfBirth)

WHERE State = ‘Golden State’ ORDER BY LastName, FirstName;

Multiple Joins and Multiple Conditions:

You can have more than one join in a query, which is essential when you need to join more than two tables at a time.

Syntax:

SELECT FirstName, LastName, Category.Category

FROM MemberDetails INNER JOIN FavCategory

ON MemberDetails.MemberId = FavCategory.MemberId

INNER JOIN Category

ON FavCategory.CategoryId = Category.CategoryId

ORDER BY LastName, FirstName;

Cross Joins:

A cross join is the most basic join, as there’s no ON clause to join the tables. Instead, all the rows from all the tables listed in the join are included in the results set. Only the usual filtering clauses associated with SELECT statements, such as a WHERE clause, limit the results set.
Example:

SELECT Category, Street

FROM Category CROSS JOIN Location

ORDER BY Street;

Self-Joins:

A self-join occurs when a table is joined to itself rather than to another table.
As a self-join is like any other join, the syntax is identical but with just one difference: When joining a table to itself, you must give the table an alias.

Example:

SELECT MD1.FirstName, MD1.LastName, MD2.FirstName, MD2.LastName,

MD1.ZipCode, MD2.ZipCode, MD1.Street, MD2.Street

FROM MemberDetails AS MD1 INNER JOIN MemberDetails AS MD2

ON MD1.Street = MD2.Street AND MD1.ZipCode = MD2.ZipCode;

Outer Joins:

An outer join, however, doesn’t require a match on both sides. You can specify which table always returns results regardless of the conditions in the ON clause, although the results are still subject to the filtering effects of any WHERE clause.
There are three types of outer join: right outer join, left outer join, and full outer join.
The syntax for outer joins is identical to that for inner joins. The only elements that change are the OUTER JOIN keyword and the difference in results produced.

Left Outer Join:

In a left outer join, all the records from the table named on the left of the OUTER JOIN statement are returned, regardless of whether there is a matching record in the table on the right of the OUTER JOIN statement.
Syntax:

SELECT column_list

FROM left_table LEFT OUTER JOIN right_table

ON condition

Example:

SELECT column_list

FROM left_table LEFT OUTER JOIN right_table

ON condition

SELECT Location.Street, MemberDetails.Street

FROM Location LEFT OUTER JOIN MemberDetails

ON Location.Street = MemberDetails.Street;

Right Outer Join

A right outer join is simply the reverse of a left outer join, in that instead of all the records in the left table being returned regardless of a successful match in the ON clause, now records from the table on the right of the join are returned.
Syntax:

SELECT column_list

FROM left_table RIGHT OUTER JOIN right_table

ON condition

Example:

SELECT Location.Street, MemberDetails.Street

FROM Location RIGHT OUTER JOIN MemberDetails

ON Location.Street = MemberDetails.Street;

Full Outer Join

A full outer join is essentially a combination of left and right outer joins in that records from the table on the left are included even if there is no matching Records on the right and records from the table on the right are included even if there are no matching records on the left.
Syntax:

SELECT column_list

FROM left_table FULL OUTER JOIN right_table

ON condition

Example:

SELECT Location.Street, MemberDetails.Street

FROM Location FULL OUTER JOIN MemberDetails

ON Location.Street = MemberDetails.Street;

I took assistance and consulted from the underneath named sources of information:

Beginning SQL.
Sams' Teach Yourself SQL in 21 Days.
Microsoft SQL Server Black Book.
SQL - A Practical Introduction.
sql_intro.

Encyclopedia website.

www.wikipedia.com