06 SQL

Chapter 6
The database Language SQL

Spring 2011
Instructor: Hassan Khosravi
SQL is a very-high-level language, in which the programmer is able to
avoid specifying a lot of data-manipulation details that would be

necessary in languages like C++.
What makes SQL viable is that its queries are optimized quite well,
yielding efficient query executions.

The principal form of a query is:
SELECT desired attributes

FROM one or more tables
WHERE condition about tuples of the tables
SQL introduction
6.2
Simple Queries in SQL

Our SQL queries will be based onthe following database schema.
Movie(title, year, length, inColor, studioName, producerC)
StarsIn(movieTitle, movieYear, starName)
MovieStar(name, address, gender, birthdate)
MovieExec(name, address, cert#, netWorth)
Studio(name, address, cert#, netWorth)
6.3
Simple Queries in SQL

Query all movies produced by Disney Studios in 1990
studioName=DisneyAND year=1990(Movies))
title
SELECT
FROM
WHERE
AND
*
Movies
studioName = Disney
year = 1990;
year length inColor
Pretty
1990 119
Women
true
studioName procucerC#
Disney
6.4
999
Projection in SQL
Find the title and length of all movies produced by Disney Studios
in 1990.
title,length (studioName=DisneyAND year=1990(Movies))
studioName=DisneyAND year=1990title, length ((Movies))

SELECT title, length
FROM
WHERE
AND
Movies
studioName = Disney
year = 1990;
title
length
Pretty Women
119
6.5
Projection in SQL
we can modify the name of attributes. We can change title to name and
length to duration in the previous example.
SELECT title AS name, length AS duration
FROM
WHERE
AND
Movies
studioName = Disney
year = 1990;
We can compute the length in hours

SELECT title AS name,
FROM
WHERE
AND
length/60 AS Length_In_Hours
Movies
studioName = Disney
year = 1990;
6.6
Projection in SQL
SELECT title,
FROM
WHERE
AND
length/60 AS Length
hrs. AS inHours
Movies
studioName = Disney
year = 1990;
title
length
inHours
Pretty Women
1.98334
hrs.
6.7
Selection in SQL
We may build the WHERE part using six common comparison
operators (=, <>, <, >, <=, >=)

Movies made by MGM studios that either were made after 1970 or
were less than 90 minutes long.

SELECT title,
FROM
WHERE
MGM
Movies
( year > 1970 or length <90) AND studioName =
We can compare strings
Dictionary rules.
6.8
Pattern Matching in SQL

Retrieves the titles that starts with Star, then one blank and the 4 last
chars can be anything.

SELECT title
FROM
WHERE
Movies
title LIKE Star _ _ _ _;
So, possible matches can be:
Star War, Star Trek
6.9
Dates and Times

A date constant is represented by the keyword DATE followed by a
quoted string.
For example: DATE 1961-08-24
Note the strict format of the YYYY-mm-dd
6.10
Ordering the Output

To get output in sorted order, we add to the select-from-where statement a
clause:
ORDER BY <list of attributes>
The order is by default ascending (ASC), but we can get the output highest-
first by appending the keyword DESC.

To get the movies listed by length, shortest first, and among movies of equal
length, alphabetically, we can say:

SELECT *
FROM Movie
WHERE studioName = Disney AND year = 1990
ORDER BY length, title;
6.11
QUERIES INVOLVING MORE

THAN ONE RELATION
Products and Joins in SQL

Disambiguating Attributes
Tuple Variables
6.12
Products and Joins in SQL

Suppose we want to know the name of the producer of star wars.
title=StarWarsANDproducerC#=cert#(Movies
SELECT *
FROM
Movies, MovieExec
WHERE
title = Star Wars
AND
producerC# = cert#;
6.13
MovieExec)
Basic Selects
Basics on Selects examples
6.14
Disambiguating Attributes
Sometimes we ask a query involving several relations, with two or
more attributes with the same name.

R.A refers to attribute A of relation R.

SELECT MovieStar.name, MovieExec.name
FROM
MovieStar, MovieExec
WHERE MovieStar.address =
MovieExec.address;
6.15
Tuple Variables
Two stars that share an address
SELECT Star1.name, Star2.name
FROM MovieStar Star1, MovieStar Star2

WHERE Star1.address = Star2.address
AND
Star1.name < Star2.name;
What happens if the second condition is omitted?
6.16
Union, Intersection, and Difference of

Queries
Its possible to use Union, Intersection, and except in SQL queries.
Query the names and addresses of all female movie stars who are
also movie executives with a net worth over $10,000,000

(SELECT name, address FROM MovieStar
WHERE
gender = F)
INTERSECT
(SELECT name, address FROM MovieExec
WHERE
netWorth > 10000000)
6.17

Queries
Query the names and addresses of movie stars who are not movie
executives.
(SELECT name, address FROM MovieStar)
except
(SELECT name, address FROM MovieExec)
6.18

Queries
The two tables most be compatible
Query all the titles and years of movies that appeared in either the
Movies or StarsIn relations.

(SELECT title, year FROM Movies)
UNION
(SELECT movieTitle AS title, movieYear AS year
FROM StarsIn)
6.19
Basic Variables and set operators

Table variables and set operators examples
6.20
Null Values and Comparisons Involving

NULL
Different interpretations for NULL values:
1.
Value unknown
I know there is some value here but I dont know what it is?
1.
2.
Value inapplicable
There is no value that make sense here.
1.
3.
Unknown birth date
Spouse of a single movie star
Value withheld
We are not entitled to know this value.
1.
Telephone number of stars which is known but may be shown as

null
6.21
Null Values and Comparisons Involving

NULL
Two rules
Null plus arithmetic operators is null
When comparing the value of a null if we use = or like the value is

unknown.
We use: x IS NULL or x IS NOT NULL
How unknown operates in logical expressions
If true is considered 1 and false is considred 0, then unknown is

considered 0.5.
And is like min: true and unknown is unknown, false and unknown
is false.
OR is like max: true and unknown is true, false and unknown is

unknown.
Negation is 1 x: negation of unknown is unknown.
6.22
Null Values
Null Values examples
6.23
SUBQUERIES
Subqueries that Produce Scalar Values
Conditions Involving Relations
Conditions Involving Tuples

Correlated Subqueries
Subqueries in From Clauses
SQL Join Expressions

Natural Joins
Outer Joins
6.24

Query the producer of Star Wars.
SELECT name
FROM MovieExec, Movies
WHERE title = Star Wars AND producerC# = cert#

We just need the movie relation only to get the certificate number.
Once we have that we could query the MovieExec for the name.
6.25

use a subquery to get the producerC#
SELECT name
FROM MovieExec
WHERE cert# = (SELECT producerC#
FROM
Movies
WHERE
title = Star Wars
);
What would happen if the subquery retrieve zero or more than one
tuple?
Runtime error
SELECT name
FROM MovieExec
WHERE cert# = 12345
6.26
6.3.2 Conditions Involving Relations

There are a number of SQL operators that can be applied to a relation
R and produces a Boolean result.

EXISTS R is true iff R is not empty.
s IN R is true iff s is equal to one of the values in R.
s > ALL R is true iff s is greater than every value in unary relation R.
Other comparison operators (<, <=, >=, =, <>) can be used.

s > ANY R is true iff s is greater than at least one value in unary relation
R. Other comparison operators (<, <=, >=, =, <>) can be used.
6.27
6.3.2 Conditions Involving Relations

To negate EXISTS, ALL, and ANY operators, put NOT in front of the
entire expression.
NOT EXISTS R, NOT s > ALL R, NOT s > ANY R
s NOT IN R is the negation of IN operator.
Some situations of these operators are equal to other operators.
For example:
s <> ALL R is equal to s NOT IN R

s = ANY R is equal to s IN R
6.28
6.3.3 Conditions Involving Tuples

A tuple in SQL is represented by a parenthesized list of scalar values.
Examples:
(123, I am a string, 0, NULL)

(name, address, salary)
The first example shows all constants and the second shows attributes.
Mixing constants and attributes are allowed.
6.29
6.3.3 Conditions Involving Tuples

(contd)
Example:
('Tom', 'Smith') IN
(SELECT firstName, LastName

FROM
foo);
Note that the order of the attributes must be the same in the tuple and the
SELECT list.
6.30

Example 6.20:
Query all the producers of movies in which LEONARDO DICAPRIO

stars.
Movie(title, year, length, inColor, studioName, producerC (movieTitle,
movieYear, starName)
Studio(name, address, cert#, netWorth)
SELECT name, cert#

); FROM
WHERE
MovieExec;
cert# IN
(SELECT producerC#
FROM
Movies
WHERE
(title, year) IN
(SELECT movieTitle, movieYear

FROM
StarsIN
WHERE
starName = 'LEONARDO DICAPRIO')

6.31

Note that sometimes, you can get the same result without the expensive
subqueries.
For example, the previous query can be written as follows:
SELECT name
FROM
MovieExec, Movies, StarsIN
WHERE
cert# = producerC#
AND
title = movieTitle
AND
year
And
starName = 'LEONARDO DICAPRIO';
= movieYear
6.32
Correlated Subqueries
The simplest subquery is evaluated once and the result is used in a
higher-level query.
Some times a subquery is required to be evaluated several times, once
for each assignment of a value that comes from a tuple variable outside
the subquery.
A subquery of this type is called correlated subquery.
6.33
Correlated Subqueries (cont'd)

Query the titles that have been used for two or more movies.
SELECT title
FROM
Movies old
WHERE
year < ANY

(SELECT year
FROM
Movies
WHERE
title = old.title);
Start with the inner query
If old.title was a constant this would have made total sense
Where title = king kong
Nested loop.
For each value of old title we run the the nested subquery
6.34
Subqueries
Subqueries by Dr. Widom
6.35
SELECT A1, An
FROM R1, . Rm
WHERE condition up to now we have used sub-query
SELECT A1, An use sub-query to generate an attribute

FROM R1, . Rm use sub-query to generate a table to
condition
WHERE condition
6.36

In a FROM list, we my use a parenthesized subquery.
The subquery must have a tuple variable or alias.
Query the producers of LEONARDO DICAPRIOs movies.

We can write a subquery that produces a new table that can be called in
the from part of the query.
Select name
FROM
MovieExec,
(SELECT producerC#
FROM
Movies, StarsIN
WHERE
title = movieTitle
AND
year
AND
starName = 'LEONARDO DICAPRIO'
= movieYear
) Prod
WHERE
cert# = Prod.producerC#;
6.37
Subqueries
Subqueries in From Clauses examples
6.38

Join operators construct new temp relations from existing relations.
These relations can be used in any part of the query that you can put a
subquery.
Cross join is the simplest form of a join.
Actually, this is synonym for Cartesian product.
For example:
From Movies CROSS JOIN StarsIn

is equal to:
From Movies, StarsIn
6.39

If the relations we used are:
Movies(title, year, length, genre, studioName, producerC#)
Then the result of the CROSS JOIN would be a relation with the
following attributes:
R(title, year, length, genre, studioName, producerC#, movieTitle,

movieYear, starName)
Note that if there is a common name in the two relations, then the
attributes names would be qualified with the relation name.
6.40

Cross join by itself is rarely a useful operation.
Usually, a theta-join is used as follows:
FROM R JOIN S ON condition

For example:
Movies JOIN StarsIn ON

title = movieTitle AND
year = movieYear
The result would be the same number of attributes but the tuples would
be those that agree on both the title and year.
6.41

Note that in the previous example, the title and year are repeated twice.
Once as title and year and once as movieTitle and movieYear.

Considering the point that the resulting tuples have the same value for
title and movieTitle, and year and movieYear, then we encounter the
redundancy of information.
One way to remove the unnecessary attributes is projection. You can
mention the attributes names in the SELECT list.
6.42
Natural Joins
Natural join and theta-join differs in:
1.
The join condition

All pairs of attributes from the two relations having a common
name are equated, and also there are no other conditions.
2.
The attributes list

One of each pair of equated attributes is projected out.
Example
MovieStar NATURAL JOIN MovieExec
6.43
Natural Joins
Query those stars who are executive as well.
The relations are:
SELECT MovieStar.name
FROM MovieStar NATURAL JOIN MovieExec
6.44
Outer Joins
Outer join is a way to augment the result of a join by dangling tuples,
padded with null values.

Example 6.25
Consider the following relations:

MovieExec(name, address, cert#, netWorth) Then
MovieStar NATURAL FULL OUTER JOIN MovieExec
Will produce a relation whose tuples are of 3 kinds:
1.
Those who are both movie stars and executive
2.
Those who are movie star but not executive
3.
Those who are executive but not movie star
6.45
Outer Joins (cont'd)

We can replace keyword FULL with LEFT or RIGHT to get two new
join.
NATURAL LEFT OUTER JOIN
would yield the first two tuples but not
the third.
NATURAL RIGHT OUTER JOIN
would yield the first and third tuples
but not the second.
We can have theta-outer-join as follows:
R FULL OUTER JOIN S ON condition
R LEFT OUTER JOIN S ON condition
R RIGHT OUTER JOIN S ON condition
6.46
FULL-RELATION OPERATIONS
Eliminating Duplicates
Duplicates in Unions, Intersections, and Differences
Grouping and Aggregation in SQL
Aggregation Operators
Grouping
Grouping, Aggregation, and Nulls
Having Clauses
Exercises for Section 6.4
47
6.47
Eliminating Duplicates
Query all the producers of movies in which LEONARDO DICAPRIO stars.
SELECT DISTINCT name
FROM
MovieExec, Movies, StarsIN
WHERE
cer# = producerC#
AND
title = movieTitle
AND
year
And
starName = LEONARDO DICAPRIO';
= movieYear
6.48
Duplicates in Unions, Intersections,

and Differences
Duplicate tuples are eliminated in UNION, INTERSECT, and EXCEPT.
In other words, bags are converted to sets.
If you don't want this conversion, use keyword ALL after the operators.
(SELECT title, year FROM Movies)

UNION ALL
(SELECT movieTitle AS title, movieYear AS year FROM
StarsIn);
6.49
Grouping and Aggregation in SQL

We can partition the tuples of a relation into "groups" based on the
values of one or more attributes. The relation can be an output of a

SELECT statement.
Then, we can aggregate the other attributes using aggregation
operators.
For example, we can sum up the salary of the employees of each
department by grouping the company into departments.
6.50
SQL uses the five aggregation operators:
SUM, AVG, MIN, MAX, and COUNT

These operators can be applied to scalar expressions, typically, a
column name.
One exception is COUNT(*) which counts all the tuples of a query
output.
We can eliminate the duplicate values before applying aggregation
operators by using DISTINCT keyword. For example:

COUNT(DISTINCT x)
Find the average net worth of all movie executives.
SELECT AVG(netWorth)
FROM
MovieExec;
6.51
Count the number of tuples in the StarsIn relation.
SELECT COUNT(*)
FROM
StarsIn;
SELECT COUNT(starName)
FROM
StarsIn;
These two statements do the same but you will see the difference in later
slides.
6.52
Grouping
We can group the tuples by using GROUP BY clause following the
WHERE clause.
The keywords GROUP BY are followed by a list of grouping attributes.
Find sum of the movies length each studio is produced.

SELECT
studioName,
SUM(length) AS Total_Length
FROM
Movies
GROUP BY studioName;
6.53
Grouping
In a SELECT clause that has aggregation, only those attributes that are
mentioned in the GROUP BY clause may appear unaggregated.

For example, in previous example, if you want to add genre in the
SELECT list, then, you must mention it in the GROUP BY list as well.
SELECT
studioName, genre,
SUM(length) AS Total_Length
FROM
Movies
GROUP BY studioName, genre;
6.54
Grouping
It is possible to use GROUP BY in a more complex queries about
several relations.
In these cases the following steps are applied:

1.
Produce the output relation based on the

select-from-where parts.
2.
Group the tuples according to the list of attributes mentioned in the

GROUP BY list.
3.
Apply the aggregation operators
Create a list of each producer name and the total length of film produced.
SELECT name, SUM(length)
FROM
MovieExec, Movies
WHERE
producerC# = cert#
GROUP BY name;
6.55

What would happen to aggregation operators if the attributes have null
values?
There are a few rules to remember
1.
NULL values are ignored when the aggregation operator is

applied on an attribute.
2.
COUNT(*) counts all tuples of a relation, therefore, it counts the

tuples even if the tuple contains NULL value.
3.
NULL is treated as an ordinary value when forming groups.
4.
When we perform an aggregation, except COUNT, over an empty

bag, the result is NULL. The COUNT of an empty bag is 0
6.56

Consider a relation R(A, B) with one tuple, both of whose components are
NULL. What's the result of the following SELECT?
SELECT A, COUNT(B)
FROM
GROUP BY A;
The result is (NULL, 0) but why?

What's the result of the following SELECT?
SELECT A, COUNT(*)
FROM
GROUP BY A;
The result is (NULL, 1) because COUNT(*) counts the number of tuples

and this relation has one tuple.
6.57

What's the result of the following SELECT?
SELECT A, SUM(B)
FROM
GROUP BY A;
The result is (NULL, NULL) because SUM(B) address one NULL value
which is NULL.
6.58
HAVING Clauses
So far, we have learned how to restrict tuples from contributing in the
output of a query.
How about if we don't want to list all groups?
HAVING clause is used to restrict groups.
HAVING clause followed by one or more conditions about the group.
Query the total film length for only those producers who made at least one
film prior to 1930.
SELECT name, SUM(length)
FROM
MovieExec, Movies
WHERE
producerC# = cert#
GROUP BY name
HAVING MIN(year) < 1930;
6.59
HAVING Clauses
The rules we should remember about HAVING:
1.
An aggregation in a HAVING clause applies only to the tuples of

the group being tested.
2.
Any attribute of relations in the FROM clause may be aggregated

in the HAVING clause, but only those attributes that are in the
GROUP BY list may appear unaggregated in the HAVING clause
(the same rule as for the SELECT clause).
6.60
HAVING Clauses
The order of clauses in SQL queries would be:
SELECT
FROM
WHERE
GROUP BY
HAVING
Only SELECT and FROM are mandatory.

There is one important difference between SQL HAVING and SQL
WHERE clauses. The SQL WHERE clause condition is tested against

each and every row of data, while the SQL HAVING clause condition is
tested against the groups and/or aggregates specified in the SQL
GROUP BY clause and/or the SQL SELECT column list.
6.61
DATABASE MODIFICATIONS
Insertion
Deletion
Updates
6.62
Insertion
The syntax of INSERT statement:
INSERT INTO R(A1, ..., AN)

VALUES (v1, ..., vn);
If the list of attributes doesn't include all attributes, then it put default
values for the missing attributes.
6.63
Insertion
If we are sure about the order of the attributes, then we can write
the statement as follows:
INSERT INTO StarsIn

VALUES ('The Maltese Falcon', 1942, 'Sydney
Greenstreet');
If not
INSERT INTO StarsIn(MovieTitle, movieYear,
starName)
VALUES ('The Maltese Falcon', 1942, 'Sydney
Greenstreet');
6.64
Insertion
The simple insert can insert only one tuple, however, if you want to
insert multiple tuples , then you can use the following syntax:
INSERT INTO R(A1, ..., AN)
SELECT v1, ..., vn
FROM
R1, R2, ..., RN
WHERE <condition>;
Suppose that we want to insert all studio names that are mentioned in
the Movies relation but they are not in the Studio yet.
INSERT INTO Studio(name)

SELECT studioName
FROM
Movies
WHERE studionName NOT IN
(SELECT name
FROM Studio);
6.65
Deletion
The syntax of DELETE statement:
DELETE FROM R
WHERE <condition>;
Every tuples satisfying the condition will be deleted from the relation R.
DELETE FROM StarsIn

WHERE
movieTitle = 'The Maltese Falcon' AND

movieYear = 1942 AND
starName = 'Sydney Greenstreet';

Delete all movie executives whose net worth is less than ten million
dollars.
DELETE FROM MovieExec

WHERE
netWorth < 10000000;

6.66
Updates
The syntax of UPDATE statement:
UPDATE R
SET
<value-assignment>
WHERE <condition>;
Every tuples satisfying the condition will be updated from the relation R.
If there are more than one value-assignment, we should separate them
with comma.
Attach the title 'Pres.' in front of the name of every movie executive who is
the president of a studio.
UPDATE MovieExec
SET name = 'Pres.' || name
WHERE
cert# IN (SELECT presC# FROM Studio);
6.67
TRANSACTIONS IN SQL
Serializability
Atomicity
Transactions
Read-Only Transactions
Dirty Reads
Other Isolation Levels
Exercises for Section 6.6
6.68
6.6 Transactions in SQL

Up to this point, we assumed that:
the SQL operations are done by one user.
The operations are done one at a time.
There is no hardware/software failure in middle of a database

modification. Therefore, the operations are done atomically.
In Real life, situations are totally different.
There are millions of users using the same database and it is possible
to have some concurrent operations on one tuple.
6.69
6.6.1 Serializability
In applications like web services, banking, or airline reservations,
hundreds to thousands operations per second are done on one

database.
It's quite possible to have two or more operations affecting the same,
let's say, bank account.

If these operations overlap in time, then they may act in a strange way.
Let's take an example.
6.70
6.6.1 Serializability (cont'd)

Example 6.40
Consider an airline reservation web application. Users can book their
desired seat by themselves.
The application is using the following schema:
Flights(fltNo, fltDate, seatNo, seatStatus)

When a user requests the available seats for the flight no 123 on date
2011-12-15, the following query is issued:
71
6.71

SELECT seatNo
FROM
Flights
WHERE
fltNo = 123 AND
fltDate = DATE '2011-12-25' AND

seatStatus = 'available';
When the customer clicks on the seat# 22A, the seat status is changed by
the following SQL:
UPDATE Flights
SET
seatStatus = 'occupied'
WHERE
fltNo = 123 AND
fltDate = DATE '2011-12-25' AND

seatNo = '22A';
6.72

What would happen if two users at the same time click on the reserve
button for the same seat#?

Both see the same seats available and both reserve the same seat.
To prevent these happen, SQL has some solutions.
We group a set of operations that need to be performed together. This
is called 'transaction'.
6.73

For example, the query and the update in example 6.40 can be
grouped in a transaction.
SQL allows the programmer to state that a certain transaction must be
serializable with respect to other transactions.

That is, these transactions must behave as if they were run serially,
one at a time with no overlap.
6.74
6.6.2 Atomicity
What would happen if a transaction consisting of two operations is in
progress and after the first operation is done, the database and/or
network crashes?
Let's take an example.
6.75
6.6.2 Atomicity (cont'd)

Example 6.41
Consider a bank's account records system with the following relation:
Accounts(acctNo, balance)
Let's suppose that $100 is going to transfer from acctNo 123 to acctNo
456.
To do this, the following two steps should be done:
1.
Add $100 to account# 456
2.
Subtract $100 from account# 123.
6.76

The needed SQL statements are as follows:
UPDATE Accounts
SET balance = balance + 100
WHERE acctNo = 456;
UPDATE Accounts
SET balance = balance - 100
WHERE acctNo = 123;
What would happen if right after the first operation, the database crashes?
6.77

The problem addressed by example 6.41 is that certain combinations of
operations need to be done atomically.

That is, either they are both done or neither is done.
6.78
6.6.3 Transactions
The solution to the problems of serialization and atomicity is to group
database operations into transactions.

A transaction is a set of one or more operations on the database that
must be executed atomically and in a serializable manner.

To create a transation, we use the following SQL command:
START TRANSACTION
6.79
6.6.3 Transactions (cont'd)

There are two ways to end a transaction:
1.
The SQL receives COMMIT command.
2.
The SQL receives ROLLBACK command.
COMMIT command causes all changes become permanent in the

database.
ROLLBACK command causes all changes undone.
6.80
6.6.4 Read-Only Transactions

We saw that when a transaction read a data and then want to write
something, is prone to serialization problems.

When a transaction only reads data and does not write data, we have
more freedom to let the transaction execute in parallel with other

transactions.
We call these transactions read-only.
6.81
6.6.4 Read-Only Transactions (cont'd)

Example 6.43
Suppose we want to read data from the Flights relation of example 6.40 to
determine whether a certain seat was available?
What's the worst thing that can happen?
When we query the availability of a certain seat, that seat was being
booked or was being released by the execution of some other program.
Then we get the wrong answer.
6.82

If we tell the SQL that our current transaction is read-only, then SQL
allows our transaction be executed with other read-only transactions in

parallel.
The syntax of SQL command for read-only setting:
SET TRANSACTION READ ONLY;

We put this statement before our read-only transaction.
6.83

The syntax of SQL command for read-write setting:
SET TRANSACTION READ WRITE;

We put this statement before our read-write transaction.
This option is the default.
84
6.84
6.6.5 Dirty Reads

The data that is written but not committed yet is called dirty data.
A dirty read is a read of dirty data written by another transaction.
The risk in reading dirty data is that the transaction that wrote it never
commit it.
Sometimes dirty read doesnt matter much and is not worth
The time consuming work by the DBMS that is needed to prevent

data reads
The loss of parallelism that results from waiting until there is no

possibility of a dirty read
6.85
6.6.5 Dirty Reads (cont'd)

Example 6.44
Consider the account transfer of example 6.41.
Here are the steps:
1.
Add money to account 2.
2.
Test if account 1 has enough money?

a.
If there is not enough money, remove the money from

account 2 and end.
b.
If there is, subtract the money from account 1 and end.
Imagine, there are 3 accounts A1, A2, and A3 with $100, $200, and $300.
86
6.86

Let's suppose:
Transaction T1 transfers $150 from A1 to A2
Transaction T2 transfers $250 from A2 to A3
What would happen if the dirty read is allowed?
T2 executes step (1) adds 250 to A3 which now has 550
T1 executes step (1) adds 150 to A2 which now has 350
T2 executes step (2), A2 has enough fund
T1 executes step (2) A1 doesnt have enough fund
T2 executes step (2b) and leaves A2 with $100
T1 executes step (2a) and leaves A1 with $-50
How important is it in the reservation scenario?
87
6.87

The syntax of SQL command for dirty-read setting:
SET TRANSACTION READ WRITE

ISOLATION LEVEL READ UNCOMMITTED;
We put this statement before our read-write transaction.
This option is the default.
88
6.88
6.6.6 Other Isolation Levels

There are four isolation level.
We have seen the first two before.
Serializable (default)
Read-uncommitted
Read-committed
Syntax:
SET TRANSACTION
ISOLATION LEVEL READ COMMITTED;
89
6.89
6.6.6 Other Isolation Levels (cont'd)

For each the default is 'READ WRITE' (except the isolation READ
UNCOMMITTED that the default is 'READ ONLY') and if you want

'READ ONLY', you should mention it explicitly.
The default isolation level is 'SERIALIZABLE'.
Note that if a transaction T is acting in 'SERIALIZABLE' level and the
other one is acting in 'READ UNCOMMITTED' level, then this

transaction can see the dirty data of T. It means that each one acts
based on their level.
90
6.90

Under READ COMMITTED isolation, it forbids reading the dirty data.
But it does not guarantee that if we issue several queries, we get the
same tuples.
That's because there may be some new committed tuples by other
transactions.
The query may show more tuples because of the phantom tuples.
A phantom tuple is a tuple that is inserted by other transactions.
91
6.91

Example 6.46
Let's consider the seat choosing problem under 'READ COMMITTED'
isolation.
Your query won't see seat as available if another transaction reserved it
but not committed yet.
You may see different set of seats in subsequent queries depends on if
the other transactions commit their reservations or rollback them.
92
6.92

Properties of SQL isolation levels
Isolation
Level
Dirty Read
Phantom
Read
Uncommitted
Read
Committed
Serializable
95
6.95

06 SQL

Uploaded by

Copyright:

Available Formats

06 SQL

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

06 SQL

Uploaded by

Copyright:

Available Formats

Chapter 6

The database Language SQL

Instructor: Hassan Khosravi

SQL is a very-high-level language, in which the programmer is able to

avoid specifying a lot of data-manipulation details that would be

yielding efficient query executions.

SELECT desired attributes

Simple Queries in SQL

Simple Queries in SQL

year length inColor

title,length (studioName=DisneyAND year=1990(Movies))

studioName=DisneyAND year=1990title, length ((Movies))

We can compute the length in hours

operators (=, <>, <, >, <=, >=)

were less than 90 minutes long.

We can compare strings

Pattern Matching in SQL

chars can be anything.

So, possible matches can be:

Star War, Star Trek

Dates and Times

Ordering the Output

first by appending the keyword DESC.

length, alphabetically, we can say:

QUERIES INVOLVING MORE

Products and Joins in SQL

Products and Joins in SQL

title = Star Wars

more attributes with the same name.

MovieStar(name, address, gender, birthdate)

SELECT MovieStar.name, MovieExec.name

FROM MovieStar Star1, MovieStar Star2

Star1.name < Star2.name;

What happens if the second condition is omitted?

Union, Intersection, and Difference of

also movie executives with a net worth over $10,000,000

netWorth > 10000000)

Union, Intersection, and Difference of

Union, Intersection, and Difference of

Movies or StarsIn relations.

Basic Variables and set operators

Null Values and Comparisons Involving

Unknown birth date

Spouse of a single movie star

Telephone number of stars which is known but may be shown as

Null Values and Comparisons Involving

Null plus arithmetic operators is null

When comparing the value of a null if we use = or like the value is

We use: x IS NULL or x IS NOT NULL

How unknown operates in logical expressions

If true is considered 1 and false is considred 0, then unknown is

OR is like max: true and unknown is true, false and unknown is

Negation is 1 x: negation of unknown is unknown.

Conditions Involving Tuples

SQL Join Expressions

Subqueries that Produce Scalar Values

WHERE title = Star Wars AND producerC# = cert#

Subqueries that Produce Scalar Values