SQL

Introduction

SQL (Structured Query Language) is a database sublanguage for querying and modifying relational databases. It was developed by IBM Research in the mid 70's and standardized by ANSI in 1986.

The Relational Model defines two root languages for accessing a relational database -- Relational Algebra and Relational Calculus.

Relational Algebra is a low-level, operator-oriented language. Creating a query in Relational Algebra involves combining relational operators using algebraic notation. Relational Calculus is a high-level, declarative language. Creating a query in Relational Calculus involves describing what results are desired.

SQL is a version of Relational Calculus. The basic structure in SQL is the statement. Semicolons separate multiple SQL statements.

There are 3 basic categories of SQL Statements:

1. SQL-Data Statements -- query and modify tables and columns

2. SQL-Transaction Statements -- control transactions

3. SQL-Schema Statements -- maintain schema (catalog)

Language Structure

SQL is a keyword based language. Each statement begins with a unique keyword. SQL statements consist of clauses which begin with a keyword. SQL syntax is not case sensitive.

The other lexical elements of SQL statements are:

• names -- names of database elements: tables, columns, views, users, schemas; names must begin with a letter (a - z) and may contain digits (0 - 9) and underscore (_)
• literals -- quoted strings, numeric values, datetime values
• delimiters -- + - , ( ) = < > <= >= <> . * / || ? ;

Basic database objects (tables, views) can optionally be qualified by schema name. A dot -- ".", separates qualifiers:

schema-name . table-name

Column names can be qualified by table name with optional schema qualification.

Note: Names can be case sensitive and contain spaces and other delimiters and can use keywords, by surrounding them with double quotation marks ("). For example,

"1 Name w/spaces"
"SELECT"

Quoted names must match exactly on case.

Example Tables

In the subsequent text, the following 3 example tables are used:

p Table (parts)	s Table (suppliers)	sp Table (suppliers & parts)
pno descr color P1 Widget Blue P2 Widget Red P3 Dongle Green	sno name city S1 Pierre Paris S2 John London S3 Mario Rome	sno pno qty S1 P1 NULL S2 P1 200 S3 P1 1000 S3 P2 200

code:

CREATE TABLE Examples

Creating the example tables:

CREATE TABLE s
(sno VARCHAR(5) NOT NULL PRIMARY KEY,
name VARCHAR(16),
city VARCHAR(16)
)

CREATE TABLE p
(pno VARCHAR(5) NOT NULL PRIMARY KEY,
descr VARCHAR(16),
color VARCHAR(8)
)

CREATE TABLE sp
(sno VARCHAR(5) NOT NULL REFERENCES s,
pno VARCHAR(5) NOT NULL REFERENCES p,
qty INT,
PRIMARY KEY (sno, pno)
)

Create for sp with a constraint that the qty column can't be negative:

CREATE TABLE sp
(sno VARCHAR(5) NOT NULL REFERENCES s,
pno VARCHAR(5) NOT NULL REFERENCES p,
qty INT CHECK (qty >= 0),
PRIMARY KEY (sno, pno)
)

fuente: http://www.firstsql.com