Chapter 4: Advanced SQL
Database System Concepts, 5th Ed.
©Silberschatz, Korth and Sudarshan
See [Link] for conditions on re-use
Chapter 4: Advanced SQL
● SQL Data Types and Schemas
● Integrity Constraints
● Authorization
● Embedded SQL
● Dynamic SQL
● Functions and Procedural Constructs**
● Recursive Queries**
● Advanced SQL Features**
Database System Concepts, 5th Edition, Oct 5. 2006 4.2 ©Silberschatz, Korth and Sudarshan
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
Database System Concepts, 5th Edition, Oct 5. 2006 4.3 ©Silberschatz, Korth and Sudarshan
Build-in Data Types in SQL (Cont.)
● Can extract values of individual fields from date/time/timestamp
● Example: extract (year from [Link])
● Can cast string types to date/time/timestamp
● Example: cast <string-valued-expression> as date
● Example: cast <string-valued-expression> as time
Database System Concepts, 5th Edition, Oct 5. 2006 4.4 ©Silberschatz, Korth and Sudarshan
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.
Database System Concepts, 5th Edition, Oct 5. 2006 4.5 ©Silberschatz, Korth and Sudarshan
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)
Database System Concepts, 5th Edition, Oct 5. 2006 4.6 ©Silberschatz, Korth and Sudarshan
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.
Database System Concepts, 5th Edition, Oct 5. 2006 4.7 ©Silberschatz, Korth and Sudarshan
Integrity Constraints
● Integrity constraints guard against accidental damage to the
database, by ensuring that authorized changes to the database do
not result in a loss of data consistency.
● A checking account must have a balance greater than
$10,000.00
● A salary of a bank employee must be at least $4.00 an hour
● A customer must have a (non-null) phone number
Database System Concepts, 5th Edition, Oct 5. 2006 4.8 ©Silberschatz, Korth and Sudarshan
Constraints on a Single Relation
● not null
● primary key
● unique
● check (P ), where P is a predicate
Database System Concepts, 5th Edition, Oct 5. 2006 4.9 ©Silberschatz, Korth and Sudarshan
Not Null Constraint
● Declare branch_name for branch is not null
branch_name char(15) not null
● Declare the domain Dollars to be not null
create domain Dollars numeric(12,2) not null
Database System Concepts, 5th Edition, Oct 5. 2006 4.10 ©Silberschatz, Korth and Sudarshan
The Unique Constraint
● unique ( A1, A2, …, Am)
● The unique specification states that the attributes
A1, A2, … Am
form a candidate key.
● Candidate keys are permitted to be null (in contrast to primary keys).
Database System Concepts, 5th Edition, Oct 5. 2006 4.11 ©Silberschatz, Korth and Sudarshan
The check clause
● check (P ), where P is a predicate
Example: Declare branch_name as the primary key for branch
and ensure that the values of assets are non-negative.
create table branch
(branch_name char(15),
branch_city char(30),
assets integer,
primary key (branch_name),
check (assets >= 0))
Database System Concepts, 5th Edition, Oct 5. 2006 4.12 ©Silberschatz, Korth and Sudarshan
The check clause (Cont.)
● The check clause in SQL-92 permits domains to be restricted:
● Use check clause to ensure that an hourly_wage domain allows only values
greater than a specified value.
create domain hourly_wage numeric(5,2)
constraint value_test check(value > = 4.00)
● The domain has a constraint that ensures that the hourly_wage is greater
than 4.00
● The clause constraint value_test is optional; useful to indicate which
constraint an update violated.
Database System Concepts, 5th Edition, Oct 5. 2006 4.13 ©Silberschatz, Korth and Sudarshan
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.
Database System Concepts, 5th Edition, Oct 5. 2006 4.14 ©Silberschatz, Korth and Sudarshan
Referential Integrity in SQL – Example
create table customer
(customer_name char(20),
customer_street char(30),
customer_city char(30),
primary key (customer_name ))
create table branch
(branch_name char(15),
branch_city char(30),
assets numeric(12,2),
primary key (branch_name ))
Database System Concepts, 5th Edition, Oct 5. 2006 4.15 ©Silberschatz, Korth and Sudarshan
Referential Integrity in SQL – Example (Cont.)
create table account
(account_number char(10),
branch_name char(15),
balance integer,
primary key (account_number),
foreign key (branch_name) references branch )
create table depositor
(customer_name char(20),
account_number char(10),
primary key (customer_name, account_number),
foreign key (account_number ) references account,
foreign key (customer_name ) references customer )
Database System Concepts, 5th Edition, Oct 5. 2006 4.16 ©Silberschatz, Korth and Sudarshan