▪

▪
▪
▪

▪
▪
▪
▪
▪
▪

▪
▪
▪
▪
▪
▪ DELETE FROM … WHERE …
▪

▪
▪

delete from instructor

where dept_name in (
select dept_name
from department
where building = 'Watson');
▪ DELETE FROM … WHERE …
▪

▪
▪
delete from instructor
where salary < (
select avg (salary)
from instructor);

▪
➔
▪ INSERT INTO 𝑅 VALUES (…) ➔ 𝑅
▪ INSERT INTO 𝑅 ➔ 𝑅
▪

▪
▪

insert into instructor

select id, name, dept_name, 38000
from student
where dept_name='Finance'
and tot_cred > 100;
▪ UPDATE … SET … WHERE …
▪

▪
▪

update instructor
set salary = salary * 1.05
where salary < (select avg (salary)
from instructor);
▪
▪
▪
▪

▪
▪
▪
▪

▪
▪
▪
▪
▪
▪

SELECT ename, sal, deptno

FROM emp E
WHERE sal >
(SELECT AVG(sal)
FROM emp
WHERE deptno = [Link]);

▪
▪
▪

▪
▪
➔
▪
SELECT DISTINCT OrderID
▪
FROM OrderLine_T O ▪
WHERE EXISTS ➔
(SELECT * ➔
FROM Product_T
WHERE ProductID = [Link] ▪
AND ProductFinish=‘Natural Ash’);
➔
➔
▪
▪

▪ ▪
▪ ▪

SELECT [Link], [Link] SELECT [Link], [Link]

FROM dept D1 FROM dept D
EXCEPT WHERE NOT EXISTS
SELECT [Link], [Link] (SELECT *
FROM dept D2, emp E2 FROM emp E
WHERE [Link] = [Link] WHERE [Link] = [Link])
ORDER BY [Link]; ORDER BY [Link];
▪
SELECT R.A, R.B
(SELECT R.A, R.B FROM R
FROM R) WHERE EXISTS(
INTERSECT SELECT *
(SELECT S.A, S.B FROM S
FROM S) WHERE R.A=S.A AND R.B=S.B)

SELECT R.A, R.B

(SELECT R.A, R.B FROM R
FROM R) WHERE NOT EXISTS(
EXCEPT SELECT *
(SELECT S.A, S.B FROM S
FROM S) WHERE R.A=S.A AND R.B=S.B)
▪
▪
▪
▪

▪
▪
▪
▪
➔ →
SELECT DISTINCT [Link]
FROM Company, Product
WHERE [Link] = [Link]
AND [Link] < 100

▪
▪ →
▪
➔
SELECT DISTINCT [Link]
FROM Company
WHERE [Link] NOT IN(
SELECT [Link]
FROM [Link] >= 100)
▪
▪
▪
▪

▪
▪
▪
▪
▪

select id, name, dept_name

from instructor;

➔
▪
▪
▪
▪
CREATE VIEW V AS <query expression>

▪
▪
▪
▪
create view faculty as
select id, name, dept_name
from instructor;

▪
select name
from faculty
where dept_name = ‘Biology’
▪
▪
create view departments_total_salary(dept_name, total_salary) as
select dept_name, sum(salary)
from instructor
group by dept_name

▪
create view physics_fall_2017 as create view physics_fall_2017_watson as
select c.course_id, building, room_number select course_id, room_number
from course c, section s from physics_fall_2017
where c.course_id = s.course_id where building= 'Watson';
and c.dept_name = 'Physics'
and [Link] = 'Fall'
and [Link] = '2017';
▪
▪
➔

create view faculty as

select id, name, dept_name
from instructor;

insert into faculty

values ('30765', 'Green', 'Music');

▪
➔
▪
▪
▪
▪
▪
▪
create view instructor_info as
select ID, name, building
from instructor i, department d
where i.dept_name= d.dept_name;

▪
insert into instructor_info
values ('69987', 'White', 'Taylor');

➔
▪
▪
▪
create view history_instructors as
select *
from instructor
where dept_name= 'History';

▪
insert into history_instructors
values ('25566', 'Brown', 'Biology’, 100000);

▪
▪
▪
▪
▪

▪
▪
▪
▪

▪
▪
▪
▪
▪
▪
▪
▪
▪

▪
▪
▪
▪
CREATE TABLE employees ( ▪
id INT, ▪
first_name VARCHAR (50),
last_name VARCHAR (50),
▪
birth_date DATE not null, ▪
joined_date DATE CHECK (joined_date > birth_date), ▪
salary numeric CHECK (salary > 0),
PRIMARY key(id), ▪
CHECK (birth_date > '1900-01-01'), ▪
UNIQUE (first_name, last_name)
);
▪
▪
▪
▪

foreign key (dept_name) references department (dept_name)

foreign key (dept_name) references department

▪
▪
▪
▪
▪
▪
▪

create table course (

...
dept_name varchar(20),
foreign key (dept_name) references department
on delete cascade
on update cascade,
...
);
▪
▪

▪
▪ create table course (
▪ ...
→ dept_name varchar(20),
foreign key (dept_name)
▪ references department
on delete cascade
▪ on update cascade,
...
);
▪
▪
▪

▪
➔

▪
▪
▪ →