A.
(1,2) of R
B.
(3,4) of R
C.
(2,4,6) of S
D.
All of the above
E.
None of the above
90
A
B
1
2
3
4
5
6
B
C
D
2
4
6
4
6
8
4
7
9
R(A,B) S(B,C,D)
Back to SQL
So far
……
We have seen simple Select From Where examples
We have seen union intersect and except (Minus)
examples
We have nested queries
We have seen aggregate functions being used in
SQL Queries, as well as group by and having clauses
We have seen nulls and how and what we mean by
outer joins
But
…
How do we modify our relations?
What are assertions and triggers?
91
92
Database Manipulation
Insertion redux
Can insert a single tuple using:
INSERT INTO
S
tudent
VALUES
(53688, ‘Smith’, ‘222 W.15
th
ave’, 333-4444, MATH)
or
INSERT INTO
S
tudent (sid, name, address, phone, major)
VALUES
(53688, ‘Smith’, ‘222 W.15
th
ave’, 333-4444, MATH)
Add a tuple to student with null address and phone:
INSERT INTO
S
tudent (sid, name, address, phone, major)
VALUES
(33388, ‘Chan’, null, null, CPSC)
93
Database Manipulation
Insertion redux (cont)
Can add values selected from another table
Enroll student 51135593 into every class taught by
faculty 90873519
INSERT INTO
Enrolled
SELECT
51135593, name
FROM Class
WHERE fid = 90873519
The select-from-where statement is fully evaluated
before any of its results are inserted
So, queries like
INSERT INTO
table1 SELECT * FROM table1
are ok.
94
Database Manipulation
Deletion
Note that only whole tuples are deleted.
Can delete all tuples satisfying some condition
(e.g., name = Smith):
DELETE
FROM Student
WHERE
name = ‘Smith’
Delete all students whose age is above the
average student age:
DELETE FROM Student
WHERE age > (SELECT avg(age)
FROM Student)
95
Database Manipulation
Updates
Increase the age of all students by 2 (should not
be more than 100)
Need to write two
updates:
UPDATE
Student
SET
age = 100
WHERE
age >= 98
UPDATE
Student
SET age = age + 2
WHERE age < 98
Is the order important?
96
Integrity Constraints (Review)
An IC describes conditions that every
legal
instance
of a relation must satisfy.
Inserts/deletes/updates that violate IC’s are
disallowed.
Can ensure application semantics (e.g.,
sid
is a key),
or prevent inconsistencies (e.g.,
sname
has to be a
string,
age
must be < 200)
Types of IC’s
:
domain constraints,
primary key constraints,
foreign key constraints,
general constraints
97
General Constraints: Check
Created with a CHECK clause.
Constraints can be named
Can use subqueries to express
constraint
CREATE TABLE
Student
( snum
INTEGER,
sname
CHAR(32),
major
CHAR(32),
standing
CHAR(2)
age
REAL,
PRIMARY KEY
(snum),
CHECK
( age >= 10
AND
age < 100 );
CREATE TABLE
Enrolled
( snum
INTEGER,
cname
CHAR(32),
PRIMARY KEY
(snum, cname),
CONSTRAINT
noR15
CHECK
(`R15’ <>
(
SELECT
c.room
FROM
class c
WHERE
c.name=cname)));
Check constraints are
checked when tuples are
inserted or modified
98
Constraints over Multiple Relations:
Remember this one?
We couldn’t express
“every employee works in a department and
every department has some employee in it”?
Neither foreign-key nor not-null constraints in
Works_In can do that.
