1 of 71

��SQL: Structured Query Language�(SQL Basics) �

Professor Sharad Mehrotra

UC Irvine

2 of 71

SQL in Different Roles

Data definition language:

Allows users to describe the relations and constraints.

Query language:

Relationally complete, supports aggregation and grouping

Updates in SQL:

Allows users to insert, delete and modify tables

Constraint specification language:

Commands to specify constraints ensured by DBMS

View definition language:

Commands to define views

Using SQL from within programming languages

Has been embedded in a variety of host languages--C, C++, PERL, Smalltalk, Java (vendor dependent)

Transaction Control:

Commands to specify beginning and end of transactions.

Covered earlier

Next topic

Later

Covered in CS 122b

Covered in CS 223

3 of 71

Sample Schema

Patients(name, age) -- patients who currently have some disease

RecoveredPatients(name, age) -- patients who have recovered from the disease they had. They may still have other diseases in which case they will also be part of the Patients table as well.

Diagnosis(pname, disease, diagnosis_date) -- the diagnosis made about patients on a given date.

ToDiagnose(disease, test) -- tests used to diagnose the disease

Outcomes(pname, test, result, test_date) -- the result of the test when conducted on the patient pid on the given date.

3

4 of 71

SQL as a Query Language

4

select A1, A2, …, An� from R1, R2, …, Rm

where <conditions>;

Examples:

Query 1: Find name of patients who have been diagnosed with Flu

select pname

from Diagnosis

where disease = ‘Flu’;

Query 2: List tests that can be used to for Flu

select test

from ToDiagnose

where disease = ‘Flu’

5 of 71

SQL vs Relational Algebra

5

Difference:

Relational algebra uses set semantics
Most SQL operators uses bag semantics
However, SQL set operators use set semantics

SELECT A1, A2, …, An� FROM R1, R2, …, Rm

WHERE <conditions>;

Π_A1,…,An(σ_cond(R1 × R2 × … Rm))

~

=

6 of 71

“Select” clause

Specify attributes to project onto

different from the “selection” operator in the relational algebra)

Can use relation prefix

especially when we need to disambiguate attribute names

Use star * to denote all attributes:

SELECT *

FROM Patients

WHERE age > 18

Will return <name, age> of all the patients over 18 years of age

6

7 of 71

Eliminate duplicates

“SELECT” does not automatically eliminate duplicates.

Select pname

From Diagnosis;

If there are more than 1 record for Mary, she will show up multiple times in the result.

Use keyword distinct to explicitly remove duplicates

Select distinct pname

From Diagnosis;

7

8 of 71

The select Clause (Cont.)

An asterisk in the select clause denotes “all attributes”

select *� from Diagnosis

An attribute can be a literal with no from clause

select "Mary"

Results is a table with one column and a single row with value “Mary”
Can give the column a name using:

select ‘Mary’ as FOO

An attribute can be a literal with from clause

select "A"� from Diagnosis

Result is a table with one column and N rows (number of tuples in the Diagnosis table), each row with value “A”

©Silberschatz, Korth and Sudarshan

3.8

Database System Concepts - 6^th Edition

9 of 71

The select Clause (Cont.)

The select clause can contain arithmetic expressions involving the operation, +, –, *, and /, and operating on constants or attributes of tuples.

The query:

select name, age+3� from Patients

would return a relation that is the same as the Patients relation, except that the value of the attribute age replaced by age+3

Can rename “age+3” using the as clause:

select name, age+3 as future_age�

©Silberschatz, Korth and Sudarshan

3.9

Database System Concepts - 6^th Edition

10 of 71

“FROM” clause

Specify relations
Renaming relations:

Use “as” to define “tuple variables,” to disambiguate multiple references to the same relation

“list all patients except the oldest”

SELECT distinct P1.name

FROM Patients as P1, Patients as P2

WHERE P1.age < P2.age

10

11 of 71

“WHERE” clause

Specify conditions
Optional
Comparison operators: =, !=, <, >, <=, >=
Complicated conditions:

AND, OR, NOT, …
“names of patient who have had a positive outcome to test A”

SELECT pname

FROM Outcomes

WHERE test = “A” AND result = true;

String patterns:

“s LIKE p”: string s matches pattern p
Percent %: zero, one, or multiple occurrences of any character

dname LIKE ‘TOM %’
‘TOM KERRY’, ‘TOM JOHNSON’ …

underbar _: one-character wildcard

dname LIKE ‘a _ c’
‘abc’ ‘adc’ ‘azc’ ‘a9c’ …

11

12 of 71

Conditions in a “WHERE” clause

Operations on strings (e.g., “||” for concatenation).
Lexicographic order on strings.
Special operations for comparing dates and times.

Use relation prefix to disambiguate attribute names

SELECT Patients.name

FROM Patients, Diagnosis

WHERE Patients.name = Diagnosis.pname;

12

13 of 71

“WHERE” Clause Predicates

SQL includes a “between” comparison operator
Example:

select * from Outcomes

where test_date between '2020-01-01' and '2021-01-01';

Tuple comparison

select Patients.name, RecoveredPatients.name

from Patients, RecoveredPatients

where (Patients.name, Patients.age) = (RecoveredPatients.name, RecoveredPatients.age);

13

14 of 71

Ordering output tuples

select *

from Outcomes

order by result, test, pname desc;

Order the tuples by

result (0 before 1 - since default is ascending).
Within each result, order by test (ascending, A before B..), and
within that order by name descending (Mary before Alex)

14

15 of 71

Set Operations

Use the set semantics - automatically eliminate duplicates
Union: ∪. “names of people who are either patients or recovered patients”

(select name from Patients)

union

(select name from RecoveredPatients);

Intersect: ∩. ← Not in MySQL

(select name from Patients)

intersect

(select name from RecoveredPatients);

Except: -. ← Not in MySQL

(select name from Patients)

except

(select name from RecoveredPatients);

15

16 of 71

Example Query with Set Operations

16

Relations: R(A), S(A), T(A)

R = {1,2, 4 } S = {1,3} T {1,4,6} {1,4}

All values that are in R and also either in S or in T

Query: “R ∩ (S ∪ T)”

select * from R intersect

((select * from S) union (select * from T))

R

S

T

17 of 71

Could we write it as a SPJ Query?

17

Relations: R(A), S(A), T(A)

R = {1,2, 4 } S = {1,3} T {1,4,6} {1,4}

All values that are in R and also either in S or in T

SELECT distinct R.A

FROM R, S, T

WHERE R.A=S.A OR R.A=T.A;

But is this correct?

R

S

T

18 of 71

Unintuitive SQL query

18

Relations: R(A), S(A), T(A)

Query: “R ∩ (S ∪ T)”

SELECT R.A

FROM R, S, T

WHERE R.A=S.A OR R.A=T.A;

But what happens if T is empty?

The SQL result becomes empty
Be careful when you translate a relational algebra expression to SQL

E.g., R= {1,2, 4} S = {1,2,4} answer we want {1,2,4}, answer we get is empty

R

S

T

19 of 71

Conserving Duplicates

19

To keep duplicates, use “ALL” after the operators:

UNION ALL, INTERSECT ALL, EXCEPT ALL
Example:

(select name from Patients)

union all

(select name from RecoveredPatients);

20 of 71

Set Operations (Cont.)

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

20

21 of 71

Aggregations

MIN, MAX, SUM, COUNT, AVG

input: collection of numbers/strings (depending on operation)
output: relation with a single attribute with a single row

Example:

select min(age), max(age), avg(age) from Patients;

21

22 of 71

Aggregations (cont)

Except “count,” all aggregations apply to a single attribute
“Count” can be used on more than one attribute, even “*”

SELECT Count(*) FROM Patients;

SELECT Count(name) FROM Patients;

22

23 of 71

Duplication in aggregations

“What is the number of different tests in the Outcome table”

Select count(test)

From Outcomes

Is this correct?

23

'Al','G','1','2020-02-16'

'Alex','A','1','2020-01-03'

'Alex','G','1','2020-01-03'

'Barbara','G','0','2020-02-10'

'Bob','C','1','2021-02-01'

'Jane','B','1','2021-01-01'

'Jane','C','0','2021-01-01'

'Jane','F','0','2021-01-01'

'Mary','A','1','2020-01-01'

'Mary','A','1','2021-01-01'

'Mary','B','0','2021-01-01'

'Mary','D','1','2021-01-01'

'Mehdi',' F','1','2021-01-02'

'Mehdi','E','0','2021-01-02'

'Mehdi','E','1','2021-01-02'

24 of 71

Duplication in aggregations

Right query:

select count(distinct test)

from Outcomes;

24

25 of 71

Group By clause

Group by used to apply aggregate function to a group of sets of tuples. Aggregate applied to each group separately.

Example: For each disease diagnosis, list its total number of patients

select disease,count(pname)

from Diagnosis

group by disease;

25

26 of 71

Group By clause

Group by used to apply aggregate function to a group of sets of tuples. Aggregate applied to each group separately.

Example: For each person, list total number tests performed on that person�

select pname, count(*)

from Outcomes

group by pname;

26

27 of 71

Group By clause (cont)*

Attributes in the SELECT clause (other than the ones which are being aggregated) MUST be in the GROUP BY Clause

Later SQL standard allows for additional attributes, if they are functionally dependent upon the group by attributes. Notice that in such a case there a single value for the attribute

select Outcomes.pname, count(*), age

from Outcomes, Patients

group by pname;

27

14:04:30 select Outcomes.pname, count(*), age from Outcomes, Patients group by pname LIMIT 0, 1000 Error Code: 1055. Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'patientdb.Patients.age' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 0.00032 sec

28 of 71

Group By clause (cont)

The following query:

SELECT name

FROM Patients

GROUP BY name;

Will have the same result as:

SELECT DISTINCT name

FROM Patients;�

28

29 of 71

Having Clause

Having clause used along with group by clause to select some groups.
Predicate in having clause applied after the formation of groups.

select pname, count(*)

from Outcomes

group by pname

having count(*) > 3

29

30 of 71

A general SQL query

30

List all tests for which there are at least 2 patients have had a positive and a negative result

SELECT O1.test, count(*)

FROM Outcomes AS O1, Outcomes AS O2

WHERE O1.test = O2.test AND

O1.result = true AND

O2.result = false AND

O1.pname = O2.pname

GROUP BY O1.test

HAVING count(*) > 1

ORDER BY by O1.test;

31 of 71

A general SQL query (cont)

31

SELECT O1.test, count(*) ---------(5)

FROM Outcomes AS O1, Outcomes AS O2 -- (1)

WHERE O1.test = O2.test AND ----- ---- (2)

O1.result = true AND

O2.result = false AND

O1.pname = O2.pname

GROUP BY O1.test ---------(3)

HAVING count(*) > 1 ----------(4)

ORDER BY by O1.test; ----------(6)

Execution steps:

Step 1: tuples are formed (Cartesian product)

Step 2: tuples satisfying the conditions are chosen (selection)

Step 3: groups are formed (grouping)

Step 4: groups are eliminated using “Having” (selection applied on groups)

Step 5: the aggregates are computed for the select line, flattening the groups

Step 6: the output tuples are ordered and printed out.

32 of 71

Nulls in SQL

32

33 of 71

NULL Values in SQL

NULL is a special value for representing data that we don’t have (but: NULL is not a constant)

At least three different interpretations

Unknown: there is a value that makes sense here, we just don’t know it

e.g., unknown birth date

Inapplicable: no value makes sense here

e.g., NULL value in spouse column for unmarried person

Withheld: we are not entitled to know the value that belongs here

e.g., unlisted phone number

33

34 of 71

Using NULL Values

Operations (*,+,-,/) on NULL

NULL + 3 → NULL
NULL – NULL → NULL
0 * NULL → NULL

Comparisons (=, >, <, etc.) on NULL

NULL > 5 → UNKNOWN
NULL = NULL → UNKNOWN

Three-valued logic (true, false, unknown)

34

35 of 71

Truth Table for Three-valued Logic

One suggested way to remember the rules (though they are very intuitive, IMO):

TRUE = 1, FALSE = 0, UNKNOWN = 0.5
x AND y = min(x,y)
x OR y = max(x,y)
NOT x = 1 - x

35

TRUE

FALSE

x

y

x AND y

x OR y

NOT x

TRUE

UNKNOWN

TRUE

FALSE

TRUE

FALSE

TRUE

FALSE

UNKNOWN

TRUE

UNKNOWN

TRUE

UNKNOWN

FALSE

False

UNKNOWN

FALSE

TRUE

FALSE

TRUE

FALSE

UNKNOWN

FALSE

UNKNOWN

TRUE

FALSE

TRUE

36 of 71

NULL is Not a SQL Constant

To check if a value x is a NULL

Cannot use: “x = NULL”!

(I.e., NULL is not a value, it’s the state of a value)

Use “x IS NULL” in predicates instead

Produces TRUE if x has value NULL
Produces FALSE otherwise

36

37 of 71

NULL Examples

SELECT *

FROM Patients

WHERE age IS NULL;

37

Patients Table

38 of 71

NULL Examples (cont.)

SELECT *

FROM Patients

WHERE age > 40;

38

(Only tuples for which condition evaluates to TRUE become part of answer!)

!!!

SELECT *

FROM Patients

WHERE age <= 40

OR age >= 40;

39 of 71

Null Values and Aggregates

39

Total all age

select avg (age )

from Patients

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
What if collection has only null values?

count returns 0
All other aggregates return null

40 of 71

SQL: Subqueries, Joins, �Modifications.

40

41 of 71

Nested Subqueries

Subqueries are embedded/nested inside outer queries.
SQL provides a mechanism for the nesting of subqueries. A subquery is a select-from-where expression that is nested within another query.
The nesting can be done in the following SQL query�� select A₁, A₂, ..., A_n� from r₁, r₂, ..., r_m� where P

�as follows:

A_ican be replaced be a subquery that generates a single value.
r_i can be replaced by any valid subquery
P can be replaced with an expression of the form:

[B] <operation> (subquery)

©Silberschatz, Korth and Sudarshan

3.41

Database System Concepts - 6^th Edition

42 of 71

Subqueries in WHERE Clause

Example: Who has same disease as Mehdi

42

SELECT pname

FROM Diagnosis

WHERE disease IN (

SELECT disease

FROM Diagnosis

WHERE pname = 'Mehdi'

);

SELECT D1.pname

FROM Diagnosis AS D1, Diagnosis AS D2

WHERE D1.disease = D2.disease

AND D2.pname = 'Mehdi';

Semantics:

A nested query returns a relation containing disease that Mehdi has

for each tuple in Diagnosis, evaluate the nested query and check if D.disease appears in the set of diseases returned by nested query.

43 of 71

Conditions involving Relations

43

Usually subqueries produce a relation as an answer.

Conditions involving relations:

s > ALL (R) -- s is greater than every value in unary relation R
s IN (R) -- s is equal to one of the values in R
s > ANY (R), s > SOME R -- s is greater than at least 1 element in unary relation R.

any is a synonym of some in SQL

EXISTS (R) -- R is not empty.

Other operators (<, = , <=, >=, <>) could be used instead of >.
EXISTS, ALL, ANY can be negated (e.g. s NOT IN R).

44 of 71

Definition of “all” Clause

F <comp> all r ⇔ ∀ t ∈ r (F <comp> t)

0

5

6

(5 < all

) = false

6

10

4

) = true

5

4

6

(5 ≠ all

) = true (since 5 ≠ 4 and 5 ≠ 6)

(5 < all

) = false

(5 = all

(≠ all) ≡ not in

However, (= all) ≡ in

©Silberschatz, Korth and Sudarshan

3.44

Database System Concepts - 6^th Edition

45 of 71

Example 1: Find the patients with the highest age.

45

SELECT name

FROM Patients

WHERE age >= ALL (select age from Patients);

SELECT name

FROM Patients

WHERE age >= ALL (select age from Patients where age is not null);

Equivalent query:

SELECT name

FROM Patients where age = (SELECT max(age) FROM Patients);

< all, <= all, >= all, = all, <> all also permitted

Will not work if age could be null

modified query to take care of null values

46 of 71

Definition of “some” Clause

F <comp> some r ⇔ ∃ t ∈ r such that (F <comp> t )�Where <comp> can be: <, ≤, >, =, ≠

0

5

6

(5 < some

) = true

0

5

0

) = false

5

0

5

(5 ≠ some

) = true (since 0 ≠ 5)

(read: 5 < some tuple in the relation)

(5 < some

) = true

(5 = some

(= some) ≡ in

However, (≠ some) ≡ not in

3.46

Database System Concepts - 6^th Edition

47 of 71

Example 2

47

Whose age is more than someone diagnosed with Mono?

SELECT name FROM Patients

WHERE age > SOME

(SELECT age FROM Patients, Diagnosis

WHERE Patients.name = Diagnosis.pname AND Diagnosis.disease = 'Mono');

“< some, <= some, >= some, > some =some, <> some” are permitted

48 of 71

Testing Empty Relations

“Exists” checks for non empty set
Find Patients who are older than some recovered patients SELECT name

FROM Patients P1

WHERE exists

(SELECT name

FROM RecoveredPatients P2

WHERE P1.age > P2.age);

48

Patients

RecoveredPatients

Resultset

49 of 71

Testing Empty Relations (cont)

The nested query uses attributes name of E1 defined in outer query. These two queries are called correlated.

Semantics: for each assignment of a value to some term in the subquery that comes from a tuple variable outside, the subquery needs to be executed
Clearly the database can do a much better job!!

Similarly, “NOT EXISTS” can be used.

49

50 of 71

Subqueries producing one value

50

Sometimes subqueries produce a single value

select name

from Patients

where patients.name =

(select pname

from Diagnosis

where disease =”Mono”);

Assume there is only one diagnosis of “Mono” then the subquery returns one value.
If it returns more, it’s a run-time error.
Result on above query: Error Code: 1242. Subquery returns more than 1 row

51 of 71

Subqueries in the From Clause

3.51

Database System Concepts - 6^th Edition

52 of 71

Example Query

Find the average age of diagnosed patients of those diseases where the average age of diagnosed patients is greater than 50.”

select disease, avg(P1.age)�from Diagnosis, Patients P1

where P1.name =Diagnosis.pname� Group by disease � Having avg(P1.age) > 50;

We could write the same query using a nested subquery in the from clause

3.52

Database System Concepts - 6^th Edition

53 of 71

Subqueries in the Form Clause

SQL allows a subquery expression to be used in the from clause
Find the average age of diagnosed patients of those diseases where the average age of diagnosed patients is greater than 50

select A.disease, A.avg_age

from (select disease, avg (age) as avg_age

from Diagnosis D1, Patients P1

where P1.name =D1.pname

Group by disease) as A

where A.avg_age > 50;

Note that we do not need to use the having clause
We need an alias for the subquery

3.53

Database System Concepts - 6^th Edition

54 of 71

With Clause

The with clause provides a way of defining a temporary relation whose definition is available only to the query in which the with clause occurs.

Find all Patients with the maximum age �� with max_age(age) as

(select max(age)

from Patients)

select Patients.name

from Patients, max_age

where Patients.age = max_age.age;

3.54

Database System Concepts - 6^th Edition

55 of 71

Complex Queries using With Clause

Find disease whose average age is greater than the total average age

with

disease_avg (disease, age) as

(select d1.disease, avg(age)

from Patients, Diagnosis d1

where d1.pname = Patients.name

group by disease),

avg_age(age) as

(select avg(age)

from Patients)

select distinct disease_avg.disease

from avg_age, disease_avg

where disease_avg.age> avg_age.age;

3.55

Database System Concepts - 6^th Edition

56 of 71

Subqueries in SELECT Clause

Scalar subquery is one which is used where a single value is expected

List people and number of diseases they have

select distinct pname, (select count(*)

from Diagnosis as D1

where D1.pname =Diagnosis.pname )

from Diagnosis

;; Runtime error if subquery returns more than one result tuple

Alternate query

select pname, count(*) from Diagnosis group by pname

3.56

Database System Concepts - 6^th Edition

57 of 71

57

Joins

Expressed implicitly using SELECT-FROM-WHERE clause.
Alternatively, joins can be expressed using join expressions.
Different vendors might have different implementations.

58 of 71

58

Cross Join

“CROSS JOIN”: Patients(id, name, age), Diagnosis(pname, disease, diagnosis_date)

SELECT * from Diagnosis CROSS JOIN Patients;

Result is a Cartesian product.

“JOIN … ON”:

SELECT Diagnosis.disease, Patients.age

FROM Patients JOIN Diagnosis ON Patients.name = Diagnosis.pname;

After the Cartesian product, “Patients.name = Diagnosis.pname” is applied.

59 of 71

Natural Joins

select * from Outcomes NATURAL JOIN ToDiagnose;

Equivalent to:

SELECT Outcomes.test, Outcomes.pname, Outcomes.result, Outcomes.test_Date,ToDiagnose.disease

FROM Outcomes CROSS JOIN ToDiagnose ON Outcomes.test =ToDiagnose.test;

59

60 of 71

Natural Left/Right Outer Joins

Outcomes o NATURAL LEFT JOIN ToDiagnose td ;

Pad NULL values to dangling tuples of ToDiagnose.

60

Outcomes o NATURAL RIGHT OUTER JOIN ToDiagnose td ;

Pad NULL values to dangling tuples of Outcomes.

61 of 71

Natural Full Outer Joins

SELECT *

FROM ToDiagnose NATURAL FULL OUTER JOIN Outcomes;

Pad NULL values to both relations. /* NOT SUPPORTED in MYSQL */

instead use

SELECT * FROM ToDiagnose NATURAL LEFT OUTER JOIN Outcomes union

SELECT * FROM ToDiagnose NATURAL RIGHT OUTER JOIN Outcomes;

61

62 of 71

Outer Join on different attributes

FULL OUTER JOIN ON <condition>
Useful when two relations have different attribute names
“ON <cond>” must exist
Example: student(sid, dno), dept(dept#, chair)

student FULL OUTER JOIN dept

ON student.dno = dept.dept#; → different attribute names

Similarly, we have:

LEFT OUTER JOIN ON <condition>
RIGHT OUTER JOIN ON <condition>

62

63 of 71

Join Summary

R CROSS JOIN S
R JOIN S ON <condition>
R NATURAL JOIN S
R NATURAL FULL OUTER JOIN S
R NATURAL LEFT OUTER JOIN S
R NATURAL RIGHT OUTER JOIN S
R FULL OUTER JOIN S ON <condition>
R LEFT OUTER JOIN S ON <condition>
R RIGHT OUTER JOIN S ON <condition>

Again: Different vendors might have different implementations.

63

64 of 71

Test your SQL knowledge …which of these are equivalent queries!

(select name from Patients)

except

(select name from RecoveredPatients);

select P.name

from Patients as P LEFT JOIN RecoveredPatients as R on P.name = R.name

where R.name is NULL

select P.name

from Patients as P NATURAL LEFT JOIN RecoveredPatients as R

where R.name is NULL

select P.name

from Patients as P

where P.name not in (select name from RecoveredPatients)

64

65 of 71

Test your SQL knowledge …which of these are equivalent queries!

What would your answer be under the following conditions:

Name is a key in both relations
Name cannot be NULL in both relations
Neither Name or age can be NULL in both relation & Name is the key.

65

66 of 71

Database Modifications

66

Insert Delete Update

67 of 71

Delete

67

DELETE FROM relation [WHERE conditions];

Example:

DELETE

FROM Patients

WHERE name in (select name from RecoveredPatients);

DELETE

FROM Patients; → all tuples will be deleted

68 of 71

Insertion of a tuple

68

INSERT INTO R(A1,…,An) VALUES (v1,…,vn)

Example:

INSERT INTO `Outcomes`(pname,test, test_date, result) VALUES ('Mary','H','2022-09-01', 0);

Can drop attribute names if we provide all of them in order.

INSERT INTO `Outcomes` VALUES ('Mary','H',0,'2022-09-01');

If we don’t provide all attributes, they will be filled with NULL.

INSERT INTO Patients(name)

VALUES (‘Daniel’);

69 of 71

Insertion of a query’s result

INSERT INTO relation (subquery);

INSERT INTO Patients

( SELECT *

FROM RecoveredPatients

WHERE name not in (select name from Patients)

);

.

69

70 of 71

Delete (cont)

70

Delete all patients who have been diagnosed with Strep

DELETE

FROM Patients P1

WHERE EXISTS

(SELECT pname

FROM Diagnosis D1

WHERE P1.name = D1.pname AND D1.disease='Strep');

71 of 71

Update

71

UPDATE relation SET assignments WHERE condition

“Change patients whose age is 35 by 36.”

UPDATE Patients

SET age = 36

WHERE age = 35;

Multiple assignments separated by “,”

UPDATE PATIENTS

SET name = ‘Mary’, age = age +5

WHERE name = ‘Mary’;