Database System Concepts - SQL

 

SQL (Structured Query Language) is THE standard DML for relational database products. The query language is based on relational algebra, but borrows from tuple relational calculus.

Topics:

• the data-definition language (DDL) - creating, deleting and modifying relation schemas

• the data-manipulation language (DML) - the query language

• modification of relations (insert, delete, update)

• integrity constraints (domain constraints and foreign keys)

• creation and use of views

• transaction control

• application programming

Five major standards have been defined for SQL:

• SQL-86

• SQL-89

• SQL-92

• SQL:1999

• SQL:2003

Each standard is essentially a superset of the previous ones. Most major commercial systems support essentially the SQL-92 standard along with some part of the SQL:1999 and SQL:2003 standards.

In the following examples, we continue to use the flyshop database with the following schema (primary  key attributes underlined):

 

schema name	attributes
Fly-schema	(s  tocknum, pattern, size, color, inventory)
Customer-schema	(c  ustnum, name, address)
Flyrod-schema	(s  tocknum, manufacturer, length, lineweight, inventory)
Purchased-schema	(c  ustnum, s  tocknum, pdate)
Requestedby-schema	(s  tocknum, c  ustnum)
Employee-schema	(s  sn, name, address, salary)

 

 

1 Data Definition

In SQL, relations are created using the create table command:

 

create table r(A₁D₁, A₂, D₂, . . . , A_nD_n,<constraints>)

 

where r is the relation name, A_i is an attribute name, and D_i is the domain of A_i. The <constraints> are optional and are described later. A newly created relation is empty.

The domains in SQL are:

 

domain type	description
char(n)	strings of (user specified) length n
varchar(n)	variable length strings of (user specified) maximum length n
int	integer
smallint	smaller subset of integer
numeric(p, d)	fixed-point numbers with a maximum of p digits, with d to the right of the decimal point
real	like float
double precision	like double
float(n)	float with user specified precision n
date	includes year, month and day
time	in hours, minutes and seconds
timestamp	combination of date and time

Table  1:  Domains in SQL

 

Example constants:

• date: ’2004-02-05’

• time: ’16:32’, ’16:32:30’

• timestamp: ’2004-02-05 16:32.00’

Dates,  times and timestamps can be compared with <,  = and so on.  Each type can be converted to the   others, and can be used in arithmetic expressions.

In DB2, timestamps must be written like: ’2004-02-05-16.32.00’

In DB2, the components (year, second, minute, day) etc. can be extracted from a date, time or timestamp using the name of the component. For example, if the pdate attribute of purchased is of type date, then the year of each purchase can be found by:

 

select year(pdate) from purchased

 

The ANSI standard uses the keyword extract for this purpose, i.e. extract(year from pdate).

In the create table command, any attribute can be declared to be not null, which makes the DBMS reject any insertion of a tuple with value null for that attribute. (Recall that null values are used for information that doesn’t exist or is not available.) The create table command can also include integrity constraints that tuples inserted into the relation (table) must satisfy. These integrity constraints include:

• primary key(A₁, A₂, . . . A_n)

 

which specifies that the set of attributes {A₁, A₂, . . . A_n} is the primary key of the relation

• unique(A₁, A₂, . . . A_n)

 

which specifies that the set of attributes {A₁, A₂, . . . A_n} is a candidate key for the relation

• check(P )

 

which specifies an arbitrary predicate P that every tuple inserted into the relation must satisfy. Example: the command to create the fly relation: