SQL Concepts - PDF

SQL SQL  Domains  Schema definition  Table definition SQL SQL  The name is an acronym for Structured Query Language  Far richer than a query language: both a DML and a DDL  History: – First proposal: SEQUEL (IBM R...

SQL SQL  Domains  Schema definition  Table definition SQL SQL  The name is an acronym for Structured Query Language  Far richer than a query language: both a DML and a DDL  History: – First proposal: SEQUEL (IBM Research, 1974) – First implementation in SQL/DS (IBM, 1981)  Initial “standardization” crucial for its diffusion – Since 1983, standard de facto – First standard, 1986, revised in 1989 (SQL-89) – Second standard, 1992 (SQL-2 or SQL-92) – Third standard, 1999 (SQL-3 or SQL-99) – Etc.  Most relational systems support the base functionality of the standard and offer proprietary extensions SQL Domains  Domains specify the datatype of attributes  Two categories – Elementary (predefined by the standard) – User-defined SQL Examples elementary domains  Character – Single characters or strings – Strings may be of variable length – A Character set different from the default one can be used (e.g., Latin, Greek, Cyrillic, etc.) – Syntax: character [ varying ] [ (Length) ] [ character set CharSetName ] – It is possible to use char and varchar, respectively for character and character varying SQL Examples elementary domains (cont.d)  Bit – Single boolean values or strings of boolean values (may be variable in length) – Syntax: bit [ varying ] [ (Length) ]  Exact numeric domains – Exact values, integer or with a fractional part – Different alternatives: numeric [ ( Precision [, Scale ] ) ] decimal [ ( Precision [, Scale ] ) ] integer smallint SQL Examples elementary domains (cont.d)  Approximate numeric domains – Approximate real values – Based on a floating point representation float [ ( Precision ) ] double precision real SQL Examples elementary domains (cont.d) Temporal instants date time [ ( Precision) ] [ with time zone ] timestamp [ ( Precision) ] [ with time zone ] Temporal intervals interval FirstUnitOfTime [ to LastUnitOfTime ] – Units of time are divided into two groups: year, month day, hour, minute, second SQL Schema definition  A schema is a collection of objects: – domains, tables, indexes, assertions, views, privileges  A schema has a name and an owner (the authorization)  Syntax: create schema [ SchemaName ] [ [ authorization ] Authorization ] { SchemaElementDefinition } SQL Table definition  An SQL table consists of – an ordered set of attributes – a (possibly empty) set of constraints Statement create table – defines a relation schema, creating an empty instance Syntax: create table TableName ( AttributeName Domain [ DefaultValue ] [ Constraints ] {, AttributeName Domain [ DefaultValue ] [ Constraints ] } [ OtherConstraints ] ) SQL Example of create table create table Employee ( RegNo character(6) primary key, FirstName character(20) not null, Surname character(20) not null, Dept character (15) references Department(DeptName) on delete set null on update cascade, Salary numeric(9) default 0, City character(15), unique(Surname,FirstName) ) NOTE: For now, please ignore the part in bold (will revisit later) SQL User defined domains  Comparable to the definition of variable types in programming languages  A domain is characterized by – name – elementary domain – default value – set of constraints  Syntax: create domain DomainName as ElementaryDomain [ DefaultValue ] [ Constraints ]  Example: create domain smallintNULL as smallint default null SQL Default domain values  Define the value that the attribute must assume when a value is not specified during row insertion  Syntax: default < GenericValue | user | null >  GenericValue represents a value compatible with the domain, in the form of a constant or an expression  user is the login name of the user who issues the command