SQL Syntax

SQL (pronounced "ess-que-el") stands for Structured Query Language and is used to communicate with databases. The SQL is a standard language for specifying accesses and modifications to relational databases. Originally standardized by the American National Standards Institute (ANSI) and the International Standards Organization (ISO) in 1986 as being the standard language for relational database manaement systems. Standards Organization (ISO) in 1986.

Most common relational database management systems use SQL, e.g.:


• MySQL
• mSQL
• PostgresSQL
• Micro$oft SQL
• Access
• DB2
• Oracle
• Sybase

All of these databases support all the basic sql syntax e.g. CREATE, INSERT, SELECT, DELETE and DROP, but most of them also have their own additional extensions which aren't supported by other vendors.

Tables

Relational databases store information within one or more tables which are uniquely identifiable by their names. Tables are made from columns and rows, where the columns have their own uniquely identifiable name within the table (Note: columns do not need to be unique between tables, only within each table). Each column is defined by it's name, it's data type and by constraints it's creator decides to place on it. (see more about constraints in 'create table bellow').
Rows on the other hand contain the records/information for the database related to each column. Here is an example of a table where a Table contains a State Name and an unique integer for refferencing it.

States
State_id	State
1	Alabama
2	Alaska
3	Arizona
4	Arkansas
5	California
6	Colorado
7	Connecticut
8	Delaware
9	Florida
10	Georgia

Creating tables

The general form of the CREATE statement is as follows:

CREATE TABLE table_name(
columnName1 dataType constraints,
columnName2 dataType constraints,
...
columnNameN dataType constraints)


The constraints are optional. The most common constraints are 'UNIQUE', 'NOT NULL' and 'PRIMARY KEY'. Constraints are simply rules which will be inforced on a column if supplied. E.g. if you were to put the 'UNIQUE' constraint on a column, it would mean that no two rows could have the same value within this column, in other words all the values within this column must be unique. The same goes for the other two mentioned constraints: 'NOT NULL' is a rule which states that no value in this particular column can be left empty/blank, and 'PRIMARY KEY' defines a unique identification of each row within the table.

Example: creating a table to reference states by a number


CREATE TABLE States(
	State_id INTEGER PRIMARY KEY NOT NULL,
	State CHAR(20));


SQL syntax/reserved words are not case sensitive, so it does not matter wether you type in CREATE/CrEaTe/ crEaTe. Names of tables and table columns may or may not be case sensitive depending on the particular database.

Adding records to tables

To add a record to a table, you use the INSERT statement. The general form of the INSERT statement is as follows:


	INSERT INTO table_name(column_name_1, column_name_2, ..., column_name_3)
	VALUES(value_1, value_2, ..., value_3);


When using the INSERT statement to add records to a table, you use INSERT INTO followed by the table_name of the relivent table. The table name is followed by a list of column names within the table enclosed in parenthesis, which is then followed by the VALUES statment, which is finally followed by a list of values enclosed in parenthesis. The order of the values most be the same as the order of the column names, that is, the first value within the VALUES parenthesis will be placed within the first column within the INSERT INTO parenthesis.
Strings should be between single quotes: 'string'.

Example: Inserting data into a table which holds data for Corvettes.

INSERT INTO Corvettes(Vette_id, Body_style, Miles, Year, State) VALUES (37, 'convertible', 25.5, 1986, 17);

Retrieving data from tables

To retrieve data which matches the criteria you specify, you use the select statement. The general form of the SELECT statement is as follows:

SELECT column names FROM table names [WHERE condition]

When using the SELECT statement, the column names which follow it will be the columns which will be returned in the results (you can use * to select all of them). The tablename following FROM specifies the table which will be queried. If the WHERE statement is excluded, the query will return the contents of all the records within the specified tables (with the requested columns). When the WHERE statment is used, you have the following conditional selection:
• = Equal
• < Less than
• > Greater than
• >= Greater than or equal
• <= Less than or equal
• <> Not equal to
• LIKE [pattern] - Match pattern

Example: searching for body_style of Corvettes which were made after 1994:

SELECT Body_style FROM Corvettes WHERE Year > 1994;

Changing data within a pre-existing record

To change data within pre-existing records which match a specified criteria, you use the UPDATE statement. The general form of the UPDATE statement is as follows:

UPDATE table_name SET
	column_name_1 = value_1,
	column_name_2 = value_2,
	...,
	column_name_n = value_n
WHERE column_name = value

Example: changing the year of a Corvette which had been wrongly typed in.

UPDATE Corvettes
SET Year = 1996
WHERE Vette_id = 17;

Removing records from a table

To remove records from a table, you use the DELETE statement. The general form of the DELETE statement is as follows:

DELETE FROM table_name
WHERE column_name = value


The where clause specifies the primary key of the record you wish to remove.

Example: Removing the record of a Corvette which is identified as nr. 17 (e.g. imagine this is a database for a car sale, and this car has just been sold and can be removed from the database)

DELETE FROM Corvettes
WHERE Vette_id = 27