PowerPoint Presentation
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5Magnus Wahlström
Department Of Computer Science
McCrea – 120A
5. SQL
BI5631 – Database Systems
Slides adapted from Database System Concepts, 6th Edition
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
2SQLBI5631 – Database Systems
SQL History
• IBM Sequel language developed as part of System R
project at the IBM San Jose Research Laboratory
• Renamed Structured Query Language (SQL)
• ANSI and ISO standard SQL:
• SQL-86
• SQL-89
• SQL-92
• SQL:1999 (language name became Y2K compliant!)
• SQL:2003
• Commercial systems offer most, if not all, SQL-92
features, plus varying feature sets from later
standards and special proprietary features.
• Not all examples here may work on all systems.
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
3SQLBI5631 – Database Systems
SQL – more than just a query language
• It can define the structure of the data
• Modify the data
• Specify security constraints
1. Data Definition Language (DDL): provides
commands for defining relation schemas, deleting
relations, modifying relation schemas
1. Interactive Data Manipulation Language (DML): a
query language based on relational algebra
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
4SQLBI5631 – Database Systems
Overview of (most of ) the Course
Text description ER diagram
Relational ModelSQL
Relational Algebra
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
5SQLBI5631 – Database Systems
Data Definition Language
• The schema for each relation.
• The domain of values associated with each attribute.
• Integrity constraints
• Security and authorisation information for each relation.
• The physical storage structure of each relation on disk.
Allows the specification of not only a set of relations but
also information about each relation
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
6SQLBI5631 – Database Systems
Data definition
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
7SQLBI5631 – Database Systems
Domain Types in SQL
• char(n). Fixed length character string, with user-specified length n.
• varchar(n). Variable length character strings, with user-specified
maximum length n.
• int. Integer (a finite subset of the integers that is machine-
dependent).
• smallint. Small integer (a machine-dependent subset of the integer
domain type).
• numeric(p,d). Fixed point number, with user-specified precision of p
digits, with d digits to the right of decimal point.
• real, double precision. Floating point and double-precision floating
point numbers, with machine-dependent precision.
• float(n). Floating point number, with user-specified precision of at
least n digits.
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
8SQLBI5631 – Database Systems
Create Table Construct
• An SQL relation is defined using the create table
command:
create table r (A1 D1, A2 D2, …, An Dn,
(integrity-constraint1),
…,
(integrity-constraintk))
• r is the name of the relation
• each Ai is an attribute name in the schema of relation r
• Di is the data type of values in the domain of attribute Ai
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
9SQLBI5631 – Database Systems
Example:
create table branch
(branch_name varchar(15),
branch_city varchar(30),
assets integer,
primary key (branch_name))
Note that the primary-key attributes are required to be non-null
and unique.
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
10SQLBI5631 – Database Systems
Drop and Alter Table Constructs
• The drop table command deletes all information about the
dropped relation from the database.
• The alter table command is used to add attributes to an existing
relation:
alter table r add A D
where A is the name of the attribute to be added to relation r and
D is the domain of A.
• All tuples in the relation are assigned null as the value for the new attribute.
• The alter table command can also be used to drop attributes of a
relation:
alter table r drop A
where A is the name of an attribute of relation r
• Dropping of attributes not supported by many databases
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
11SQLBI5631 – Database Systems
Basic query structure
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
12SQLBI5631 – Database Systems
Basic Query Structure
• A typical SQL query has the form:
select A1, A2, …, An
from r1, r2, …, rm
where P
• Ai represents an attribute
• ri represents a relation
• P is a predicate.
• This query is equivalent to the relational algebra expression:
• The result of an SQL query is a relation.
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
13SQLBI5631 – Database Systems
The select Clause
• The select clause lists the desired attributes in the result of a query
it corresponds to the projection operation of relational algebra
• Example: find the names of all branches in the loan relation:
select branch_name
from loan
In relational algebra, the query would be:
branch_name (loan)
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
14SQLBI5631 – Database Systems
• SQL allows duplicates in relations as well as in query
results
• To force the elimination of duplicates, insert the keyword distinct
after select.
Example: find the names of all branches in the loan relation, and
remove duplicates
select distinct branch_name
from loan
• The keyword all specifies that duplicates not be removed.
select all branch_name
from loan
(all is the default, hence it will be omitted in subsequent examples).
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
15SQLBI5631 – Database Systems
• An asterisk in the select clause denotes “all attributes”
select *
from loan
• The select clause can contain arithmetic expressions involving
the operation, +, –, , and /, and operating on constants or
attributes of tuples.
Example: The query:
select loan_number, branch_name, amount * 100
from loan
would return a relation that is the same as the loan relation,
except that the value of the attribute amount is multiplied by 100.
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
16SQLBI5631 – Database Systems
The where Clause
• The where clause specifies conditions that the result must satisfy
it corresponds to the selection predicate of the relational algebra.
• Example: To find all loan number for loans made at the Perryridge
branch with loan amounts greater than $1200.
select loan_number
from loan
where branch_name = ‘Perryridge’ and amount > 1200
• Comparison results can be combined using the logical connectives
and, or, and not.
• Comparisons can be applied to results of arithmetic expressions.
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
17SQLBI5631 – Database Systems
• SQL includes a between comparison operator
Example: find the loan number for loans with loan
amounts between $90,000 and $100,000 (that is,
$90,000 and $100,000)
select loan_number
from loan
where amount between 90000 and 100000
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
18SQLBI5631 – Database Systems
The from Clause
• The from clause lists the relations involved in the query
it corresponds to the Cartesian product operation of
relational algebra.
Example: find the Cartesian product borrower x loan
select *
from borrower, loan
Example: find the name, loan number and loan amount of all
customers having a loan at the Perryridge branch.
select customer_name, borrower.loan_number,
amount
from borrower, loan
where borrower.loan_number = loan.loan_number
and
branch_name = ‘Perryridge’
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
19SQLBI5631 – Database Systems
Points to remember
1) Unlike the result of a relational-algebra expression,
the result of a SQL query may contain multiple copies
of some tuples
select clause
from clause
where clause
∏ projection
x Cartesian Product
σ selection
2)
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
20SQLBI5631 – Database Systems
The rename operation
• SQL allows renaming relations and attributes using the as
clause:
old-name as new-name
• as can appear both in the select and from clauses
Example: find the name, loan number and loan amount of
all customers; rename the column name loan_number as
loan_id
select customer_name, borrower.loan_number as loan_id,
amount
from borrower, loan
where borrower.loan_number = loan.loan_number
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
21SQLBI5631 – Database Systems
Tuple Variables
• Tuple variables are defined in the from clause via the use of the as clause.
Example: find the customer names and their loan numbers for all
customers having a loan at some branch.
select customer_name, T.loan_number, S.amount
from borrower as T, loan as S
where T.loan_number = S.loan_number
Example: find the names of all branches that have assets greater than at
least one branch located in Brooklyn.
select distinct T.branch_name
from branch as T, branch as S
where T.assets > S.assets and S.branch_city = ‘Brooklyn’
• Keyword as is optional and may be omitted:
borrower as T ≡ borrower T
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
22SQLBI5631 – Database Systems
String Operations
• SQL includes a string-matching operator for comparisons on character
strings. The operator like uses patterns that are described using two
special characters:
• percent (%). The % character matches any substring.
• underscore (_). The _ character matches any character.
Example: find the names of all customers whose street includes the
substring “Main”.
select customer_name
from customer
where customer_street like ‘% Main%’
Example: Match the name “Main%”
like ‘Main\%’ escape ‘\’
• SQL supports a variety of string operations such as
• concatenation (using “||”)
• converting from upper to lower case (and vice versa)
• finding string length, extracting substrings, etc.
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
23SQLBI5631 – Database Systems
Ordering the display of tuples
• Example: list in alphabetic order the names of all
customers having a loan in Perryridge branch
select distinct customer_name
from borrower, loan
where borrower.loan_number = loan.loan_number and
branch_name = ‘Perryridge’
order by customer_name
• We may specify desc for descending order or asc for
ascending order, for each attribute; ascending order is the
default.
• Example: order by customer_name desc
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
24SQLBI5631 – Database Systems
Set operations
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
25SQLBI5631 – Database Systems
Set Operations
• The set operations union, intersect, and except operate
on relations and correspond to the relational algebra
operations
• Each of the above operations automatically eliminates
duplicates; to retain all duplicates use the corresponding
multiset versions union all, intersect all and except all.
• Suppose a tuple occurs m times in r and n times in s, then,
it occurs:
• m + n times in r union all s
• min(m,n) times in r intersect all s
• max(0, m – n) times in r except all s
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
26SQLBI5631 – Database Systems
Find all customers who have a loan, an account, or
both:
(select customer_name from depositor)
except
(select customer_name from borrower)
(select customer_name from depositor)
intersect
(select customer_name from borrower)
Find all customers who have an account but no loan.
(select customer_name from depositor)
union
(select customer_name from borrower)
Find all customers who have both a loan and an account.
Examples:
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
27SQLBI5631 – Database Systems
Aggregate functions
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
28SQLBI5631 – Database Systems
Aggregate Functions
• These functions operate on the multiset of values of a
column of a relation, and return a value
avg: average value
min: minimum value
max: maximum value
sum: sum of values
count: number of values
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
29SQLBI5631 – Database Systems
Find the average account balance at the Perryridge branch.
Find the number of depositors in the bank.
Find the number of tuples in the customer relation.
select avg (balance)
from account
where branch_name = ‘Perryridge’
select count (*)
from customer
select count (distinct customer_name)
from depositor
Examples:
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
30SQLBI5631 – Database Systems
Aggregate Functions – group by
• Find the average account balance at each branch.
select branch_name, avg (balance)
from account
group by branch_name
• Find the number of depositors for each branch.
select branch_name, count (distinct customer_name)
from depositor, account
where depositor.account_number =
account.account_number
group by branch_name
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
31SQLBI5631 – Database Systems
Aggregate Functions – having clause
• Find the names of all branches where the average
account balance is more than $1,200.
Note: predicates in the having clause are applied after the formation of
groups whereas predicates in the where clause are applied before forming
groups
select branch_name, avg (balance)
from account
group by branch_name
having avg (balance) > 1200
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
32SQLBI5631 – Database Systems
Null Values
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
33SQLBI5631 – Database Systems
Null Values
• It is possible for tuples to have a null value, denoted by null, for
some of their attributes
• null signifies an unknown value or that a value does not exist.
• The predicate is null can be used to check for null values.
Example: find all loan number which appear in the loan relation
with null values for amount.
select loan_number
from loan
where amount is null
• The result of any arithmetic expression involving null is null
• Example: 5 + null returns null
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
34SQLBI5631 – Database Systems
Null Values and Three Valued Logic
• Any comparison with null returns unknown
• Example: 5 < null or null = null
• Three-valued logic using the truth value unknown:
• OR: (unknown or true) = true,
(unknown or false) = unknown
(unknown or unknown) = unknown
• AND: (true and unknown) = unknown,
(false and unknown) = false,
(unknown and unknown) = unknown
• NOT: (not unknown) = unknown
• “P is unknown” evaluates to true if predicate P evaluates to unknown
• Result of where clause predicate is treated as false if it
evaluates to unknown
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
35SQLBI5631 – Database Systems
Null Values and Aggregates
• Example: find the total of all loan amounts
select sum (amount )
from loan
• Above statement ignores null amounts
• Result is null if there is no non-null amount
• All aggregate operations except count(*) ignore
tuples with null values on the aggregated attributes.
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
36SQLBI5631 – Database Systems
Nested subqueries
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
37SQLBI5631 – Database Systems
Nested Subqueries
• SQL provides a mechanism for the nesting of
subqueries.
• A subquery is a select-from-where expression that
is nested within another query.
• A common use of subqueries is to perform tests for
set membership and set comparisons
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
38SQLBI5631 – Database Systems
Example
• Find all customers who have both an account and a
loan at the bank.
• Find all customers who have a loan at the bank but
do not have an account at the bank
select distinct customer_name
from borrower
where customer_name not in (select customer_name
from depositor )
select distinct customer_name
from borrower
where customer_name in (select customer_name
from depositor )
The in connective tests for set membership
The not in connective tests for absence of set membership
Set Membership
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
39SQLBI5631 – Database Systems
Example
• Find all customers who have both an account and a
loan at the Perryridge branch
Note: Above query can be written in a much simpler manner …
select distinct customer_name
from borrower, loan
where borrower.loan_number = loan.loan_number and
branch_name = 'Perryridge' and
(branch_name, customer_name ) in
(select branch_name, customer_name
from depositor, account
where depositor.account_number =
account.account_number )
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
40SQLBI5631 – Database Systems
Set Comparison – some, all
• Find all the branches that have assets greater than
those of at least one branch located in Brooklyn.
• greater than at least one can be written as > some
select branch_name
from branch
where assets > some (select assets
from branch
where branch_city = ‘Brooklyn’)
select distinct T.branch_name
from branch as T, branch as S
where T.assets > S.assets and S.branch_city =
‘Brooklyn’
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
41SQLBI5631 – Database Systems
Example
• Find the names of all branches that have an asset
value greater than that of each branch in Brooklyn.
select branch_name
from branch
where assets > all (select assets
from branch
where branch_city = ‘Brooklyn’)
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
42SQLBI5631 – Database Systems
Complex Queries
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
43SQLBI5631 – Database Systems
This subquery generates
a relation consisting of
the names of all
branches and their
corresponding average
account balances
Derived Relations
• SQL allows a subquery expression to be used in the from clause
Example: find the average account balance of those branches where the
average account balance is greater than $1200.
select branch_name, avg_balance
from (select branch_name, avg (balance)
from account
group by branch_name )
as branch_avg (branch_name, avg_balance )
where avg_balance > 1200
We compute the temporary relation branch_avg in the from clause, and
the attributes of branch_avg can be used directly in the where clause.
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
44SQLBI5631 – Database Systems
The With Clause
• The with clause provides a way of defining a
temporary view whose definition is available only to
the query in which the with clause occurs.
Example: Find all accounts with the maximum
balance
with max_balance (value) as
select max (balance)
from account
select account_number
from account, max_balance
where account.balance = max_balance.value
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
45SQLBI5631 – Database Systems
Joins
The most common reason to combine tables in a query:
•Table 1 contains a reference to something (for
example a unique customer name or ID number)
•Table 2 contains additional information about that
object (name, address, country…)
Example: Combine borrower(LoanNumber,CustomerName) and
customer(CustomerName,CustomerStreet,CustomerCity) to add
information about the borrower’s home city to a list of loans
select LoanNumber, customer.CustomerName,
CustomerCity
from borrower inner join customer
on borrower.CustomerName=customer.CustomerName;
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
46SQLBI5631 – Database Systems
Types of Joins
● Inner join: the most common form. Produces outputs for
values that occur in both tables. The expression
select * from t1 inner join t2 on t1.ID = t2.ID
is equivalent to this expression:
select * from t1, t2 where t1.ID = t2.ID
● Outer (left/right/full) join: Also produce outputs for values
from (first table/second table/both tables) that are missing
in the other, filling up the missing attributes with null values
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
47SQLBI5631 – Database Systems
Joins, comments
● When you do a join on an attribute, that attribute is
usually a foreign key or primary key in each table
● Example borrower/customer: CustomerName is foreign key in
borrower (referencing customer), primary key in customer
● There is a shorthand version, with the using keyword.
The following statements are equivalent:
● select * from borrower inner join customer on
borrower.CustomerID = customer.CustomerID;
● select * from borrower inner join customer using
(CustomerID);
● Joins are less bug-prone than from t1,t2 variant:
● “select * from t1, t2”: Legal statement, which creates
full Cartesian product (with size(t1)*size(t2) rows)
● “select * from t1 inner join t2”: Illegal statement,
since the on or using part is missing
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
48SQLBI5631 – Database Systems
Views
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
49SQLBI5631 – Database Systems
Views
• In some cases, it is not desirable for all users to see the entire logical
model (that is, all the actual relations stored in the database)
• E.g. a person who needs to know a customer’s name, loan number
and branch name, but has no need to see the loan amount. This
person should see a relation described, in SQL, by
select customer_name, borrower.loan_number,
branch_name
from borrower, loan
where borrower.loan_number = loan.loan_number
• A view provides a mechanism to hide certain data from the view of
certain users.
• Any relation that is not of the conceptual model but is made visible to
a user as a “virtual relation” is called a view.
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
50SQLBI5631 – Database Systems
View Definition
• A view is defined using the create view statement
which has the form
create view v as
where
expression. The view name is represented by v.
• Once a view is defined, the view name can be used to
refer to the virtual relation that the view generates.
When a view is created, the query expression is
stored in the database; the expression is
substituted into queries using the view.
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
51SQLBI5631 – Database Systems
Example
• A view consisting of branches and their customers
• Find all customers of the Perryridge branch
create view all_customer as
(select branch_name, customer_name
from depositor, account
where depositor.account_number =
account.account_number )
union
(select branch_name, customer_name
from borrower, loan
where borrower.loan_number = loan.loan_number )
select customer_name
from all_customer
where branch_name = ‘Perryridge’
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
52SQLBI5631 – Database Systems
View implementation
• When we define a view, the database system stores
the definition of the view itself, rather than the result
of evaluating the expression that defines the view.
• Wherever a view relation appears in the query, the
view relation gets re-computed.
• Materialized views and view maintenance
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
53SQLBI5631 – Database Systems
Views Defined Using Other Views
• One view may be used in the expression defining
another view
• A view relation v1 is said to depend directly on a view
relation v2 if v2 is used in the expression defining v1
• A view relation v1 is said to depend on view relation
v2 if either v1 depends directly to v2 or there is a
path of dependencies from v1 to v2
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
54SQLBI5631 – Database Systems
View Expansion
• A way to define the meaning of views defined in terms of
other views.
• Let view v1 be defined by an expression e1 that may itself
contain uses of view relations.
• View expansion of an expression repeats the following
replacement step:
repeat
Find any view relation vi in e1
Replace the view relation vi by the expression defining vi
until no more view relations are present in e1
• As long as the view definitions are not recursive, this loop
will terminate
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
55SQLBI5631 – Database Systems
Database modifications
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
56SQLBI5631 – Database Systems
Deletion
Example: Delete all account tuples at the Perryridge
branch
delete from account
where branch_name = ‘Perryridge’
Example: Delete all accounts at every branch
located in Brooklyn
delete from account
where branch_name in (select branch_name
from branch
where branch_city = ‘Brooklyn’)
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
57SQLBI5631 – Database Systems
Example
• Delete the record of all accounts with balances below
the average at the bank.
delete from account
where balance < (select avg (balance )
from account )
Problem?
as we delete tuples from deposit, the average balance changes
Solution used in SQL:
1. First, compute avg balance and find all tuples to delete
2. Next, delete all tuples found above (without recomputing avg or
retesting the tuples)
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
58SQLBI5631 – Database Systems
Insertion
• Add a new tuple to account
insert into account
values ('A-9732', 'Perryridge', 1200)
or equivalently:
insert into account (branch_name, balance,
account_number)
values ('Perryridge', 1200, 'A-9732')
• Add a new tuple to account with balance set to null
insert into account
values ('A-777','Perryridge', null )
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
59SQLBI5631 – Database Systems
Updates
• Example: Increase all accounts with balances over
$10,000 by 6%, all other accounts receive 5%.
• Write two update statements:
update account
set balance = balance 1.05
where balance 10000
update account
set balance = balance 1.06
where balance > 10000
Problem?
• The order is important. (why?)
• Can be done better using the case statement (next slide)
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
60SQLBI5631 – Database Systems
Case Statement for Conditional Updates
• Same query as before: Increase all accounts with
balances over $10,000 by 6%, all other accounts
receive 5%.
update account
set balance = case
when balance <= 10000 then balance *1.05
else balance * 1.06
end
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
61SQLBI5631 – Database Systems
Updates with Views
• Difficulty: a modification to the database expressed in
terms of a view must be translated to a modification to the
actual relations in the logical model of the database
Example: Create a view of all loan data in the loan relation, hiding the
amount attribute
create view branch_loan as
select loan_number, branch_name
from loan
Add a new tuple to branch_loan
insert into branch_loan
values ('L-37‘, 'Perryridge‘)
This insertion must be represented by the insertion of the tuple ('L-37',
'Perryridge', null ) into the loan relation
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
62SQLBI5631 – Database Systems
• Modifications are generally not permitted on view
relations.
• Most SQL implementations allow updates only on
simple views (without aggregates) defined on a
single relation
• SQL views to which modifications can be applied are
called updatable
Slide 1
Slide 2
Slide 3
Slide 4
Slide 5
Slide 6
Slide 7
Slide 8
Slide 9
Slide 10
Slide 11
Slide 12
Slide 13
Slide 14
Slide 15
Slide 16
Slide 17
Slide 18
Slide 19
Slide 20
Slide 21
Slide 22
Slide 23
Slide 24
Slide 25
Slide 26
Slide 27
Slide 28
Slide 29
Slide 30
Slide 31
Slide 32
Slide 33
Slide 34
Slide 35
Slide 36
Slide 37
Slide 38
Slide 39
Slide 40
Slide 41
Slide 42
Slide 43
Slide 44
Slide 45
Slide 46
Slide 47
Slide 48
Slide 49
Slide 50
Slide 51
Slide 52
Slide 53
Slide 54
Slide 55
Slide 56
Slide 57
Slide 58
Slide 59
Slide 60
Slide 61
Slide 62