insert into 
values 
Deletion is expressed by:
delete from r
where P
which deletes all tuples from relation r that satisfy predicate P.
Example - delete Elvis Presley from the customer relation:
delete from customer
where name = "Elvis Presley"
As with the select clause, an omitted where
clause defaults to where true
Example - delete all tuples from the customer relation:
delete from customer
This does not delete the customer relation.
The where clause can refer to other relations, or even use
a nested query.
Example - delete all customers who haven't purchased a flyrod over 8.0 feet long:
delete from customer
where cust-num not in
(select cust-num
from purchased, flyrod
where purchased.flyrod-stock-num = flyrod.flyrod-stock-num
and length > 8.0)
In older versions of SQL, the relation in the delete clause
could not appear in the from clause of an embedded query.
Newer versions adopt a semantics that avoids problems with such
delete statements.
Example - delete all employees who make more than the average salary:
delete from employee
where salary >
(select avg(salary)
from employee)
Note that the average would change while the deletion was occurring. To fix this problem, modern versions of SQL do the following:
where clause for each tuple of the relation
being deleted from. No tuples are deleted in this step, but the result
of evaluating the where clause is stored for each tuple.
where clause is evaluated for each tuple, all
tuples that satisfied the where clause are deleted.
That is, SQL marks tuples for deletion first, and then deletes all tuples.
This guarantees that the where clause is always
evaluated over the value of the relation before any deletions occur.
Insertion is expressed by:
insert into
values
The attributes of relation r must be 
, and 
must be a value of the domain of 
. The attributes and values need
not be listed in the order of the attributes of r.
Example - If I bought an Orvis 9 weight (flyrod-stock-num of 2) on
10/23/95, either of the following insert statements
will add the appropriate tuple to the purchased relation:
insert into purchased (cust-num, flyrod-stock-num, date)
values (1, 2, 10/23/95)
insert into purchased (flyrod-stock-num, date, cust-num)
values (2, 10/23/95, 1)
If the attribute values in the values clause are ordered in
the same order as the attributes of the relation scheme, the attribute list
in the insert clause can be omitted.
Example - since Purchased-scheme = (cust-num, flyrod-stock-num, date), the
following is equivalent to the previous insert statements:
insert into purchased
values (1, 2, 10/23/95)
The values can be replaced with a select
statement to insert the result of a query into a relation.
Example - insert all employees into the customer relation, using their social-security-num as their cust-num.
insert into customer
select name, address, social-security-num
from employee
This adds an extra tuple for Elvis (with a different cust-num). Thus, Elvis now has 2 cust-num's.
Fix:
insert into customer
select name, address, social-security-num
from employee
where (name, address) not in
(select name, address
from customer)
Old versions of SQL would not allow such an insert
statement, because the same relation appears in the insert
clause and a nested query.
Newer versions permit such insert statements, using a semantics
like that used for delete statements.
This avoids the apparent contradiction in the previous example.
The insert statement can insert null values into a relation.
This can be useful if partial information needs to be stored, but causes
many problems.
Example - to add the Winston flyrod with unknown length to the flyrod relation:
insert into flyrod
values ("Winston", null, 7, 4)
An update statement is used to modify multiple tuples in the
database at one time.
Example - suppose that all employees who make more than $20000 get a 5% raise:
update employee
set salary = salary * 1.05
where salary > 20000
The order of updates can be significant. Suppose that all
employees who make more than $20000 get a 5% raise, and those who make
less than or exactly $20000 get a 6% raise. The sequence of updates:
update employee
set salary = salary * 1.06
where salary <= 20000
update employee
set salary = salary * 1.05
where salary > 20000
gives employees who were making slightly less than $20000 an 11.3% raise! Reversing the order of the updates fixes the problem.
The where clause of an update statement can contain
a nested query, and the nested query can reference the relation being updated.
Example - give all employees making less than the average salary a 5% raise:
update employee
set salary = salary * 1.05
where salary <
(select avg(salary)
from employee)
As with insert and delete, the updates to perform
are calculated first, and then all updates are performed. This prevents
surprising behavior.
Review: a view is a "subset" of the conceptual level of a DBMS.
In the relational model, views are "virtual relations". Views can be used anywhere relations can be used, but are not actually stored in the database.
Common reasons for using views:
Example (restricted access): suppose the flyshop hires Theodore Kaczynski on a prison work release program. He should NOT have access to customer and employee addresses.
Example (convenient interface): a view that gives both customer and employee addresses for sending out the newsletter, etc.
In SQL, a view is defined as follows:
create view view-name as SQL-query
where:
Examples:
the views for Ted Kaczynski:
create view customer-Ted as
select name, cust-num
from customer
create view employee-Ted as
select name, social-security-num, salary
from employee
the newsletter view:
create view newsletter as
(select name, address
from customer)
union
(select name, address
from employee)
Ted should not have access to the newsletter view.
Because a view can change whenever one of the relations it depends on changes, views are usually not stored as relations in the database. Instead, the SQL query itself is stored and used to compute the view whenever it is referenced.
A view name can be used anywhere a relation name can be used.
Example - find the name of everyone receiving the newsletter:
select name
from newsletter
To execute this query, the query defining newsletter is executed, and then name is selected from that result relation.
A view definition is deleted by name using the following command:
drop view view-name
The use of views in pure queries is not problematic.
Database modification through views is problematic. As views are not stored as relations, any modification of a view must be translated into modifications of the underlying relation(s).
Example - Ted tries to add a new customer:
insert into customer-Ted
values ("Donny Osmond", 5)
There are two reasonable responses:
As before, null values are problematic.
Example - a view cust-manufacturer, which associates the names of customers with the manufacturers of flyrods they have purchased:
create view cust-manufacturer as
(select name, manufacturer
from customer, purchased, flyrod
where customer.cust-num = purchased.cust-num and
purchased.flyrod-stock-num = flyrod.flyrod-stock-num)
Consider the insertion:
insert into cust-manufacturer
values ("Tim Wahls", "G. Loomis")
Again, the options for the DBMS are:
As {name} is not a superkey for customer, the system can't assume that the newly inserted tuple refers to the same "Tim Wahls" who is already a customer (and similarly for the new tuple of flyrod).
Consider the query:
select *
from cust-manufacturer
which just returns the tuples in the cust-manufacturer view. However, this query does not have ("Tim Wahls", "G. Loomis") in its result. As comparisons involving null values are false, the new tuples of customer and flyrod are not "put back together" when the view is recomputed.
To a user of the view, it appears as if the insertion had no effect. This is called the view-update anomaly.
To avoid the view-update anomaly,
many SQL-based database systems permit modification (insert,
delete, or update) through a view if and only if
the view is defined in terms of one relation.
In SQL, relations are created using the create table command:
create table r 
where r is the relation name, is an attribute name, and 
is
the domain of . A newly created relation is empty.
The domains in SQL-92 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 |
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.
In SQL-92, domains can be named in much the same way that types can be defined
in many programming languages. The syntax used is create domain.
Example - a domain for manufacturers:
create domain manufacturer-type char(40)
The create table command can also include integrity constraints
that
tuples inserted into the relation (table) must satisfy. These
integrity constraints include:
primary key( 
)
which specifies that the set of attributes 
is the
primary key of the relation, and
check(P)
(in SQL-92 only) which specifies an arbitrary predicate P that every tuple inserted into the relation must satisfy.
Example - the command to create the flyrod relation:
create table flyrod
(manufacturer char(40),
length real,
line-weight int,
flyrod-stock-num int not null,
primary key (flyrod-stock-num),
check (length > 0.0 and length < 20.0
and line-weight > 0 and line-weight <= 15))
With this definition of flyrod, the DBMS will reject insertion of a tuple if:
check clause
In SQL-89, any attribute appearing in the primary key clause
must be defined as not null. In SQL-92, attributes appearing
in the primary key clause are implicitly defined as
not null, so the definition of flyrod-stock-num as
not null above would be redundant.
The check clause can check that the value of some attribute
occurs in a given set. For example, if the flyshop only carries flyrods
by certain manufacturers, the following could be added to the above
check clause:
and manufacturer in ("Sage",
"Orvis",
"G. Loomis",
"Winston")
The set can be given as an SQL query, and this query can reference other relations (more on this later).
Relations are deleted by name using drop table as follows:
drop table r
deletes relation r. This deletes the entire relation (including the scheme), while
delete from r
deletes only the tuples of r.
In SQL-92, attributes can be added to or deleted from relations using the
alter table command.
To add attribute A with domain D to table r:
alter table r add A D
The value of the new attribute is null for all tuples in the relation.
To delete attribute A from table r:
alter table r drop A