databasedev.co.uk - database solutions and downloads for microsoft access

The Basics of SQL - Structured Query Language

SQL is a relational database that establishes relationships between tables of data. Each table is assigned a unique name to identify its collection of columns and rows. Each row represents a record. The record contains fields that are identified by column names. Each column (or field) has specific attributes, such as data type and format.

The basic SQL statements include methods for creating, manipulating and deleting tables as follows:

• Create a table consisting of rows and columns of data.
• Insert records or rows into the table.
• Update or modify records and data types in the table.
• Select or query the database for data matching some prescribed criteria.
• Delete records from the table.
• Drop or delete the table in its entirety.

All SQL statements include keywords that are used to define the operation being performed on the table. A semicolon (;) marks the end of a SQL statement. SQL statements may include constraints that establish rule-sets for the data fields in a record. There are three primary constraints:

• Unique - specifies that no two records can have the same value in a particular field.
• Not Null - specifies that a particular field cannot be blank.
• Primary Key - specifies that each record should contain a unique identifier.

Both table and column names must begin with a letter, their lengths may not exceed 30 characters and the name must not include any reserved words. Characters may include letters, numbers and underscores.

SQL provides for various data types to structure your data. Some of the most common data types include:

Common SQL Data Types

Data Type	Attributes
Char (size)	Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
varchar (size)	Variable-length character string. Max size is specified in parenthesis.
number (size)	Number value with a max number of column digits specified in parenthesis.
Date	Date value
number (size, d)	Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal.

SQL also provides for conditional operators, which includes the following:

SQL Conditional Operators

Operator	Description
=	Equal
>	Greater Than
<	Less Than
>=	Greater Than Or Equal To
<=	Less Than Or Equal To
<>	Not Equal To
LIKE	Matches a prescribed character pattern. The % symbol is used as a wildcard.

The following outlines basic SQL statements, along with the associated syntax. Optional values and clauses are enclosed in double brackets [ ].

• The create table statement is used to create new tables and define the format of each field in the record. Each record can have as many fields or columns as necessary to represent your data. You also have the option of attaching constraints to each field in a record. The syntax of a simple create table statement is as follows:
  
  CREATE TABLE "tablename" ("column1" "data type", "column2" "data type", "column3" "data type");
  
  When you include optional constraints, the syntax is as follows:
  
  create table "tablename" ("column1" "data type" [constraint], "column2" "data type" [constraint], "column3" "data type" [constraint]);
• The insert into statement is used to insert new records into a table. The syntax to insert a record into a table is as follows:
  
  INSERT INTO "tablename" (first_column,...last_column) values (first_value,...last_value);
• The update statement is used to update or modify records according to some prescribed criteria. The criteria is specified in the statement’s where clause as shown in the following syntax:
  
  UPDATE "tablename" set "columnname" = "newvalue" [,"nextcolumn" = "newvalue2"...]
  WHERE "columnname" OPERATOR "value" [and|or "column" OPERATOR "value"];
• The select statement initiates a query into the database to retrieve data that matches some prescribed criteria. The syntax for a simple select statement is as follows:
  
  SELECT "column1" [,"column2",…] from "tablename" [where "condition"];
  
  You may select any number of column names or you may use the wildcard character "*" to select all columns in a table. You may also include the LIKE operator in the where clause. The LIKE operator allows for the retrieval of records that match a prescribed character pattern. The percentage symbol (%) is used as a wildcard in the where clause. The syntax of the select statement using the LIKE operator is as follows:
  
  SELECT "column1" [,"column2",…] from "tablename" [where first LIKE ‘[%]”characters”’];
  Or
  SELECT "column1" [,"column2",…] from "tablename" [where last LIKE ‘[%]”characters”’];
• The delete statement is used to delete records from a table. The where clause is used to specify the criteria for deleting records. The syntax for the delete statement is as follows:
  
  DELETE from "tablename" where "columnname" OPERATOR "value" [and|or "column" OPERATOR "value"];
• The drop table command is used to delete an entire table. If you use the delete statement to delete all records from a table, a blank table, with record fields and constraints, still exits. The drop table command completely removes the table and all prescribed definitions. The syntax for the drop table statement is as follows:
  
  DELETE TABLE "tablename"