SQL [CREATE INDEX]

The CREATE INDEX statement is used to create indexes in tables. Indexes allow the database application to find data fast; without reading the whole table. The users cannot see the indexes, they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.

Look up its syntax.

SQL constraints concepts

Constraints are used to limit the type of data that can go into a table.

Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).

Here are several SQL constraints:

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY - A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
• CHECK
• DEFAULT

SQL Joins

There are several joins in SQL:

INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL JOIN


Database basic concepts - Keys, Primary Keys

Tables in a database are often related to each other with keys.

A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.

Look at the "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Note that the "P_Id" column is the primary key in the "Persons" table. This means that no two rows can have the same P_Id. The P_Id distinguishes two persons even if they have the same name.

SQL wildcards

SQL wildcards can substitute for one or more characters when searching for data in a database. SQL wildcards must be used with the SQL LIKE operator.

Wildcard	Description
%	A substitute for zero or more characters
_	A substitute for exactly one character
[charlist]	Any single character in charlist
[^charlist] or [!charlist]	Any single character not in charlist

SQL TOP clause and its MySQL equivalent

A note on SQL and MySQL:

SELECT TOP number|percent column_name(s)
FROM table_name

is equivalent to:

SELECT column_name(s)
FROM table_name
LIMIT number

Example:

SELECT *
FROM Persons
LIMIT 5

Pratice your SQL syntax with W3School

http://www.w3schools.com/sql/sql_tryit.asp

To get you familiar with SQL syntax quickly is to use it!

SQL and Relational Algebra

Relational Algebra operators:

• Selection corresponds to WHERE clause in SQL syntax
• Projection corresponds to SELECT clause in SQL syntax
• Cross-product combines fields of selected tables
• Join is basically a cross-product followed by a selection followed by a projection
• conditional joins: cross-product followed by a selection
• equijoins: cross-product followed by a 'equivalence' selection followed by a projection to exclude repeated fields in the resulting table
• natural join: equijoin on all fields that have the same name

http://en.wikipedia.org/wiki/Relational_algebra

Little note on SQL

SQL is not case sensitive.