SQL is a set based language. It does have some procedural commands, but most
work is done with sets, not sequential proceedures. The order of operations
may not be what you expected. Records can be returned in any order, unless
you specify a sort. While there are several versions, with slightly different
commands in some cases, most implementations of SQL use very simular synatax.
(see Microsoft SQL Server, Transact SQL, T-SQL)

Relational: SQL is for Relational
Databases^,
and thus is based on Relational
Algebra^.
RA is a family of algebra with a well-founded semantics used for modelling
the data stored in relational databases, and defining queries on it.

Relational Databases are not the only solution: MongoDB, Hadoop, etc. are
useful especially in Big Data.

In SQL, all columns must relate to the record (functional dependencies).
Each record in a table has exactly the same fields.

Normalized: SQL databases tend to be
normalized^
inorder to avoid the limits of functional dependencies: Database normalization
is the process of minimize data redundancy by organizing the fields of a
single monolithic table into many tables within the database which are linked
by relations between the tables. For example, instead of storing the state
name for each record in an address table, the zip code can be used as a link
to a separate table which lists states by zip code. The state name is then
removed from the address table. If a state changes it's name, far fewer records
must be updated, and when the address is viewed with the related state name
pulled from the zip/state table, all the addresses will show the new state.

Complete normalization is not always the goal. e.g. Do-normalization can
increase performance.

Syntax:

Statements end with a semicolon and can be spread over many lines. A convention
is to put all commands in upper case and everything else in lowercase. The
most common command is:

The SELECT statement

SELECT fields FROM table WHERE condition SORT BY fields

Multiple tables can be joined.

SELECT t1.field AS field1, t2.field AS t2
FROM table1 AS t1
JOIN table2 AS t2
ON t1.ifield = t2.ifield;

Alias: Notice that the table name has an alias (table1 is t1) which
is used for each field selected. The keyword AS is optional for tables, but
not for fields.

Types of JOIN: The JOIN can be one of several types:

INNER JOIN (or just JOIN): Only records that match will
be returned.

OUTER JOIN: All records will be returned, but the ones that match
will be combined.

LEFT JOIN: All records in the first table, but only matching records
from the second.

RIGHT JOIN: All records from the second table, but only matching
records from the first.

The field list can include "column functions" which compute new data
on the fly from the table fields. E.g. LEFT(last_name,1) will return the
first letter of the last name, assuming there is a last_name field in the
table.

Tables or Views: Tables are the data, Views is a way of looking at
one or more tables via SELECT queries. Reuse queries, Restrict access: Can
be used to keep the user out of base tables, Encapsulate Business Rules,
Get rid of frickin codes!

Example:All the Films the actors are in.

SELECT film.title, act.first_name, act.last_name/* The table names above are optional since the column names are unique
to those tables. I include them to assist in readability and maintainability.
The table names in the joins below are not optional since the same column
name occurs in both tables.*/FROM actor act #Notice the table alias
INNER JOIN film_actor #No alias, this table is just to eliminate a many
to many relationship
ON act.actor_id = film_actor.actor_id
INNER JOIN film #No alias because the name is short
ON film.film_id = film_actor.film_id;

Revised to avoid duplicate listings and show only records in Canadian stores
in DESCending order by film title. The DISTICT keyword removes records that
are the same as the previous record returned in the given order.

The INSERT Statement

Used to insert data. Biggest problem is making sure foreign key fields are
correct. e.g. If one table links via a field called "other_id" and you insert
a value that isn't an id in that other table, your insert fails.

CURDATE() is a calculated value that returns the current date. Notice that
nothing was specified for an ID field. If there is one (there should be)
it will be automatically created. E.g. There should be an ID field in the
actor table and it should be set with a default value of "auto-increment"
when new records are added. The first ever record added will have an ID of
1, the second will be 2 and so on. Even if record #2 is deleted, the next
record (2nd of 2 in the table) will have and ID of 3.

The DELETE Statement

DELETE FROM actor
WHERE last_name = 'Zappa';

Very Important! If you dont put in a WHERE clause you will erase all
the data in the table. Instead, use a SELECT to make sure your WHERE is getting
you the records you want to delete, then change the SELECT to a DELETE keeping
the same WHERE.

If you want to get rid of a table sometimes it is better to DROP it instead.

Sometimes called temporary/temp tables in SQL Server, but are really temporary
views. SQL Server temp tables are not CTEs, but created on disk and then
used. CTEs use the underlying indexes. Temp tables need their own indexes.
There are times when it is appropriate in SQL Server to use temp tables and
not CTEs, but not often. Necessary for recursion.

After you find an appropriate page, you are invited to
your
to this massmind site! (posts will be visible only to you before review)
Just type in the box and press the Post button.
(HTML welcomed, but not the <A tag:
Instead, use the link box to link to another page.
A tutorial is availableMembers can
login
to post directly, become page editors, and be credited for their posts.

Link? Put it here:
if you want a response,
please enter your email address:
Attn spammers: All posts are reviewed before being made visible to anyone other than the poster.