13-06-2012, 11:13 AM
Advanced SQL
Built-in Data Types in SQL
date: Dates, containing a (4 digit) year, month and date
Example: date ‘2005-7-27’
time: Time of day, in hours, minutes and seconds.
Example: time ‘09:00:30’ time ‘09:00:30.75’
timestamp: date plus time of day
Example: timestamp ‘2005-7-27 09:00:30.75’
interval: period of time
Example: interval ‘1’ day
Subtracting a date/time/timestamp value from another gives an interval value
Interval values can be added to date/time/timestamp values
User-Defined Types
create type construct in SQL creates user-defined type
create type Dollars as numeric (12,2) final
create domain construct in SQL-92 creates user-defined domain types
create domain person_name char(20) not null
Types and domains are similar. Domains can have constraints, such as not null, specified on them.
Domain Constraints
Domain constraints are the most elementary form of integrity constraint. They test values inserted in the database, and test queries to ensure that the comparisons make sense.
New domains can be created from existing data types
Example: create domain Dollars numeric(12, 2) create domain Pounds numeric(12,2)
We cannot assign or compare a value of type Dollars to a value of type Pounds.
However, we can convert type as below (cast r.A as Pounds) (Should also multiply by the dollar-to-pound conversion-rate)
Large-Object Types
Large objects (photos, videos, CAD files, etc.) are stored as a large object:
blob: binary large object -- object is a large collection of uninterpreted binary data (whose interpretation is left to an application outside of the database system)
clob: character large object -- object is a large collection of character data
When a query returns a large object, a pointer is returned rather than the large object itself.
Referential Integrity
Ensures that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.
Example: If “Perryridge” is a branch name appearing in one of the tuples in the account relation, then there exists a tuple in the branch relation for branch “Perryridge”.
Primary and candidate keys and foreign keys can be specified as part of the SQL create table statement:
The primary key clause lists attributes that comprise the primary key.
The unique key clause lists attributes that comprise a candidate key.
The foreign key clause lists the attributes that comprise the foreign key and the name of the relation referenced by the foreign key. By default, a foreign key references the primary key attributes of the referenced table.